How should I multiple insert multiple records?












25















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?










share|improve this question

























  • Thanks for all your suggestions! i'll take @Giorgi's answer cause it more or less answers the original question

    – bottlenecked
    Jun 7 '10 at 7:02











  • you can use user-definedtable type in SQl server to pass DataTable to the SQL server fourthbottle.com/2014/09/…

    – Venki
    Nov 20 '15 at 4:42
















25















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?










share|improve this question

























  • Thanks for all your suggestions! i'll take @Giorgi's answer cause it more or less answers the original question

    – bottlenecked
    Jun 7 '10 at 7:02











  • you can use user-definedtable type in SQl server to pass DataTable to the SQL server fourthbottle.com/2014/09/…

    – Venki
    Nov 20 '15 at 4:42














25












25








25


10






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?










share|improve this question
















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?







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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 2 '11 at 6:28









Marek Grzenkowicz

14.1k66893




14.1k66893










asked Jun 4 '10 at 9:45









bottleneckedbottlenecked

1,28911428




1,28911428













  • Thanks for all your suggestions! i'll take @Giorgi's answer cause it more or less answers the original question

    – bottlenecked
    Jun 7 '10 at 7:02











  • you can use user-definedtable type in SQl server to pass DataTable to the SQL server fourthbottle.com/2014/09/…

    – Venki
    Nov 20 '15 at 4:42



















  • Thanks for all your suggestions! i'll take @Giorgi's answer cause it more or less answers the original question

    – bottlenecked
    Jun 7 '10 at 7:02











  • you can use user-definedtable type in SQl server to pass DataTable to the SQL server fourthbottle.com/2014/09/…

    – Venki
    Nov 20 '15 at 4:42

















Thanks for all your suggestions! i'll take @Giorgi's answer cause it more or less answers the original question

– bottlenecked
Jun 7 '10 at 7:02





Thanks for all your suggestions! i'll take @Giorgi's answer cause it more or less answers the original question

– bottlenecked
Jun 7 '10 at 7:02













you can use user-definedtable type in SQl server to pass DataTable to the SQL server fourthbottle.com/2014/09/…

– Venki
Nov 20 '15 at 4:42





you can use user-definedtable type in SQl server to pass DataTable to the SQL server fourthbottle.com/2014/09/…

– Venki
Nov 20 '15 at 4:42












8 Answers
8






active

oldest

votes


















23














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.






share|improve this answer



















  • 7





    The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

    – AMissico
    Jun 5 '10 at 7:13











  • so, also the cmd.ExecuteNonQuery(); should be inside the loop?

    – Ciccio
    Feb 5 '15 at 16:17






  • 1





    @Giorgi What to do when the number of parameters can exceed the limit of 2100?

    – Bruno Pessanha
    Oct 19 '15 at 15:11



















46














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["@key"].Value = oSetting.Key;
oCommand.Parameters["@value"].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();
oConnection.Close();
throw;
}
}
}
}
}





share|improve this answer





















  • 1





    I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

    – JᴀʏMᴇᴇ
    Oct 7 '18 at 22:03





















8














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,






share|improve this answer


























  • Or better use Linq2Sql and let Linq2Sql handle this.

    – Amitabh
    Jun 4 '10 at 10:02






  • 2





    The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

    – AMissico
    Jun 5 '10 at 7:14



















2














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






