Bind data to ASP.Net GridView using Stored Procedure

Bind data to ASP.Net GridView using Stored Procedure

Here I am explaining how to create and call stored procedures in .net using ADO.Net classes.

In my previous article I explained Using Stored Procedures in SQL Server Database

Here I am explaining how to call stored procedures that return data using ADO.Net. I’ll also explain how to bind the results to databound controls like GridView or DataGrid.

For this tutorial I am using the Employees Table of NorthWind Database. You can download the NorthWind Database from here

Connection String

<connectionStrings>

      <add name="conString"

        connectionString="Data Source=.\SQLEXPRESS;

                          database=Northwind;Integrated Security=true"/>

connectionStrings>

Namespaces

You will need to import the following namespaces

C#

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

 

Select Stored Procedures

Here is a simple Select Stored Procedure that brings all the records from the Employees table

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetAllEmployeeDetails]

AS

BEGIN

      SET NOCOUNT ON;

      SELECT FirstName, LastName, BirthDate, City, Country

      FROM Employees

END

Now I’ll call the stored procedure using ADO.Net in my ASP.Net website and bind the results to a GridView. Since I need to fetch multiple rows I’ll be using ExecuteReader method of SQL Command object

C#

String strConnString =  ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

SqlConnection con =  new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "GetAllEmployeeDetails";

cmd.Connection = con;

try

{

    con.Open();

    GridView1.EmptyDataText = "No Records Found";

    GridView1.DataSource = cmd.ExecuteReader() ;

    GridView1.DataBind(); 

}

catch (Exception ex)

{

    throw ex;

}

finally

{

    con.Close();

    con.Dispose();

}

VB.Net

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "GetAllEmployeeDetails"

cmd.Connection = con

Try

    con.Open()

    GridView1.EmptyDataText = "No Records Found"

    GridView1.DataSource = cmd.ExecuteReader()

    GridView1.DataBind()

Catch ex As Exception

    Throw ex

Finally

    con.Close()

    con.Dispose()

End Try

Select Stored Procedures with Parameters

Here I will get filtered records based on Employee ID. In the stored procedure below I am passing Employee ID as parameter and based on the ID the records will be fetched.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetEmployeeDetailsByID]

      @EmployeeID int = 0

AS

BEGIN

      SET NOCOUNT ON;

      SELECT FirstName, LastName, BirthDate, City, Country

      FROM Employees WHERE EmployeeID=@EmployeeID

END

Now in order to call the above stored procedure in an asp.net application using ADO.Net we will have to do it the following way. You will notice all remains the same except here I am passing @EmployeeID as parameter. Also you will notice txtID which is a TextBox in which user enters the EmployeeID of the Employee whose details he wants to view

C#

String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "GetEmployeeDetailsByID";

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtID.Text.Trim();        

cmd.Connection = con;

try

{

    con.Open();

    GridView1.EmptyDataText = "No Records Found";

    GridView1.DataSource = cmd.ExecuteReader();

    GridView1.DataBind();

}

catch (Exception ex)

{

    throw ex;

}

finally

{

    con.Close();

    con.Dispose();

}

VB.Net

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "GetEmployeeDetailsByID"

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtID.Text.Trim()

cmd.Connection = con

Try

     con.Open()

     GridView1.EmptyDataText = "No Records Found"

     GridView1.DataSource = cmd.ExecuteReader()

     GridView1.DataBind()

Catch ex As Exception

     Throw ex

Finally

     con.Close()

     con.Dispose()

End Try

This completes the article in my next article I’ll be explaining how to call insert stored procedures in asp.net using ADO.Net. You can download the code in VB.Net and C# along with the SQL Scripts using the link below

Download Sample