ADO.NET in a Nutshell

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; // ...