Thursday, November 7, 2019

Bulk Insert in Dapper into MS SQL

I am collecting costs by employee from 6 sources and then insert them into a a GeneratedDetail table. But the collected data has 1000+ rows, and the insertion one by one row is very slow. I needed to find a bulk insert solution.
SQL Server DevOps
I am using Dapper as ORM in my C# project, and MS SQL Database.
According to research, my approach would be as below. It works now quite fast, I am satisfied with the speed of execution. Feel free to comment and propose a better solution.
note: spGeneratedDetailInsert  is a stored procedure with many parameters.

        public  void GeneratedDetailInsertBulk1(List<GeneratedDetail> DetailList)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                var sql = "";           
                foreach (var mydet in DetailList)
                {               
                    sql = sql + "Exec spGeneratedDetailInsert '" + mydet.BillingCode + "', " +
                        mydet.Emp_id + ", '" +
                        mydet.Emp_name + "', " +
                        mydet.HrId + ", " +
                        mydet.Entity_id + ", " +
                        mydet.BU_id + ", '" +
                        mydet.Currency + "', '" +
                        mydet.Item + "', " +
                        mydet.ExchangeRate + ", " +
                        mydet.InitValueRON + ", " +
                        mydet.InitValueCurr + ", " +
                        mydet.MUValueCurr + ", " +
                        mydet.TotalRon + ", '" +
                        mydet.ServiceType + "', '" +
                        mydet.CostCategory + "', '" +
                        mydet.UpdatedBy + "', '" +
                        mydet.UpdatedAt + "', '" +
                        mydet.PeriodId + "' ; ";                                   
                }
                 connection.Execute(sql);
            }
        }
Note: I am not using Dapper Plus, and I don't want to.

Dapper Plus | Learn how to use Dapper Bulk Insert with ...

No comments:

Post a Comment