Добавление одной ко многим записям в хранимой процедуре

Хотя этот вопрос может показаться слишком широким и, следовательно, не по теме, он хорошо написан и описывает проблему, с которой сталкиваются многие неопытные разработчики, поэтому я думаю, что он заслуживает ответа.
Итак, давайте разберем его на составляющие:

1. Вопрос. Как вернуть значение столбца identity после вставки?
   О: SQL Server предоставляет несколько способов сделать это, самым простым из которых, вероятно, будет < a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">scope_identity , но лучше всего использовать output.

2. Вопрос. Как отправить несколько строк в хранимую процедуру?
   Ответ. Используйте табличный параметр.

Теперь давайте посмотрим, как именно мы собираемся это сделать.

Первое, что нам нужно сделать, это создать определяемый пользователем тип таблицы, который будет использоваться для параметра с табличным значением, поэтому:

CREATE TYPE [dbo].[Udt_OrderItems] AS TABLE
(
    productId       int NOT NULL,
    productQuantity int,
);
GO

Затем мы можем создать хранимую процедуру, используя этот тип в качестве табличного параметра. Мы также отправим детали заказа в виде скалярных параметров в хранимую процедуру:

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

Что касается части c #, это зависит от того, как вы подключаетесь к базе данных - я покажу пример базовой версии ADO.Net:

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();
    }
}