public static void Transfer(SupermarketsEntities sqlserver)
{
using (var mysql = new MySqlSupermarket())
{
// SET IDENTITY_INSERT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188059.aspx
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Vendors ON");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Measures ON");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Products ON");
var mysqlTables = new IEnumerable[] { mysql.Vendors, mysql.Measures, mysql.Products };
var sqlserverEntityFactories = new Func<object>[] { () => new Vendor(), () => new Measure(), () => new Product() };
var sqlserverTables = new DbSet[] { sqlserver.Vendors, sqlserver.Measures, sqlserver.Products };
for (int ii = 0; ii < mysqlTables.Length; ii++)
{
foreach (var mysqlObject in mysqlTables[ii])
{
var sqlserverObject = sqlserverEntityFactories[ii]();
sqlserverObject.LoadPropertiesFrom(mysqlObject);
sqlserverTables[ii].Add(sqlserverObject);
}
}
sqlserver.SaveChanges();
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Vendors OFF");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Measures OFF");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Products OFF");
}
}
public static void Transfer(SupermarketsEntities sqlserver)
{
var data = ExcelReader.GetReportsData("files\\reports.zip", "files\\temp");
foreach (var superMarketSales in data.GroupBy(o => o[0]))
{
var supermarket = sqlserver.Supermarkets.Where(v => v.Name == superMarketSales.Key).FirstOrDefault();
if (supermarket == null)
{
supermarket = new Supermarket { Name = superMarketSales.Key };
sqlserver.Supermarkets.Add(supermarket);
}
foreach (var saleData in superMarketSales)
{
var sale = new ProductSupermarketSale
{
Supermarket = supermarket,
DateSold = DateTime.Parse(saleData[1]),
ProductId = int.Parse(saleData[2]),
UnitPrice = decimal.Parse(saleData[3]),
Quantity = decimal.Parse(saleData[4]),
};
sqlserver.Sales.Add(sale);
}
}
sqlserver.SaveChanges();
}
public static void WriteVendorExpensesReport(SupermarketsEntities sqlserver, string filename)
{
using (XmlReader reader = XmlReader.Create(filename))
{
string currentVendor = string.Empty;
while (reader.Read())
{
if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "sale"))
{
currentVendor = reader.GetAttribute("vendor");
if (sqlserver.Vendors.Any(v => v.Name == currentVendor))
{
Vendor vendor = new Vendor();
vendor.Name = currentVendor;
sqlserver.Vendors.Add(vendor);
}
}
if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "expenses"))
{
Vendor vendor = sqlserver.Vendors.Where(v => v.Name == currentVendor).FirstOrDefault();
if (vendor != null)
{
DateTime monthDate = DateTime.Parse(string.Format("01-{0}", reader.GetAttribute("month")));
VendorExpenses expense = new VendorExpenses();
expense.VendorId = vendor.Id;
expense.Month = monthDate.Month;
expense.Year = monthDate.Year;
expense.Expenses = reader.ReadElementContentAsDecimal();
sqlserver.VendorExpenses.Add(expense);
AddToMongoDB(expense);
}
}
}
sqlserver.SaveChanges();
}
}
