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.