Bulk insert/Update con Petapoco

Question

Estoy utilizando el método Save() para insertar o actualizar registros, pero me gustaría que realice una inserción masiva y una actualización masiva con solo una base de datos. ¿Cómo hago esto?

Answer 1

A continuación se muestra un método BulkInsert de PetaPoco que se expande en la muy inteligente idea de taylonr de usar la técnica SQL de insertar múltiples filas a través de la INSERT INTO tab(col1, col2) OUTPUT inserted.[ID] VALUES (@0, @1), (@2, 3), (@4, @5), ..., (@n-1, @n) .

También devuelve los valores de incremento automático (identidad) de los registros insertados, lo que no creo que suceda en la implementación de IvoTops.

NOTA: SQL Server 2012 (y más abajo) tiene un límite de 2,100 parámetros por consulta. (Este es probablemente el origen de la excepción de desbordamiento de pila referenciada por el comentario de Zelid). Deberá dividir manualmente sus lotes en función del número de columnas que no estén decoradas como Ignore o Result . Por ejemplo, un POCO con 21 columnas debe enviarse en tamaños de lote de 99, o (2100 - 1) / 21 . Puedo refactorizar esto para dividir dinámicamente lotes basados ​​en este límite para SQL Server; sin embargo, siempre verá los mejores resultados al administrar el tamaño de lote externo a este método.

Este método mostró una ganancia aproximada del 50% en el tiempo de ejecución sobre mi técnica anterior de usar una conexión compartida en una sola transacción para todas las inserciones.

Esta es un área donde Massive realmente brilla: Massive tiene un objeto Save (params object []) que crea una matriz de IDbCommands y ejecuta cada una de ellas en una conexión compartida. Funciona fuera de la caja y no se topa con los límites de los parámetros.

/// <summary>
/// Performs an SQL Insert against a collection of pocos
/// </summary>
/// <param name="pocos">A collection of POCO objects that specifies the column values to be inserted.  Assumes that every POCO is of the same type.</param>
/// <returns>An array of the auto allocated primary key of the new record, or null for non-auto-increment tables</returns>
/// <remarks>
///     NOTE: As of SQL Server 2012, there is a limit of 2100 parameters per query.  This limitation does not seem to apply on other platforms, so 
///           this method will allow more than 2100 parameters.  See http://msdn.microsoft.com/en-us/library/ms143432.aspx
///     The name of the table, it''s primary key and whether it''s an auto-allocated primary key are retrieved from the attributes of the first POCO in the collection
/// </remarks>
public object[] BulkInsert(IEnumerable<object> pocos)
{
    Sql sql;
    IList<PocoColumn> columns = new List<PocoColumn>();
    IList<object> parameters;
    IList<object> inserted;
    PocoData pd;
    Type primaryKeyType;
    object template;
    string commandText;
    string tableName;
    string primaryKeyName;
    bool autoIncrement;


    if (null == pocos)
        return new object[] {};

    template = pocos.First<object>();

    if (null == template)
        return null;

    pd = PocoData.ForType(template.GetType());
    tableName = pd.TableInfo.TableName;
    primaryKeyName = pd.TableInfo.PrimaryKey;
    autoIncrement = pd.TableInfo.AutoIncrement;

    try
    {
        OpenSharedConnection();
        try
        {
            var names = new List<string>();
            var values = new List<string>();
            var index = 0;
            foreach (var i in pd.Columns)
            {
                // Don''t insert result columns
                if (i.Value.ResultColumn)
                    continue;

                // Don''t insert the primary key (except under oracle where we need bring in the next sequence value)
                if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)
                {
                    primaryKeyType = i.Value.PropertyInfo.PropertyType;

                    // Setup auto increment expression
                    string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo);
                    if (autoIncExpression != null)
                    {
                        names.Add(i.Key);
                        values.Add(autoIncExpression);
                    }
                    continue;
                }

                names.Add(_dbType.EscapeSqlIdentifier(i.Key));
                values.Add(string.Format("{0}{1}", _paramPrefix, index++));
                columns.Add(i.Value);
            }

            string outputClause = String.Empty;
            if (autoIncrement)
            {
                outputClause = _dbType.GetInsertOutputClause(primaryKeyName);
            }

            commandText = string.Format("INSERT INTO {0} ({1}){2} VALUES",
                            _dbType.EscapeTableName(tableName),
                            string.Join(",", names.ToArray()),
                            outputClause
                            );

            sql = new Sql(commandText);
            parameters = new List<object>();
            string valuesText = string.Concat("(", string.Join(",", values.ToArray()), ")");
            bool isFirstPoco = true;

