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