share|improve this answer

































    1














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





    share|improve this answer































      0














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





      share|improve this answer































        -1














        Stored procedure to insert multiple records using single insertion:



        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


        Code behind:



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





        share|improve this answer





















        • 8





          What if I have 2 million entries?

          – Adrian Marinica
          Mar 20 '13 at 12:02



















        -3














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





        share|improve this answer





















        • 2





          please do not answer only with code.

          – Cybermaxs
          Oct 3 '12 at 11:03






        • 17





          This is a work of art.

          – Ashe
          Aug 1 '13 at 1:48






        • 1





          Picasso would be proud

          – Monkey
          Jul 26 '16 at 20:32











        Your Answer






        StackExchange.ifUsing("editor", function () {
        StackExchange.using("externalEditor", function () {
        StackExchange.using("snippets", function () {
        StackExchange.snippets.init();
        });
        });
        }, "code-snippets");

        StackExchange.ready(function() {
        var channelOptions = {
        tags: "".split(" "),
        id: "1"
        };
        initTagRenderer("".split(" "), "".split(" "), channelOptions);

        StackExchange.using("externalEditor", function() {
        // Have to fire editor after snippets, if snippets enabled
        if (StackExchange.settings.snippets.snippetsEnabled) {
        StackExchange.using("snippets", function() {
        createEditor();
        });
        }
        else {
        createEditor();
        }
        });

        function createEditor() {
        StackExchange.prepareEditor({
        heartbeatType: 'answer',
        autoActivateHeartbeat: false,
        convertImagesToLinks: true,
        noModals: true,
        showLowRepImageUploadWarning: true,
        reputationToPostImages: 10,
        bindNavPrevention: true,
        postfix: "",
        imageUploader: {
        brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
        contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
        allowUrls: true
        },
        onDemand: true,
        discardSelector: ".discard-answer"
        ,immediatelyShowMarkdownHelp:true
        });


        }
        });














        draft saved

        draft discarded


















        StackExchange.ready(
        function () {
        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f2972974%2fhow-should-i-multiple-insert-multiple-records%23new-answer', 'question_page');
        }
        );

        Post as a guest















        Required, but never shown

























        8 Answers
        8






        active

        oldest

        votes








        8 Answers
        8






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes









        23














        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.






        share|improve this answer



















        • 7





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:13











        • so, also the cmd.ExecuteNonQuery(); should be inside the loop?

          – Ciccio
          Feb 5 '15 at 16:17






        • 1





          @Giorgi What to do when the number of parameters can exceed the limit of 2100?

          – Bruno Pessanha
          Oct 19 '15 at 15:11
















        23














        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.






        share|improve this answer



















        • 7





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:13











        • so, also the cmd.ExecuteNonQuery(); should be inside the loop?

          – Ciccio
          Feb 5 '15 at 16:17






        • 1





          @Giorgi What to do when the number of parameters can exceed the limit of 2100?

          – Bruno Pessanha
          Oct 19 '15 at 15:11














        23












        23








        23







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jun 4 '10 at 10:02









        GiorgiGiorgi

        25.9k1272111




        25.9k1272111








        • 7





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:13











        • so, also the cmd.ExecuteNonQuery(); should be inside the loop?

          – Ciccio
          Feb 5 '15 at 16:17






        • 1





          @Giorgi What to do when the number of parameters can exceed the limit of 2100?

          – Bruno Pessanha
          Oct 19 '15 at 15:11














        • 7





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:13











        • so, also the cmd.ExecuteNonQuery(); should be inside the loop?

          – Ciccio
          Feb 5 '15 at 16:17






        • 1





          @Giorgi What to do when the number of parameters can exceed the limit of 2100?

          – Bruno Pessanha
          Oct 19 '15 at 15:11








        7




        7





        The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

        – AMissico
        Jun 5 '10 at 7:13





        The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

        – AMissico
        Jun 5 '10 at 7:13













        so, also the cmd.ExecuteNonQuery(); should be inside the loop?

        – Ciccio
        Feb 5 '15 at 16:17





        so, also the cmd.ExecuteNonQuery(); should be inside the loop?

        – Ciccio
        Feb 5 '15 at 16:17




        1




        1





        @Giorgi What to do when the number of parameters can exceed the limit of 2100?

        – Bruno Pessanha
        Oct 19 '15 at 15:11





        @Giorgi What to do when the number of parameters can exceed the limit of 2100?

        – Bruno Pessanha
        Oct 19 '15 at 15:11













        46














        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["@key"].Value = oSetting.Key;
        oCommand.Parameters["@value"].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();
        oConnection.Close();
        throw;
        }
        }
        }
        }
        }





        share|improve this answer





















        • 1





          I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

          – JᴀʏMᴇᴇ
          Oct 7 '18 at 22:03


















        46














        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["@key"].Value = oSetting.Key;
        oCommand.Parameters["@value"].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();
        oConnection.Close();
        throw;
        }
        }
        }
        }
        }





        share|improve this answer





















        • 1





          I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

          – JᴀʏMᴇᴇ
          Oct 7 '18 at 22:03
















        46












        46








        46







        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["@key"].Value = oSetting.Key;
        oCommand.Parameters["@value"].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();
        oConnection.Close();
        throw;
        }
        }
        }
        }
        }





        share|improve this answer















        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["@key"].Value = oSetting.Key;
        oCommand.Parameters["@value"].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();
        oConnection.Close();
        throw;
        }
        }
        }
        }
        }






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Feb 12 at 11:03









        İlker Elçora

        385310




        385310










        answered Jun 5 '10 at 7:53









        AMissicoAMissico

        18.6k664100




        18.6k664100








        • 1





          I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

          – JᴀʏMᴇᴇ
          Oct 7 '18 at 22:03
















        • 1





          I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

          – JᴀʏMᴇᴇ
          Oct 7 '18 at 22:03










        1




        1





        I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

        – JᴀʏMᴇᴇ
        Oct 7 '18 at 22:03







        I'd rather reference parameters by their name than int index. Still, +1. Old answer, still applicable.

        – JᴀʏMᴇᴇ
        Oct 7 '18 at 22:03













        8














        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,






        share|improve this answer


























        • Or better use Linq2Sql and let Linq2Sql handle this.

          – Amitabh
          Jun 4 '10 at 10:02






        • 2





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:14
















        8














        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,






        share|improve this answer


























        • Or better use Linq2Sql and let Linq2Sql handle this.

          – Amitabh
          Jun 4 '10 at 10:02






        • 2





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:14














        8












        8








        8







        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,






        share|improve this answer















        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,







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 27 '18 at 14:23

























        answered Jun 4 '10 at 9:59









        RangoRango

        365k46469729




        365k46469729













        • Or better use Linq2Sql and let Linq2Sql handle this.

          – Amitabh
          Jun 4 '10 at 10:02






        • 2





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:14



















        • Or better use Linq2Sql and let Linq2Sql handle this.

          – Amitabh
          Jun 4 '10 at 10:02






        • 2





          The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

          – AMissico
          Jun 5 '10 at 7:14

















        Or better use Linq2Sql and let Linq2Sql handle this.

        – Amitabh
        Jun 4 '10 at 10:02





        Or better use Linq2Sql and let Linq2Sql handle this.

        – Amitabh
        Jun 4 '10 at 10:02




        2




        2





        The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

        – AMissico
        Jun 5 '10 at 7:14





        The SQLCommand is optimized for the process that Giorgi describes. The underlying connection will be maintained as Tim points out. I would also use a Transaction recommended by Tim.

        – AMissico
        Jun 5 '10 at 7:14











        2














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






        share|improve this answer






























          2














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






          share|improve this answer




























            2












            2








            2







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






            share|improve this answer















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







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Aug 20 '18 at 20:38









            smdrager

            5,25743045




            5,25743045










            answered Jun 5 '10 at 8:07









            Tim MahyTim Mahy

            1,017926




            1,017926























                1














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





                share|improve this answer




























                  1














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





                  share|improve this answer


























                    1












                    1








                    1







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





                    share|improve this answer













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






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Feb 22 '18 at 14:11









                    simagleisimaglei

                    590611




                    590611























                        0














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





                        share|improve this answer




























                          0














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





                          share|improve this answer


























                            0












                            0








                            0







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





                            share|improve this answer













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






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Sep 6 '16 at 21:42









                            00140014

                            4542827




                            4542827























                                -1














                                Stored procedure to insert multiple records using single insertion:



                                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


                                Code behind:



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





                                share|improve this answer





















                                • 8





                                  What if I have 2 million entries?

                                  – Adrian Marinica
                                  Mar 20 '13 at 12:02
















                                -1














                                Stored procedure to insert multiple records using single insertion:



                                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


                                Code behind:



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





                                share|improve this answer





















                                • 8





                                  What if I have 2 million entries?

                                  – Adrian Marinica
                                  Mar 20 '13 at 12:02














                                -1












                                -1








                                -1







                                Stored procedure to insert multiple records using single insertion:



                                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


                                Code behind:



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





                                share|improve this answer















                                Stored procedure to insert multiple records using single insertion:



                                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


                                Code behind:



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






                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited Dec 2 '11 at 6:27









                                Marek Grzenkowicz

                                14.1k66893




                                14.1k66893










                                answered Dec 2 '11 at 5:39









                                premkumarpremkumar

                                71




                                71








                                • 8





                                  What if I have 2 million entries?

                                  – Adrian Marinica
                                  Mar 20 '13 at 12:02














                                • 8





                                  What if I have 2 million entries?

                                  – Adrian Marinica
                                  Mar 20 '13 at 12:02








                                8




                                8





                                What if I have 2 million entries?

                                – Adrian Marinica
                                Mar 20 '13 at 12:02





                                What if I have 2 million entries?

                                – Adrian Marinica
                                Mar 20 '13 at 12:02











                                -3














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





                                share|improve this answer





















                                • 2





                                  please do not answer only with code.

                                  – Cybermaxs
                                  Oct 3 '12 at 11:03






                                • 17





                                  This is a work of art.

                                  – Ashe
                                  Aug 1 '13 at 1:48






                                • 1





                                  Picasso would be proud

                                  – Monkey
                                  Jul 26 '16 at 20:32
















                                -3














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





                                share|improve this answer





















                                • 2





                                  please do not answer only with code.

                                  – Cybermaxs
                                  Oct 3 '12 at 11:03






                                • 17





                                  This is a work of art.

                                  – Ashe
                                  Aug 1 '13 at 1:48






                                • 1





                                  Picasso would be proud

                                  – Monkey
                                  Jul 26 '16 at 20:32














                                -3












                                -3








                                -3







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





                                share|improve this answer















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






                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited Sep 21 '12 at 19:45









                                Andrew Barber

                                33.9k1479109




                                33.9k1479109










                                answered Sep 12 '12 at 14:08









                                MichaelMichael

                                19




                                19








                                • 2





                                  please do not answer only with code.

                                  – Cybermaxs
                                  Oct 3 '12 at 11:03






                                • 17





                                  This is a work of art.

                                  – Ashe
                                  Aug 1 '13 at 1:48






                                • 1





                                  Picasso would be proud

                                  – Monkey
                                  Jul 26 '16 at 20:32














                                • 2





                                  please do not answer only with code.

                                  – Cybermaxs
                                  Oct 3 '12 at 11:03






                                • 17





                                  This is a work of art.

                                  – Ashe
                                  Aug 1 '13 at 1:48






                                • 1





                                  Picasso would be proud

                                  – Monkey
                                  Jul 26 '16 at 20:32








                                2




                                2





                                please do not answer only with code.

                                – Cybermaxs
                                Oct 3 '12 at 11:03





                                please do not answer only with code.

                                – Cybermaxs
                                Oct 3 '12 at 11:03




                                17




                                17





                                This is a work of art.

                                – Ashe
                                Aug 1 '13 at 1:48





                                This is a work of art.

                                – Ashe
                                Aug 1 '13 at 1:48




                                1




                                1





                                Picasso would be proud

                                – Monkey
                                Jul 26 '16 at 20:32





                                Picasso would be proud

                                – Monkey
                                Jul 26 '16 at 20:32


















                                draft saved

                                draft discarded




















































                                Thanks for contributing an answer to Stack Overflow!


                                • Please be sure to answer the question. Provide details and share your research!

                                But avoid



                                • Asking for help, clarification, or responding to other answers.

                                • Making statements based on opinion; back them up with references or personal experience.


                                To learn more, see our tips on writing great answers.




                                draft saved


                                draft discarded














                                StackExchange.ready(
                                function () {
                                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f2972974%2fhow-should-i-multiple-insert-multiple-records%23new-answer', 'question_page');
                                }
                                );

                                Post as a guest















                                Required, but never shown





















































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown

































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown







                                Popular posts from this blog

                                A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                                Calculate evaluation metrics using cross_val_predict sklearn

                                Insert data from modal to MySQL (multiple modal on website)