Users Online

· Guests Online: 133

· Members Online: 0

· Total Members: 188
· Newest Member: meenachowdary055

Forum Threads

Newest Threads
No Threads created
Hottest Threads
No Threads created

Latest Articles

· Do while loop
· The for loop in C
· C while loop
· Pointers in C
· Functions in C

Articles Hierarchy

Articles Home » DOTNET » ADO.NET Using Stored Procedures in C#

ADO.NET Using Stored Procedures in C#

ADO.NET Using Stored Procedures in C#

In this article, I am going to discuss ADO.NET Using Stored Procedures in C# with Examples. Please read our previous article, where we discussed ADO.NET DataSet using SQL Server in C# with Examples. At the end of this article, you will understand how to call a stored procedure without a parameter as well as how to call a Stored Procedure using both Inputs as well as Outputs parameters. Before understanding how to call a stored procedure, let us first understand What is a Stored Procedure.

 

What is a Stored Procedure in SQL?

A Stored Procedure in SQL is a database object which contains Pre-Compiled SQL Statements. In simple words, we can also say that the Stored Procedure is a block of code that is designed to perform a specific task whenever it is called. Please Click Here to learn SQL Server Stored Procedure in detail.

 

Example to understand ADO.NET using Stored Procedure in C#:

Let us understand how to use the Stored Procedure in C# with one example. We are going to use the following Student table in this demo to understand the concept of ADO.NET using Stored Procedure.

  

Example to understand ADO.NET using Stored Procedure

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

 

CREATE DATABASE StudentDB;
 GO 
 
 USE StudentDB;
 GO 
 
 CREATE TABLE Student(
[Id] [int] IDENTITY(100,1) PRIMARY KEY,
[Name] [varchar](100) NULL,
[Email] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
)
 GO 
 
 INSERT INTO Student VALUES ('Anurag','Anurag@dotnettutorial.net','1234567890')
 INSERT INTO Student VALUES ('Priyanka','Priyanka@dotnettutorial.net','2233445566')
 INSERT INTO Student VALUES ('Preety','Preety@dotnettutorial.net','6655443322')
 INSERT INTO Student VALUES ('Sambit','Sambit@dotnettutorial.net','9876543210')
SQL Server Stored Procedure without Parameters:

So, the first thing is always first. Let us create a stored procedure that will not take any input parameter but will return all the records from the Student table. Please use the below SQL Script to create the stored procedure in the SQL server StudentDB database.

  

CREATE PROCEDURE spGetStudents
 AS 
 BEGIN 
SELECT Id, Name, Email, Mobile
FROM Student
 END
How to Call a Stored Procedure using C# ADO.NET.

Let us see the step-by-step procedure to call the above-stored procedure. Please have a look at the below image. What we need to do is, first we need to create an instance of the SqlCommand object and then specify the CommandTest property value as the Stored Procedure name, and here you need to specify the CommandType property as Stored Procedure. By default, the command type is going to be Text which is nothing but T-SQL. And when we want to execute we need to specify the command type as Stored Procedure.

How to call a stored procedure using C# ADO.NET

 

You can also use the other overloaded constructor of the SqlCommand class as shown in the below image which takes the stored procedure and the connection object as a parameter. As you can see, here we are passing the stored procedure name and the connection object to the constructor of the command object and then specify the command type as a Stored procedure.

 

How to call a stored procedure without parameter using C# ADO.NET.

Example to understand how to Call a Stored Procedure in C# without Parameters:

The following example code is self-explained, so please go through the comment lines. In the below example, I am showing how to call a stored procedure without parameters 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=StudentDB; integrated security=SSPI";
 
//Create the SqlConnection object 
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Create the SqlCommand object by passing the stored procedure name and connection object as parameters 
SqlCommand cmd = new SqlCommand("spGetStudents", connection) 
{ 
//Specify the command type as Stored Procedure 
CommandType = CommandType.StoredProcedure 
};
 
//Open the Connection 
connection.Open();
 
