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

Tip

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.