public void Export(string xmlFullFileName, SupermarketContext sqlServerContext) { XDocument xmlDoc = XDocument.Load(xmlFullFileName); var sales = xmlDoc.Descendants("sale").ToList(); int vendorId = -1; string vendorName = string.Empty; foreach (var sale in sales) { vendorName = sale.Attribute("vendor").Value; vendorId = sqlServerContext.Vendors.Where(x => x.VendorName == vendorName). Select(x => x.Id).FirstOrDefault(); var expenses = sale.Descendants("expenses"); foreach (var expense in expenses) { MongoDbExpenseFormat expenseObject = new MongoDbExpenseFormat() { VendorName = vendorName, VendorId = vendorId, Month = DateTime.Parse(expense.Attribute("month").Value), Expense = decimal.Parse(expense.Value), }; MongoDbProvider.db.SaveData<MongoDbExpenseFormat>(expenseObject); Expense sqlExpense = new Expense() { VendorId = expenseObject.VendorId, Month = expenseObject.Month, ExpenseValue = expenseObject.Expense }; sqlServerContext.Expenses.Add(sqlExpense); sqlServerContext.SaveChanges(); } } }
private void ExportSingleExcelFile(FileInfo file, int dateId, OleDbConnection excelConnection, SupermarketContext sqlServerDb) { excelConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + string.Format("Data Source={0};", file.FullName) + @"Extended Properties=""Excel 12.0;HDR=YES"""; excelConnection.Open(); int supermarketId = -1; string supermarketName = file.Name.Substring(0, file.Name.IndexOf("-Sales-")); if (sqlServerDb.Supermarkets. Where(x => x.Name == supermarketName).Count() == 0) { sqlServerDb.Supermarkets.Add(new Supermarket() { Name = supermarketName }); sqlServerDb.SaveChanges(); } supermarketId = sqlServerDb.Supermarkets. Where(x => x.Name == supermarketName). Select(x => x.SupermarketId).First(); DataTable table = new DataTable(); using (excelConnection) { OleDbDataAdapter adapter = new OleDbDataAdapter(@"SELECT * FROM [Sales$]", excelConnection); adapter.Fill(table); for (int i = 0; i < table.Rows.Count - 1; i++) { int productId = -1, quantity = -1; decimal unitPrice = -1, sum = -1; IEnumerable<object> data = table.Rows[i].ItemArray.SkipWhile(x => x.GetType().Equals(typeof(System.DBNull))); int currentColumnIndex = 0; foreach (var item in data) { if (item.GetType() == typeof(System.DBNull)) { //Console.WriteLine(item.GetType()); continue; } else { switch (currentColumnIndex) { case 0: { productId = (int)((double) item); break; } case 1: { quantity = (int)((double) item); break; } case 2: { unitPrice = (decimal)((double) item); break; } case 3: { sum = (decimal)((double) item); break; } } } currentColumnIndex++; } if (productId != -1) { //Console.WriteLine("{0}. {1} -> {2}", i, currentColumnIndex, item); AddSale(productId, quantity, unitPrice, sum, sqlServerDb, dateId, supermarketId); } } } }
public void Export(string zipFileName, string extractionFolder, OleDbConnection excelConnection, SupermarketContext sqlServerDb) { ExtractTo(zipFileName, extractionFolder); DirectoryInfo directory = new DirectoryInfo(extractionFolder); foreach (var dir in directory.GetDirectories()) { int dateId = -1; //Console.WriteLine("{0}", dir.Name); DateTime date = DateTime.Parse(dir.Name); if (sqlServerDb.SoldDates.Where(x => x.Date == date).Count() == 0) { sqlServerDb.SoldDates.Add(new SoldDate() { Date = date }); sqlServerDb.SaveChanges(); } dateId = sqlServerDb.SoldDates.Where(x => x.Date.Equals(date)).Select(x => x.SoldDateId).First(); foreach (var file in dir.GetFiles()) { ExportSingleExcelFile(file, dateId, excelConnection, sqlServerDb); } } }
private void AddSale(int productId, int quantity, decimal unitPrice, decimal sum, SupermarketContext sqlServerDb, int dateId, int supermarketId) { Sale sale = new Sale() { ProductId = productId, Quantity = quantity, UnitPrice = unitPrice, Sum = sum, SoldDateId = dateId, SupermarketId = supermarketId }; sqlServerDb.Sales.Add(sale); sqlServerDb.SaveChanges(); }