            foreach (object poco in pocos)
            {
                parameters.Clear();
                foreach (PocoColumn column in columns)
                {
                    parameters.Add(column.GetValue(poco));
                }

                sql.Append(valuesText, parameters.ToArray<object>());

                if (isFirstPoco)
                {
                    valuesText = "," + valuesText;
                    isFirstPoco = false;
                }
            }

            inserted = new List<object>();

            using (var cmd = CreateCommand(_sharedConnection, sql.SQL, sql.Arguments))
            {
                if (!autoIncrement)
                {
                    DoPreExecute(cmd);
                    cmd.ExecuteNonQuery();
                    OnExecutedCommand(cmd);

                    PocoColumn pkColumn;
                    if (primaryKeyName != null && pd.Columns.TryGetValue(primaryKeyName, out pkColumn))
                    {
                        foreach (object poco in pocos)
                        {
                            inserted.Add(pkColumn.GetValue(poco));
                        }
                    }

                    return inserted.ToArray<object>();
                }

                // BUG: the following line reportedly causes duplicate inserts; need to confirm
                //object id = _dbType.ExecuteInsert(this, cmd, primaryKeyName);

                using(var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        inserted.Add(reader[0]);
                    }
                }

                object[] primaryKeys = inserted.ToArray<object>();

                // Assign the ID back to the primary key property
                if (primaryKeyName != null)
                {
                    PocoColumn pc;
                    if (pd.Columns.TryGetValue(primaryKeyName, out pc))
                    {
                        index = 0;
                        foreach(object poco in pocos)
                        {
                            pc.SetValue(poco, pc.ChangeType(primaryKeys[index]));
                            index++;
                        }
                    }
                }

                return primaryKeys;
            }
        }
        finally
        {
            CloseSharedConnection();
        }
    }
    catch (Exception x)
    {
        if (OnException(x))
            throw;
        return null;
    }
}

Answer 2

Aquí está el código para BulkInsert que puede agregar a v5.01 PetaPoco.cs

Puede pegarlo en algún lugar cerca de la inserción regular en la línea 1098

Le das un IEnumerable de Pocos y lo enviará a la base de datos.

en lotes de x juntos. El código es el 90% de la inserción regular.

No tengo comparación de rendimiento, hágamelo saber :)

    /// <summary>
    /// Bulk inserts multiple rows to SQL
    /// </summary>
    /// <param name="tableName">The name of the table to insert into</param>
    /// <param name="primaryKeyName">The name of the primary key column of the table</param>
    /// <param name="autoIncrement">True if the primary key is automatically allocated by the DB</param>
    /// <param name="pocos">The POCO objects that specifies the column values to be inserted</param>
    /// <param name="batchSize">The number of POCOS to be grouped together for each database rounddtrip</param>        
    public void BulkInsert(string tableName, string primaryKeyName, bool autoIncrement, IEnumerable<object> pocos, int batchSize = 25)
    {
        try
        {
            OpenSharedConnection();
            try
            {
                using (var cmd = CreateCommand(_sharedConnection, ""))
                {
                    var pd = PocoData.ForObject(pocos.First(), primaryKeyName);
                    // Create list of columnnames only once
                    var names = new List<string>();
                    foreach (var i in pd.Columns)
                    {
                        // Don''t insert result columns
                        if (i.Value.ResultColumn)
                            continue;

                        // Don''t insert the primary key (except under oracle where we need bring in the next sequence value)
                        if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)
                        {
                            // Setup auto increment expression
                            string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo);
                            if (autoIncExpression != null)
                            {
                                names.Add(i.Key);
                            }
                            continue;
                        }
                        names.Add(_dbType.EscapeSqlIdentifier(i.Key));
                    }
                    var namesArray = names.ToArray();

                    var values = new List<string>();
                    int count = 0;
                    do
                    {
                        cmd.CommandText = "";
                        cmd.Parameters.Clear();
                        var index = 0;
                        foreach (var poco in pocos.Skip(count).Take(batchSize))
                        {
                            values.Clear();
                            foreach (var i in pd.Columns)
                            {
                                // Don''t insert result columns
                                if (i.Value.ResultColumn) continue;

                                // Don''t insert the primary key (except under oracle where we need bring in the next sequence value)
                                if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)
                                {
                                    // Setup auto increment expression
                                    string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo);
                                    if (autoIncExpression != null)
                                    {
                                        values.Add(autoIncExpression);
                                    }
                                    continue;
                                }

                                values.Add(string.Format("{0}{1}", _paramPrefix, index++));
                                AddParam(cmd, i.Value.GetValue(poco), i.Value.PropertyInfo);
                            }

                            string outputClause = String.Empty;
                            if (autoIncrement)
                            {
                                outputClause = _dbType.GetInsertOutputClause(primaryKeyName);
                            }

                            cmd.CommandText += string.Format("INSERT INTO {0} ({1}){2} VALUES ({3})", _dbType.EscapeTableName(tableName),
                                                             string.Join(",", namesArray), outputClause, string.Join(",", values.ToArray()));
                        }
                        // Are we done?
                        if (cmd.CommandText == "") break;
                        count += batchSize;
                        DoPreExecute(cmd);
                        cmd.ExecuteNonQuery();
                        OnExecutedCommand(cmd);
                    }
                    while (true);

                }
            }
            finally
            {
                CloseSharedConnection();
            }
        }
        catch (Exception x)
        {
            if (OnException(x))
                throw;
        }
    }


    /// <summary>
    /// Performs a SQL Bulk Insert
    /// </summary>
    /// <param name="pocos">The POCO objects that specifies the column values to be inserted</param>        
    /// <param name="batchSize">The number of POCOS to be grouped together for each database rounddtrip</param>        
    public void BulkInsert(IEnumerable<object> pocos, int batchSize = 25)
    {
        if (!pocos.Any()) return;
        var pd = PocoData.ForType(pocos.First().GetType());
        BulkInsert(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, pd.TableInfo.AutoIncrement, pocos);
    }

