//bulk insert for Secondary Auto-Acceptance Criteria public bool BulkInsert(DataTable dt, DataTable dtColumns, bool IsOverWrite = false, int?CommentId = null) { bool DataInsert = false; CompanyFacade fac = new CompanyFacade(this.CurrentClient.ApplicationDBConnectionString, Helper.UserName); using (SqlConnection connection = new SqlConnection(this.CurrentClient.ApplicationDBConnectionString)) { if (connection.State == ConnectionState.Closed) { connection.Open(); } //Creating Transaction so that it can rollback if got any error while uploading SqlTransaction trans = connection.BeginTransaction(); //Start bulkCopy using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers, trans)) { //Setting timeout to 0 means no time out for this command will not timeout until upload complete. //Change as per you bulkCopy.BulkCopyTimeout = 0; foreach (DataRow drCol in dtColumns.Rows) { bulkCopy.ColumnMappings.Add(drCol["Excelcolumn"].ToString(), drCol["Tablecolumn"].ToString()); } bulkCopy.DestinationTableName = "ext.SecondaryAutoAcceptanceCriteriaGroup"; try { bulkCopy.WriteToServer(dt); trans.Commit(); DataInsert = true; SettingFacade sfac = new SettingFacade(this.CurrentClient.ApplicationDBConnectionString); string Message = sfac.MergeSecondaryAutoAcceptCriteria(IsOverWrite, Helper.oUser.UserId, Convert.ToInt32(CommentId)); TempData["BulkMessage"] = "Data Inserted Successfully."; if (!string.IsNullOrEmpty(Message)) { TempData["BulkMessage"] = Message; } } catch (Exception ex) { TempData["BulkMessage"] = ex.Message.ToString(); DataInsert = false; } } } return(DataInsert); }