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); } } } } }