将对象数组传递到SQL存储过程。

I have 2 tables

我有两个表

payment (payment_id, otherCosts, GarageCosts)
付款(payment_id otherCosts GarageCosts)
spareparts (payment_id, sparepartId, sparePartQty)
备件(payment_id sparepartId sparePartQty)

In payment table payment_id is autogenerated. Apart from otherCosts and garagecosts values, in my C# asp.net application there is an array of objects with

在支付表中，payment_id是自动生成的。除了其他成本和garagecosts值之外，在我的c# asp.net应用程序中有一个对象数组。

{ sparepartId : 'Somevalue', sparePartQty : 'somevalue' }

What I need to do is in a stored procedure first enter the record into payment table with garage costs and others costs value. Then return the last generated payment ID and enter it to spareParts table as paymentId for each of the value pairs in the array.

我需要做的是，在一个存储过程中，首先将记录输入到支付表中，其中包含车库成本和其他成本。然后返回最后一个生成的支付ID，并将其输入到spareParts表中，作为数组中每个值对的payd。

What is the way to achieve this? Please help.

实现这一目标的途径是什么?请帮助。

2 个解决方案

#1

Based on your tags I am going to assume we are talking about SQL Server / T-SQL.
You could do all this in one stored procedure:

基于你的标签，我将假设我们讨论的是SQL Server / T-SQL。你可以在一个存储过程中完成所有这些:

CREATE PROCEDURE dbo.Foo ... /* input parameters */
AS
BEGIN
    DECLARE @PaymentId int

    INSERT INTO payment(otherCosts, GarageCosts) VALUES (...)
    SET @PaymentId = SCOPE_IDENTITY()
    INSERT INTO spareparts(payment_id, sparepartId, sparePartQty) VALUES(@PaymentId, ...) 

END
GO

You may want to also look into @@IDENTITY but make sure you read about @@IDENTITY and SCOPE_IDENTITY and understand the risks associated with the first one.

您可能想要查看@@IDENTITY，但是请确保您阅读了@@IDENTITY和SCOPE_IDENTITY，并了解与第一个相关的风险。

If you need to have two separate sprocs you can do that too and here is how the first sproc would look like. Note that the @PaymentId is an output parameter which means that the caller can retrieve it and pass it to the second procedure.

如果你需要两个单独的sprocs，你也可以这样做，这是第一个sproc的样子。注意，@ paymentd是一个输出参数，这意味着调用者可以检索它并将其传递给第二个过程。

CREATE PROCEDURE dbo.Foo 
    /* input parameters */
    @PaymentId int OUT
AS
BEGIN
    INSERT INTO payment(otherCosts, GarageCosts) VALUES (...)
    SET @PaymentId = SCOPE_IDENTITY()
END
GO

Edit - after the scope of the question was clarified:

编辑-在问题的范围被澄清后:

If you need to call the second stored procedure and pass it an array of parameters, with SQL Server 2008 or newer you can use TVP (Table Value Parameters). To see how you can use them in stored procedures and how you can pass them from C# code see Table Value Parameters in SQL Server 2008 and .NET (C#) or Table-Valued Parameters.
You can also use TVPs with the solution where you only have one sproc.

如果您需要调用第二个存储过程并将其传递给它，那么使用SQL Server 2008或更新您可以使用TVP(表值参数)。要了解如何在存储过程中使用它们，以及如何从c#代码中传递它们，请参见SQL Server 2008和。net (c#)或表值参数中的表值参数。你也可以使用TVPs的解决方案，你只有一个sproc。

#2

To solve your problem try this

解决你的问题试试这个。

First insert your data in payment table with otherCosts and GarageCosts.

首先，在支付表中插入您的数据，并使用其他成本和GarageCosts。

Then create a procedure to get the latest stored payment_id from payment table

然后创建一个过程，从支付表中获取最新的存储payment_id。

create procedure select_last_payment_id
as
begin
    select top 1 payment_id
    from payment
    order by payment_id desc  
end

Lastly get that payment_id by running stored procedure and assigning it to payment_id of spareparts table and storing spareparts data.

最后，通过运行存储过程并将其分配给spareparts表的payment_id并存储spareparts数据，获得payment_id。

Hope it works for you.

希望它对你有用。

#1