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);
}
private string LoadProductTableFromTemp(bool temploadStatus)
{
string message = "";
LoadProductData loadDataObject = new LoadProductData();
if (temploadStatus)
{
message = " \r\n " + " Excel Validated for product......";
try
{
int numNewCategory = loadDataObject.LoadCategory();
message = message + " \r\n " + " Successfully loaded " + numNewCategory + " number of category";
int numManufacturer = loadDataObject.LoadManufacturer();
message = message + " \r\n " + " Successfully loaded " + numManufacturer + " number of Brands";
int loadedProductCount = loadDataObject.LoadProductsAndMapping();
message = message + " \r\n " + "Successfully loaded " + loadedProductCount + " number of Products";
}
catch (Exception ex)
{
message = message + " \r\n " + ex.Message;
}
}
else
{
message = message + " \r\n " + " Excel is having issue. Please Check for Column is blank or any issue in excel data......";
message = message + " \r\n " + "Error loading products";
message = message + " \r\n " + "Error loading products...Excel Is having issue. Please Check for Column is blank or any issue in excel data";
}
message = message + " \r\n " + " Done with Updating Products... " + DateTime.Now.ToString();
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);
}