我应该如何多次插入多个记录？

问题描述：

我宣布这样一个名为Entry类：我应该如何多次插入多个记录？

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

，然后将使用ADO.NET接受多个这样的Entry对象插入到数据库的方法：

static void InsertEntries(IEnumerable<Entry> 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 
     ... 
    } 
}

我的问题是这样的：我应该继续使用上面的方式发送多个插入语句（构建一串插入语句及其参数并通过网络发送），还是应该保持一个打开的连接并为每个插入语句发送一个Entry喜欢这个：

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(); 
     } 
    } 
}

你觉得呢？两者之间的Sql Server会有性能差异吗？我应该注意到其他的后果吗？

感谢您的所有建议！我将采取@ Giorgi的答案，因为它或多或少地回答了原始问题 – bottlenecked 2010-06-07 07:02:44

您可以在SQl服务器中使用用户定义的表类型将数据表传递给SQL服务器http://www.fourthbottle.com/2014/09/how- to-insert-multiple-records-into.html – Venki 2015-11-20 04:42:47

答

如果我是你，我不会使用其中任一他们。

第一个缺点是，如果列表中有相同的值，参数名称可能会发生冲突。

第二个缺点是您正在为每个实体创建命令和参数。

最好的方法是让命令文本和参数构造一次（使用Parameters.Add添加参数）在循环中更改它们的值并执行命令。这样声明只准备一次。在启动循环之前，您还应该打开连接并关闭它。

“SQLCommand”针对Giorgi描述的过程进行了优化。如Tim所指出的那样，基础连接将保持不变。我还会使用Tim推荐的'Transaction'。 – AMissico 2010-06-05 07:13:23

那么，'cmd.ExecuteNonQuery（）;'应该在循环内部吗？ – Ciccio 2015-02-05 16:17:49

@Giorgi当参数的数量可以超过2100的限制时该怎么办？ – 2015-10-19 15:11:54

答

你应该在每个循环中执行命令，而不是建立一个庞大的命令文本（顺便说一句，StringBuilder为该制造）底层的连接不会关闭并重新打开每个循环，让连接池管理器处理这个。看看这个链接进行进一步的信息：Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

如果你想确保每个命令执行成功，如果需要，您可以使用Transaction和回滚，

或者更好地使用Linq2Sql并让Linq2Sql处理这个问题。 – Amitabh 2010-06-04 10:02:01

“SQLCommand”针对Giorgi描述的过程进行了优化。如Tim所指出的那样，基础连接将保持不变。我还会使用Tim推荐的'Transaction'。 – AMissico 2010-06-05 07:14:22

答

static void InsertSettings(IEnumerable<Entry> 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; 
       } 
      } 
     } 
    } 
}

答

当它是一个条目很多考虑使用SqlBulkCopy的

答

存储过程使用单一插入插入多条记录：

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

后面的代码：

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(); 
}

如果我有200万条记录怎么办？ – 2013-03-20 12:02:58

答

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

请不要只用代码回答。 – Cybermaxs 2012-10-03 11:03:25

+13

这是一件艺术品。 – Ashe 2013-08-01 01:48:25

毕加索会很自豪 – Monkey 2016-07-26 20:32:42

答

你可以直接插入一个如果创建正确，则为DataTable。

首先确保访问表列具有相同的列名称和类似的类型。然后你可以使用这个我认为非常快速和优雅的功能。

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

答

跟进@Tim马伊 - 有两种可能的方式养活SqlBulkCopy的：一个DataReader或通过数据表。这里代码为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); 
}

我应该如何多次插入多个记录？

相关推荐