Use of SQL Stored Procedure inADO.NET code.

Here i am share an code snippet for using SQL Stored Procedure in ADO.NET code.

I write and used this code in my shopping cart application.

 public static Product GetAProduct(int productid)

{

    using (SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))

    {

        using (SqlCommand command = new
SqlCommand("GetAProduct", connection))

        {

            command.CommandType =
CommandType.StoredProcedure;

           
command.Parameters.Add(new SqlParameter("@ProductID", productid));

            connection.Open();
Product temp;

            using (SqlDataReader
reader = command.ExecuteReader())

            {

               
reader.Read();

               
temp = new Product( (int)reader["ProductID"],

                                                
(int)reader["CategoryID"],

                                                
(int)reader["ManufacturerID"],

                                                
(string)reader["Name"],

                                                
(int)reader["Weight"],

                                                
(int)reader["Price"],

                                                
(string)reader["Description"]);

            }

            return temp;

        }

    }

}

All the code is same is we use in simple SQL query only difference is that we
set sql command type to StoredProcedure in statement below command.



CommandType = CommandType.StoredProcedure;



one mere thing to be noted is that Connection string is retrieved from .config
file of the project in very first line of the code. As I have user Stored
Procedure named GetAProducts, we should write code for this stored procedurt in
SQL database Stored Procedure. which is as follows.

ALTER PROCEDURE GetAProduct

    @ProductID int


AS

    SELECT * FROM [Products] WHERE [Products].[ProductID] = @ProductID

RETURN

In the code snippet above first CREATE keyword is used in place of ALTER to
create the stored procedure, as soon as we save this stored procedure it is
converted to ALTER. If we want to make any change in this Stored Procedure then
this ALTER keyword is used as it is.

Than you.

Happy Programming.