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.
// 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; // ...
Get ADO.NET in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.