SqlBulkCopy Multiple Tables Insert under single Transaction OR Bulk Insert Operation between Entity Framework and Classic Ado.net

Question

I have two tables which need to be inserted when my application run.
Let's say that I have tables as followed

tbl_FirstTable and tbl_SecondTable

My problem is data volume.
I need to insert over 10,000 rows to tbl_FirstTable and over 500,000 rows to tbl_SecondTable.

So fristly, I use entity framework as follow.

public bool Save_tbl_FirstTable_Vs_tbl_SecondTable(List<tbl_FirstTable> List_tbl_FirstTable, List<tbl_SecondTable> 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;
}

That is the place I face error Time out exception.
I think that exception will be solved If I use below code.

DummyDBClass_ObjectContext.CommandTimeout = 1800; // 30 minutes

So I used it. It solved but I face another error OutOfMemory Exception.
So I searched the solutions, fortunately, I found below articles.

According to that articles, I change my code from Entity Framework to Classic ADO.net 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;
}

Finally, It perform insert process in less than 15 seconds for over 500,000 rows.

There is two reasons why I post this scenario.

I would like to share what I found out.
As I am not perfect, I still need to get more suggestion from you.

So, every better solution will be appreciated.

why & what do you worried about? – TalentTuner Mar 18 '13 at 06:54 — TalentTuner, Mar 18 '13 at 06:54

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

1) Use EF6.x, which has much better performance than EF5.x

Here are more suggestions (from Bulk insert with EF)

2) Keep the active Context Graph small by using a new context for each Unit of Work

3) Turn off AutoDetechChangesEnabled - context.Configuration.AutoDetectChangesEnabled = false;

4) Batching, in your loop, Call SaveChanges periodically

edited May 23 '17 at 12:09

Community

1
1

answered May 17 '15 at 21:42

Carl Prothman

1,313
11
21

score 0 · Answer 2 · answered Mar 18 '13 at 07:09

0

Have you tried setting AutoDetectChangesEnabled = false in the Context.Configuration before all the inserts?

answered Mar 18 '13 at 07:09

Thabo

319
3
5

Yes, sure , I did it. But I found Exception `does not contain a definition for 'Configuration' and no extension method 'Configuration'`. I used `System.Data.Objects.ObjectContext` – Frank Myat Thu Mar 18 '13 at 07:17
The very first way I tried is `AutoDetectChangesEnable=False` according to that link http://stackoverflow.com/a/5942176/900284 . But I still don't know why I cannot use `Context.Configuration`. – Frank Myat Thu Mar 18 '13 at 07:21
Sorry about the late response. Your context should be defined by you and inherit from System.Data.Entity.DbContext to use that property, not the ObjectContext. If you use EF 5, I hear the EF Designer even generates it for you. Continue using the "using" on the DbContext to keep it short lived. – Thabo Mar 19 '13 at 17:37

SqlBulkCopy Multiple Tables Insert under single Transaction OR Bulk Insert Operation between Entity Framework and Classic Ado.net

2 Answers2

Linked