Friday, October 24, 2008

ADO.Net - Return data from a stored procedure - Untyped

Suppose we have a stored procedure CustOrderHist in the Northwind database. The stored proc returns the ProductName and the total quantity ordered for that Product Name for a given customer and looks like

[dbo].[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


To call this stored proc (for customerid ALFKI) and return the data to a datatable use the following:


string strConn;
strConn = "Data Source=myMachine;Initial Catalog=Northwind;Persist Security Info=True;User ID=web_user;Password=hehe";
SqlConnection cn = new SqlConnection(strConn);

if (cn.State != ConnectionState.Open)
{
cn.Open();
}
SqlCommand cmd = new SqlCommand("CustOrderHist", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustomerID", "ALFKI");

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("CustOrderHist");

da.Fill(dt);

cn.Close();




Have fun.

No comments: