Posted on 12/28/2020 1:13:35 PM by Admin

Calling Stored procedure in Asp.Net application using Ado.net

A stored procedure is a group of T-SQL (Transact SQL) statements. If an application uses the same query multiple times then we create a store procedure for the same and later they are called by their name in the application.

There are several advantages of using Stored Procedures over adhoc queries (inline SQL) , some of them are as follow:
1. Execution plan retention and reusability - when a SQL query is executed, SQL Server creates an execution plan, an Execution plan can be considered as the best possible way to execute a query so that if the same query needs to be executed again the SQL server could use the same plan, it increases performance. Stored Procedures are compiled and their execution plan is cached and used again when the same SP is executed again. Although ad-hoc queries also create and reuse plans, the plan is reused only when the query is a textual match and the datatypes are matching with the previous call. Any change in the datatype or you have an extra space in the query then, a new plan is created.
2. Code reusability and better maintainability - A stored procedure can be reused with multiple applications. If the logic has to change, we only have one place to change, whereas if it is inline SQL, and if you have to use it in multiple applications, we end up with multiple copies of this inline SQL. If the logic has to change, we have to change at all the places, which makes it harder maintaining inline SQL.
3. Better Security - A database user can be granted access to an SP and prevent them from executing direct "select" statements against a table.
4. Avoids SQL Injection attack - SP's prevent SQL injection attack.
5. Reduces network traffic - we only need to pass the store procedure name from application to SQL server.

Due to all these advantages, it is very possible that you are asked to create SP and call them from your application.

Store procedure are mainly of three types
      1. SP without parameters.
      2. SP with input parameters.
      3. SP with output parameters.

Let's understand each of them one by one. To understand SP , we will need a database table , so let's create one.

Step1.Open SQL Server Management Studio and Run below script, it will create a database with the name Test and Two tables Employee and Department and populate them with some Test Data.


   Create database Test
   go
   use Test
   go
  CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
  );

  CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
  );
  SET IDENTITY_INSERT [dbo].[Department] ON
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
  SET IDENTITY_INSERT [dbo].[Department] OFF
  SET IDENTITY_INSERT [dbo].[Employee] ON
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
  SET IDENTITY_INSERT [dbo].[Employee] OFF

Step 2.create an Empty webform application.

Step 3 : Include connection string in web.config file


  <connectionStrings>
  <add name="CS" connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"/>
  </connectionStrings>

SP without parameters (Display all Employees with their department in a Gridview.)

Employee details
Employee details

Step1. Create the procedure.


   create procedure spGetEmployees
   as
   begin
   select emp.Name,emp.Salary,dep.Name from Employee emp
   join Department dep
  on emp.DepartmentId=dep.Id
  end

Step 2. Drag and drop a GridView into the webform.

Step 3.Copy and paste below code into the code behind.


        private void BindGrid()
        {        
            using (SqlConnection con = new SqlConnection(cs))
            {
               //Create the SqlCommand object
                SqlCommand cmd = new SqlCommand("spGetEmployees",con);
                 //Specify that the SqlCommand is a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;
              //Open the connection ,execute the query and Bind Grid
                con.Open();
                GridView1.DataSource = cmd.ExecuteReader();
                GridView1.DataBind();
            }
        }

Please notice to call a store procedure we simply specify the name of SP in the constructor of SqlCommand object and Then Specify the command type then as usual to execute the command we use ExecuteReader() method.

step 4. Call this method in page load event so that Grid could load automatically.


      protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();        
             }
        }

SP with input parameters (Add employee details into the database.)

Employee Entry Screen
Employee Entry Screen

Step 1.create the store procedure.


  create procedure spAddEmployee
  @Name varchar(50),
  @Salary int,
  @DepId int
  as
  begin
  insert into Employee values (@Name,@Salary,@DepId)
  end

step 2. Design a form to take user input. copy paste below html.


   <table border="1" style="background: brown; color: White">
            <tr>
                <td>Name</td>
                <td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Salary</td>
                <td><asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Department</td>
                <td>
                    <asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id"></asp:DropDownList>
                </td>
            </tr>
           <tr>
               <td colspan="2">
                     <asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click"/>
               </td>
           </tr>
        </table>

