private void ReplicateProducts(SalesSystemContext sqlContext) { var oracleDb = new OracleEntities(); var products = oracleDb.PRODUCTS.Select(p => new { p.NAME, p.MESURE_ID, p.PRICE, p.VENDOR_ID, }); foreach (var product in products) { Measure measure = Measure.Pcs; switch ((int)product.MESURE_ID) { case 2: measure = Measure.L; break; case 3: measure = Measure.Ml; break; case 4: measure = Measure.Kg; break; case 5: measure = Measure.Gr; break; } sqlContext.Products.AddOrUpdate(p => p.Name, new Product() { Name = product.NAME, Price = product.PRICE, VendorId = (int)product.VENDOR_ID, Measure = measure }); } sqlContext.SaveChanges(); }
public static void PushXmlToDb(string filePath) { try { // LOAD XML XDocument xmlDocument = XDocument.Load(filePath); var vendors = xmlDocument.Descendants("vendor"); if (vendors.Any() == false) { throw new ArgumentException(@"No data found, ensure that the file contains any vendors reports."); } else { using (var db = new SalesSystemContext()) { foreach (var vendor in vendors) { // VENDRO NAME var vendorName = vendor.FirstAttribute.Value; var vendorExpenses = vendor.Descendants("expenses"); // VENDOR ID var vendorId = db.Vendors.FirstOrDefault(v => v.Name == vendorName).Id; foreach (var vendorExpense in vendorExpenses) { // EXPENSE PERIOD (MONTH - YEAR) var vendorExpensePeriod = vendorExpense.FirstAttribute.Value; // EXPENSE TOTAL (MONEY) var vendorExpenseTotal = decimal.Parse(vendorExpense.Value); // ADD EXPENSE db.Expenses.Add(new Expense() { VendorId = vendorId, Period = vendorExpensePeriod, Total = vendorExpenseTotal }); } } // ON SUCCESS db.SaveChanges(); MessageBox.Show(@"Xml data successiful imported in MSSQL database."); } } } catch (Exception e) { MessageBox.Show(e.StackTrace); } }
// MIGRATE DATA public static void MigrateData(string zipFilePath) { ExtractZipFile(zipFilePath); using (var db = new SalesSystemContext()) { IList<Sale> allSales = new List<Sale>(); GetSales(TempFolderForExtract, db, allSales); foreach (var sale in allSales) { db.Sales.Add(sale); } db.SaveChanges(); } Directory.Delete(TempFolderForExtract, true); }
// GET SALES FROM EXCEL FILES private static ICollection<Sale> GetSalesFromExcelFiles(string directory, SalesSystemContext db) { IList<Sale> sales = new List<Sale>(); string[] excelFilePaths = Directory.GetFiles(directory, @"*.xls"); foreach (var excelFilePath in excelFilePaths) { var excelConnectionString = String.Format(Settings.Default.ExcelReadConnectionString, excelFilePath); var excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataSet dataSet = new DataSet(); using (excelConnection) { var selectAllRowsCommandString = "SELECT * FROM [Sales$]"; var selectAllRowsCommand = new OleDbCommand(selectAllRowsCommandString, excelConnection); var excelAdapter = new OleDbDataAdapter(selectAllRowsCommand); excelAdapter.Fill(dataSet, @"Sales"); } var excelRows = dataSet.Tables[@"Sales"].Rows; var supermarketName = excelRows[0][0].ToString(); if (!db.Supermarkets.Any(s => s.Name == supermarketName)) { db.Supermarkets.Add(new Supermarket() { Name = supermarketName, Location = supermarketName.Replace("Supermarket", "").Trim() }); db.SaveChanges(); } var rowsCount = excelRows.Count; for (int i = 2; i < rowsCount - 1; i++) { var productName = excelRows[i][0].ToString(); var product = db.Products.FirstOrDefault(p => p.Name == productName); bool isAdded = false; string saleDateString = Path.GetFileName(Path.GetDirectoryName(excelFilePath)); int supermarketId = db.Supermarkets.First(s => s.Name == supermarketName).Id; if (db.Sales.Any()) { var addedsales = db.Sales.Select(s => new { Date = s.Date, Spp = s.SupermarketId, }); foreach (var addsale in addedsales) { if (addsale.Date == DateTime.Parse(saleDateString) && addsale.Spp == supermarketId) { isAdded = true; } } } // IF PRODUCT EXIST if (product != null && isAdded == false) { int productId = product.Id, quantity; int.TryParse(excelRows[i][1].ToString(), out quantity); decimal price; decimal.TryParse(excelRows[i][2].ToString(), out price); sales.Add(new Sale() { Date = DateTime.Parse(saleDateString), Product = db.Products.Find(productId), Quantity = quantity, Supermarket = db.Supermarkets.Find(supermarketId), Price = price, ItemSum = price*quantity }); } // ADD NEW PRODUCT else { // todo add new product db.Products.Add(new Product() { Name = productName, VendorId = 1, Measure = new Measure(), Price = 1 }); db.SaveChanges(); } } } // RETURN VALUE return sales; }