Answer 3

Aquí está la versión actualizada de la respuesta de Steve Jansen que se divide en funciones de un máximo de 2100 pacos

Comenté el siguiente código, ya que produce duplicados en la base de datos ...

                //using (var reader = cmd.ExecuteReader())
                //{
                //    while (reader.Read())
                //    {
                //        inserted.Add(reader[0]);
                //    }
                //}

Código actualizado

    /// <summary>
    /// Performs an SQL Insert against a collection of pocos
    /// </summary>
    /// <param name="pocos">A collection of POCO objects that specifies the column values to be inserted.  Assumes that every POCO is of the same type.</param>
    /// <returns>An array of the auto allocated primary key of the new record, or null for non-auto-increment tables</returns>
    public object BulkInsert(IEnumerable<object> pocos)
    {
        Sql sql;
        IList<PocoColumn> columns = new List<PocoColumn>();
        IList<object> parameters;
        IList<object> inserted;
        PocoData pd;
        Type primaryKeyType;
        object template;
        string commandText;
        string tableName;
        string primaryKeyName;
        bool autoIncrement;

        int maxBulkInsert;

        if (null == pocos)
        {
            return new object[] { };
        }

        template = pocos.First<object>();

        if (null == template)
        {
            return null;
        }

        pd = PocoData.ForType(template.GetType());
        tableName = pd.TableInfo.TableName;
        primaryKeyName = pd.TableInfo.PrimaryKey;
        autoIncrement = pd.TableInfo.AutoIncrement;

        //Calculate the maximum chunk size
        maxBulkInsert = 2100 / pd.Columns.Count;
        IEnumerable<object> pacosToInsert = pocos.Take(maxBulkInsert);
        IEnumerable<object> pacosremaining = pocos.Skip(maxBulkInsert);

        try
        {
            OpenSharedConnection();
            try
            {
                var names = new List<string>();
                var values = new List<string>();
                var index = 0;

                foreach (var i in pd.Columns)
                {
                    // Don''t insert result columns
                    if (i.Value.ResultColumn)
                        continue;

                    // Don''t insert the primary key (except under oracle where we need bring in the next sequence value)
                    if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)
                    {
                        primaryKeyType = i.Value.PropertyInfo.PropertyType;

                        // Setup auto increment expression
                        string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo);
                        if (autoIncExpression != null)
                        {
                            names.Add(i.Key);
                            values.Add(autoIncExpression);
                        }
                        continue;
                    }

                    names.Add(_dbType.EscapeSqlIdentifier(i.Key));
                    values.Add(string.Format("{0}{1}", _paramPrefix, index++));
                    columns.Add(i.Value);
                }

                string outputClause = String.Empty;
                if (autoIncrement)
                {
                    outputClause = _dbType.GetInsertOutputClause(primaryKeyName);
                }

                commandText = string.Format("INSERT INTO {0} ({1}){2} VALUES",
                                _dbType.EscapeTableName(tableName),
                                string.Join(",", names.ToArray()),
                                outputClause
                                );

                sql = new Sql(commandText);
                parameters = new List<object>();
                string valuesText = string.Concat("(", string.Join(",", values.ToArray()), ")");
                bool isFirstPoco = true;
                var parameterCounter = 0;

                foreach (object poco in pacosToInsert)
                {
                    parameterCounter++;
                    parameters.Clear();

                    foreach (PocoColumn column in columns)
                    {
                        parameters.Add(column.GetValue(poco));
                    }

                    sql.Append(valuesText, parameters.ToArray<object>());

                    if (isFirstPoco && pocos.Count() > 1)
                    {
                        valuesText = "," + valuesText;
                        isFirstPoco = false;
                    }
                }

                inserted = new List<object>();

                using (var cmd = CreateCommand(_sharedConnection, sql.SQL, sql.Arguments))
                {
                    if (!autoIncrement)
                    {
                        DoPreExecute(cmd);
                        cmd.ExecuteNonQuery();
                        OnExecutedCommand(cmd);

                        PocoColumn pkColumn;
                        if (primaryKeyName != null && pd.Columns.TryGetValue(primaryKeyName, out pkColumn))
                        {
                            foreach (object poco in pocos)
                            {
                                inserted.Add(pkColumn.GetValue(poco));
                            }
                        }

                        return inserted.ToArray<object>();
                    }

                    object id = _dbType.ExecuteInsert(this, cmd, primaryKeyName);

                    if (pacosremaining.Any())
                    {
                        return BulkInsert(pacosremaining);
                    }

                    return id;

                    //using (var reader = cmd.ExecuteReader())
                    //{
                    //    while (reader.Read())
                    //    {
                    //        inserted.Add(reader[0]);
                    //    }
                    //}

                    //object[] primaryKeys = inserted.ToArray<object>();

                    //// Assign the ID back to the primary key property
                    //if (primaryKeyName != null)
                    //{
                    //    PocoColumn pc;
                    //    if (pd.Columns.TryGetValue(primaryKeyName, out pc))
                    //    {
                    //        index = 0;
                    //        foreach (object poco in pocos)
                    //        {
                    //            pc.SetValue(poco, pc.ChangeType(primaryKeys[index]));
                    //            index++;
                    //        }
                    //    }
                    //}

                    //return primaryKeys;
                }
            }
            finally
            {
                CloseSharedConnection();
            }
        }
        catch (Exception x)
        {
            if (OnException(x))
                throw;
            return null;
        }
    }

