Home > DeveloperSection > Articles > Using Stored Procedure in ASP.Net

Using Stored Procedure in ASP.Net


ASP.Net ASP.Net 
Ratings:
1 Comment(s)
 5196  View(s)
Rate this:

Using Stored Procedure in ASP.Net

This article demonstrates how to use ASP.NET and ADO.NET with Visual C# .NET to create and to call a Microsoft SQL Server stored procedure.

Stored Procedure:

A stored procedure is a batch of Transact-SQL statements (like select, insert and update) compiled into a single execution that can be re-used. If you find yourself using the same query over and over again, it would make sense to put them into a stored procedure. Every time you write a query it is parsed in the database. If you have written a stored procedure for it, it will be compiled once and can be executed multiple times.

There are two steps involved in executing a stored procedure from your program. First, you set the command object property CommandText as the stored procedure name; second, you set the CommandType property as CommandType.StoredProcedure.

Step 1:

Now, we create a table in a database named logintab and it has the three columns Login, Password and Id.
the Database table looks like the Figure 1 shown below.

Using Stored Procedure in ASP.Net

 (Figure 1)

Step 2:

Now we create a stored procedure in the database named storlog. The stored procedure looks like Figure 2.

 

Using Stored Procedure in ASP.Net

(Figure 2)

Step 3: Calling stored procedure

Taking three TextBoxes, one Button and a Label control on the form, the form looks like this.
Using Stored Procedure in ASP.Net
 (Figure 3)

Now double-click on the Save button control and add the following code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

 

namespace StoreProcedure

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void ButtonSave_Click(object sender, EventArgs e)

        {

            string str = "Data Source=.;uid=sa;pwd=Password$2;database=master";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            SqlCommand com = new SqlCommand("storlog", con);

            com.Parameters.Add("@username", SqlDbType.VarChar).Value = TextBoxUsername .Text;

            com.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBoxPassword.Text;

            com.Parameters.Add("@Id", SqlDbType.Int).Value = TextBoxId.Text ;

            com.CommandType = CommandType.StoredProcedure;

            com.ExecuteNonQuery();

            Label1 .Text= "record has been saved";

            con.Close();

        }

    }

}

 

The above code defines the connection with the database and command object property CommandText as the stored procedure name; second, you set the CommandType property as CommandType.StoredProcedure.

Step 4:

Now run the application and enter the username, password and Id in the textboxes.

The form looks like this.

Using Stored Procedure in ASP.Net
(Figure 4)

Step 5:

Now click on the save Button. All record has been saved in the database.

Using Stored Procedure in ASP.Net
(Figure 5)

Step 6:

Now open the database and test it.

 

Using Stored Procedure in ASP.Net




Using Stored Procedure in ASP.Net

By John Smith on   5 years ago
nice article

Don't want to miss updates? Please click the below button!

Follow MindStick