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
SelectStoredProcs.zip (6.72 kb)
