ADO.NET DataSet Using Stored Procedure in C# with Examples
Posted by Superadmin on November 15 2023 11:06:38

ADO.NET DataSet Using Stored Procedure in C# with Examples

In this article, I am going to discuss how to call a stored procedure and store the result in a DataSet using C# ADO.NET. Please read our previous article where we discussed how to call stored procedures using SqlCommand Object and using SqlDataReader.

 

Using ADO.NET DataSet with Stored Procedure in C#:

Suppose, you are calling a stored procedure that returns a result set, then you can store that result set in a DataSet. Let us understand this with an example. We are going to use the following Employee table to understand this concept.

 

ADO.NET DataSet Using Stored Procedure in C# with Examples

Please use the below SQL Script to Create and Populate the database EmployeeDB and table Employee with the required sample data.

     

CREATE DATABASE EmployeeDB;
 GO 
 
 USE EmployeeDB;
 GO 
 
 CREATE TABLE Employee(
Id INT IDENTITY(1000,1) PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(50),
Mobile VARCHAR(50),
Age INT,
Department VARCHAR(50)
)
 GO 
 
 INSERT INTO Employee VALUES ('Anurag','Anurag@dotnettutorial.net','1234567890', 25, 'IT')
 INSERT INTO Employee VALUES ('Priyanka','Priyanka@dotnettutorial.net','2233445566', 35, 'IT')
 INSERT INTO Employee VALUES ('Preety','Preety@dotnettutorial.net','6655443322', 35, 'IT')
 INSERT INTO Employee VALUES ('Sambit','Sambit@dotnettutorial.net','9876543210', 25, 'IT')
 INSERT INTO Employee VALUES ('Pranaya','Pranaya@dotnettutorial.net','1234567890', 25, 'HR')
 INSERT INTO Employee VALUES ('Pratik','Pratik@dotnettutorial.net','2233445566', 35, 'HR')
 INSERT INTO Employee VALUES ('Santosh','Santosh@dotnettutorial.net','6655443322', 32, 'HR')
 INSERT INTO Employee VALUES ('Rakesh','Rakesh@dotnettutorial.net','9876543210', 27, 'HR')
 GO
Create SQL Server Stored Procedure with Input Parameters:

Now, we will see how to call a stored procedure with input parameters using ADO.NET DataSet. Let us first create the SQL Server Stored Procedure inside the EmployeeDB database. So, please use the following SQL Script to create the stored procedure. The following stored procedure will return the list of employees based on Age and Department conditions. Here, Age and Dept are the input parameters, and those parameter values we need to pass while calling this stored procedure from our C#.NET Application.

 

CREATE PROCEDURE spGetEmployeesByAgeDept
(
@Age INT,
@Dept VARCHAR(50)
)
 AS 
 BEGIN 
SELECT Id, Name, Email, Mobile, Age, Department
FROM Employee
WHERE Age = @Age AND Department = @Dept
 END
Example to Understand How to Call a Stored Procedure with Input Parameter in C# using ADO.NET DataSet:

The following example code is self-explained, so please go through the comment lines. In the below example, I am showing how to call SQL Server Stored Procedure with input parameters that return a result set and how we can store that result set in a DataSet or DataTable using ADO.NET and C#.

   

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace ADOUsingStoredProcedure
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
//Store the connection string in the ConnectionString variable 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
 
//Creating the connection object using the ConnectionString 
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Creating DataSet Object 
DataSet ds = new DataSet();
 
//Creating SQL Command Object  
SqlCommand sqlCmd = new SqlCommand
{ 
CommandText = "spGetEmployeesByAgeDept", //Specifying the Stored Procedure Name 
CommandType = CommandType.StoredProcedure, //We are going to Execute the command is a Stored Procedure 
Connection = connection //Specifying the connection object whhere the Stored Procedure is going to be execute 
};
 
//Create the Input Parameter for the Stored Procedure 
SqlParameter paramAge = new SqlParameter
{ 
ParameterName = "@Age", //Parameter name defined in stored procedure 
SqlDbType = SqlDbType.Int, //Data Type of Parameter 
Value = 25, //Set the value
Direction = ParameterDirection.Input //Specify the parameter as input
};
//Add the parameter to the SqlCommand object
sqlCmd.Parameters.Add(paramAge);
//You can also directly add the parameter to the command object using AddWithValue method
sqlCmd.Parameters.AddWithValue("@Dept", "IT");
//Create SqlDataAdapter object
SqlDataAdapter da = new SqlDataAdapter
{
//Specify the Select Command as the command object we created
SelectCommand = sqlCmd
};
//Call the Fill Method to fill the dataset
da.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
//Accessing the Data using the string column name as key
Console.WriteLine(row["Id"] + ", " + row["Name"] + ", " + row["Email"] + ", " + row["Mobile"] + ", " + row["Age"] + ", " + row["Age"]);
//Accessing the Data using the integer index position as key
//Console.WriteLine(row[0] + ", " + row[1] + ", " + row[2] + ", " + row[3] + ", " + row[4] + ", " + row[5]);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

Example to Understand How to Call a Stored Procedure with Input Parameter in C# using ADO.NET DataSet

Another Approach of Calling Stored Procedure and Storing the Result in a DataSet:

