title: SqlBulkCopy Multiple Tables Insert under single Transaction OR Bulk Insert Operation between Entity Framework and Classic Ado.net
body: <p>I have two tables which need to be inserted when my application run.<br>
  Let's say that I have tables as followed</p>
  
  <ul>
  <li>tbl_FirstTable and tbl_SecondTable</li>
  </ul>
  
  <p>My problem is data volume.<br>
  I need to insert over 10,000 rows to tbl_FirstTable and over 500,000 rows to tbl_SecondTable.</p>
  
  <p>So fristly, I use entity framework as follow.</p>
  
  <pre><code>public bool Save_tbl_FirstTable_Vs_tbl_SecondTable(List&lt;tbl_FirstTable&gt; List_tbl_FirstTable, List&lt;tbl_SecondTable&gt; List_tbl_SecondTable)
  {
      bool IsSuccessSave = false;
      try
      {
          using (DummyDBClass_ObjectContext _DummyDBClass_ObjectContext = new DummyDBClass_ObjectContext())
          {           
              foreach (tbl_FirstTable _tbl_FirstTable in List_tbl_FirstTable)
              {
                  _DummyDBClass_ObjectContext.tbl_FirstTable.InsertOnSubmit(_tbl_FirstTable);
              }
  
              foreach (tbl_SecondTable _tbl_SecondTable in List_tbl_SecondTable)
              {
                  _DummyDBClass_ObjectContext.tbl_SecondTable.InsertOnSubmit(_tbl_SecondTable);
              }
  
              _DummyDBClass_ObjectContext.SubmitChanges();
              IsSuccessSave = true;
          }
      }
      catch (Exception ex)
      {
          Log4NetWrapper.WriteError(string.Format("{0} : {1} : Exception={2}",
                                      this.GetType().FullName,
                                      (new StackTrace(new StackFrame(0))).GetFrame(0).GetMethod().Name.ToString(),
                                      ex.Message.ToString()));
  
          if (ex.InnerException != null)
          {
              Log4NetWrapper.WriteError(string.Format("{0} : {1} : InnerException Exception={2}",
                                      this.GetType().FullName,
                                      (new StackTrace(new StackFrame(0))).GetFrame(0).GetMethod().Name.ToString(),
                                      ex.InnerException.Message.ToString()));
          }
      }
  
      return IsSuccessSave;
  }
  </code></pre>
  
  <p>That is the place I face error <code>Time out exception</code>.<br>
  I think that exception will be solved If I use below code.</p>
  
  <pre><code>DummyDBClass_ObjectContext.CommandTimeout = 1800; // 30 minutes
  </code></pre>
  
  <p>So I used it. It solved but I face another error <code>OutOfMemory Exception</code>.<br>
  So I searched the solutions, fortunately, I found below articles.</p>
  
  <ol>
  <li> <a href="http://praneethmoka.wordpress.com/2012/02/22/problem-with-bulk-insert-using-entity-framework/" rel="noreferrer">Problem with Bulk insert using Entity Framework</a> </li>
  <li> <a href="https://web.archive.org/web/20211016211619/http://www.4guysfromrolla.com/articles/111109-1.aspx" rel="noreferrer">Using Transactions with SqlBulkCopy</a> </li>
  <li> <a href="http://msdn.microsoft.com/en-sg/library/tchktcdk(v=vs.80).aspx" rel="noreferrer">Performing a Bulk Copy Operation in a Transaction</a> </li>
  </ol>
  
  <p>According to that articles, I change my code from Entity Framework to Classic ADO.net code.</p>
  
  <pre><code>public bool Save_tbl_FirstTable_Vs_tbl_SecondTable(DataTable DT_tbl_FirstTable, DataTable DT_tbl_SecondTable)
  {
      bool IsSuccessSave = false;
      SqlTransaction transaction = null;
      try
      {
          using (DummyDBClass_ObjectContext _DummyDBClass_ObjectContext = new DummyDBClass_ObjectContext())
          {
              var connectionString = ((EntityConnection)_DummyDBClass_ObjectContext.Connection).StoreConnection.ConnectionString;
              using (SqlConnection connection = new SqlConnection(connectionString))
              {
                  connection.Open();
                  using (transaction = connection.BeginTransaction())
                  {
                      using (SqlBulkCopy bulkCopy_tbl_FirstTable = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))                            
                      {
                          bulkCopy_tbl_FirstTable.BatchSize = 5000;
                          bulkCopy_tbl_FirstTable.DestinationTableName = "dbo.tbl_FirstTable";
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("ID", "ID");
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("UploadFileID", "UploadFileID");
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("Active", "Active");
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("CreatedUserID", "CreatedUserID");
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("CreatedDate", "CreatedDate");
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("UpdatedUserID", "UpdatedUserID");
                          bulkCopy_tbl_FirstTable.ColumnMappings.Add("UpdatedDate", "UpdatedDate");
                          bulkCopy_tbl_FirstTable.WriteToServer(DT_tbl_FirstTable);
                      }
  
                      using (SqlBulkCopy bulkCopy_tbl_SecondTable = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))                            
                      {
  
                          bulkCopy_tbl_SecondTable.BatchSize = 5000;
                          bulkCopy_tbl_SecondTable.DestinationTableName = "dbo.tbl_SecondTable";
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("ID", "ID");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("UploadFileDetailID", "UploadFileDetailID");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("CompaignFieldMasterID", "CompaignFieldMasterID");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("Value", "Value");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("Active", "Active");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("CreatedUserID", "CreatedUserID");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("CreatedDate", "CreatedDate");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("UpdatedUserID", "UpdatedUserID");
                          bulkCopy_tbl_SecondTable.ColumnMappings.Add("UpdatedDate", "UpdatedDate");
                          bulkCopy_tbl_SecondTable.WriteToServer(DT_tbl_SecondTable);
                      }
  
  
                      transaction.Commit();
                      IsSuccessSave = true;
                  }
                  connection.Close();
              }
          }
      }
      catch (Exception ex)
      {
          if (transaction != null)
              transaction.Rollback();
  
          Log4NetWrapper.WriteError(string.Format("{0} : {1} : Exception={2}",
                                      this.GetType().FullName,
                                      (new StackTrace(new StackFrame(0))).GetFrame(0).GetMethod().Name.ToString(),
                                      ex.Message.ToString()));
  
          if (ex.InnerException != null)
          {
              Log4NetWrapper.WriteError(string.Format("{0} : {1} : InnerException Exception={2}",
                                      this.GetType().FullName,
                                      (new StackTrace(new StackFrame(0))).GetFrame(0).GetMethod().Name.ToString(),
                                      ex.InnerException.Message.ToString()));
          }
      }
  
      return IsSuccessSave;
  }
  </code></pre>
  
  <p>Finally, It perform insert process in less than 15 seconds for over 500,000 rows.</p>
  
  <p>There is two reasons why I post this scenario.</p>
  
  <ol>
  <li>I would like to share what I found out.<br></li>
  <li>As I am not perfect, I still need to get more suggestion from you.</li>
  </ol>
  
  <p>So, every better solution will be appreciated.</p>