step 3.Bind department so that Admin can select the department from dropdownlist.


     protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
                BindDepartment();
            }
        }
      private void BindDepartment()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Department", con);
                da.Fill(ds);
            }
            ListItem li = new ListItem() { Text = "Please Select", Value = "-1" };
            ddlDepartment.Items.Insert(0, li);
            ddlDepartment.DataSource = ds;
            ddlDepartment.DataBind();
        }

Step 3.Double click on the button control to generate button click event handler and copy paste below code.


     protected void Button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                //Create the SqlCommand object
                SqlCommand cmd = new SqlCommand("spAddEmployee", con);
                 //Specify that the SqlCommand is a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;
                              //Add the input parameters to the command object
                cmd.Parameters.AddWithValue("@Name",txtName.Text);
                cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
                cmd.Parameters.AddWithValue("@DepId", ddlDepartment.SelectedItem.Value);
              //Open the connection and execute the query

                con.Open();
                cmd.ExecuteNonQuery();
                BindGrid();
            }
        }

Please notice , here we are specifying SP name in the constructor of SqlCommand object and then specifying the command type as store procedure and then providing necessary input parameters using AddWithValue() method .

SP with output parameters (display total count of employee in HR department in a label)

Employee as HR
total employee in HR department

Step 1.Create SP

To create an SP with output parameter, we use the keywords OUT or OUTPUT. @empCount is an OUTPUT parameter. Notice, it is specified with OUT keyword.


  create procedure spGetHrEmployeeCount
  @empCount int out
  as
  begin
  select @empCount =Count(*) from Employee
  where DepartmentId= (Select Id from Department where Name='HR')
  end

Step 2. Call sp from code behind and set label's Text as output Parameter's value.


    private void GetHrCount()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spGetHrEmployeeCount", con);
                cmd.CommandType = CommandType.StoredProcedure;
                //Add the output parameter to the command object
                SqlParameter outPutParameter = new SqlParameter();
                outPutParameter.ParameterName = "@EmployeeId";
                outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
                outPutParameter.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(outPutParameter);
                //Open the connection and execute the query
                con.Open();
                cmd.ExecuteNonQuery();
                //Retrieve the value of the output parameter and set the label Text
                string HrCount = outPutParameter.Value.ToString();
                lblMessage.Text = "Total Employee in HR department = " + HrCount;
            }       

The complete source code looks like below.

code behind (aspx.cs)

   public partial class StoreProcedureExample : System.Web.UI.Page
    {
        string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
                BindDepartment();
                GetHrCount();
            }
        }
        private void BindGrid()
        {          
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployees",con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                GridView1.DataSource = cmd.ExecuteReader();
                GridView1.DataBind();
            }
        }

        private void BindDepartment()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Department", con);
                da.Fill(ds);
            }
            ListItem li = new ListItem() { Text = "Please Select", Value = "-1" };
            ddlDepartment.Items.Insert(0, li);
            ddlDepartment.DataSource = ds;
            ddlDepartment.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spAddEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name",txtName.Text);
                cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
                cmd.Parameters.AddWithValue("@DepId", ddlDepartment.SelectedItem.Value);

                con.Open();
                cmd.ExecuteNonQuery();
                BindGrid();
            }
        }

        private void GetHrCount()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spGetHrEmployeeCount", con);
                cmd.CommandType = CommandType.StoredProcedure;
                //Add the output parameter to the command object
                SqlParameter outPutParameter = new SqlParameter();
                outPutParameter.ParameterName = "@empCount";
                outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
                outPutParameter.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(outPutParameter);
                //Open the connection and execute the query
                con.Open();
                cmd.ExecuteNonQuery();
                //Retrieve the value of the output parameter
                string HrCount = outPutParameter.Value.ToString();
                lblMessage.Text = "Total Employee in HR department = " + HrCount;
            }      
        }
     }

The html (aspx page)

    <form id="form1" runat="server">
    <div>       
        <table border="1" style="background: brown; color: White">
            <tr>
                <td>Name</td>
                <td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Salary</td>
                <td><asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Department</td>
                <td>
                    <asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id"></asp:DropDownList>
                </td>
            </tr>
           <tr>
               <td colspan="2">
                     <asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click"/>
               </td>
           </tr>
        </table>
        <asp:Label Text="text" runat="server" ID="lblMessage" />
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" 
        GridLines="Vertical">
            <AlternatingRowStyle BackColor="#DCDCDC" />
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#0000A9" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#000065" />
        </asp:GridView>
     </div>
    </form>

all results
the complete view