在存储过程中添加一个到多个记录

然后,我们可以使用此类型创建存储过程作为表值参数.我们还将订单详细信息作为标量参数发送到存储过程:

CREATE PROCEDURE stp_InsertOrderWithItems
(
    @customerName    varchar(30),
    @customerAddress varchar(30),
    @submitDate      date,
    @realizationDate date,
    @deliveryTypeId  int,
    @orderItems dbo.Udt_OrderItems readonly -- Table valued parameters must be readonly
)
AS

    DECLARE @Ids AS TABLE (orderId int NOT NULL) -- for the output clause

    INSERT INTO dbo.Orders (customerName, customerAddress, submitDate, realizationDate, deliveryTypeId)
    OUTPUT inserted.Id INTO @Ids
    VALUES(@customerName, @customerAddress, @submitDate, @realizationDate, @deliveryTypeId)

    INSERT INTO dbo.OrderItems (orderId, productId, productQuantity)
    SELECT orderId, productId, productQuantity
    FROM @orderItems
    CROSS JOIN @Ids -- We only have one value in @Ids so cross join is safe
GO

using(var con = new SqlConnection(connectionString))
{
    using(var cmd = new SqlCommand(con, "stp_InsertOrderWithItems"))
    {
        var dt = new DataTable()
        dt.Columns.Add("productId", typeof(int));
        dt.Columns.Add("productQuantity", typeof(int));

        // populate data table here

        cmd.Parameters.Add("@customerName", SqlDbType.VarChar, 30).Value = customerName;
        // all the other scalar parameters here...
        cmd.Parameters.Add(@orderItems, SqlDbType.Structured).Value = dt;

        con.Open();
        cmd.ExecuteNonQuery();
    }
}