How to use Stored Procedure in ADO.NET C#?. The Complete ADO.NET Developer Course 2023 [Videos]

In this Video, I am going to discuss ADO.NET Using Stored Procedure in detail. Please read our previous Video, where we discussed ADO.NET DataSet Architecture. At the end of this Video, you will understand how to call a stored procedure without parameter as well as how to call a stored procedure from ADO.NET using both inputs as well as the output parameter.

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 Procedures are 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:

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
Stored Procedure without a parameter:

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.

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. So, what you need to do is, first create an instance of command object and then specify the commandTest property value as the stored procedure name and the most important point is you 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 Command class as shown in the below image. 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.

The complete example is given below.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ADOUsingStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGetStudents", connection)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    connection.Open();
                    SqlDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr["Id"] + ",  "+sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                    }
                }             
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.
" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

ADO.NET Using Stored Procedure

Stored procedure with Input Parameter:

Now, we will see how to call a stored procedure with an input parameter. 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.

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 an input parameter in C# ADO.NET?

In order to understand how to call a stored procedure with an input parameter, please have a look at the following image. We already discussed the command 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, the data type, value, and the direction of the parameter.

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

The complete example is given below.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ADOUsingStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                  
                    //Create the command object
                    SqlCommand cmd = new SqlCommand()
                    {
                        CommandText = "spGetStudentById",
                        Connection = connection,
                        CommandType = CommandType.StoredProcedure
                    };
                    //Set 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 SqlCommand object
                    cmd.Parameters.Add(param1);
                    connection.Open();
                    SqlDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr["Id"] + ",  "+sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                    }
                }             
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.
" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

Stored Procedure with both Input and Output Parameter:

In our previous example, we understand how to call a stored procedure with an input parameter. Now, let us see how to call a stored procedure with both input and output parameters. So, please use the below SQL Script to create the 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 the above-stored procedure, takes four parameters (3 input + 1 output). The above-stored procedure is very simple, takes the Student Name, Email and Mobile and inserts it into the Student table. As we created the student table with Id as an Identity column, so, we dont 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.

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

In order to understand how to call a stored procedure with both input and output parameters, please have a look at the following image. As you can see, while defining the Output Parameter you need to specify the parameter direction as Output and you dont require to set the value property.

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

Note: By default the parameter direction is Input.

The complete code is given below.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ADOUsingStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; 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 SqlParameter
                    SqlParameter param1 = new SqlParameter
                    {
                        ParameterName = "@Name", //Parameter name defined in stored procedure
                        SqlDbType = SqlDbType.NVarChar, //Data Type of Parameter
                        Value = "Test",
                        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 SqlParameter
                    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
                    };
                    //add the parameter to the SqlCommand object
                    cmd.Parameters.Add(outParameter);
                    
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    
                    Console.WriteLine("Newely Generated Student ID : " + outParameter.Value.ToString());
                }             
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.
" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

How to use Stored Procedure in ADO.NET C#?. The Complete ADO.NET Developer Course 2023 [Videos].

What is a Stored Procedure in SQL?

Example to understand ADO.NET using Stored Procedure:

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

The complete example is given below.

Stored procedure with Input Parameter:

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

The complete example is given below.

Stored Procedure with both Input and Output Parameter:

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

The complete code is given below.

Comments (427 Comments)

Submit Your Comment

Related Posts

What is ADO.NET?

How to using ADO.NET with SQL Server?

What is ADO.NET SqlConnection Class in C#?

What is Bitcoin and Cryptocurrency Technologies?

What is LINQ Extension Methods in C#?

What is Asynchronous Programming with Entity Framework?

The Pros of Selling on Amazon

Instagram SEO Tactics to Rank Higher in 2022

What is Differences between IEnumerable and IQueryable in C#?

ASP.NET Web API Test Questions and Answers

ASP.NET MVC Test Questions and Answers

How to Sell on Amazon International Marketplaces?