Insertar múltiples filas (Datos masivos) C#, WinForm, SQL, Bulk Copy, TVP, Transact -SQL, Commit y Rollback

Tutoriales de Programación y Desarrollo de software

Insertar múltiples filas (Datos masivos) C#, WinForm, SQL, Bulk Copy, TVP, Transact -SQL, Commit y Rollback

Introducción

Hola, en esta oportunidad aprenderemos a como insertar múltiples filas o inserción masiva en una única transacción desde una datagridview y lista de objetos, principalmente es necesario para insertar los detalles de una tabla, el más común es el detalle de venta, usaremos lenguaje C#, Windows form, SQL Server y full programación orientada a objetos (POO).

Bueno existen muchas maneras de hacerlo, desde un nivel básico, intermedio, avanzado, eficiente, no eficiente, con buenas prácticas o malas prácticas, y siendo sincero, fue difícil elegir que método usar en este tutorial, ya que muchos apenas están iniciando en el mundo de la programación, y otros ya tienen conocimientos sólidos y avanzados. Sin embargo realizar tutoriales básicos, conlleva a malas prácticas, poco eficientes y menos seguros.

Por ejemplo, en muchas ocasiones cuando somos principiantes hacemos esto:

    public void addErrorBasic_MultipleStatementsSingleInsert(IEnumerable<Entity> entities)
    {
        foreach (var item in entities)
        {
            var connection = ConnectionToSql.getConnection();
            connection.Open();//Open connection to sql server
            var command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = "insert into saleDetail values ('" + item.description + "'," + item.units + "," + item.amount + ")";
            command.ExecuteNonQuery();
            connection.Close();//Close connection to sql server
        }
    }

Esto es un grave error!

La razón es simple, lo describiré por bloques:

Administrar la Conexión a SQL Server

El primer error es abrir y cerrar la conexión en cada ciclo, eso tiene un grave impacto en la base de datos, del mismo modo ocurre si ejecutas el ciclo desde la capa de presentación o negocio, ten en cuenta que el ciclo siempre lo debes de ejecutar en la capa de acceso a datos.

Consultas parametrizadas (@parámetro)

El segundo error, es concatenar los valores en el comando de texto, principalmente esto conlleva 2 inconvenientes: Primero,  Estamos introduciendo agujeros de seguridad, es decir, es totalmente vulnerable a la inyección SQL.  Segundo, tenemos que ocuparnos de cosas como el formato adecuado de las fechas y poner comillas alrededor del texto y eso resulta  tedioso, para solucionar el inconveniente debemos de usar consultas parametrizadas, es decir usar parámetros (@parameter).

sqlCommand.CommandText = "insert into table values (@Parameter)"; 
sqlCommand.Parameters.AddWithValue("@Parameter", valor);

SQL- Commit Transaction y RollBack Transaction

Al no utilizar una transacción SQL, no garantiza que todos los datos se inserten correctamente, por ejemplo, supongamos que insertaremos 10 mil filas, y en uno de los campos insertamos datos incorrectos o nulos, entonces puede pasar el caso de que solo se insertaron 5 mil filas y 5 mil filas no fueron insertados, eso seria un grave problema. Para solventar el problema, debemos de envolver todas las instrucciones sql en una transacción y volcar los datos en una única transacción, con COMMIT TRANSACTION. Si en caso que ocurra cualquier tipo de error inesperado, simplemente revertimos todo con ROLLBACK TRANSACTION, es decir, borrará todo y no se insertará ninguna fila, imagina que es como regresar en el tiempo, y dejarlo tal como estaba antes de empezar la transacción.

Parámetros SQL y tipo de datos

Por otro lado, muchos programadores prohíben el uso del método AddWithValue(), estoy en desacuerdo en ciertas maneras, si bien es cierto que tiene un inconveniente, es que el método AddWithValue(), tiene que inferir el tipo de dato en la base de datos para su parámetro de consulta, y en muchas ocasiones esta conversión se equivoca, además que la operación resulta costosa si se tiene miles y miles de filas en una tabla.

Por lo tanto la solución es conocer el tipo de dato y luego crear un parámetro de consulta que use este tipo exacto.

sqlCommand.Parameters.Add("@Parameter", SqlDbType.DateTime).Value = MyDateTimeVariable;

Obviamente no es obligatorio, podemos seguir usando directamente el método AddWithValue(), teniendo claro el tipo de dato y si se realiza pequeñas consultas, o la aplicación y la base de datos es pequeña, además no tiene sentido volver a declarar los parámetros si se usa procedimientos almacenados, ya que en ellos ya se declara los parámetros con los tipos de dato, en ese caso debemos usar el método AddWithValue().

Declaración Using

