Posted 25 Apr 2018
Link to this post
public override void SaveWorkbook(Workbook workbook)
{
string DBName = "";
DBName = GetSql.POGProd;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBName))
{
//conn.Open();
//string query = "DELETE FROM BulkSkuUpdate";
//SqlCommand clearCmd = new SqlCommand(query, conn);
//clearCmd.ExecuteNonQuery();
//conn.Close();
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Create a datatable to store the data in - do not create dataset - a dataset will add to much
// overhead to the insert process. ---- First create and VAR a datarow in the loop, Next in the VAR
// the Row.Cell and loop thru each row and add the data to dataset row
// Last use SqlBulkCopy to insert all the data into the database.
// Below are the setting that I have found work the best currently 04-16-2018
// sqlBulkCopy.BatchSize = 10000; sqlBulkCopy.NotifyAfter = 15000; sqlBulkCopy.BulkCopyTimeout = 300;
// We can isert 15,000 rows and 10 columns = 150,000 datapoints in less than 1:30s in Edge/Firefox/Chrome
// Internet explore does not insert the data is just sets there. IE can only handle about 6,000 rows of data
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
int rowcount = workbook.Sheets[0].Rows.Count;
string hits = Convert.ToString(rowcount);
DataTable dt = new DataTable("MyTable");
dt.Columns.Add(new DataColumn("storenum", typeof(string)));
dt.Columns.Add(new DataColumn("ard", typeof(string)));
dt.Columns.Add(new DataColumn("dm", typeof(string)));
dt.Columns.Add(new DataColumn("som", typeof(string)));
dt.Columns.Add(new DataColumn("sku", typeof(string)));
dt.Columns.Add(new DataColumn("description", typeof(string)));
dt.Columns.Add(new DataColumn("First_Request_Date", typeof(string)));
dt.Columns.Add(new DataColumn("qtytokeep", typeof(string)));
dt.Columns.Add(new DataColumn("notes", typeof(string)));
dt.Columns.Add(new DataColumn("lastupdateddate", typeof(string)));
foreach (var row in workbook.Sheets[0].Rows)
{
DataRow dr = dt.NewRow();
foreach (var cell in row.Cells)
{
dr[(int)cell.Index] = cell.Value;
}
dt.Rows.InsertAt(dr, (int)row.Index);
}
string insertQuery = "INSERT INTO BulkSkuUpdate(storenum, ard, dm, som, sku, description, First_Request_Date, Qty_to_Keep, notes, Last_Updated_Date) " +
" VALUES(@storenum, @ard, @dm, @som, @sku, @description, @First_Request_Date, @qtytokeep, @notes, @lastupdateddate)";
System.Data.SqlClient.SqlConnection DBConn2;
try
{
string bulksql = GetSql.POGProd;
DBConn2 = new System.Data.SqlClient.SqlConnection(bulksql);
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DBConn2))
{
DBConn2.Open();
//Set the database table name
sqlBulkCopy.BatchSize = 10000;
sqlBulkCopy.NotifyAfter = 15000;
sqlBulkCopy.BulkCopyTimeout = 300;
sqlBulkCopy.DestinationTableName = "BulkSkuUpdate";
sqlBulkCopy.WriteToServer(dt);
DBConn2.Close();
}
}
}
finally
{
DBConn2 = null;
}
LogAction LogAction2 = new LogAction(Globals._servername, Globals._URLName, Globals._ADID, hits, insertQuery);
}
}