[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:
Post a Comment