Use of SQL stored procedure in ADO.NET code Samples and examples

Here i am share an code snippet for using SQL Stored Procedure in ADO.NET code.
I write an 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.

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.

Online Members

Use of SQL stored procedure in ADO.NET code

Post Comment:

Email subscription