Los bloques Using garantiza ejecutar automáticamente los métodos desechables (Dispose();) de una instancia. Es decir, no necesitamos desechar explícitamente el objeto en el código, la declaración Using se encarga de ello. Por lo tanto no es necesario cerrar la conexión, o limpiar los parámetros de SqlCommand, ya que igualmente el objeto se eliminará al terminar de ejecutar los códigos dentro del bloque, y la conexión se cerrará, sin necesidad de invocar el método cerrar (Close();) , y esto sucederá incluso si se produce una excepción en los bloques try catch.

    using (var connection = new SqlConnection("server=(local); database=myDataBase; integrated security =true"))
    {  
        //Codes             
    }

Recomendaciones finales

Para insertar, editar o eliminar datos masivos (múltiples filas), ten en cuenta las siguientes recomendaciones:

  • Debemos de abrir la conexión una sola vez hasta que termine todas las instrucciones (antes del ciclo), y cerrar la conexión después de ejecutar todas las instrucciones (después del ciclo).
  • Debemos utilizar consultas parametrizadas.
  • Debemos utilizar los bloques Try-Catch.
  • Debemos envolver todas las instrucciones en una TRANSACTION, en ello usar Commit Transaction para guardar todos los cambios y RollBack Transaction, si ocurre un error y así poder revertir los cambios.
  • Si utilizas instrucciones SQL (Transact-SQL) para realizar grandes consultas en la aplicación, debes crear los parámetros con los tipos de datos.
  • Si usas procedimiento almacenado puedes utilizar el método AddWithValue() libremente, de igual manera para consultas pequeñas y si la base de datos es pequeña.

Siguiendo todas estas recomendaciones tendríamos de la siguiente manera:

Ejemplo- Inserción masiva con Instrucción SQL parametrizada

        public void add_MultipleStatementsSingleInsert(IEnumerable<Entity> entities)
        {
            using (var connection = ConnectionToSql.getConnection())
            {
                connection.Open();

                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    using (var command = new SqlCommand())
                    {
                        command.Connection = connection;
                        command.Transaction = transaction;
                        command.CommandType = CommandType.Text;
                        command.CommandText = "insert into saleDetail values (@desc,@units,@amount)";
                        command.Parameters.Add("@desc", SqlDbType.NVarChar, 50);
                        command.Parameters.Add("@units", SqlDbType.Int);
                        command.Parameters.Add("@amount", SqlDbType.Float);
                        try
                        {
                            foreach (var item in entities)
                            {
                                command.Parameters["@desc"].Value = item.description;
                                command.Parameters["@units"].Value = item.units;
                                command.Parameters["@amount"].Value = item.amount;
                                command.ExecuteNonQuery();
                            }
                            transaction.Commit();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            connection.Close();
                            throw;
                        }
                    }
                }
            }
        }

Maneras/ métodos de insertar múltiples filas

Existen muchas manera de realizar inserciones masivas, con buenas practicas, buen rendimiento y eficiencia, por ejemplo:

  • Mediante Instrucciones SQL parametrizadas con múltiples declaraciones INSERT e insertadas individualmente en cada bucle y envuelta en una única transacción (Ver ejemplo anterior).
  • Mediante Instrucciones SQL parametrizadas con múltiples declaraciones INSERT e insertadas en lote fuera del bucle en una única transacción, sin embargo, esta limitada a 2100 parámetros.
  • Mediante Instrucciones SQL parametrizadas en una sola declaración INSERT e insertadas en lote en una única transacción. Sin embargo, esta limitada a 2100 parámetros y 1000 inserciones de fila.
  • Mediante el método Update() de la clase DataAdapter.
  • Mediante Table-Valued Parameters (TVP), costo de inicio mínimo y puedes insertar hasta 1000 filas con buen rendimiento y rapidez, luego de superar las mil filas el rendimiento disminuye.
  • Mediante Bulk Insert, costo de inicio mayor, de igual manera el rendimiento disminuye rápidamente a medida que aumentan las filas.
  • Mediante SQL Bulk Copy, simplemente es necesario cargar los datos desde una DataTable o DataReader, puedes insertar hasta 700 mil filas con un rendimiento considerable, luego el rendimiento disminuye.
  • Mediante Entity Framework, Dapper, u otro ORM, que nos facilita y simplifica las cosas.

Al usar Instrucciones SQL parametrizadas ya sea con múltiples declaraciones o declaraciones en lote para realizar pequeñas operaciones de inserción de filas (menos de 100 filas), se obtiene un buen rendimiento y eficacia en lugar de operaciones Bulk Insert, Table-Valued Parameters o Sql Bulk Copy, pero estos métodos son recomendables para insertar cientos o miles de filas. Sin embargo, el rendimiento disminuye a medida que aumentan las filas.

Tutorial


Inserción masiva con SqlBulkCopy, C#, SQL y Windows Form

Bien, empecemos con el tutorial, en esta ocasión les enseñaré como insertar múltiples filas o inserción masiva con la clase SqlBulkCopy.

Base de datos

create database myDataBase
go
	use myDataBase
go
create table saleDetail
(
	id int identity (1,1) primary key,
	description nvarchar (50) not null,
	units int not null,
	amount float not null
)

