Calling Select SQL Server Stored Procedures using ADO.Net

Here, I’ll go over how to use ADO.Net to invoke stored procedures that return data. Additionally, I’ll discuss how to link the outcomes to data-driven controls like GridView or DataGrid.

Connection String

<connectionStrings>

      <add name="conString"

        connectionString="Data Source=.\SQLEXPRESS;

                          database=Northwind;Integrated Security=true"/>

</connectionStrings>

Namespaces

You must import the subsequent namespaces.

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

Select Stored Procedures

This straightforward Select stored procedure pulls all the data 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 use ADO.Net in my ASP.Net website to run the stored method and connect the outcomes to a GridView. I’ll be utilising the SQL Command object’s ExecuteReader method because I need to fetch numerous rows.

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();

}

Select Stored Procedures with Parameters

I’ll get filtered results here based on the employee ID. I am sending the Employee ID as a parameter to the stored procedure below, and the records will be obtained based on the ID.

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
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();

}

 

Now, in order to use ADO.Net to invoke the aforementioned stored procedure in an ASP.NET application, we will need to complete these steps. Everything is still the same, but I’m supplying @EmployeeID as a parameter here. Additionally, txtID, a TextBox where the user inputs the EmployeeID of the Employee whose data he wishes to examine, is shown.

 

Submit a Comment

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

Subscribe

Select Categories