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 Rssle Sauerkraut 15 of Lakkalikri 16 of Grandma's Boysenberry Spread 20 of Flotemysost 2 of Original Frankfurter grne Soe 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.

ADO. NET in a Nutshell
ADO.NET in a Nutshell
ISBN: 0596003617
EAN: 2147483647
Year: 2005
Pages: 415

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net