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



  Modified On Nov-30-2017 12:50:44 AM

Leave Comment