Answer 4

Aquí hay una buena actualización de 2018 usando FastMember de NuGet:

    private static void SqlBulkCopyPoco<T>(PetaPoco.Database db, IEnumerable<T> data)
    {
        var pd = PocoData.ForType(typeof(T), db.DefaultMapper);
        using (var bcp = new SqlBulkCopy(db.ConnectionString))
        using (var reader = ObjectReader.Create(data)) 
        {
            // set up a mapping from the property names to the column names
            var propNames = typeof(T).GetProperties().Select(propertyInfo => propertyInfo.Name).ToArray();
            foreach (var propName in propNames)
            {
                bcp.ColumnMappings.Add(propName, "[" + pd.GetColumnName(propName) + "]");
            }
            bcp.DestinationTableName = pd.TableInfo.TableName;
            bcp.WriteToServer(reader);
        }
    }

Answer 5

En mi caso, aproveché el método database.Execute() .

Creé un parámetro SQL que tenía la primera parte de mi inserción:

var sql = new Sql("insert into myTable(Name, Age, Gender) values");

for (int i = 0; i < pocos.Count ; ++i)
{
   var p = pocos[i];
   sql.Append("(@0, @1, @2)", p.Name, p.Age , p.Gender);
   if(i != pocos.Count -1)
     sql.Append(",");
}

Database.Execute(sql);

Answer 6

Insertar en una consulta SQL es mucho más rápido .

Aquí hay un método del cliente para la clase PetaPoco.Database que agrega la capacidad de hacer una inserción masiva de cualquier colección:

