public string ImportDataTableToProduct(DataTable dt, string branchName, int storeId, int branchId)
{
string message = "";
try
{
bool temploadStatus = LoadTempProductAndPricing(dt, branchName, storeId, branchId);
bool tempFeaturesUploaded = LoadTempKeyFeaturesAndDetailedSpec(dt);
string loadProductStatus = LoadProductTableFromTemp(temploadStatus);
message = message + loadProductStatus;
LoadProductData loadDataObject = new LoadProductData();
int loadPricingStatus = loadDataObject.LoadPricing();
message = message + "Updated Pricing for" + loadPricingStatus.ToString();
int loadKeyFeatureStatus = loadDataObject.LoadKeyFeatures();
message = message + "Updated KeyFeatures for" + loadKeyFeatureStatus.ToString();
int loadDetailedSpecStatus = loadDataObject.LoadDetailedSpecification();
message = message + "Updated KeyFeatures for" + loadDetailedSpecStatus.ToString();
//Load filters.
int masterFilter = loadDataObject.LoadCategoryMaster();
loadDataObject.LoadProductFilterValueTableFromtemp_ProductFilterValueWithProductName();
int loadProductFilterStatus = loadDataObject.LoadProductFilterValue();
message = message + "Updated KeyFeatures for" + loadProductFilterStatus.ToString();
}
catch (Exception ex)
{
return(message + ex.Message);
}
return(message);
}
internal object LoadTempProductFilterValueWithProductName(string filename)
{
string ssqltable = "temp_ProductFilterValueWithProductName";
var affectedRows = 0;
string myexceldataquery = @"select PRODUCTNAME,FilterParameter,FilterValue,FilterValueText
from [Sheet1$]";
try
{
//create our connection strings
// string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + filename + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + filename +
";extended properties=" + "\"excel 8.0;hdr=yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
//execute a query to erase any previous data from our destination table
//TODO:-
//series of commands to bulk copy data from the excel file into our sql table
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
try
{
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlConnectionString);
bulkcopy.DestinationTableName = ssqltable;
MapProductFilterValueWithNameColumns(bulkcopy);
bulkcopy.WriteToServer(dr);
oledbconn.Close();
LoadProductData executeLoadProduct = new LoadProductData();
executeLoadProduct.LoadProductFilterValueTableFromtemp_ProductFilterValueWithProductName();
executeLoadProduct.LoadCategoryMaster();
affectedRows = executeLoadProduct.LoadProductFilterValue();
}
catch (Exception ex)
{
var s = ex.Message;
logger.Error("-------------------ERROR LOG -----------------");
logger.Error(ex.Message + ex.InnerException);
logger.Error("-----------------END OF ERROR LOG-----------------");
}
finally
{
oledbconn.Close();
}
return(affectedRows);
}
catch (Exception ex)
{
var s = ex.Message;
logger.Error("-------------------ERROR LOG -----------------");
logger.Error(ex.Message + ex.InnerException);
logger.Error("-----------------END OF ERROR LOG-----------------");
}
finally
{
}
return(affectedRows);
}