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.

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:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8