Aplicación

Clase Conexión

using System.Data.SqlClient;
    class ConnectionToSql
    {
        public static SqlConnection getConnection()
        {
            return new SqlConnection("server=(local); database=myDataBase; integrated security =true");
        }
    }

Clase Modelo Detalle Venta

using System.Data;
using System.Data.SqlClient;
    public class SaleDetail
    {
        //attributes
        public int id { get; set; }
        public string description { get; set; }
        public int units { get; set; }
        public float amount { get; set; }

        //methods
        public void insertMassiveData(IEnumerable<SaleDetail> detailsList)
        {
            //create table
            var table = new DataTable();
            table.Columns.Add("id", typeof(int));
            table.Columns.Add("description", typeof(string));
            table.Columns.Add("units", typeof(int));
            table.Columns.Add("amount", typeof(float));

            foreach (var itemDetail in detailsList)
            {
                table.Rows.Add(new object[]
                    {
                        itemDetail.id,
                        itemDetail.description,
                        itemDetail.units,
                        itemDetail.amount
                    });
            }

            //insert to db
            using (var connection = ConnectionToSql.getConnection())
            {
                connection.Open();

                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                    {
                        try
                        {
                            bulkCopy.DestinationTableName = "saleDetail";
                            bulkCopy.WriteToServer(table);
                            transaction.Commit();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            connection.Close();
                            throw;
                        }
                    }
                }
            }
        }

Diseño de Formulario – DataGridView

Código de Formulario

    private void Form1_Load(object sender, EventArgs e)
    {
        dataGridView1.AllowUserToAddRows = false;            
    }

    private void btnAdd_Click(object sender, EventArgs e)
    {
        List<SaleDetail> objectDetailList = new List<SaleDetail>();

        foreach (DataGridViewRow dgvRow in dataGridView1.Rows)
        {
            var detail = new SaleDetail()
            {
                description=Convert.ToString(dgvRow.Cells["description"].Value),
                units=Convert.ToInt32(dgvRow.Cells["units"].Value),
                amount=(float)Convert.ToDouble(dgvRow.Cells["amount"].Value)
            };
            objectDetailList.Add(detail);
        }

        SaleDetail detailModel = new SaleDetail();
        detailModel.insertMassiveData(objectDetailList);
        MessageBox.Show("Data inserted correctly"); 
    }

Si deseas agregar una fila cada vez que presiones el botón enter como en el vídeo tutorial realiza lo siguiente.

    private void dataGridView1_KeyDown(object sender, KeyEventArgs e)
    {
        if (e.KeyCode == Keys.Enter)
        {
            dataGridView1.Rows.Add();
            dataGridView1.CurrentCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0];
        }
    }

Ver Video Tutorial

https://youtu.be/fne9TMDlLN4
5 4 votes
Article Rating
Suscribir
Notificar a
5 Comments
más antiguo
Lo más nuevo más votado
Inline Feedbacks
View all comments
leodan

Hola Buenas noches, Primeramento quiero felicitarles por los tutoriales que me han ayudado mucho, soy iniciante en vb.net. Que sigan publicando estos fantasticos totoriales, ya sea en c# o vb.net. Por favor, si puedem darme alguna idea en cuanto a este tema de insercion masiva de datos en sql utilizando datagridview. Estoy desarrollando un sistema en 4 capas utilizando el metodo store procedure en sql. Mi duda es la siguiente: tengo un form en la capa de presentacion con unos textbox los cuales se encargan de rellenar un datagridview, no mas que 50 filas, pero tampoco solamente una fila, es… Lee más...

wildibust

Gracias por el gran aporte, hay alguna forma de enviar campos vacíos de tipo DataTime a la base de datos, tengo el caso que no todos los campos Fecha están llenos, ahí me lanza el error siguiente: No se puede convertir un objeto DBNull en otros tipos

He intentado así
FECHA_RES_HEM = Convert.ToDateTime(dgvRow.Cells[«FECHA_RES_HEM»].Value),
Y Así
FECHA_RES_HEM = Convert.ToDateTime(dgvRow.Cells[«FECHA_RES_HEM»].Value == DBNull.Value ? DBNull.Value : dgvRow.Cells[«FECHA_RES_HEM»].Value),

FECHA_SOLIC_H = Convert.ToDateTime(dgvRow.Cells[«FECHA_SOLIC_H»].Value == DBNull.Value ? DBNull.Value : dgvRow.Cells[«FECHA_SOLIC_H»].Value),
El error es el mismo.

josegjimenez

Saludos Excelentes tutoriales, tendras alguno para exportar del datagrid a dos tablas de un bd? es decir; un datagrid tiene 6 columnas pero en una tabla quiero guardar 3 y en la otra 3 estan relacionadas.

landeropedro

Hola me gusto mucho este tema pudieran hacerlo con vb.net y sql server

5
0
Would love your thoughts, please comment.x
()
x