Insertar mas de 1000 registros en SQL Server con C#

Posted on Posted in .Net, SQL, SQL Server, Uncategorized

Existen varias ocasiones donde normalmente se necesita insertar mas de 1000 registros en SQL Server al mismo tiempo, o quizá menos de 1000 pero si una cantidad considerable

Y normalmente tienes que asegurarte que se inserten todos o ninguno, y hacerlo creando el query en un string para después ejecutarlo no es la mejor manera, te puede traer muchos problemas de cabeza al tratar de escapar las comillas, al debuggear el string infinito, al tratar de asegurar que se inserten todos, etc.

Ya sea que estés por subir la información inicial para que un sistema que acabas de hacer empiece a funcionar o porque tus usuarios trabajan datos en Excel y después los quieren subir al sistema, o tienes un conjunto enorme de entidades y subirlo de uno por uno es arriesgado, en fin, existe una manera de subirlos de insertar mas de 1000 registros en SQL Server de UN SOLO GOLPE.

Como funciona?

Básicamente el código lo que hará es mapear una tabla en la base de datos en SQL Server contra un DataTable que nosotros generaremos en este caso.

Primero, necesitaremos crear la infraestructura de la base de datos, por simplicidad las columnas en el DataTable van a tener el mismo nombre que en la tabla en la base de datos.

private static DataTable GetTableSchema()
{
    var dt = new DataTable();

    dt.Columns.Add("LlaveForanea", typeof(int));
    dt.Columns.Add("MiTexto", typeof(string));
    dt.Columns.Add("EstaActivo", typeof(bool));

    return dt;
}

Después meteremos los registros que queramos subir en el DataTable, en este ejemplo, puedes pasar un parámetro que cumpla con la interfaz IEnumerable como por ejemplo un List<MiEntidad>

private static DataTable ConvertListToDataTable(IEnumerable<MiEntidad> lista)
{	
    var dt = GetTableSchema();

    foreach (var entidad in lista)
    {
        dt.Rows.Add(new object[] {
            entidad.LlaveForanea,
            entidad.MiTexto,
            entidad.EstaActivo,
        });
    }

    return dt;
}

Crearemos un método para ligar las columnas del DataTable a la tabla de SQL Server

private static List<SqlBulkCopyColumnMapping> GetColumnMappings(DataTable dt)
{
    var columnsMapping = new List<SqlBulkCopyColumnMapping>();

    foreach (DataColumn column in dt.Columns)
        columnsMapping.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));

    return columnsMapping;
}

Ahora es hora de abrir la conexión y le pasamos la transacción al objeto SqlBulkCopy.

La transacción nos asegurara que se pueda insertar mas de 1000 registros en SQL Server correctamente o en su defecto ninguno.

public static bool InsertAll(IEnumerable<MiEntidad> lista)
{
    var cadenaDeConexion = "Server=...User=...Password=...";
    var nombreDeLaTablaEnSQLServer = "MiEntidad";

    var miDataTable = ConvertListToDataTable(lista);

    var conn = new SqlConnection(connectionString);
    conn.Open();
    using (var transaction = conn.BeginTransaction())
    {
        using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
        {
            bulkCopy.DestinationTableName = nombreDeLaTablaEnSQLServer;
            bulkCopy.ColumnMappings.Clear();
            var columnMappings = GetColumnMappings(miDataTable);
            foreach (var mapping in columnMappings) bulkCopy.ColumnMappings.Add(mapping);
            bulkCopy.WriteToServer(miDataTable);
        }
        transaction.Commit();
    }
    return true;
}

Listo, con eso debería de ser suficiente; obviamente si ya tienes el DataTable creado puede ser mucho mas fácil, o incluso puedes llenar de otra forma el DataTable, de cualquier forma, es solamente un ejemplo para saber como usar el SqlBulkCopy y el BeginTransaction, que al final de cuentas son los que hacen la magia.