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.
Please use the below SQL Script to Create and Populate the database EmployeeDB and table Employee with the required sample data.
CREATE DATABASE EmployeeDB;
Id INT IDENTITY(1000,1) PRIMARY KEY,
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')
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
SELECT Id, Name, Email, Mobile, Age, Department
WHERE Age = @Age AND Department = @Dept
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.Data.SqlClient;
namespace ADOUsingStoredProcedure
static void Main(string[] args)
//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
//Call the Fill Method to fill the dataset
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]);
Console.WriteLine($"Exception Occurred: {ex.Message}");
Output:
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.Data.SqlClient;
namespace ADOUsingStoredProcedure
static void Main(string[] args)
//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
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]);
Console.WriteLine($"Exception Occurred: {ex.Message}");
Output:
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
SELECT Id, Name, Email, Mobile, Age, Department
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.Data.SqlClient;
namespace ADOUsingStoredProcedure
static void Main(string[] args)
//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"]);
Console.WriteLine($"Exception Occurred: {ex.Message}");
public static DataSet ExecuteStoredProcedureReturnDataSet(string connectionString, string procedureName, params SqlParameter[] paramterList)
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);
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.