public void BulkInsertRecords<T>(IEnumerable<T> collection)
        {
            try
            {
                OpenSharedConnection();
                using (var cmd = CreateCommand(_sharedConnection, ""))
                {
                    var pd = Database.PocoData.ForType(typeof(T));
                    var tableName = EscapeTableName(pd.TableInfo.TableName);
                    string cols = string.Join(", ", (from c in pd.QueryColumns select tableName + "." + EscapeSqlIdentifier(c)).ToArray());
                    var pocoValues = new List<string>();
                    var index = 0;
                    foreach (var poco in collection)
                    {
                        var values = new List<string>();
                        foreach (var i in pd.Columns)
                        {
                            values.Add(string.Format("{0}{1}", _paramPrefix, index++));
                            AddParam(cmd, i.Value.GetValue(poco), _paramPrefix);
                        }
                        pocoValues.Add("(" + string.Join(",", values.ToArray()) + ")");
                    }
                    var sql = string.Format("INSERT INTO {0} ({1}) VALUES {2}", tableName, cols, string.Join(", ", pocoValues));
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                CloseSharedConnection();
            }
        }

Answer 7

Probé dos métodos diferentes para insertar una gran cantidad de filas más rápido que el Insertar predeterminado (que es bastante lento cuando tienes muchas filas).

1) Creando una Lista <T> con el primero de los poco y luego insertándolos a la vez dentro de un bucle (y en una transacción):

using (var tr = PetaPocoDb.GetTransaction())
{
    foreach (var record in listOfRecords)
    {
        PetaPocoDb.Insert(record);
    }
    tr.Complete();
}

2) SqlBulkCopy una tabla de datos:

var bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock);
bulkCopy.DestinationTableName = "SomeTable";
bulkCopy.WriteToServer(dt);

Para obtener mi lista <T> en una tabla de datos, utilicé Marc Gravells. ¿ Convertir lista genérica / enumerable a tabla de datos? función que funcionó ootb para mí (después de haber reorganizado las propiedades de Poco para estar exactamente en el mismo orden que los campos de la tabla en la base de datos).

La SqlBulkCopy fue más rápida, 50% más o menos que el método de transacciones en las pruebas de rendimiento (rápidas) que hice con ~ 1000 filas.

Hth

Answer 8

Usted puede simplemente hacer un foreach en sus registros.

foreach (var record in records) {
    db.Save(record);
}

Answer 9

Y en las mismas líneas si quieres BulkUpdate:

public void BulkUpdate<T>(string tableName, string primaryKeyName, IEnumerable<T> pocos, int batchSize = 25)
{
    try
    {
        object primaryKeyValue = null;

        OpenSharedConnection();
        try
        {
            using (var cmd = CreateCommand(_sharedConnection, ""))
            {
                var pd = PocoData.ForObject(pocos.First(), primaryKeyName);

                int count = 0;
                do
                {
                    cmd.CommandText = "";
                    cmd.Parameters.Clear();
                    var index = 0;

                    var cmdText = new StringBuilder();

                    foreach (var poco in pocos.Skip(count).Take(batchSize))
                    {
                        var sb = new StringBuilder();
                        var colIdx = 0;
                        foreach (var i in pd.Columns)
                        {
                            // Don''t update the primary key, but grab the value if we don''t have it
                            if (string.Compare(i.Key, primaryKeyName, true) == 0)
                            {
                                primaryKeyValue = i.Value.GetValue(poco);
                                continue;
                            }

                            // Dont update result only columns
                            if (i.Value.ResultColumn)
                                continue;

                            // Build the sql
                            if (colIdx > 0)
                                sb.Append(", ");
                            sb.AppendFormat("{0} = {1}{2}", _dbType.EscapeSqlIdentifier(i.Key), _paramPrefix,
                                            index++);

                            // Store the parameter in the command
                            AddParam(cmd, i.Value.GetValue(poco), i.Value.PropertyInfo);
                            colIdx++;
                        }

                        // Find the property info for the primary key
                        PropertyInfo pkpi = null;
                        if (primaryKeyName != null)
                        {
                            pkpi = pd.Columns[primaryKeyName].PropertyInfo;
                        }


                        cmdText.Append(string.Format("UPDATE {0} SET {1} WHERE {2} = {3}{4};/n",
                                                     _dbType.EscapeTableName(tableName), sb.ToString(),
                                                     _dbType.EscapeSqlIdentifier(primaryKeyName), _paramPrefix,
                                                     index++));
                        AddParam(cmd, primaryKeyValue, pkpi);
                    }

                    if (cmdText.Length == 0) break;

                    if (_providerName.IndexOf("oracle", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        cmdText.Insert(0, "BEGIN/n");
                        cmdText.Append("/n END;");
                    }

                    DoPreExecute(cmd);

                    cmd.CommandText = cmdText.ToString();
                    count += batchSize;
                    cmd.ExecuteNonQuery();
                    OnExecutedCommand(cmd);

                } while (true);
            }
        }
        finally
        {
            CloseSharedConnection();
        }
    }
    catch (Exception x)
    {
        if (OnException(x))
            throw;
    }
}