Stored Procedure Execution In ADO.NET
Stored Procedure is a database object that contains precompiled sql statements. Whenever you called stored procedure then it skip the compilation and run directly, so performance will be increased.
Advantages of Stored Procedure:
1. It's contains precompiled statements, so no need of compilation every time.
2. Due to skipping compilation, performance will be increased.
3. Prevents from Sql Injection.
4. Code reusability
Use below namespaces for executing stored procedure in ADO.Net:
using System.Data;
using System.Data.SqlClient;
Below we are defining connection string that will use in all next examples:
Stored Procedure Without Parameter
For executing stored procedure you have to specify Command object property CommandType as StoredProcedure. In below example, we are executing a stored procedure that have no any input or output parameter:
SqlCommand cmd = new SqlCommand("Sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Stored Procedure With Input Parameters
Sometimes you have to pass input parameters to stored procedure. Use Parameters.AddWithValue that contains parameter name and that value. Below examples demonstrate that how to pass input parameters:
SqlCommand cmd = new SqlCommand("Sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", "Jitesh");
cmd.Parameters.AddWithValue("@Points", 100);
cmd.Parameters.AddWithValue("@DOJ", DateTime.Now);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Stored Procedure With Input & Output Parameter
Sometimes you have to use input and output parameters to stored procedure. Input parameter used to pass parameter value to stored procedure. Output parameter used to retrieve return parameter value from stored procedure.
Use SqlParameter object for output parameter that contains parameter name and that data type and you have to specify ParameterDirection as output. Below example demonstrate that how to use input and output parameter in stored procedure execution:
SqlCommand cmd = new SqlCommand("Sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", "Jitesh");
cmd.Parameters.AddWithValue("@Points", 100);
cmd.Parameters.AddWithValue("@DOJ", DateTime.Now);
SqlParameter param = new SqlParameter("@Id", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
int newId = Convert.ToInt16(cmd.Parameters["@Id"].Value);
Stored Procedure With Multiple Output Parameters
Below example demonstrate that how to use multiple output parameters in stored procedure execution:
SqlParameter param;
SqlCommand cmd = new SqlCommand("Sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", "1");
param = new SqlParameter("@Id", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
param = new SqlParameter("@Name", SqlDbType.VarChar);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
param = new SqlParameter("@Points", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
param = new SqlParameter("@DOJ", SqlDbType.DateTime);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
string name = Convert.ToString(cmd.Parameters["@Name"].Value);
int points = Convert.ToInt32(cmd.Parameters["@Points"].Value);