Introduction

Stored procedures (sprocs) in SQL Server are generally an ordered series of Transact-SQL statements bundled into a single logical unit. They allow for variables and parameters, as well as selection and looping constructs. A key point is that sprocs are stored in the database rather than in a separate file. In this article, we will learn how to use stored procedures in an ASP.NET application.

Advantages of stored procedures over inline SQL queries include,

  1. Referred to using short names rather than a long string of text; therefore, less network traffic is required to run the code within the sproc.
  2. Pre-optimized and precompiled, so they save an incremental amount of time with each sproc call/execution.
  3. Encapsulate a process for added security or to simply hide the complexity of the database.
  4. Can be called from other sprocs, making them reusable and reducing code size.
  5. Stored procedures are executed on the database server and can be better for performance.
  6. Stored procedures are also secure and do not expose database details in application.

Parameterization

A stored procedure gives us some procedural capability, and also gives us a performance boost by using mainly two types of parameters,

From outside the sproc, parameters can be passed in either by position or reference.

Declaring Parameters in SQL Server Stored Procedures

To declare parameters in a SQL Server stored procedure, you will need,

The syntax is,

@parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]

Let's now create a stored procedure named "Submitrecord".

First open Microsoft SQL Server -> Enterprise Manager, then navigate to the database in which you want to create the stored procedure and select New Stored Procedure.

Using Stored Procedures with ASP.NET
See the below Stored Procedure Properties for what to enter, then click OK.
Using Stored Procedures with ASP.NET
Now create an application named Store Procedure in .net to use the above sprocs.

Stored Procedure.aspx page code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1transitional.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">    
   <head runat="server">  
        <title>Store Procedure</title>  
   </head>   
   <body>  
        <form id="form1" runat="server">  
            <div>  
                <asp:Label ID="Label1" runat="server" Text="ID"></asp:Label>  
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /><br />  
                <asp:Label ID="Label2" runat="server" Text="Password"></asp:Label>  
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /><br />  
                <asp:Label ID="Label3" runat="server" Text="Confirm Password"></asp:Label>  
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /><br />  
                <asp:Label ID="Label4" runat="server" Text="Email ID"></asp:Label>  
                <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /><br /><br />  
                <asp:Button ID="Button1" runat="server" Text="Submit Record" OnClick="Button1_Click" /> </div>  
        </form>  
    </body>  
</html>
Stored Procedure.aspx.cs page code
using System;  
using System.Data;  
using System.Configuration;  
using System.Web;  
using System.Web.Security;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Web.UI.WebControls.WebParts;  
using System.Web.UI.HtmlControls;  
using System.Data.SqlClient;  
public partial class _Default: System.Web.UI.Page {  
    DataSet ds = new DataSet();  
    SqlConnection con;  
    //Here we declare the parameter which we have to use in our application  
    SqlCommand cmd = new SqlCommand();  
    SqlParameter sp1 = new SqlParameter();  
    SqlParameter sp2 = new SqlParameter();  
    SqlParameter sp3 = new SqlParameter();  
    SqlParameter sp4 = new SqlParameter();  
    protected void Page_Load(object sender, EventArgs e) {}  
    protected void Button1_Click(object sender, EventArgs e) {  
        con = new SqlConnection("server=(local); database= gaurav;uid=sa;pwd=");  
        cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = TextBox1.Text;  
        cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;  
        cmd.Parameters.Add("@ConfirmPassword", SqlDbType.VarChar).Value = TextBox3.Text;  
        cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox4.Text;  
        cmd = new SqlCommand("submitrecord", con);  
        cmd.CommandType = CommandType.StoredProcedure;  
        con.Open();  
        cmd.ExecuteNonQuery();  
        con.Close();  
    }  
}  

When we run the application, the window will look like this,

Using Stored Procedures with ASP.NET

After clicking the submit button the data is appended to the database as seen below in the SQL Server table record:

Using Stored Procedures with ASP.NET

Summary

In this article, we learned how to execute a SQL Server stored procedure from an ASP.NET Web application.

Check out SQL and SQL Server categories of C# Corner to learn about these technologies and get code samples.