Tuesday, October 9, 2018

C# entity framework BULK INSERT

This post demonstrates How to make bulk insert in entity framework by using SQLBulkCopy and in my previous post (Entity Framework - Bulk Update) I explained that How entity framework has a performace issue to perform bulk insert or update records.

SQLBulkCopy class is used to bulk load a SQL Server table with data from another source and SqlBulkCopy  has a method WriteToServer() , which copies all rows from a data source to a destination table specified by the DestinationTableName property of the SqlBulkCopy object

public static void BulkInsertRecord<TEntity>(IEnumerable items,string schemaName) where TEntity : class
        {           

            DbContext.Database.Connection.Open();
            var conn = DbContext.Database.Connection;        
            SqlBulkCopy sqlBulk = new SqlBulkCopy(conn as SqlConnection)
            {           

            DestinationTableName = schemaName + $".{typeof(TEntity).Name}",

            };                
   
                
                   var dbReader = items.AsDataReader();


sqlBulk.WriteToServer(dbReader);

                
        }



Here, ProjectData is Domain or DTO class, which is being mapped with Project (Data Entity Class) and this Project Entity maps to Project Table and schema is dbo

   public bool BulkInsertProject (List records)
        {
            List entityProject = new List>();
            foreach (ProjectData record in records)
            {
Project project = new Project
                {
                    ProjectID = record.ProjectID
                    ProejctDesc = record.ProejctDesc
                };

                entityProject.Add(project);
            }

                     BulkInsert(entityProject,        "dbo");
           
           return true;
        }



Thanks for visiting !!

No comments:

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...