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];
}
}