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