Dapper para insertar múltiples filas en dos tablas utilizando el procedimiento almacenado

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

Pregunta

Estoy utilizando Dapper ( https://github.com/StackExchange/Dapper ) en el proyecto asp.net core myweb api. Tengo un requisito para crear un registro maestro e insertar un conjunto de filas en una tabla secundaria.

Ex. OrdersMaster table
    create an order id in this table

    OrderDetails table
    ordermasterid
    order items

¿Cómo puedo hacer esto usando Dapper? Por favor comparte algunos fragmentos de código si es posible.

Respuesta aceptada

Usar el procedimiento almacenado como se menciona en otra respuesta es una buena solución. Mi respuesta implementa lo mismo sin el procedimiento almacenado.

Tienes que usar la transacción. De esta forma, todos los cambios serán confirmados o revertidos. El siguiente código supone que está utilizando Identity como clave principal. Por favor refiérase a esta pregunta para discusión sobre @@IDENTITY que he usado en el siguiente código.

Aunque el código no está completo, he puesto comentarios detallados para explicar los pasos.

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

Respuesta popular

Implementaré las operaciones de inserción como un procedimiento almacenado transaccional, y luego lo invocaré desde su aplicación .NET.

Es posible que necesite un tipo de valor de tabla para pasar una lista de datos, como esta:

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

El procedimiento podría verse así

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

Luego, en C #, sugeriría crear métodos para crear tu TVP. No es tan simple como te gustaría. Esto requiere el using Microsoft.SqlServer.Server y el 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);
    }

y luego, donde necesita hacer una consulta, puede hacer:

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

El argumento commandType es súper importante. Su valor predeterminado es texto SQL y generará un error si envía el nombre de un proceso.

Si desea realizar varios pedidos a la vez, deberá usar los parámetros con valores de tabla y el paquete Dapper.Tvp.

Consulte esta pregunta sobre SO utilizando Dapper.TVP TableValueParameter con otros parámetros , así como esta documentación en TVP de Microsoft https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters -database-engine . No creo que todos los proveedores de SQL sean compatibles con los TVP.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué