Entity Framework BulkInsert Temp table without creating

Question

Hi,

I am creating Temp Table and inserting data with EF BulkInsert , then I use that temp table in the dynamic script.

I need to BulkInsert 100K rows into SQL Server but it was keep loading . I tried to insert insert for loop but I am getting a error "Table already exist"

How to BulkInsert into temp table without creating temp ( from second iteration ) table using EF BulkInsert.

If not possible by using BulkInsert, any better way to create temp table with large amount of rows with same DBTransaction

I am using .NET 4.5 , EF 6

Answer 1

Hi @siva_dev , Welcome to Microsoft Q&A,

Another method is to use SqlBulkCopy, the SqlBulkCopy Class allows you to efficiently bulk load SQL Server tables using data from other sources.

The code is as follows:

          /// <summary>
         /// Batch insert
         /// </summary>
         /// <typeparam name="T">Type of generic collection</typeparam>
         /// <param name=" dbContext ">Connection object</param>
         /// <param name="tableName">Insert the generic collection into the table name of the local database table</param>
         /// <param name="list">To insert a large generic collection</param>
         public static bool BulkInsert<T>(DbContext dbContext, string tableName, IList<T> list)
         {
             try
             {
                 if (list == null || list.Count == 0) return true;
                 if (dbContext.Database.Connection.State != ConnectionState.Open)
                 {
                     dbContext.Database.Connection.Open(); //Open Connection connection
                 }
                 using (var bulkCopy = new SqlBulkCopy(dbContext.Database.Connection.ConnectionString))
                 {
                     bulkCopy.BatchSize = list.Count;
                     bulkCopy.DestinationTableName = tableName;
 
                     var table = new DataTable();
                     var props = TypeDescriptor.GetProperties(typeof(T))
 
                         .Cast<PropertyDescriptor>()
                         .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                         .ToArray();
 
                     foreach (var propertyInfo in props)
                     {
                         bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                         table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                     }
 
                     var values = new object[props.Length];
                     foreach (var item in list)
                     {
                         for (var i = 0; i < values.Length; i++)
                         {
                             values[i] = props[i].GetValue(item);
                         }
                         table.Rows.Add(values);
                     }
 
                     bulkCopy.WriteToServer(table);
                     if (dbContext.Database.Connection.State != ConnectionState.Closed)
                     {
                         dbContext.Database.Connection.Close(); //Close Connection
                     }
                     return true;
                 }
             }
             catch (Exception ex)
             {
                 if (dbContext.Database.Connection.State != ConnectionState.Closed)
                 {
                     dbContext.Database.Connection.Close(); //Close Connection
                 }
                 return false;
             }
      }

Best Regards,

Wenbin

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.