Using Stored Procedures in ADO.Net

Stored Procedures are a set of sql commands which are compiled and are stored inside the database. Every time you execute a sql command, the command is parsed, optimization is done and then the command is executed. Parsing and optimization the command each time you run the query is very expensive. To solve this we have a set of commands collectively called as stored procedure, which are already parsed and optimized and are executed when ever we call them.

Initially create an object of SqlConnection class Open the connection using the Open() method

SqlConnection con = new SqlConnection(@"Data Source=LAB4-SYS12\SQLEXPRESS ; initial catalog= StudentDB ; Integrated Security =SSPI");
con.open();

Create the following stored procedure on the Student _info table in the StudenDB database which accepts two parameters and does not have any output parameters.

CREATE PROCEDURE StudentInfoUpdate (@id INTEGER,
@Address VARCHAR(50)) AS
SET NOCOUNT OFF
UPDATE Student_info
SET Address = @Address
WHERE ID = @ID

Create a SqlCommand object with the parameters as the name of the stored procedure that is to be executed and the connection object con to which the command is to be sent for execution.

SqlCommand command = new SqlCommand("StudentInfoUpdate",con);

Change the command objects CommandType property to stored procedure.

command.CommandType = CommandType.StoredProcedure;

Add the parameters to the command object using the Parameters collection and the SqlParameter class.

command.Parameters.Add(new SqlParameter("@id",SqlDbType.Int,0,"id"));
command.Parameters.Add(new SqlParameter("@Address",SqlDbType.VarChar,50,"Address"));

Specify the values of the parameters using the Value property of the parameters

command.Parameters[0].Value=1;
command.Parameters[1].Value="Khyber PakhtoonKhuwa";

Execute the stored procedure using the ExecuteNonQuery method which returns the number of rows affected by the stored procedure.

int result =command.ExecuteNonQuery();

Now let us see how to execute stored procedures which has output parameters and how to access the results using the output parameters.

Create the following stored procedure which has one output parameter.

CREATE PROCEDURE StudentCount (@Students int OUTPUT) AS
SELECT @Students = Count(*) from Student_info

The above stored procedure counts the student records and results it as the output parameter.

SqlCommand command1 = new SqlCommand("StudentCount",con);
command1.CommandType = CommandType.StoredProcedure;

Add the parameters to the command1
command1.Parameters.Add(new SqlParameter("@Students" ,SqlDbType.Int,0 ,"Students"));

Observe that the parameter Students is added with the ParameterDirection as Output.

Assign the UpdatedRowSource property of the SqlCommand object to UpdateRowSource.OutputParameters to indicate that data will be returned from this stored procedure via output parameters.

command1.UpdatedRowSource = UpdateRowSource.OutputParameters;

Call the stored procedure and access the Students using the value property of the parameter.

command1.ExecuteNonQuery();
int studentCount =(int) command1.Parameters["@Students"].Value;

Close the sql connection.

con.Close();

In the same way you can call the stored procedure that returns a set of rows by defining the parameters as appropriate and executing the command using ExecuteReader() that is used to traverse the records returned by the command.

Please leave your questions and comments in the comments section below.

Thank you.