Reg : Better option to move data into my Db

Reg : Better option to move data into my Db

• Eric_D

  I am interested to hear the performance differences.

  cheers!

  
  
  

• AngryHank

  Cool. . . I was hoping you had a chance to do the batch method. I have not had a chance to use it in production, but I bet its on a scale of 10 times faster than the indvidual insert.

  cheers!

  
  
  

• ShortBus

  Hi

  Thanks for ur interest to know. I used the first one.

  I jus needed an alternative to open connection once and insert all records and close the Connection.

  Once the Connection is opened only once and task completed, Then surely thats great.

  coz earlier i got my n/w connection cut in the middle of the operation..

  Thanks buddy.

  
  

• TechWriter

  Thanks sir  

  I ll try it in my application

   

  
  

• BlackTigerX

  I agree with using the Batching feature. In addition, you may want to look at the SqlBulkInsert feature http://www.knowdotnet.com/articles/bulkcopy_intro1.html . However both of these presuppose that you want to move the data client side.You may want to look at using a server side feature like DTS. It depends on your specific scenario of course. Check out the Article above though and let me know if you're interested in it. The code itself uses Diagnostics to test the performance and my experience has been that it's amazing.

  
  Cheers,

  Bill

  
  
  

• libragirl

  so how much faster was it which solution did you apply
  
  

• rgsiii

  this should speed things up:

  note: just spitting out code, not tested for errors!!!

  ---

  SqlCommand cmd2 =new SqlCommand(query,cn);

  i = ds.Tables[0].Rows.Count;
  string query ="Insert into newDefaultDetailsTable values (@p1, @p2, @p3,@p4, @p5, @p6, @p7, @p7, @p8, @p9)";
  SqlCommand cmd2 =new SqlCommand(query,cn);
  cn.Open();
  foreach (DataRow dr in ds.Tables[0].Rows)
  {
      if(cmd2.Parameters.Count=0)
      {
          cmd2.Parameters.AddWithValue("@p1",dr[ 0 ]);        
          cmd2.Parameters.AddWithValue("@p2",dr[ 1 ]);
          cmd2.Parameters.AddWithValue("@p3",dr[ 2 ]);
          cmd2.Parameters.AddWithValue("@p4",dr[ 3 ]);
          cmd2.Parameters.AddWithValue("@p5",dr[ 4 ].ToString()=="YES" 'Y' :'N');
          cmd2.Parameters.AddWithValue("@p6",dr[ 5 ].ToString().Replace("'",""));
          cmd2.Parameters.AddWithValue("@p7",dr[ 6 ]);
          cmd2.Parameters.AddWithValue("@p8",dr[ 7 ]);
          cmd2.Parameters.AddWithValue("@p9",dr[ 8 ]);    
      }
      else
      {
          cmd2.Parameters["@p1"].Value = dr[ 0 ];
          cmd2.Parameters["@p2"].Value = dr[ 1 ];
          cmd2.Parameters["@p3"].Value = dr[ 2 ];
          cmd2.Parameters["@p4"].Value = dr[ 3 ];
          cmd2.Parameters["@p5"].Value = dr[ 4 ].ToString()=="YES" 'Y' :'N';
          cmd2.Parameters["@p6"].Value = dr[ 5 ].ToString().Replace("'","");
          cmd2.Parameters["@p7"].Value = dr[ 6 ];
          cmd2.Parameters["@p8"].Value = dr[ 7 ];
          cmd2.Parameters["@p9"].Value = dr[ 8 ];
      }
      int count = cmd2.ExecuteNonQuery();
  }
  cn.Close();

  ---

  also, you could use the new DataAdapter batch updating:

  ---

  SqlCommand cmd2 =new SqlCommand(query,cn);

  i = ds.Tables[0].Rows.Count;
  string query ="Insert into newDefaultDetailsTable values (@p1, @p2, @p3,@p4, @p5, @p6, @p7, @p7, @p8, @p9)";
  SqlCommand cmd2 =new SqlCommand(query,cn);
  System.Data.SqlClient.SqlDataAdapter da =
                  new System.Data.SqlClient.SqlDataAdapter(
                  "select from newDefaultDetailsTable where 0 <> 1", cn);
  da.InsertCommand = cmd;
  DataTable dt = new DataTable();
  cn.Open();
  da.Fill(dt);
  cn.Close();
  foreach (DataRow dr in ds.Tables[0].Rows)
      dt.Rows.Add(new object[]
    {
        dr[ 0 ], dr[ 1 ], dr[ 2 ] ,dr[ 3 ], (dr[ 4 ].ToString()=="YES" 'Y' :'N'),
        dr[ 5 ].ToString().Replace("'",""),dr[ 6 ],dr[ 7 ],dr[ 8 ]
    } );
  da.UpdateBatchSize = 0;
  cn.Open();
  da.Update(dt);
  cn.Close();

  ---

  Also take a look at the SqlBulkCopy class

  
  
  

• sead saric

  And what if this SQL table already exists What if all the fields that you are inserting into already have names and data types. How would you insert into, for example, a table with a 100 fields

  Thanks.

  
  
  
• Reg : Better option to move data into my Db