//Execute the command i.e. Executing the Stored Procedure using ExecuteReader method
//SqlDataReader requires an active and open connection
SqlDataReader sdr = cmd.ExecuteReader();
//Read the data from the SqlDataReader
//Read() method will returns true as long as data is there in the SqlDataReader
while (sdr.Read())
{
//Accessing the data using the string key as index
Console.WriteLine(sdr["Id"] + ", " + sdr["Name"] + ", " + sdr["Email"] + ", " + sdr["Mobile"]);
//Accessing the data using the integer index position as key
//Console.WriteLine(sdr[0] + ", " + sdr[1] + ", " + sdr[2] + ", " + sdr[3]);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

ADO.NET Using Stored Procedure

Note: The input Parameter is used to bring a value into the stored procedure before execution and the output parameter is used to bring out a value after the stored procedure execution.

SQL Server Stored Procedure with Input Parameters:

Now, we will see how to call a stored procedure with an input parameter using ADO.NET and C#. Let us first create the Stored Procedure inside the StudentrDB database. So, please use the below SQL Script to create the stored procedure which will return the student details by id. Here, student id is the input parameter, and that parameter value we need to pass while calling this stored procedure from our C#.NET Application using ADO.NET.

 

CREATE PROCEDURE spGetStudentById
(
@Id INT
)
AS
BEGIN
SELECT Id, Name, Email, Mobile
FROM Student
WHERE Id = @Id
END
How to Call a Stored Procedure with Input Parameter using ADO.NET and C#?

In order to understand how to call a stored procedure with an input parameter using C# and ADO.NET, please have a look at the following image. We already discussed the SqlCommand object. The point that you need to focus on here is the SqlParameter object. As you can see here we are creating an instance of the SqlParameter object and then setting the Parameter Name i.e. @Id, the data type i.e. Int, the value i.e. 101, and the direction of the parameter i.e. Input. The direction will specify whether it is an input parameter or an output parameter. In our case, it is an input parameter so we set the direction as Input. Once we create the parameter object, then we need to add the parameter object to the Parameters collection of the SqlCommand object.

How to call a stored procedure with input parameter in C# ADO.NET?

Example to Understand How to Call a Stored Procedure with Input Parameter in C#:

The following example code is self-explained, so please go through the comment lines. In the below example, I am showing how to call a stored procedure with one input parameter 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=StudentDB; integrated security=SSPI";
//Create the SqlConnection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand()
{
CommandText = "spGetStudentById", //Specify the Stored procedure name
Connection = connection, //Specify the connection object where the stored procedure is going to execute
CommandType = CommandType.StoredProcedure //Specify the command type as Stored Procedure
};
//Create an instance of SqlParameter
SqlParameter param1 = new SqlParameter
{
ParameterName = "@Id", //Parameter name defined in stored procedure
SqlDbType = SqlDbType.Int, //Data Type of Parameter
Value = 101, //Value passes to the paramtere
Direction = ParameterDirection.Input //Specify the parameter as input
};
//Add the parameter to the Parameters property of SqlCommand object
cmd.Parameters.Add(param1);
//Open the Connection
connection.Open();
//Execute the command i.e. Executing the Stored Procedure using ExecuteReader method
//SqlDataReader requires an active and open connection
SqlDataReader sdr = cmd.ExecuteReader();
//Read the data from the SqlDataReader
//Read() method will returns true as long as data is there in the SqlDataReader
while (sdr.Read())
{
//Accessing the data using the string key as index
Console.WriteLine(sdr["Id"] + ", " + sdr["Name"] + ", " + sdr["Email"] + ", " + sdr["Mobile"]);
//Accessing the data using the integer index position as key
//Console.WriteLine(sdr[0] + ", " + sdr[1] + ", " + sdr[2] + ", " + sdr[3]);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output: 101, Priyanka, Priyanka@dotnettutorial.net, 2233445566

Note: It is also possible to add multiple input parameters if required.

SQL Server Stored Procedure with Both Input and Output Parameters:

In our previous example, we understand how to call a stored procedure with an input parameter. Now, let us see how to call a SQL Server Stored Procedure with both input and output parameters. First, use the below SQL Script to create the SQL Server Stored Procedure with both input and output parameters.

CREATE PROCEDURE spCreateStudent
(
@Name VARCHAR(100),
@Email VARCHAR(50),
@Mobile VARCHAR(50),
@Id int Out
)
AS
BEGIN
INSERT INTO Student VALUES (@Name,@Email,@Mobile)
SELECT @Id = SCOPE_IDENTITY()
END

As you can see in the above SQL Server Stored Procedure, it takes four parameters (3 input + 1 output). This Stored Procedure is very simple, takes the Name, Email, and Mobile of a student as input and then inserts the student’s details into the Student table. As we created the Student table with Id as an Identity column, so, we don’t require to pass the Id value in the insert statement. But what we want here is we need to return the newly created student Id and this is where the output parameter comes into the picture. Here, we set the output parameter value with the newly generated student Id by using the SCOPE_IDENTITY() function.

How to Call a Stored Procedure with both Input and Output Parameters in C#?

We have already seen how to use the Input parameter in C#. In order to understand how to call a stored procedure with output parameters, please have a look at the following image. As you can see, while defining the Output Parameter we need to specify the parameter direction as Output and we don’t require to set the value property as the stored procedure is going to set the value.

How to call a stored procedure with both input and output parameter in C#?

Note: By default the parameter direction is Input.

Example to Understand Stored Procedure with both Input and Output Parameters in C#:

The following example code is self-explained, so please go through the comment lines. In the below example, I am showing how to call a stored procedure with both input and output parameters using ADO.NET and C#.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ADOUsingStoredProcedure
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the Command Object
SqlCommand cmd = new SqlCommand()
{
CommandText = "spCreateStudent",
Connection = connection,
CommandType = CommandType.StoredProcedure
};
//Set Input Parameter
SqlParameter param1 = new SqlParameter
{
ParameterName = "@Name", //Parameter name defined in stored procedure
SqlDbType = SqlDbType.NVarChar, //Data Type of Parameter
Value = "Test", //Set the value
Direction = ParameterDirection.Input //Specify the parameter as input
};
//Add the parameter to the SqlCommand object
cmd.Parameters.Add(param1);
//Another approach to add Input Parameter
cmd.Parameters.AddWithValue("@Email", "Test@dotnettutorial.net");
cmd.Parameters.AddWithValue("@Mobile", "1234567890");
//Set Output Parameter
SqlParameter outParameter = new SqlParameter
{
ParameterName = "@Id", //Parameter name defined in stored procedure
SqlDbType = SqlDbType.Int, //Data Type of Parameter
Direction = ParameterDirection.Output //Specify the parameter as ouput
//No need to specify the value property
};
//Add the parameter to the Parameters collection property of SqlCommand object
cmd.Parameters.Add(outParameter);
connection.Open();
cmd.ExecuteNonQuery();
//Now you can access the output parameter using the value propery of outParameter object
Console.WriteLine("Newely Generated Student ID : " + outParameter.Value.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}" );
}
Console.ReadKey();
}
}
}
Output:

Example to Understand Stored Procedure with both Input and Output Parameters in C#

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

· Posted by Superadmin on November 15 2023 · 0 Comments · 72 Reads · Print

Comments

No Comments have been Posted.

Post Comment

Please Login to Post a Comment.

Ratings

Rating is available to Members only.

Please login or register to vote.
No Ratings have been Posted.
Render time: 0.96 seconds
10,813,799 unique visits