/// <summary> /// Should be executed only once - when we need to populate the /// MS SQL Server database. /// </summary> private static void CopyMySQLDataToMSSQLServer() { using (SupermarketModel mySQLContext = new SupermarketModel()) { using (SupermarketEntities msSQLServerContext = new SupermarketEntities()) { foreach (var mySQLVendor in mySQLContext.Vendors) { var msSQLServerVendor = new EntityFrameworkModels.Vendor { VendorName = mySQLVendor.VendorName }; msSQLServerContext.Vendors.Add(msSQLServerVendor); msSQLServerContext.SaveChanges(); } foreach (var mySQLUnit in mySQLContext.Units) { var msSQLServerUnit = new EntityFrameworkModels.Unit { UnitName = mySQLUnit.UnitName }; msSQLServerContext.Units.Add(msSQLServerUnit); msSQLServerContext.SaveChanges(); } foreach (var mySQLProduct in mySQLContext.Products) { var msSQLServerProduct = new EntityFrameworkModels.Product { VendorId = mySQLProduct.VendorID, UnitId = mySQLProduct.UnitID, ProductName = mySQLProduct.ProductName, BasePrice = mySQLProduct.BasePrice }; msSQLServerContext.Products.Add(msSQLServerProduct); msSQLServerContext.SaveChanges(); } } } }
/// <summary> /// Method for extracting expenses reports in XML format /// </summary> /// <param name="filePath">Filepath for the .xml file</param> public static void ReadVendorMonthlyExpenses(string filePath) { XmlReader reader = XmlReader.Create(filePath); List<MongoVendorExpense> mongoVendorExpensesList = new List<MongoVendorExpense>(); using (var msSQLServerContext = new SupermarketEntities()) { using (reader) { int vendorId = 0; while (reader.Read()) { //Insert a new vendor if (reader.NodeType == XmlNodeType.Element && reader.Name == "sale") { string vendorName = reader.GetAttribute("vendor"); var vendor = msSQLServerContext.Vendors.FirstOrDefault(v => v.VendorName == vendorName); if (vendor == null) { // the vendor doesn't exist vendor = new Vendor { VendorName = vendorName }; msSQLServerContext.Vendors.Add(vendor); msSQLServerContext.SaveChanges(); } vendorId = vendor.VendorId; } //Insert Expenses if (reader.NodeType == XmlNodeType.Element && reader.Name == "expenses") { string month = reader.GetAttribute("month"); DateTime monthAsDate = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture); decimal amount = reader.ReadElementContentAsDecimal(); var vendorMonthlyExpense = new VendorExpense { VendorId = vendorId, Month = monthAsDate.Month, Year = monthAsDate.Year, Amount = amount }; var mongoVendorExpense = new MongoVendorExpense { VendorId = vendorId, Month = monthAsDate.Month, Year = monthAsDate.Year, Amount = amount }; mongoVendorExpensesList.Add(mongoVendorExpense); msSQLServerContext.VendorExpenses.Add(vendorMonthlyExpense); } } } MongoDBManager<MongoVendorExpense> mongoDBInserter = new MongoDBManager<MongoVendorExpense>(); mongoDBInserter.InsertInMongoDB(mongoVendorExpensesList, "VendorExpenses"); msSQLServerContext.SaveChanges(); } }
private static void InsertExcelFileDataInMSSQLServerDB( DateTime reportDate, System.Data.DataTable contentsTable, SupermarketEntities msSQLServerContext) { using (TransactionScope scope = new TransactionScope()) { string localShopName = contentsTable.Rows[0][0].ToString().Trim(); var localShop = msSQLServerContext.LocalShops.FirstOrDefault(ls => ls.LocalShopName == localShopName); if (localShop == null) { localShop = new LocalShop { LocalShopName = localShopName }; msSQLServerContext.LocalShops.Add(localShop); msSQLServerContext.SaveChanges(); } int rowsCount = contentsTable.Rows.Count; int colsCount = contentsTable.Columns.Count; var report = new Report { LocalShopId = localShop.LocalShopId, ReportDate = reportDate, ReportTotalSum = decimal.Parse(contentsTable.Rows[rowsCount - 1][3].ToString()) }; msSQLServerContext.Reports.Add(report); msSQLServerContext.SaveChanges(); for (int row = 2; row < rowsCount - 1; row++) { int productId = int.Parse(contentsTable.Rows[row][0].ToString()); double productQuantity = double.Parse(contentsTable.Rows[row][1].ToString()); decimal productUnitPrice = decimal.Parse(contentsTable.Rows[row][2].ToString()); decimal productTotalSum = decimal.Parse(contentsTable.Rows[row][3].ToString()); var sale = new Sale { ReportId = report.ReportId, ProductId = productId, ProductQuantity = productQuantity, ProductUnitPrice = productUnitPrice, ProductTotalSum = productTotalSum }; msSQLServerContext.Sales.Add(sale); } msSQLServerContext.SaveChanges(); scope.Complete(); } }