Stored Procedure in ADO.NET

It is a precompiled set of SQL statement which is compiled in native form and stored in the backend. They are very fast on their first execution also.

Benefits Using SP:
· They allow modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.
· They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
· They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
· They can be used as a security mechanism.
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
· It’s easy to update logic/code in them because it stored at one place in the database.

Sample Stored Procedure:
Create proc GetSalary(@EmpID int,@EmpSal Money out) as
Begin
select @EmpSal=EmpSalary from Employee where EmpID=@EmpID
End

Example:
SqlConnection con=new SqlConnection();
con.ConnectionString = “your connection string”
SqlCommand cmd=new SqlCommand();
cmd.CommandText="GetSalary"; //SP name
cmd.CommandType=CommandType.StoredProcedure;
cmd.Connection=con;

SqlParameter ID=new SqlParameter("@EmpID",SqlDbType.Int);
SqlParameter sal=new SqlParameter("EmpSal",SqlDbType.Money);
sal.Direction=ParameterDirection.Output;
sal.Value = DBNull.Value;
// Parameter direction is enum it has the parameters
// Input - The parameter is an input parameter.
// InputOutput - The parameter is capable of both input and output.
// Output - The parameter is an output parameter.
// ReturnValue - The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

ID.Value= “provide employee id”;
cmd.Parameters.Add(ID);
cmd.Parameters.Add(sal);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(sal.Value.ToString());
Note:
· Every Input Parameter must have a value before the command is executed. If required we can set to DBNull.Value.
· As far as possible try to execute all SQL statements using SP.