Tech Junkie Blog: ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6

Monday, February 16, 2015

ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call selProductsBySupplierID.  The stored procedure takes one input parameter call @SupplierID which takes an int.

Sql Parameter

Here is how you would do it in C#

1.  First you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file
string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure and output the result from the SqlDataReader
  using (SqlConnection conn = new SqlConnection(connectString))
  {    
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "selProductsBySupplierID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@SupplierID";
            parameter.SqlDbType = SqlDbType.Int;
            parameter.Value = 1;
            cmd.Parameters.Add(parameter);

            SqlDataReader dataReader = cmd.ExecuteReader();

            try
            {
                 while (dataReader.Read())
                 {
                     Response.Write("Products: " + dataReader[0] + " $" + 
                     dataReader[1] + "<br>");
                 }
             }
             finally
             {
                 dataReader.Close();
             }
    }
In the above code you add the parameter by using SqlParameter. You specify the name, type, and value. Then add it to command object's parameters list. When you execute the reader the parameter @SupplierID is passed into the stored procedure.

Blogs In the T-SQL Series:

No comments:

Post a Comment