Chandrashekar
5/6/2020 - 9:05 AM
SqlBulkCopy using DataTable(Sql Bulk Copy)
public void BulkInsertTotalCaseVolume(List<TotalCaseVolume> totalCaseVolumes)
{
try
{
DataTable tbl = new DataTable();
tbl.Columns.Add(new DataColumn("Case_Number", typeof(string)));
tbl.Columns.Add(new DataColumn("Case_Status", typeof(string)));
tbl.Columns.Add(new DataColumn("Case_Type", typeof(string)));
tbl.Columns.Add(new DataColumn("Delivery_Method", typeof(string)));
tbl.Columns.Add(new DataColumn("Case_Origin", typeof(string)));
tbl.Columns.Add(new DataColumn("Create_Date", typeof(DateTime)));
tbl.Columns.Add(new DataColumn("Account_Name", typeof(string)));
tbl.Columns.Add(new DataColumn("Case_Title", typeof(string)));
tbl.Columns.Add(new DataColumn("Level_3_Description", typeof(string)));
tbl.Columns.Add(new DataColumn("Level_4_Description", typeof(string)));
tbl.Columns.Add(new DataColumn("Level_5_Description", typeof(string)));
tbl.Columns.Add(new DataColumn("Generation", typeof(string)));
tbl.Columns.Add(new DataColumn("Product_Line", typeof(string)));
tbl.Columns.Add(new DataColumn("Product_Group", typeof(string)));
tbl.Columns.Add(new DataColumn("Product_No", typeof(string)));
tbl.Columns.Add(new DataColumn("Serial_Number", typeof(string)));
tbl.Columns.Add(new DataColumn("Business", typeof(string)));
tbl.Columns.Add(new DataColumn("Service_Portfolio", typeof(string)));
tbl.Columns.Add(new DataColumn("Severity", typeof(string)));
tbl.Columns.Add(new DataColumn("CSC_Facility", typeof(string)));
tbl.Columns.Add(new DataColumn("Manager_Email_Address", typeof(string)));
tbl.Columns.Add(new DataColumn("Primary_Role", typeof(string)));
tbl.Columns.Add(new DataColumn("Secondary_Role", typeof(string)));
tbl.Columns.Add(new DataColumn("Engineer_Email_Address", typeof(string)));
tbl.Columns.Add(new DataColumn("Product_Model", typeof(string)));
tbl.Columns.Add(new DataColumn("Fiscal_Year", typeof(string)));
tbl.Columns.Add(new DataColumn("Fiscal_Quarter", typeof(string)));
tbl.Columns.Add(new DataColumn("Month", typeof(string)));
tbl.Columns.Add(new DataColumn("Week", typeof(string)));
tbl.Columns.Add(new DataColumn("Closed_Date", typeof(DateTime)));
tbl.Columns.Add(new DataColumn("Resolution_Code", typeof(string)));
tbl.Columns.Add(new DataColumn("Sub_Resolution_Code", typeof(string)));
tbl.Columns.Add(new DataColumn("MRU_Code", typeof(string)));
tbl.Columns.Add(new DataColumn("Sub_MRU_Code", typeof(string)));
tbl.Columns.Add(new DataColumn("Region", typeof(string)));
tbl.Columns.Add(new DataColumn("Geography", typeof(string)));
tbl.Columns.Add(new DataColumn("SubGeo1", typeof(string)));
tbl.Columns.Add(new DataColumn("SubGeo2", typeof(string)));
tbl.Columns.Add(new DataColumn("Country_Name", typeof(string)));
tbl.Columns.Add(new DataColumn("Case_Coverage_Response", typeof(string)));
tbl.Columns.Add(new DataColumn("Case_Entitlement_SLA", typeof(string)));
tbl.Columns.Add(new DataColumn("Order_Type_Code", typeof(string)));
tbl.Columns.Add(new DataColumn("Resolution_Type", typeof(string)));
tbl.Columns.Add(new DataColumn("Support_Level", typeof(string)));
tbl.Columns.Add(new DataColumn("CSCERTCase", typeof(string)));
tbl.Columns.Add(new DataColumn("Total_Case_Volume", typeof(string)));
foreach (var item in totalCaseVolumes)
{
DataRow dr = tbl.NewRow();
dr["Case_Number"] = item.Case_Number;
dr["Case_Status"] = item.Case_Status;
dr["Case_Type"] = item.Case_Type;
dr["Delivery_Method"] = item.Delivery_Method;
dr["Case_Origin"] = item.Case_Origin;
dr["Create_Date"] = item.Create_Date;
dr["Account_Name"] = item.Account_Name;
dr["Case_Title"] = item.Case_Title;
dr["Level_3_Description"] = item.Level_3_Description;
dr["Level_4_Description"] = item.Level_4_Description;
dr["Level_5_Description"] = item.Level_5_Description;
dr["Generation"] = item.Generation;
dr["Product_Line"] = item.Product_Line;
dr["Product_Group"] = item.Product_Group;
dr["Product_No"] = item.Product_No;
dr["Serial_Number"] = item.Serial_Number;
dr["Business"] = item.Business;
dr["Service_Portfolio"] = item.Service_Portfolio;
dr["Severity"] = item.Severity;
dr["CSC_Facility"] = item.CSC_Facility;
dr["Manager_Email_Address"] = item.Manager_Email_Address;
dr["Primary_Role"] = item.Primary_Role;
dr["Secondary_Role"] = item.Secondary_Role;
dr["Engineer_Email_Address"] = item.Engineer_Email_Address;
dr["Product_Model"] = item.Product_Model;
dr["Fiscal_Year"] = item.Fiscal_Year;
dr["Fiscal_Quarter"] = item.Fiscal_Quarter;
dr["Month"] = item.Month;
dr["Week"] = item.Week;
dr["Closed_Date"] = item.Closed_Date;
dr["Resolution_Code"] = item.Resolution_Code;
dr["Sub_Resolution_Code"] = item.Sub_Resolution_Code;
dr["MRU_Code"] = item.MRU_Code;
dr["Sub_MRU_Code"] = item.Sub_MRU_Code;
dr["Region"] = item.Region;
dr["Geography"] = item.Geography;
dr["SubGeo1"] = item.SubGeo1;
dr["SubGeo2"] = item.SubGeo2;
dr["Country_Name"] = item.Country_Name;
dr["Case_Coverage_Response"] = item.Case_Coverage_Response;
dr["Case_Entitlement_SLA"] = item.Case_Entitlement_SLA;
dr["Order_Type_Code"] = item.Order_Type_Code;
dr["Resolution_Type"] = item.Resolution_Type;
dr["Support_Level"] = item.Support_Level;
dr["CSCERTCase"] = item.CSCERTCase;
dr["Total_Case_Volume"] = item.Total_Case_Volume;
tbl.Rows.Add(dr);
}
string connection = (new MetricsJunctionEntities()).Database.Connection.ConnectionString;
SqlConnection con = new SqlConnection(connection);
//create object of SqlBulkCopy which help to insert
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.BulkCopyTimeout = 0;
//assign Destination table name
objbulk.DestinationTableName = "TotalCaseVolume_RawDump";
objbulk.ColumnMappings.Add("Case_Number", "Case_Number");
objbulk.ColumnMappings.Add("Case_Status", "Case_Status");
objbulk.ColumnMappings.Add("Case_Type", "Case_Type");
objbulk.ColumnMappings.Add("Delivery_Method", "Delivery_Method");
objbulk.ColumnMappings.Add("Case_Origin", "Case_Origin");
objbulk.ColumnMappings.Add("Create_Date", "Create_Date");
objbulk.ColumnMappings.Add("Account_Name", "Account_Name");
objbulk.ColumnMappings.Add("Case_Title", "Case_Title");
objbulk.ColumnMappings.Add("Level_3_Description", "Level_3_Description");
objbulk.ColumnMappings.Add("Level_4_Description", "Level_4_Description");
objbulk.ColumnMappings.Add("Level_5_Description", "Level_5_Description");
objbulk.ColumnMappings.Add("Generation", "Generation");
objbulk.ColumnMappings.Add("Product_Line", "Product_Line");
objbulk.ColumnMappings.Add("Product_Group", "Product_Group");
objbulk.ColumnMappings.Add("Product_No", "Product_No");
objbulk.ColumnMappings.Add("Serial_Number", "Serial_Number");
objbulk.ColumnMappings.Add("Business", "Business");
objbulk.ColumnMappings.Add("Service_Portfolio", "Service_Portfolio");
objbulk.ColumnMappings.Add("Severity", "Severity");
objbulk.ColumnMappings.Add("CSC_Facility", "CSC_Facility");
objbulk.ColumnMappings.Add("Manager_Email_Address", "Manager_Email_Address");
objbulk.ColumnMappings.Add("Primary_Role", "Primary_Role");
objbulk.ColumnMappings.Add("Secondary_Role", "Secondary_Role");
objbulk.ColumnMappings.Add("Engineer_Email_Address", "Engineer_Email_Address");
objbulk.ColumnMappings.Add("Product_Model", "Product_Model");
objbulk.ColumnMappings.Add("Fiscal_Year", "Fiscal_Year");
objbulk.ColumnMappings.Add("Fiscal_Quarter", "Fiscal_Quarter");
objbulk.ColumnMappings.Add("Month", "Month");
objbulk.ColumnMappings.Add("Week", "Week");
objbulk.ColumnMappings.Add("Closed_Date", "Closed_Date");
objbulk.ColumnMappings.Add("Resolution_Code", "Resolution_Code");
objbulk.ColumnMappings.Add("Sub_Resolution_Code", "Sub_Resolution_Code");
objbulk.ColumnMappings.Add("MRU_Code", "MRU_Code");
objbulk.ColumnMappings.Add("Sub_MRU_Code", "Sub_MRU_Code");
objbulk.ColumnMappings.Add("Region", "Region");
objbulk.ColumnMappings.Add("Geography", "Geography");
objbulk.ColumnMappings.Add("SubGeo1", "SubGeo1");
objbulk.ColumnMappings.Add("SubGeo2", "SubGeo2");
objbulk.ColumnMappings.Add("Country_Name", "Country_Name");
objbulk.ColumnMappings.Add("Case_Coverage_Response", "Case_Coverage_Response");
objbulk.ColumnMappings.Add("Case_Entitlement_SLA", "Case_Entitlement_SLA");
objbulk.ColumnMappings.Add("Order_Type_Code", "Order_Type_Code");
objbulk.ColumnMappings.Add("Resolution_Type", "Resolution_Type");
objbulk.ColumnMappings.Add("Support_Level", "Support_Level");
objbulk.ColumnMappings.Add("CSCERTCase", "CSCERTCase");
objbulk.ColumnMappings.Add("Total_Case_Volume", "Total_Case_Volume");
con.Open();
//insert bulk Records into DataBase.
objbulk.WriteToServer(tbl);
con.Close();
/*********************************Bulk Insert Using Dapper Plus*******************************************/
//DapperPlusManager.Entity<TotalCaseVolume>().Table("TotalCaseVolume_RawDump");
//using (IDbConnection db = (new MetricsJunctionEntities()).Database.Connection)
//{
// int to = db.ConnectionTimeout;
// db.BulkInsert(totalCaseVolumes);
//}
}
catch (Exception ex)
{
throw ex;
}
}