如何使用特定数据的所有可能实例填充数据表？

I am having some trouble writing a SQL Server stored procedure. I have three tables, Products,CustomerInfo and CustomerOrders.

我在编写SQL Server存储过程时遇到了一些麻烦。我有三个表,Products,CustomerInfo和CustomerOrders。

Products has columns ProductID,Product, Price, Description

产品有列ProductID,产品,价格,描述

CustomerInfo has columns CostumerID, Name, Address, Zipcode

CustomerInfo包含CostumerID,Name,Address,Zipcode列

CustomerOrders has columns like CustomerID or TransactionID, ProductID, Quantity.

CustomerOrders具有CustomerID或TransactionID,ProductID,Quantity等列。

Now I am trying to write a stored procedure which will import into a datatable all the products the customer has ever bought. I have the customers name that I can use as an parameter.

现在我正在尝试编写一个存储过程,该存储过程将导入客户购买的所有产品的数据表中。我有可以用作参数的客户名称。

Couple of things to note: every time a customer purchases something, a new CustomerID and TransactionID are generated and they are both the same. The CustomerName is the only constant across multiple orders.

需要注意的事项:每次客户购买东西时,都会生成一个新的CustomerID和TransactionID,它们都是相同的。 CustomerName是多个订单中唯一的常量。

DECLARE @TransactionID int;

SET @TransactionID = @Id;

SELECT 
    P.Product, P.Price, 
    CP.TotalProducts as ProductQuantity 
FROM 
    Products P
INNER JOIN 
    CustomerProducts CP ON CP.ProductID = P.ProductID
WHERE
    CP.CustomerID = @TransactionID

At the moment, I get the products that the customer bought the last time he shopped. However, I want to get all products he has ever bought into one table. If anyone can help me out, I would really appreciate it!

目前,我收到客户上次购买时所购买的产品。但是,我想把他买过的所有产品都放到一张桌子里。如果有人能帮助我,我会非常感激!

1 个解决方案

#1

If I understood you correctly you need joins on 3 tables in stored procedure to extract all details of customer purchased products.

如果我理解正确,您需要在存储过程中的3个表上加入以提取客户购买产品的所有详细信息。

so a stored procedure in this purpose could look like this-

所以这个目的的存储过程看起来像这样 -

CREATE PROC [dbo].[GetCustomerProductsById]  
( 
@CustId int=0
AS
BEGIN
SELECT P.ProductId ,P.Product,P.Price,P.Description,C.CustomerID,C.Name,C.Address FROM Products P
Inner Join CustomerOrders CO ON P.ProductId= CO.ProductID
Inner Join CustomerInfo C ON C.CustomerID = CO.CustomerID
WHERE C.CustomerID = @CustId
ORDER BY C.Name
END

Now once this is giving us the expected results then in c# code you can call this stored procedure like below and extract a result datatable.

现在,一旦这给了我们预期的结果,那么在c#代码中你可以像下面这样调用这个存储过程并提取结果数据表。

SqlDataAdapter SqlAda;
DataSet ds; 
using (SqlConnection Sqlcon = new SqlConnection(strCon))    
{    
using (SqlCommand cmd = new SqlCommand())    
{

Sqlcon.Open();

cmd.Connection = Sqlcon;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "GetCustomerProductsById";

cmd.Parameters.Add(new SqlParameter("@CustId", SqlDbType.Int, 50));

cmd.Parameters["@CustId"].Value = <Your Input Source>;  
SqlAda = new SqlDataAdapter(cmd);

ds = new DataSet();

SqlAda.Fill(ds);

Datatable dt = new DataTable();
dt = ds.Tables[0];
}

}

1 个解决方案

#1

更多相关文章

随机推荐