5.3 Stored Procedures with the DataReader

Using a command to execute a stored procedure query isn't much different from using one to execute a stored procedure that wraps a nonquery command such as INSERT, UPDATE, or DELETE.

The Northwind database includes a small set of stored procedure queries. One example is the CustOrderHist procedure, which returns the total number of products a given customer has ordered, grouped by product name.

Here's the SQL code to create the CustOrderHist stored procedure. It defines one parameter (shown in the first line), called @CustomerID:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
       AS
       SELECT ProductName, Total=SUM(Quantity)
       FROM Products P, [Order Details] OD, Orders O, Customers C
       WHERE C.CustomerID = @CustomerID AND
             C.CustomerID = O.CustomerID AND
             O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
       GROUP BY ProductName

GO

Example 5-5 executes this stored procedure for the customer "ALFKI" and displays the results in a console window.

Example 5-5. Using a stored procedure query

// TotalOrders.cs - Runs the CustOrderHist stored procedure.

using System;
using System.Data;
using System.Data.SqlClient;

public class TotalOrders
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                   "Initial Catalog=Northwind;Integrated Security=SSPI";
        string procedure = "CustOrderHist";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(procedure, con);
        SqlDataReader r;

        // Configure command and add parameters.
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter param;
        param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
        param.Value = "ALFKI";

        // Execute the command.
        con.Open();
        r = cmd.ExecuteReader();
        while (r.Read())
        {
            Console.WriteLine(r["Total"].ToString() + " of " + 
                              r["ProductName"].ToString());
        }

        con.Close();
    }
}

Here's the sample output for this code:

20 of Vegie-spread
15 of Raclette Courdavault
17 of Rössle Sauerkraut
15 of Lakkalikööri
16 of Grandma's Boysenberry Spread
20 of Flotemysost
2 of Original Frankfurter grüne Soße
2 of Spegesild
21 of Chartreuse verte
6 of Aniseed Syrup
40 of Escargots de Bourgogne

If you use a stored procedure that returns information through output parameters or a return value, this information won't be available until after you close the DataReader because the stored procedure will still be executing.