¿Cómo debo insertar múltiples registros múltiples?

Tengo una clase llamada Entry declarada así:

 class Entry{ string Id {get;set;} string Name {get;set;} } 

y luego un método que aceptará múltiples objetos de Entry para su inserción en la base de datos usando ADO.NET:

 static void InsertEntries(IEnumerable entries){ //build a SqlCommand object using(SqlCommand cmd = new SqlCommand()){ ... const string refcmdText = "INSERT INTO Entries (id, name) VALUES (@id{0},@name{0});"; int count = 0; string query = string.Empty; //build a large query foreach(var entry in entries){ query += string.Format(refcmdText, count); cmd.Parameters.AddWithValue(string.Format("@id{0}",count), entry.Id); cmd.Parameters.AddWithValue(string.Format("@name{0}",count), entry.Name); count++; } cmd.CommandText=query; //and then execute the command ... } } 

Y mi pregunta es esta: ¿Debería seguir utilizando la forma anterior de enviar múltiples instrucciones de inserción (crear una cadena gigante de instrucciones de inserción y sus parámetros y enviarla a través de la red), o debería mantener una conexión abierta y enviar una sola instrucción de inserción? para cada Entry como esta:

 using(SqlCommand cmd = new SqlCommand(){ using(SqlConnection conn = new SqlConnection(){ //assign connection string and open connection ... cmd.Connection = conn; foreach(var entry in entries){ cmd.CommandText= "INSERT INTO Entries (id, name) VALUES (@id,@name);"; cmd.Parameters.AddWithValue("@id", entry.Id); cmd.Parameters.AddWithValue("@name", entry.Name); cmd.ExecuteNonQuery(); } } } 

¿Qué piensas? ¿Habrá una diferencia de rendimiento en el servidor Sql entre los dos? ¿Hay otras consecuencias que deba conocer?

Si yo fuera tú, no usaría ninguno de ellos.

La desventaja de la primera es que los nombres de los parámetros pueden colisionar si hay los mismos valores en la lista.

La desventaja de la segunda es que está creando comandos y parámetros para cada entidad.

La mejor manera es tener el texto de comando y los parámetros construidos una vez (use los Parameters.Add Agregar para agregar los parámetros) cambie sus valores en el ciclo y ejecute el comando. De esta forma, la statement se preparará solo una vez. También debe abrir la conexión antes de iniciar el ciclo y cerrarlo después.

 static void InsertSettings(IEnumerable settings) { using (SqlConnection oConnection = new SqlConnection("Data Source=(local);Initial Catalog=Wip;Integrated Security=True")) { oConnection.Open(); using (SqlTransaction oTransaction = oConnection.BeginTransaction()) { using (SqlCommand oCommand = oConnection.CreateCommand()) { oCommand.Transaction = oTransaction; oCommand.CommandType = CommandType.Text; oCommand.CommandText = "INSERT INTO [Setting] ([Key], [Value]) VALUES (@key, @value);"; oCommand.Parameters.Add(new SqlParameter("@key", SqlDbType.NChar)); oCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.NChar)); try { foreach (var oSetting in settings) { oCommand.Parameters[0].Value = oSetting.Key; oCommand.Parameters[1].Value = oSetting.Value; if (oCommand.ExecuteNonQuery() != 1) { //'handled as needed, //' but this snippet will throw an exception to force a rollback throw new InvalidProgramException(); } } oTransaction.Commit(); } catch (Exception) { oTransaction.Rollback(); throw; } } } } } 

Debe ejecutar el comando en cada bucle en lugar de generar un comando enorme. Texto (por cierto, StringBuilder está hecho para esto). La conexión subyacente no se cerrará y volverá a abrir para cada bucle, dejará que el administrador del grupo de conexiones maneje esto. Eche un vistazo a este enlace para obtener más información: sintonización de agrupación de conexiones ADO.NET en aplicaciones ASP.NET

Si desea asegurarse de que cada comando se ejecuta con éxito, puede usar una Transacción y un Retroceso si es necesario,

Cuando se trata de muchas entradas, considere utilizar SqlBulkCopy . El rendimiento es mucho más rápido que una serie de inserciones individuales.

Seguimiento de @Tim Mahy: hay dos formas posibles de alimentar SqlBulkCopy: un DataReader o vía DataTable. Aquí el código para DataTable:

 DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Id", typeof(string))); dt.Columns.Add(new DataColumn("Name", typeof(string))); foreach (Entry entry in entries) dt.Rows.Add(new string[] { entry.Id, entry.Name }); using (SqlBulkCopy bc = new SqlBulkCopy(connection)) { // the following 3 lines might not be neccessary bc.DestinationTableName = "Entries"; bc.ColumnMappings.Add("Id", "Id"); bc.ColumnMappings.Add("Name", "Name"); bc.WriteToServer(dt); } 

Puede insertar directamente una DataTable si se crea correctamente.

Primero asegúrese de que las columnas de la tabla de acceso tengan los mismos nombres de columna y tipos similares. Entonces puedes usar esta función que creo que es muy rápida y elegante.

 public void AccessBulkCopy(DataTable table) { foreach (DataRow r in table.Rows) r.SetAdded(); var myAdapter = new OleDbDataAdapter("SELECT * FROM " + table.TableName, _myAccessConn); var cbr = new OleDbCommandBuilder(myAdapter); cbr.QuotePrefix = "["; cbr.QuoteSuffix = "]"; cbr.GetInsertCommand(true); myAdapter.Update(table); } 

Procedimiento almacenado para insertar múltiples registros usando una sola inserción:

 ALTER PROCEDURE [dbo].[Ins] @i varchar(50), @n varchar(50), @a varchar(50), @i1 varchar(50), @n1 varchar(50), @a1 varchar(50), @i2 varchar(50), @n2 varchar(50), @a2 varchar(50) AS INSERT INTO t1 SELECT @i AS Expr1, @i1 AS Expr2, @i2 AS Expr3 UNION ALL SELECT @n AS Expr1, @n1 AS Expr2, @n2 AS Expr3 UNION ALL SELECT @a AS Expr1, @a1 AS Expr2, @a2 AS Expr3 RETURN 

Código detrás:

 protected void Button1_Click(object sender, EventArgs e) { cn.Open(); SqlCommand cmd = new SqlCommand("Ins",cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@i",TextBox1.Text); cmd.Parameters.AddWithValue("@n",TextBox2.Text); cmd.Parameters.AddWithValue("@a",TextBox3.Text); cmd.Parameters.AddWithValue("@i1",TextBox4.Text); cmd.Parameters.AddWithValue("@n1",TextBox5.Text); cmd.Parameters.AddWithValue("@a1",TextBox6.Text); cmd.Parameters.AddWithValue("@i2",TextBox7.Text); cmd.Parameters.AddWithValue("@n2",TextBox8.Text); cmd.Parameters.AddWithValue("@a2",TextBox9.Text); cmd.ExecuteNonQuery(); cn.Close(); Response.Write("inserted"); clear(); } 
 ClsConectaBanco bd = new ClsConectaBanco(); StringBuilder sb = new StringBuilder(); sb.Append(" INSERT INTO FAT_BALANCETE "); sb.Append(" ([DT_LANCAMENTO] "); sb.Append(" ,[ID_LANCAMENTO_CONTABIL] "); sb.Append(" ,[NR_DOC_CONTABIL] "); sb.Append(" ,[TP_LANCAMENTO_GERADO] "); sb.Append(" ,[VL_LANCAMENTO] "); sb.Append(" ,[TP_NATUREZA] "); sb.Append(" ,[CD_EMPRESA] "); sb.Append(" ,[CD_FILIAL] "); sb.Append(" ,[CD_CONTA_CONTABIL] "); sb.Append(" ,[DS_CONTA_CONTABIL] "); sb.Append(" ,[ID_CONTA_CONTABIL] "); sb.Append(" ,[DS_TRIMESTRE] "); sb.Append(" ,[DS_SEMESTRE] "); sb.Append(" ,[NR_TRIMESTRE] "); sb.Append(" ,[NR_SEMESTRE] "); sb.Append(" ,[NR_ANO] "); sb.Append(" ,[NR_MES] "); sb.Append(" ,[NM_FILIAL]) "); sb.Append(" VALUES "); sb.Append(" (@DT_LANCAMENTO "); sb.Append(" ,@ID_LANCAMENTO_CONTABIL "); sb.Append(" ,@NR_DOC_CONTABIL "); sb.Append(" ,@TP_LANCAMENTO_GERADO "); sb.Append(" ,@VL_LANCAMENTO "); sb.Append(" ,@TP_NATUREZA "); sb.Append(" ,@CD_EMPRESA "); sb.Append(" ,@CD_FILIAL "); sb.Append(" ,@CD_CONTA_CONTABIL "); sb.Append(" ,@DS_CONTA_CONTABIL "); sb.Append(" ,@ID_CONTA_CONTABIL "); sb.Append(" ,@DS_TRIMESTRE "); sb.Append(" ,@DS_SEMESTRE "); sb.Append(" ,@NR_TRIMESTRE "); sb.Append(" ,@NR_SEMESTRE "); sb.Append(" ,@NR_ANO "); sb.Append(" ,@NR_MES "); sb.Append(" ,@NM_FILIAL) "); SqlCommand cmd = new SqlCommand(sb.ToString(), bd.CriaConexaoSQL()); bd.AbrirConexao(); cmd.Parameters.Add("@DT_LANCAMENTO", SqlDbType.Date); cmd.Parameters.Add("@ID_LANCAMENTO_CONTABIL", SqlDbType.Int); cmd.Parameters.Add("@NR_DOC_CONTABIL", SqlDbType.VarChar,255); cmd.Parameters.Add("@TP_LANCAMENTO_GERADO", SqlDbType.VarChar,255); cmd.Parameters.Add("@VL_LANCAMENTO", SqlDbType.Decimal); cmd.Parameters["@VL_LANCAMENTO"].Precision = 15; cmd.Parameters["@VL_LANCAMENTO"].Scale = 2; cmd.Parameters.Add("@TP_NATUREZA", SqlDbType.VarChar, 1); cmd.Parameters.Add("@CD_EMPRESA",SqlDbType.Int); cmd.Parameters.Add("@CD_FILIAL", SqlDbType.Int); cmd.Parameters.Add("@CD_CONTA_CONTABIL", SqlDbType.VarChar, 255); cmd.Parameters.Add("@DS_CONTA_CONTABIL", SqlDbType.VarChar, 255); cmd.Parameters.Add("@ID_CONTA_CONTABIL", SqlDbType.VarChar,50); cmd.Parameters.Add("@DS_TRIMESTRE", SqlDbType.VarChar, 4); cmd.Parameters.Add("@DS_SEMESTRE", SqlDbType.VarChar, 4); cmd.Parameters.Add("@NR_TRIMESTRE", SqlDbType.Int); cmd.Parameters.Add("@NR_SEMESTRE", SqlDbType.Int); cmd.Parameters.Add("@NR_ANO", SqlDbType.Int); cmd.Parameters.Add("@NR_MES", SqlDbType.Int); cmd.Parameters.Add("@NM_FILIAL", SqlDbType.VarChar, 255); cmd.Prepare(); foreach (dtoVisaoBenner obj in lista) { cmd.Parameters["@DT_LANCAMENTO"].Value = obj.CTLDATA; cmd.Parameters["@ID_LANCAMENTO_CONTABIL"].Value = obj.CTLHANDLE.ToString(); cmd.Parameters["@NR_DOC_CONTABIL"].Value = obj.CTLDOCTO.ToString(); cmd.Parameters["@TP_LANCAMENTO_GERADO"].Value = obj.LANCAMENTOGERADO; cmd.Parameters["@VL_LANCAMENTO"].Value = obj.CTLANVALORF; cmd.Parameters["@TP_NATUREZA"].Value = obj.NATUREZA; cmd.Parameters["@CD_EMPRESA"].Value = obj.EMPRESA; cmd.Parameters["@CD_FILIAL"].Value = obj.FILIAL; cmd.Parameters["@CD_CONTA_CONTABIL"].Value = obj.CONTAHANDLE.ToString(); cmd.Parameters["@DS_CONTA_CONTABIL"].Value = obj.CONTANOME.ToString(); cmd.Parameters["@ID_CONTA_CONTABIL"].Value = obj.CONTA; cmd.Parameters["@DS_TRIMESTRE"].Value = obj.TRIMESTRE; cmd.Parameters["@DS_SEMESTRE"].Value = obj.SEMESTRE; cmd.Parameters["@NR_TRIMESTRE"].Value = obj.NRTRIMESTRE; cmd.Parameters["@NR_SEMESTRE"].Value = obj.NRSEMESTRE; cmd.Parameters["@NR_ANO"].Value = obj.NRANO; cmd.Parameters["@NR_MES"].Value = obj.NRMES; cmd.Parameters["@NM_FILIAL"].Value = obj.NOME; cmd.ExecuteNonQuery(); rowAffected++; }