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