How to Call Stored Procedure:
In this article we would explain how to call stored procedure to get the data from the SQL Server database.
Stored procedure is the pre compiled set of SQL instructions. When we call the stored procedure we only pass the stored procedure name along with the argument required by the stored procedure. So it takes less number of information to be sending over the network. So pre compilation of the stored procedure and very less information to send over the network makes application fast. Its always a good idea to have to stored procedures in the applications. Second benefit to using stored procedure in the application is that we can fix the bugs in stored procedure without affecting the application.
Execute the stored procedure:
//1. Create the SqlCommand
SqlCommand cmd = new SqlCommand(“sp name “, SqlConnection object);
2// set property commandType as StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
1. Execute stored procedure-using ExecuteReader to get records:
SqlConnection conn = new SqlConnection("Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
conn.Open();
reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
while(reader.Read())
{
//read data
}
conn.Close();
2. Execute stored procedure-using ExecuteScalar to get only one value:
SqlConnection conn = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType. StoredProcedure;
cmd.Connection = conn;
conn.Open();
Object returnValue = cmd.ExecuteScalar();
conn.Close();
3. Execute stored procedure-using ExecuteNonQuery to update, delete, or insert records:
SqlConnection conn = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
conn.Open();
Int32 rowsAffected = cmd.ExecuteNonQuery();
conn.Close();
4. Execute stored procedure-using DataAdapter to retrieve records:
SqlConnection conn = new SqlConnection("Your Connection String");
SqlCommand cmd = new.SqlCommand("sp_name", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300; // 5 minutes
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
Summary: We have seen how to call the stored procedure using command object. Key is to set the CommandType to CommandType.StoredProcedure.