//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);
}