The following example code is self-explained, so please go through the comment lines. In the below example also, I am showing how to call the Stored Procedure which returns a result set and how we can store that result set in a DataSet using ADO.NET and C#.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ADOUsingStoredProcedure
{
class Program
{
static void Main(string[] args)
{
try
{
//Store the connection string in the ConnectionString variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
//Creating the connection object using the ConnectionString
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Creating DataSet Object
DataSet ds = new DataSet();
//Creating SQL Command Object
SqlCommand sqlCmd = connection.CreateCommand();
//Specifying the Stored Procedure Name
sqlCmd.CommandText = "spGetEmployeesByAgeDept";
//We are going to Execute the command is a Stored Procedure
sqlCmd.CommandType = CommandType.StoredProcedure;
//sqlCmd.Connection = connection; //No need to specify the connection object as the command object is created based on the connection object
//Add the parameter to the SqlCommand object directly using the AddWithValue method
sqlCmd.Parameters.AddWithValue("@Age", 25);
sqlCmd.Parameters.AddWithValue("@Dept", "IT");
//Create SqlDataAdapter object by passing the command object as a parameter to the constructor
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
//Call the Fill Method to fill the dataset
da.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
//Accessing the Data using the string column name as key
Console.WriteLine(row["Id"] + ", " + row["Name"] + ", " + row["Email"] + ", " + row["Mobile"] + ", " + row["Age"] + ", " + row["Age"]);
//Accessing the Data using the integer index position as key
//Console.WriteLine(row[0] + ", " + row[1] + ", " + row[2] + ", " + row[3] + ", " + row[4] + ", " + row[5]);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

Calling Stored Procedure and Storing the Result in a DataSet

Generic Method that returns DataSet from Stored Procedure in C#:

Suppose you are writing a Helper class in your application that uses ADO.NET to interact with the database. And in your application, you are going to call Stored Procedures from many different places. Writing a generic Helper method that takes in an Array of SqlParameters can be used so that you do not have to re-write the same code of calling the Stored Procedure again and again which will save a lot of time in application development.

We are having a stored procedure called spGetEmployeesByAgeDept which returns the list of employees by Age and Department. Let us create another stored procedure that is going to return all the Employees. Please execute the below SQL Statement.

CREATE PROCEDURE spGetEmployees
AS
BEGIN
SELECT Id, Name, Email, Mobile, Age, Department
FROM Employee
END

Now, let us see the process of creating a generic method that will execute the stored procedure with and without parameters and returns a DataSet. For a better understanding, please have a look at the following example. The following example code is self-explained, so please go through the comment lines.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ADOUsingStoredProcedure
{
class Program
{
static void Main(string[] args)
{
try
{
//Store the connection string in the ConnectionString variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
//Executing the spGetEmployeesByAgeDept Stored Procedure
//Create SqlParameter Required by spGetEmployeesByAgeDept Stored Procedure
SqlParameter[] paramterList = new SqlParameter[]
{
new SqlParameter("@Age", 25),
new SqlParameter("@Dept", "IT")
};
//Call the Generic Method to Execute the Stored Procedure which returns a DataSet
DataSet dataSet1 = ExecuteStoredProcedureReturnDataSet(ConnectionString, "spGetEmployeesByAgeDept", paramterList);
Console.WriteLine("spGetEmployeesByAgeDept Result:");
foreach (DataRow row in dataSet1.Tables[0].Rows)
{
//Accessing the Data using the string column name as key
Console.WriteLine(row["Id"] + ", " + row["Name"] + ", " + row["Email"] + ", " + row["Mobile"] + ", " + row["Age"] + ", " + row["Age"]);
}
//Executing the spGetEmployees Stored Procedure
DataSet dataSet2 = ExecuteStoredProcedureReturnDataSet(ConnectionString, "spGetEmployees");
Console.WriteLine("\nspGetEmployees Result:");
foreach (DataRow row in dataSet2.Tables[0].Rows)
{
//Accessing the Data using the string column name as key
Console.WriteLine(row["Id"] + ", " + row["Name"] + ", " + row["Email"] + ", " + row["Mobile"] + ", " + row["Age"] + ", " + row["Age"]);
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
public static DataSet ExecuteStoredProcedureReturnDataSet(string connectionString, string procedureName, params SqlParameter[] paramterList)
{
//Create DataSet Object
DataSet dataSet = new DataSet();
//Create the connection object using the connectionString parameter which it received as input parameter
using (var sqlConnection = new SqlConnection(connectionString))
{
//Create the command object
using (var command = sqlConnection.CreateCommand())
{
//Create the SqlDataAdapter object by passing command object as a parameter to the constructor
using (SqlDataAdapter sda = new SqlDataAdapter(command))
{
//Set the command type as StoredProcedure
command.CommandType = CommandType.StoredProcedure;
//Set the command text as the procedure name which you received as input parameter
command.CommandText = procedureName;
//If Parameter list is not null, add the parameterlist into Parameters collection of the command object
if (paramterList != null)
{
command.Parameters.AddRange(paramterList);
}
//Fill the Dataset
sda.Fill(dataSet);
}
}
}
//Return the DataSet
return dataSet;
}
}
}
Output:

In the next article, I am going to discuss the ADO.NET DataView Class in C# with Examples. Here, in this article, I try to explain ADO.NET DataSet Using a Stored Procedure with Examples. I hope you enjoy this ADO.NET DataSet Using Stored Procedure in C# with Examples article.