How should I multiple insert multiple records?

2019-01-08 17:02发布

I have a class named Entry declared like this:

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

and then a method that will accept multiple such Entry objects for insertion into the database using 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
        ...
    }
}  

And my question is this: should I keep using the above way of sending multiple insert statements (build a giant string of insert statements and their parameters and send it over the network), or should I keep an open connection and send a single insert statement for each Entry like this:

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

What do you think? Will there be a performance difference in the Sql Server between the two? Are there any other consequences I should be aware of?

8条回答
在下西门庆
2楼-- · 2019-01-08 17:25

If I were you I would not use either of them.

The disadvantage of the first one is that the parameter names might collide if there are same values in the list.

The disadvantage of the second one is that you are creating command and parameters for each entity.

The best way is to have the command text and parameters constructed once (use Parameters.Add to add the parameters) change their values in the loop and execute the command. That way the statement will be prepared only once. You should also open the connection before you start the loop and close it after it.

查看更多
Deceive 欺骗
3楼-- · 2019-01-08 17:27

When it are a lot of entries consider to use SqlBulkCopy. The performance is much faster than a series of single inserts.

查看更多
一夜七次
4楼-- · 2019-01-08 17:27
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++;
 }
查看更多
狗以群分
5楼-- · 2019-01-08 17:30

You should execute the command on every loop instead of building a huge command Text(btw,StringBuilder is made for this) The underlying Connection will not close and re-open for each loop, let the connection pool manager handle this. Have a look at this link for further informations: Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

If you want to ensure that every command is executed successfully you can use a Transaction and Rollback if needed,

查看更多
趁早两清
6楼-- · 2019-01-08 17:31

Following up @Tim Mahy - There's two possible ways to feed SqlBulkCopy: a DataReader or via DataTable. Here the code for 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);
}
查看更多
女痞
7楼-- · 2019-01-08 17:31

You can directly insert a DataTable if it is created correctly.

First make sure that the access table columns have the same column names and similar types. Then you can use this function which I believe is very fast and elegant.

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);
}
查看更多
登录 后发表回答