Bind data to ASP.Net GridView using Stored Procedure

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

 

sumber : snipet

Konfigurasi Dasar CodeIgniter

Selamat malam kawan semua, bagaimana kabarnya? semoga baik baik saja yah. Malam ini kita akan ...

Learn more
Share This:Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *