Use Stored Procedure in ASP.NET with example

  ASP.NET Snippets

Use Stored Procedure in ASP.NET with example

Step 1: Create a Stored Procedure in MS-SQL Server

CREATE PROCEDURE usp_GetAllRecords         
AS
BEGIN
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SETNOCOUNTON;
   SELECT*FROM Employee
END
GO

Step 2: Create ASP.NET Web Page with following codes.

 <%@ 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/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Use Stored Procedure in ASP.NET</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table cellpadding="2" cellspacing="2" width="50%" align="center" border="1">
            <tr>
                <td>
                    <asp:GridView ID="GridView1" runat="server" CellPadding="4" OnPageIndexChanging="GridView1_PageIndexChanging"
                        AllowPaging="True" PageSize="10" AutoGenerateColumns="False" BackColor="White"
                        BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px">
                        <RowStyle BackColor="White" ForeColor="#330099" />
                        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
                        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                        <Columns>
                            <asp:BoundField DataField="Emp_ID" HeaderText="Employee ID" SortExpression="Emp_ID" />
                            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
                            <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
                            <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                            <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                            <asp:BoundField DataField="JoinDate" HeaderText="Join Date" SortExpression="JoinDate" />
                        </Columns>
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Step 3: Write the following codes in code behind of the page created in step 2.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
Using System.IO;
using System.Text;
using System.Collections.Generic;
 
public partial class _Default : System.Web.UI.Page
{
    SqlDataAdapter da;
    DataSet ds = new DataSet();
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {           
            BindGrid();
        }
    }
 
    public void BindGrid()
    {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
 
            SqlCommand cmd = new SqlCommand("usp_GetAllRecords", con);
            cmd.CommandType = CommandType.StoredProcedure;
 
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
 
            if (!object.Equals(ds.Tables[0], null))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {                                                      
                    GridView1.DataSource =ds.Tables[0];
                    GridView1.DataBind();
                }
                else
                {
                    GridView1.DataSource = null;
                    GridView1.DataBind();
                }
            }
            else
            {
                GridView1.DataSource = null;
                GridView1.DataBind();
            }
        }
        catch (SqlException ex)
        {
        }
    }
 
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindGrid();
    }
 
}