ASP.NET: Call Stored Procedure Using SqlCommand and SqlDataReader

A lot of times you want to call a stored procedure quickly in the code to access the stored procedure.  You don't want to go through the trouble of dragging an SqlDataSource control in the design view just to use the stored procedure.

Here is how you would call the "Top Ten Most Expensive Products" stored procedure in the Northwind database.

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 = "GetProductsAvgPrice";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            Response.Write("Products Average Price is $" + (decimal)cmd.ExecuteScalar());
    }

In the above code the ExecuteScalar() method executes a call to the stored procedure called "GetProductsAvgPrice" which uses the aggregate function AVG() to get the average products price. The only thing you have to watch out for is that you have to convert the type to the appropriate type if you want to use it. Since currency is of type decimal you want to use the result into a decimal value because the ExecuteScalar() method returns an object type.