Pages

Saturday 26 January 2013

Select SQL Server Stored Procedures using ASP.Net Example

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

No comments:

Post a Comment