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