Using a stored procedure, how with Dapper can I insert many rows into two tables?

.net asp.net-core-webapi c# dapper

Question

In my MyWeb api project for Asp.net Core, I'm using Dapper (https://github.com/StackExchange/Dapper). I need to create a master record and add a number of rows to a child table.

Ex. OrdersMaster table
    create an order id in this table

    OrderDetails table
    ordermasterid
    order items

How can I accomplish this using Dapper? If you can, give some code samples.

1
2
4/4/2018 9:09:16 AM

Accepted Answer

The other answer's suggestion to use stored procedure is an excellent one. Without using a stored procedure, my solution achieves the same.

You must employ transaction. In this manner, all the modifications will either be committed or undone. The code below presumes you're usingIdentity as the Primary Key. Refer to question this for discussion.@@IDENTITY that I used in the code below.

I have included thorough comments to clarify stages even though the code is not complete.

using (var connection = new SqlCeConnection("connection_string"))
{
    connection.Open();

    //Begin the transaction
    using (var transaction = connection.BeginTransaction())
    {
        //Create and fill-up master table data
        var paramMaster = new DynamicParameters();
        paramMaster.Add("@XXX", ...);
        ....

        //Insert record in master table. Pass transaction parameter to Dapper.
        var affectedRows = connection.Execute("insert into OrdersMaster....", paramMaster, transaction: transaction);

        //Get the Id newly created for master table record.
        //If this is not an Identity, use different method here
        newId = Convert.ToInt64(connection.ExecuteScalar<object>("SELECT @@IDENTITY", null, transaction: transaction));

        //Create and fill-up detail table data
        //Use suitable loop as you want to insert multiple records.
        //for(......)
        foreach(OrderItem item in orderItems)
        {
            var paramDetails = new DynamicParameters();
            paramDetails.Add("@OrderMasterId", newId);
            paramDetails.Add("@YYY", ...);
            ....

            //Insert record in detail table. Pass transaction parameter to Dapper.
            var affectedRows = connection.Execute("insert into OrderDetails....", paramDetails, transaction: transaction);
        }

        //Commit transaction
        transaction.Commit();
    }
}
1
4/4/2018 7:27:09 AM

Popular Answer

The insert operations would be implemented as a transactional stored procedure, which your.NET application would then call.

To send in a list of data, you may require a table-valued type, such as this:

CREATE TYPE List_Of_Items AS TABLE (
    ItemID INT NOT NULL,
    Quantity INT NOT NULL
)

The process may resemble this

CREATE PROC Insert_Order_With_Details (
     @CustomerId INT,
     @Items List_Of_Items
) AS
BEGIN
    BEGIN TRANSACTION
        INSERT INTO OrdersMaster (CustomerId) VALUES @CustomerId

        DECLARE @OrderID INT
        SET @OrderID = SCOPE_IDENTITY() --last assigned id

        INSERT INTO OrderDetails (OrderId, CustomerId, ItemId, Quantity)
            SELECT @OrderID, @CustomerID, ItemID, Quantity
            FROM @Items
    COMMIT
END

Then, I'd advise writing methods in C# to create your TVP. It's not as easy as you'd want. This calls for theusing Microsoft.SqlServer.Server and using Dapper.Tvp .

    //This is a shell to create any kind of TVP
    private static void AddTableCore<T>(
        this DynamicParametersTvp dp,
        string tvpTypeName,
        Func<T, SqlDataRecord> valueProjection,
        IEnumerable<T> values,
        string parameterTableName)
    {
        var tvp = values
            .Select(valueProjection)
            .ToList();

        //If you pass a TVP with 0 rows to SQL server it will error, you must pass null instead.
        if (!tvp.Any()) tvp = null;

        dp.Add(new TableValueParameter(parameterTableName, tvpTypeName, tvp));
    }

    //This will create your specific Items TVP
    public static void AddItemsTable(this DynamicParametersTvp dp, IEnumerable<Item> items, string parameterTableName = "Items")
    {
        var columns = new[]
        {
            new SqlMetaData("ItemID", SqlDbType.Int)
            new SqlMetaData("Quantity", SqlDbType.Int)
        };

        var projection = new Func<Item, SqlDataRecord>(item =>
        {
            var record = new SqlDataRecord(columns);
            record.SetInt32(0, item.Id);
            record.SetInt32(1, item.Quantity);
            return record;
        });

        AddTableCore(dp, "Items", projection, items, parameterTableName);
    }

then, where you need to ask a question, you could:

using (var cn = new SqlConnection(myConnectionString))
{
    var p = new DynampicParametersTvp(new {
        CustomerId = myCustomerId
    });
    p.AddItemsTable(items);

    cn.Execute("Insert_Order_With_Details", p, commandType: CommandType.StoredProcedure);
}

The commandType Argumentation is quite significant. If you submit the name of a proc, it will throw an error and fallback to plain SQL text.

You must utilize the Dapper and table-valued arguments if you wish to submit several orders at once. Tvp bundle.

See this SO question Using Dapper. Other parameters for TVP TableValueParameter and this Microsoft TVP documentation https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine for further information. Not all SQL vendors, in my opinion, support TVPs.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow