How to Execute Stored Procedure With Parameters in C#
In this guide, you will learn how you can execute/call a stored procedure with parameters in C#, which means how you can pass a parameter inside a stored procedure.
You’ll learn everything from top to bottom in this compact guide. Let’s dive in!
Execute a Stored Procedure With Parameters in C#
To understand how you can call a stored procedure with parameters in C#, you need to understand all the basics regarding the stored procedures.
What are they, and for what purpose are they used? What are their advantages? How do you create and alter them?
Stored Procedures in C#
A collection of Transact-SQL statements assembled into a single execution plan is a stored procedure. Database servers use code blocks called stored procedures; it is a pre-compiled entity that is compiled once and can be used repeatedly.
A series of SQL statements can run sequentially with a stored procedure. We must employ procedure parameters to offer data to the process.
Stored procedures use the concept of parameter mapping. The names, types, and directions of the front end and procedure parameters must match, and the front end parameter length must be less than or equal to the procedure parameter length (that only can map parameters).
We utilize the return statement to return any value from a procedure.
Why We Use Stored Procedures in C#
To access your data from a database, you typically create SQL queries like select, insert, and update. It makes sense to convert a query into a stored procedure if you frequently use the same query.
Every time you write a query, the database parses it. If you’ve created a stored procedure for it, it can be executed N times after being parsed just once.
Performance can also be enhanced via stored routines. A stored procedure, a single execution block on the database server, contains all the conditional logic.
Execute Stored Procedure With Parameters in C#: Code Example
The following are the steps to execute the stored procedure with parameters in C#:
-
The first thing you need to do is add the
using System.Data.SqlClient;andusing System.Data;libraries right above the program. These will allow you to create a stored procedure in a C# program. -
In the code example below, we start creating the stored procedure by passing the path of the database into a string.
-
After that, we used the
using()method to create theConnectionobjectconand passed the path string inside. -
We need to make an object of the
SqlCommand. We can use this object to utilize SQL command properties, such asConnection. -
After that, we need to specify the command type as
StoredProcedure. -
To create a stored procedure with parameters, we need to make an object of
SqlParameterand use that object to define the name, data type, and value of the parameter we want to pass. -
The next thing we need to do is give it direction. We can either use it as an input, output, or both.
-
Now, by using
AddWithValue(), pass the parameter object you created earlier. -
Keep in mind that all of this needs to be done inside the
try-catchblock. There can be errors while fetching the data that must be caught inside thecatchblock.
Example code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace Stored_procedure {
internal class Program {
static void Main(string[] args) {
// connection with sql
try {
// string for connection information
string con_str = "pass your connection path/information";
// create connection
using (SqlConnection con = new SqlConnection(con_str)) {
// create sqlcommand object
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Write the name of your stored prcedure";
cmd.Connection = con;
// specify the command type
cmd.CommandType = CommandType.StoredProcedure;
// create object of sqlparameter class
SqlParameter param = new SqlParameter {
// set the name of parameter
ParameterName = "Write your parameter name",
// set the type of parameter
SqlDbType = SqlDbType.Int, Value = 2,
// you can specify the direction of parameter input/output/inputoutput
Direction = ParameterDirection.InputOutput
};
// adding parameter in command
cmd.Parameters.AddWithValue(param);
// open connection of db
con.Open();
// executing the query
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine(e.Message);
}
}
}
}
Haider specializes in technical writing. He has a solid background in computer science that allows him to create engaging, original, and compelling technical tutorials. In his free time, he enjoys adding new skills to his repertoire and watching Netflix.
LinkedIn