代码之家 › 专栏 › 技术社区 › bottlenecked

如何插入多个记录?

multiple-insert ado.net sql-server-2005 c#

bottlenecked · 技术社区 · 16 年前

我有一个班级叫 Entry 声明如下:

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

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

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
        ...
    }
}

进入

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性能会有差异吗?我还应该知道其他的后果吗?

8 回复 | 直到 14 年前

Giorgi 16 年前

如果我是你,我两个都不会用。

第二种方法的缺点是为每个实体创建命令和参数。

Parameters.Add 添加参数)在循环中更改它们的值并执行命令。这样的话,这份声明只会准备一次。您还应该在启动循环之前打开连接,然后关闭连接。

AMissico 7 年前

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

Tim Schmelter 7 年前

您应该在每个循环上执行命令,而不是构建一个巨大的命令文本(顺便说一句, StringBuilder Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

如果要确保每个命令都成功执行,可以使用 Transaction 如果需要可以回滚,

Ian Boyd 6 年前

真正的 可怕的 方法是执行每个 INSERT

第1批:

INSERT INTO Entries (id, name) VALUES (1, 'Ian Boyd);

第2批:

INSERT INTO Entries (id, name) VALUES (2, 'Bottlenecked);

第3批:

INSERT INTO Entries (id, name) VALUES (3, 'Marek Grzenkowicz);

第4批:

INSERT INTO Entries (id, name) VALUES (4, 'Giorgi);

第5批:

INSERT INTO Entries (id, name) VALUES (5, 'AMissico);

这真的很可怕, 可怕的 做事的方式。它给了真实的性能,因为你每次都要忍受网络的往返时间。

一个更好的解决方案是将所有的 插入 一批报表:

INSERT INTO Entries (id, name) VALUES (1, 'Ian Boyd');
INSERT INTO Entries (id, name) VALUES (2, 'Bottlenecked');
INSERT INTO Entries (id, name) VALUES (3, 'Marek Grzenkowicz');
INSERT INTO Entries (id, name) VALUES (4, 'Giorgi');
INSERT INTO Entries (id, name) VALUES (5, 'AMissico');

这样你只会遭受一次往返。此版本具有业绩胜出;大约快5倍。

更好的方法是使用 VALUES 条款:

INSERT INTO Entries (id, name)
VALUES 
(1, 'Ian Boyd'),
(2, 'Bottlenecked'),
(3, 'Marek Grzenkowicz'),
(4, 'Giorgi'),
(5, 'AMissico');

这使您在5个独立的 :

外键检查一次
唯一约束只检查一次

SQL服务器喜欢在数据收集;是维京人!

参数限制

与其说节省了服务器编译每个T-SQL批处理的性能优势(尽管在高速批量导入过程中,节省解析时间确实可以增加)

@id,@姓名

如果在单个批处理中导入1051行,则为2102个参数-将出现错误:

价值观 子句限制为1000个元组。

参数名称冲突
无限行数(可能达到2100参数限制)

所以目标是生成一个字符串,例如:

INSERT INTO Entries (id, name) VALUES
(@p1, @p2),
(@p3, @p4),
(@p5, @p6),
(@p7, @p8),
(@p9, @p10)

IEnumerable<Entry> entries = GetStuffToInsert();

SqlCommand cmd = new SqlCommand();
StringBuilder sql = new StringBuilder();
Int32 batchSize = 0; //how many rows we have build up so far
Int32 p = 1; //the current paramter name (i.e. "@p1") we're going to use

foreach(var entry in entries)
{
   //Build the names of the parameters
   String pId =   String.Format("@p{0}", p);   //the "Id" parameter name (i.e. "p1")
   String pName = String.Format("@p{0}", p+1); //the "Name" parameter name (i.e. "p2")
   p += 2;

   //Build a single "(p1, p2)" row
   String row = String.Format("({0}, {1})", pId, pName); //a single values tuple

   //Add the row to our running SQL batch
   if (batchSize > 0)
      sb.AppendLine(",");
   sb.Append(row);
   batchSize += 1;

   //Add the parameter values for this row
   cmd.Parameters.Add(pID,   System.Data.SqlDbType.Int   ).Value = entry.Id;
   cmd.Parameters.Add(pName, System.Data.SqlDbType.String).Value = entry.Name;

   if (batchSize >= 5)
   {
       String sql = "INSERT INTO Entries (id, name) VALUES"+"\r\n"+
                    sb.ToString();
       cmd.CommandText = sql;
       cmd.ExecuteNonQuery();
       cmd.Parameters.Clear();
       sb.Clear();
       batchSize = 0;
       p = 1;
   }
}

//handle the last few stragglers
if (batchSize > 0)
{
    String sql = "INSERT INTO Entries (id, name) VALUES"+"\r\n"+
                 sb.ToString();
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

simaglei 8 年前

接下来是@Tim Mahy—有两种可能的方式来提供SqlBulkCopy:DataReader或通过DataTable。下面是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);
}

smdrager 8 年前

SqlBulkCopy . 性能比一系列单个插入要快得多。

0014 10 年前

DataTable 如果创建正确。

首先确保access表列具有相同的列名和相似的类型。然后你可以使用这个功能,我相信它是非常快速和优雅的。

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

abhiroop mukherjee 5 年前

像这样的-

for (int i = 0; i < nimbusUserIds.Count; i++)
            {
                parameterValues[i] =
                    $"(0, 0, SYSDATETIME(),0, SYSDATETIME(), SYSDATETIME(),SYSDATETIME(), '{nimbusUserIds[i]}')";
            }

            string query =
                string.Format(@"INSERT INTO [dbo].[NimbusUserEmailInviteStatus] 
([AdRegistrationStatus],
[EmailSentStatus],
[EmailSentDateTime],
[InvitationStatus],
[InvitationAcceptedDateTime],
[CreatedDateTime],
[UpdatedDateTime],
[NimbusUserId]) VALUES {0}", string.Join(", ", parameterValues));

-3

Marek Grzenkowicz 14 年前

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

-5

Andrew Barber Eric Lafortune 14 年前

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