net - sqlbulkcopy c# ejemplo

Actualización masiva en C#

Question

Para insertar una gran cantidad de datos en una base de datos, solía recopilar toda la información de inserción en una lista y convertir esta lista en un DataTable . Luego inserto esa lista en una base de datos a través de SqlBulkCopy .

Donde envio mi lista generada
LiMyList
que contienen información de todos los datos masivos que deseo insertar en la base de datos
y pasarlo a mi operación de inserción masiva

InsertData(LiMyList, "MyTable");

Donde InsertData es

 public static void InsertData<T>(List<T> list,string TableName)
        {
                DataTable dt = new DataTable("MyTable");
                clsBulkOperation blk = new clsBulkOperation();
                dt = ConvertToDataTable(list);
                ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
                {
                    bulkcopy.BulkCopyTimeout = 660;
                    bulkcopy.DestinationTableName = TableName;
                    bulkcopy.WriteToServer(dt);
                }
        }    

public static DataTable ConvertToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }

Ahora quiero realizar una operación de actualización, ¿hay alguna manera de insertar datos que se realiza mediante SqlBulkCopy para actualizar datos a DataBase desde C # .Net?

Answer 1

En mi experiencia personal, la mejor manera de manejar esta situación es utilizar un procedimiento almacenado con un Table-Valued Parameter y un User-Defined Table Type . Simplemente configure el tipo con las columnas de la tabla de datos y pase dicha tabla de datos como un parámetro en el comando SQL.

Dentro del Procedimiento almacenado, puede unirse directamente a alguna clave única (si existen todas las filas que está actualizando), o, si se encuentra en una situación en la que tenga que hacer tanto actualizaciones como inserciones, use el comando Merge SQL dentro de el procedimiento almacenado para manejar tanto las actualizaciones como las inserciones, según corresponda.

Microsoft tiene una referencia de sintaxis y un artículo con ejemplos para la combinación.

Para la pieza .NET, es una simple cuestión de establecer el tipo de parámetro como SqlDbType.Structured y establecer el valor de dicho parámetro en la Tabla de datos que contiene los registros que desea actualizar.

Este método proporciona el beneficio de la claridad y la facilidad de mantenimiento. Si bien puede haber formas que ofrezcan mejoras en el rendimiento (como colocarlo en una tabla temporal y luego iterar sobre esa tabla), creo que están superados por la simplicidad de permitir que .NET y SQL manejen la transferencia de la tabla y la actualización de los registros. BESO

Answer 2

Lo que he hecho antes es realizar una inserción masiva de los datos en una tabla temporal y luego usar un comando o procedimiento almacenado para actualizar los datos que relacionan la tabla temporal con la tabla de destino. La tabla temporal es un paso adicional, pero puede obtener una ganancia de rendimiento con la inserción masiva y la actualización masiva si la cantidad de filas es grande, en comparación con la actualización de los datos fila por fila.

Ejemplo:

public static void UpdateData<T>(List<T> list,string TableName)
{
    DataTable dt = new DataTable("MyTable");
    dt = ConvertToDataTable(list);

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
    {
        using (SqlCommand command = new SqlCommand("", conn))
        {
            try
            {
                conn.Open();

                //Creating temp table on database
                command.CommandText = "CREATE TABLE #TmpTable(...)";
                command.ExecuteNonQuery();

                //Bulk insert into temp table
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                {
                    bulkcopy.BulkCopyTimeout = 660;
                    bulkcopy.DestinationTableName = "#TmpTable";
                    bulkcopy.WriteToServer(dt);
                    bulkcopy.Close();
                }

                // Updating destination table, and dropping temp table
                command.CommandTimeout = 300;
                command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // Handle exception properly
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

Observe que se usa una sola conexión para realizar la operación completa, para poder usar la tabla temporal en cada paso, porque el alcance de la tabla temporal es por conexión.

Answer 3

Me gustaría insertar nuevos valores en una tabla temporal y luego hacer una combinación contra la tabla de destino, algo como esto:

MERGE [DestTable] AS D 
USING #SourceTable S
    ON D.ID = S.ID
WHEN MATCHED THEN 
    UPDATE SET ...
WHEN NOT MATCHED 
THEN INSERT (...) 
VALUES (...);

Answer 4

Me gustaría un enfoque de TempTable porque de esa manera no estás bloqueando nada. Pero si su lógica solo necesita estar en el extremo frontal y necesita usar una copia masiva, probaría un enfoque de Eliminar / Insertar pero en la misma Transacción Sql para asegurar la integridad, que sería algo como esto:

// ...

dt = ConvertToDataTable(list);

using (SqlConnection cnx = new SqlConnection(myConnectionString))
{
    using (SqlTranscation tran = cnx.BeginTransaction())
    {
        DeleteData(cnx, tran, list);

        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cnx, SqlBulkCopyOptions.Default, tran))
        {
            bulkcopy.BulkCopyTimeout = 660;
            bulkcopy.DestinationTableName = TabelName;
            bulkcopy.WriteToServer(dt);
        }

        tran.Commit();
    }
}

Answer 5

No estoy seguro de haber captado el punto que va a archivar ... Si su pregunta es sobre el reemplazo rápido de todo el contenido de la tabla, entonces no lo haría por truncate ( http://technet.microsoft.com/en-us/library/ms177570.aspx ) y la inserción masiva de una nueva porción de datos. Pero este enfoque solo funcionará en caso de que no tenga restricciones de clave externa.

Si desea una actualización real, busque la respuesta de Guillermo Gutiérrez a continuación.

Answer 6

Pruebe SqlBulkTools disponible en Nuget.

Descargo de responsabilidad: Soy el autor de esta biblioteca.

var bulk = new BulkOperations();
var records = GetRecordsToUpdate();

using (TransactionScope trans = new TransactionScope())
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager
    .ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
    {
        bulk.Setup<MyTable>()
            .ForCollection(records)
            .WithTable("MyTable")
            .AddColumn(x => x.SomeColumn1)
            .AddColumn(x => x.SomeColumn2)
            .BulkUpdate()
            .MatchTargetOn(x => x.Identifier)
            .Commit(conn);
    }

    trans.Complete();
}  

Solo se actualizarán ''SomeColumn1'' y ''SomeColumn2''. Más ejemplos se pueden encontrar here

Answer 7

Puedes intentar construir una consulta que contenga todos los datos. Usa un case . Podría verse así

update your_table
set some_column = case when id = 1 then ''value of 1''
                       when id = 5 then ''value of 5''
                       when id = 7 then ''value of 7''
                       when id = 9 then ''value of 9''
                  end
where id in (1,5,7,9)