public static MSSQLEntityModel.Product ConvertMySqlToMSSQLProduct(OpenAccessModelSupermarkets.Product mySqlProduct)
{
MSSQLEntityModel.Product mssqlProduct = new MSSQLEntityModel.Product();
mssqlProduct.ProductName = mySqlProduct.ProductName;
mssqlProduct.ProductPrice = mySqlProduct.ProductPrice;
mssqlProduct.VendorId = mySqlProduct.VendorId;
mssqlProduct.MeasureId = mySqlProduct.MeasureId;
mssqlProduct.ProductsId = mySqlProduct.ProductId;
return(mssqlProduct);
}
private static void ExportExcelFileData(SupermarketsEntities1 mssqlSupermarketContext, DateTime currentDate, string currentFile, Dictionary <string, Supermarket> supermarketsInTheDB)
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + currentFile + ";Extended Properties=Excel 12.0;";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
using (excelConnection)
{
OleDbCommand getExcelSheetData = new OleDbCommand(
"SELECT * " +
"FROM [Sales$]", excelConnection);
OleDbDataReader reader = getExcelSheetData.ExecuteReader();
using (reader)
{
bool headerRow = true;
Report newReport = null;
while (reader.Read())
{
var firstColumnValue = reader[0];
if (headerRow)
{
string supermarketNameFull = (string)firstColumnValue;
int indexFirstQuote = supermarketNameFull.IndexOf('“');
int indexSecondQuote = supermarketNameFull.IndexOf('”');
string supermarketName = supermarketNameFull.Substring(indexFirstQuote + 1, indexSecondQuote - indexFirstQuote - 1);
Supermarket currentSupermarket;
if (supermarketsInTheDB.ContainsKey(supermarketName))
{
currentSupermarket = supermarketsInTheDB[supermarketName];
}
else
{
currentSupermarket = new Supermarket();
currentSupermarket.SupermarketName = supermarketName;
mssqlSupermarketContext.Supermarkets.Add(currentSupermarket);
supermarketsInTheDB[supermarketName] = currentSupermarket;
}
newReport = new Report();
newReport.ReportDate = currentDate;
currentSupermarket.Reports.Add(newReport);
headerRow = false;
}
;
int productId;
if (int.TryParse((string)firstColumnValue, out productId))
{
ReportDetail newReportDetail = new ReportDetail();
string secondColumnValue = reader[1].ToString();
newReportDetail.Quantity = int.Parse(secondColumnValue);
string thirdColumnValue = reader[2].ToString();
newReportDetail.UnitPrice = decimal.Parse(thirdColumnValue);
newReport.ReportDetails.Add(newReportDetail);
MSSQLEntityModel.Product currentProduct = mssqlSupermarketContext.Products.Find(productId);
currentProduct.ReportDetails.Add(newReportDetail);
}
}
}
}
}