23 ago 2011

BulkCopy carga masiva en una tabla

Tengo un archivo de texto con más de 250 mil líneas. Necesito insertar cada una de éstas como registros en una tabla, el proceso debe realizarse usando C#... Hay una clase interesante dentro del namespace System.Data.SqlClient que nos puede ayudar bastante.

Obviamente en un caso como este el insertar línea por línea en la base de datos queda descartado, sería demasiado ineficiente. Entonces necesitamos tomar conjuntos de registros e insertarlos de un solo golpe. Es aqui donde BulkCopy nos puede salvar.

Para el ejemplo de continuación contamos con una tabla de trabajo que contiene dos columnas, una es un ID y la otra es donde almacenaremos el contenido de cada linea del archivo. La columna ID funcionada como llave. Todo en SQL Server 2005.

Para cargar la información primero necesitamos definir un método por medio de cual leamos el archivo y en memoria lo guardemos en un datatable:

static DataTable TablaTemporal()
{
    string linea = string.Empty;

    double registro = 1;
    DataTable dt = new DataTable();
    DataRow dr = null;
    DataColumn dc = null;

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.Double");
    dc.ColumnName = "id";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "registro";
    dc.Unique = false;
    dt.Columns.Add(dc);

    string filePath = @"\\carpetaDFS\archivoTEST.txt";
    Stopwatch st = new Stopwatch();

    try
    {
        if (File.Exists(filePath))
        {
            st.Start();
            using (StreamReader sr = new StreamReader(filePath))
            {
                while (!sr.EndOfStream)
                {
                    dr = dt.NewRow();
                    linea = sr.ReadLine();
                    dr[0] = registro;
                    dr[1] = linea;
                    dt.Rows.Add(dr);
                    registro += 1;
                }
            }
            st.Stop();
            string tiempoCarga = (st.ElapsedMilliseconds / 1000).ToString();
            Console.WriteLine("Registros cargados en memoria: {0} en {1} segundos.", registro.ToString("N2") , tiempoCarga);
            return dt;
        }
        else
        {
            Console.WriteLine("No fue posible encontrar el archivo: " + filePath);
            return null;
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Se ha producido un error en la creacion de la tabla temporal " + ex.Message);
        return null;
    }
}

Simplemente iteramos por sobre las lineas del archivo y guardamos cada una como un datarow dentro un datatable en memoria. Ahora necesitamos usar esta tabla dentro de un método para carga la información en la base de datos.


static void CargaArchivo()
{
    Stopwatch st = new Stopwatch();
   
    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-CR", false);
    string conexion = "data source=tcp:servidor,puerto; initial catalog=DBDEFAULT; user id=usuario; password=pass";
    SqlConnection conn = new SqlConnection(conexion);

    try
    {
        conn.Open();

        //preparando los parametros del BULK Copy
        SqlBulkCopy bulkCopy = new SqlBulkCopy(conexion, SqlBulkCopyOptions.TableLock);
        bulkCopy.BatchSize = 25000;
        bulkCopy.BulkCopyTimeout = 300;
        bulkCopy.DestinationTableName = "TABLA_DE_TRABAJO"; //Nombre de la tabla en la DB

        //Cargamos una datatable con todas las lineas del archivo por medio de esta funcion
        DataTable tablaCarga = TablaTemporal();


        if (tablaCarga != null)
        {// si la tabla no es nula y tiene filas
            if (tablaCarga.Rows.Count > 0)
            {
                st.Start();
                //Cargamos en la tabla de trabajo
                bulkCopy.WriteToServer(tablaCarga);
                st.Stop();
                string tiempoCarga = (st.ElapsedMilliseconds / 1000).ToString();
                Console.WriteLine("Registros cargados en la tabla de trabajo en {0} segundos.", tiempoCarga );
            }
            else
            {
                Console.WriteLine("El archivo no tiene lineas");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Se produjo un error: " + ex.Message);
    }
    finally
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
}

Analicemos el código anterior. Primero establecemos una conexión con nuestra base de datos, la abrimos y seguidamente establecemos los parámetros de nuestro SqlBulkCopy usamos la función creada previamente  para cargar en memoria un datatable con la información del archivo y si esta no devuelve nulo procedemos a cargar en base datos por medio del método WriteToServer.  Al Finalizar cerramos la conexión,

Utilizamos la clase  Stopwatch de System.Diagnostics  para medir el tiempo de procesamiento de nuestro código.


Como se puede apreciar más de 275 mil lineas se cargan en memoria en alrededor de 35 segundos y esta misma cantidad se guardan en la base de datos en 5 segundos. En el archivo que utilizamos cada linea del archivo tiene una longitud de 136 caracteres.

Algo importante a tomar en cuenta es el borrado de la tabla de carga. Probablemente luego de la carga del archivo se dispare algún procedimiento almacenado o algún mecanismo similar para procesar la información cargada en la tabla y una vez finalizado limpiar la tabla para la próxima carga. Lo más eficiente sería usar un TRUNCATE TABLE TABLA_DE_TRABAJO pero se debe tomar en cuenta que el usuario de base de datos debe tener permiso de ALTER sobre la tabla y vigilar el log de transacciones para adaptar su tamaño si se llena.

Roy {aka. Foy}

Autor & Editor

Desarrallador y líder técnico, con experiencia en tecnologías Microsoft desde los tiempos del VB6 y el asp clásico hasta el .Net Core, pasando por COM+, javascript, angularjs, Ionic, xaml, cordova, MVC, Web Api, Sql Server, Oracle... . Ávido lector, apasionado programador.