public void MigrateDataFromMySqlToSqlServer() { using (var supermarketContextSqlServer = new SupermarketContext()) { using (var supermarketContextMySql = new SupermarketModel()) { foreach (var measureMySql in supermarketContextMySql.Measures) { if (!supermarketContextSqlServer.Measures .Any(m => m.MeasureName == measureMySql.MeasureName)) { supermarketContextSqlServer.Measures.Add(new Models.Measure() { MeasureName = measureMySql.MeasureName }); } } foreach (var vendorMySql in supermarketContextMySql.Vendors) { if (!supermarketContextSqlServer.Vendors .Any(v => v.VendorName == vendorMySql.VendorName)) { supermarketContextSqlServer.Vendors.Add(new Models.Vendor() { VendorName = vendorMySql.VendorName }); } } supermarketContextSqlServer.SaveChanges(); foreach (var productMySql in supermarketContextMySql.Products) { if (!supermarketContextSqlServer.Products .Any(p => p.Name == productMySql.ProductName)) { var vendorSqlServer = supermarketContextSqlServer.Vendors .First(v => v.VendorName == productMySql.Vendor.VendorName); var measureSqlServer = supermarketContextSqlServer.Measures .First(m => m.MeasureName == productMySql.Measure.MeasureName); supermarketContextSqlServer.Products.Add(new Models.Product() { BasePrice = productMySql.BasePrice, Name = productMySql.ProductName, Measure = measureSqlServer, Vendor = vendorSqlServer, }); } } } supermarketContextSqlServer.SaveChanges(); } }
public ActionResult Create([Bind(Include = "ID,FirstName,LastName,Age,PhoneNum")] Client client) { if (ModelState.IsValid) { db.clients.Add(client); db.SaveChanges(); return(RedirectToAction("Index")); } return(View(client)); }
public ActionResult Create([Bind(Include = "SuplierID,SuplierName,Adress,Phone")] Suplier suplier) { if (ModelState.IsValid) { db.supliers.Add(suplier); db.SaveChanges(); return(RedirectToAction("Index")); } return(View(suplier)); }
public ActionResult Create([Bind(Include = "ID_чека,Время_открытия_чека,Время_закрытия_чека,Номер_карты,ID_кассира,ID_смены,Наличные")] Чеки чеки) { if (ModelState.IsValid) { чеки.ID_чека = Guid.NewGuid(); db.Чеки.Add(чеки); db.SaveChanges(); return(RedirectToAction("Index")); } ViewBag.Номер_карты = new SelectList(db.Дисконтные_карты, "Номер_карты", "Номер_карты", чеки.Номер_карты); return(View(чеки)); }
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); } } }
static void Main() { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load("../../ExpensesByVendorAndByMonth.xml"); var context = new SupermarketContext(); XmlNodeList Vendors = xmlDoc.DocumentElement.SelectNodes("/expenses-by-month/vendor"); foreach (XmlElement item in Vendors) { string vendorName = item.Attributes["name"].Value; foreach (XmlNode expense in item.ChildNodes) { DateTime expenseMonth = DateTime.Parse(expense.Attributes["month"].Value); decimal expensePrice = Convert.ToDecimal(expense.InnerText); try { var vendorId = context.Suppliers.First(v => v.Name == vendorName).Id; context.Expenses.Add(new Expense { Date = expenseMonth, Amount = expensePrice, SupplierId = vendorId }); context.SaveChanges(); } catch { throw new ArgumentNullException("Such vendor is not found in the database", vendorName); } } } }
private static void MoveProducts() { var client = new SupermarketModel(); using (client) { var products = client.Products; using (SupermarketContext context = new SupermarketContext()) { foreach (var item in products) { var product = new SupermarketSQL.Models.Product { ProductName = item.ProductName, BasePrice = item.BasePrice, MeasureId = item.MeasureId, VendorId = item.VendorId }; context.Products.Add(product); } context.SaveChanges(); } } }
public void FillTable() { SupermarketContext context = new SupermarketContext(); using (context) { foreach (var report in this.reports) { var supermarket = context.Supermarkets.Where(x => x.Name == report.Name).FirstOrDefault(); if (supermarket == null) { var newSupermarket = new Supermarket.Model.SalesReports.Supermarket() { Name = report.Name }; context.Supermarkets.Add(newSupermarket); supermarket = newSupermarket; context.SaveChanges(); } for (int i = 0; i < report.ProductID.Count; i++) { var newSalesReport = new SalesReport() { SupermarketId = supermarket.Id, ProductId = report.ProductID[i], Quantity = report.Quantity[i], UnitPrice = report.UnitPrice[i], Sum = report.Sum[i], Date = report.Date }; context.SalesReports.Add(newSalesReport); } } context.SaveChanges(); } }
private static void FromMySqlNeverAgain() { var dbcontext = new SupermarketContext(); var mySqlContx = new SupermarketModel(); using (mySqlContx) { var products = mySqlContx.Products.OrderBy(e => e.ID).ToList(); var vendors = mySqlContx.Vendors.ToList(); var mesuares = mySqlContx.Measures.ToList(); using (dbcontext) { foreach (var mesuare in mesuares) { var newMeasure = new Measure() { ID = mesuare.ID, Name = mesuare.Name }; dbcontext.Measures.Add(newMeasure); } foreach (var vendor in vendors) { var newVendor = new Vendor() { ID = vendor.ID, Name = vendor.Name }; dbcontext.Vendors.Add(newVendor); } foreach (var product in products) { var some = new Product { BasePrice = product.BasePrice, Measure_ID = product.Measure_ID, Name = product.Name, Vendor_ID = product.Vendor_ID, }; dbcontext.Products.Add(some); } dbcontext.SaveChanges(); } } }
///<summary> ///This is summary for method that transfer records from Oracle model into MQ SQL Server model. ///The method first select records from Oracle model which have not yet been copied or deleted. ///Second it inserts record into MS SQL Method and mark them as copied into Oracle model ///</summary> public static void UpdateVendorsFromOracle() { var oracleContext = new OracleEntities(); var msSqLcontext = new SupermarketContext(); var vendors = oracleContext.VENDORS .Where(v => v.ISCOPIED == false && v.ISDELETED == false) .Select(v => new { v.VENDORNAME }) .ToList(); if (vendors.Count > 0) { var addedVendorsList = new List <string>(); foreach (var vendor in vendors) { var vendorName = vendor.VENDORNAME; try { msSqLcontext.Vendors.AddOrUpdate( v => v.VendorName, new Vendor() { VendorName = vendorName }); msSqLcontext.SaveChanges(); addedVendorsList.Add(vendorName); } catch (Exception ex) { throw new ArgumentException(); } } var vendorsToChange = oracleContext.VENDORS.Where(v => addedVendorsList.Contains(v.VENDORNAME)).ToList(); vendorsToChange.ForEach(v => v.ISCOPIED = true); oracleContext.SaveChanges(); Console.WriteLine("\nAdded new Vendors from OracleBD into MS SQL Server:"); vendorsToChange.ForEach(v => Console.WriteLine("Added vendor name: {0}", v.VENDORNAME)); } else { Console.WriteLine("\nThere is no new records to import into VENDORS table!"); } }
///<summary> ///This is summary for method that transfer records from Oracle model into MQ SQL Server model. ///The method first select records from Oracle model which have not yet been copied or deleted. ///Second it inserts record into MS SQL Method and mark them as copied into Oracle model ///</summary> public static void UpdateProductsTypesFromOracle() { var oracleContext = new OracleEntities(); var msSqLcontext = new SupermarketContext(); var productstypes = oracleContext.PRODUCTSTYPES .Where(pt => pt.ISCOPIED == false && pt.ISDELETED == false) .Select(pt => new { pt.TYPENAME }).ToList(); if (productstypes.Count > 0) { var addedProductsTypesList = new List <string>(); foreach (var type in productstypes) { var typeName = type.TYPENAME; try { msSqLcontext.ProductTypes.AddOrUpdate( pt => pt.TypeName, new ProductType() { TypeName = typeName }); msSqLcontext.SaveChanges(); addedProductsTypesList.Add(typeName); } catch (Exception ex) { throw new ArgumentException(); } } var typesToChange = oracleContext.PRODUCTSTYPES.Where(pt => addedProductsTypesList.Contains(pt.TYPENAME)).ToList(); typesToChange.ForEach(pt => pt.ISCOPIED = true); oracleContext.SaveChanges(); Console.WriteLine("\nAdded new Types from OracleBD into MS SQL Server:"); typesToChange.ForEach(tp => Console.WriteLine("Added types name: {0}", tp.TYPENAME)); } else { Console.WriteLine("\nThere is no new records to import into PRODUCTSTYPES table!"); } }
public static void MigrateFromMySqlToMSSql() { using (var productContext = new ProductContext()) { using (var supermarketContext = new SupermarketContext()) { MigrateVendorsTable(productContext, supermarketContext); MigrateMeasuresTable(productContext, supermarketContext); MigrateProductsTable(productContext, supermarketContext); supermarketContext.SaveChanges(); } } }
///<summary> ///This is summary for method that transfer records from Oracle model into MQ SQL Server model. ///The method first select records from Oracle model which have not yet been copied or deleted. ///Second it inserts record into MS SQL Method and mark them as copied into Oracle model ///</summary> public static void UpdateMeasuresFromOracle() { var oracleContext = new OracleEntities(); var msSqLcontext = new SupermarketContext(); var measures = oracleContext.MEASURES .Where(m => m.ISCOPIED == false && m.ISDELETED == false) .Select(m => new { m.MEASURENAME }).ToList(); if (measures.Count > 0) { var addedMeasuresList = new List <string>(); foreach (var measure in measures) { var measureName = measure.MEASURENAME; try { msSqLcontext.Measures.AddOrUpdate( m => m.MeasureName, new Measure() { MeasureName = measureName }); msSqLcontext.SaveChanges(); addedMeasuresList.Add(measureName); } catch (Exception ex) { throw new ArgumentException(); } } var measuresToChange = oracleContext.MEASURES.Where(m => addedMeasuresList.Contains(m.MEASURENAME)).ToList(); measuresToChange.ForEach(m => m.ISCOPIED = true); oracleContext.SaveChanges(); Console.WriteLine("\nAdded new Measures from OracleBD into MS SQL Server:"); measuresToChange.ForEach(m => Console.WriteLine("Added measure name: {0}", m.MEASURENAME)); } else { Console.WriteLine("\nThere is no new records to import into MEASURES table!"); } }
private static void FillExpensesInSql(ICollection <Expense> expenses) { SupermarketContext context = new SupermarketContext(); using (context) { foreach (var expense in expenses) { context.Expenses.Add(expense); } context.SaveChanges(); } Console.WriteLine("5. Expenses saved in SQL Server!"); }
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(); }
/// <summary> /// Check for existing product type, because we want to have distinct data in the database and if the product type do not exists the method add it to the database. /// </summary> /// <param name="prodTypeName">the type to be checked</param> /// <param name="context">the Entity Framework connection to the database</param> private void CheckProductType(string prodTypeName, SupermarketContext context) { var existPodType = context.ProductTypes .Where(t => t.TypeName == prodTypeName) .Select(t => t.TypeName) .FirstOrDefault(); if (existPodType == null) { context.ProductTypes.Add(new ProductType { TypeName = prodTypeName }); context.SaveChanges(); } }
private static void ReplicateMeasures(SupermarketContext context) { var oracleDb = new OracleEntities(); var measures = oracleDb.MEASURES.ToList(); foreach (var measure in measures) { if (context.Measures.Any(m => m.Name == measure.NAME) == false) { context.Measures.Add(new Measure { Name = measure.NAME }); } } context.SaveChanges(); }
private static void ReplicateCategories(SupermarketContext context) { var oracleDb = new OracleEntities(); var categories = oracleDb.CATEGORIES.ToList(); foreach (var category in categories) { if (context.Categories.Any(c => c.Name == category.NAME) == false) { context.Categories.Add(new Category { Name = category.NAME, Description = category.DESCRIPTION }); } } context.SaveChanges(); }
private static void InitializeDataFromExcel() { var db = new SupermarketContext(); var generator = new Generator(@"..\..\Sample-Sales-Reports.zip", @"..\..\"); var reportList = generator.Generate(); using (db) { foreach (var item in reportList) { db.DailyReports.Add(item); } db.SaveChanges(); Console.WriteLine("1. Data from Excel saved in SQL Server!"); } }
private static void ReplicateCustomers(SupermarketContext context) { var oracleDb = new OracleEntities(); var customers = oracleDb.CUSTOMERS.ToList(); foreach (var customer in customers) { if (context.Customers.Any(c => c.Name == customer.NAME) == false) { context.Customers.Add(new Customer { Name = customer.NAME, Address = customer.ADDRESS, Phone = customer.PHONE }); } } context.SaveChanges(); }
public void MigrateDataFromExcelFiles(string zipFilePath) { ExtractZipFile(zipFilePath); using (var supermarketContext = new SupermarketContext()) { IList <Models.Sale> allSales = new List <Models.Sale>(); GetSales(TempFolderForExtract, supermarketContext, allSales); foreach (var sale in allSales) { supermarketContext.Sales.Add(sale); } supermarketContext.SaveChanges(); } Directory.Delete(TempFolderForExtract, true); }
/// <summary> ///Check for existing supermarket, because we want to have distinct data in the database and if the supermarket do not exists the method add it to the database. /// </summary> /// <param name="supName">the array containig the supermarket name</param> /// <param name="context">the Entity Framework connection to the database</param> private void CheckForExistingSupermarket(object[] supName, SupermarketContext context) { string marketName = this.SupermarketName(supName); var existSupName = context.Supermarkets.Where(s => s.Name == marketName).Select(s => s.Name).FirstOrDefault(); if (existSupName == null) { context.Supermarkets.Add(new MS_SQL_Server.Supermarket { Name = marketName, IsDeleted = false }); context.SaveChanges(); Console.WriteLine("Supermarket: {0} added!", marketName); } else { Console.WriteLine("Supermarket: {0} existed!", marketName); } }
public static void ParseXML(string filePath) { StringBuilder result = new StringBuilder(); using (SupermarketContext context = new SupermarketContext()) { string vendor = string.Empty; string expence = string.Empty; string date = string.Empty; using (XmlReader reader = XmlReader.Create(filePath)) { while (reader.Read()) { if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "sale")) { vendor = (reader.GetAttribute(0)).ToString(); } if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "expenses")) { date = (reader.GetAttribute(0)).ToString(); expence = (reader.ReadElementString()); } Expense expenceObj = new Expense() { Date = date, Expenses = expence, VendorName = vendor }; context.Expenses.Add(expenceObj); context.SaveChanges(); } } } }
private static void InitializeDataFromMySQL() { var db = new SupermarketContext(); SupermarketModel model = new SupermarketModel(); using (db) { foreach (var measure in model.Measures) { db.Measurments.Add(new Measure() { Id = measure.Id, MeasureName = measure.MeasureName }); } foreach (var vendor in model.Vendors) { db.Vendors.Add(new Vendor() { Id = vendor.Id, VendorName = vendor.VendorName }); } foreach (var product in model.Products) { db.Products.Add(new Product() { Id = product.Id, BasePrice = product.BasePrice, MeasureId = product.MeasureId, ProductName = product.ProductName, VendorId = product.VendorId }); } db.SaveChanges(); Console.WriteLine("1. Data from MySQL saved in SQL Server!"); } }
private static void MoveMeasures() { var client = new SupermarketModel(); using (client) { var measures = client.Measures; using (SupermarketContext context = new SupermarketContext()) { foreach (var item in measures) { var measure = new SupermarketSQL.Models.Measure { MeasureName = item.MeasureName }; context.Measures.Add(measure); } context.SaveChanges(); } } }
/// <summary> /// Insert the content of the excel files into the database. /// </summary> /// <param name="rowData">array of data containing name, type, price and quantity of the product</param> /// <param name="context">the Entity Framework connection to the database</param> /// <param name="reportDate">the date taken from the folder with the reports</param> /// <param name="supmarketName">the name of the supermarket</param> private void InsertIntoDataBase(object[] rowData, SupermarketContext context, string reportDate, string supmarketName) { // string[] inputNameType = rowData[0].ToString().Split(); // string prodType = inputNameType[0]; // string prodName = inputNameType[1]; string prodName = rowData[0].ToString(); int quantity = int.Parse(rowData[1].ToString()); float price = float.Parse(rowData[2].ToString()); DateTime dateReport = DateTime.ParseExact(reportDate, "dd-MMM-yyyy", CultureInfo.InvariantCulture); //this.CheckProductType(prodType, context); this.CheckProduct(prodName, price, context); var productId = context.Products.Where(p => p.ProductName == prodName).Select(p => p.Id).FirstOrDefault(); var marketId = context.Supermarkets.Where(s => s.Name == supmarketName).Select(s => s.SupermarketId).FirstOrDefault(); var existMarketSalesProduct = context.SupermarketSalesProducts .Where(s => s.SupermarketId == marketId && s.ProductId == productId && s.SalesDate == dateReport) .Select(s => new { s.ProductId, s.SupermarketId, s.SalesDate }).FirstOrDefault(); if (existMarketSalesProduct == null) { context.SupermarketSalesProducts.Add(new SupermarketSalesProduct { SupermarketId = marketId, ProductId = productId, Quantity = quantity, Price = (decimal)price, SalesDate = dateReport }); context.SaveChanges(); } }
/// <summary> /// Check for existing product type, because we want to have distinct data in the database and if the product do not exists the method add it to the database. /// </summary> /// <param name="productName">the name to be checked</param> /// <param name="price">the price needed to be eventually created new product</param> /// <param name="context">the Entity Framework connection to the database</param> private void CheckProduct(string productName, float price, SupermarketContext context) { var existProd = context.Products .Where(p => p.ProductName == productName) .Select(p => p.ProductName) .FirstOrDefault(); if (existProd == null) { context.Products.Add(new Product { ProductName = productName, Price = price }); context.SaveChanges(); Console.WriteLine("Product: {0} added!", productName); } else { Console.WriteLine("Product: {0} existed!", productName); } }
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 LoadVendorExpensesToSqlServer(IList <string[]> expenses) { using (var supermarketContext = new SupermarketContext()) { foreach (var expense in expenses) { string vendorName = expense[0]; int vendorID = supermarketContext.Vendors.First(v => v.VendorName == vendorName).ID; decimal value = decimal.Parse(expense[2]); Models.Expense newExpense = new Models.Expense() { VendorID = vendorID, Month = DateTime.Parse(expense[1]), Value = value }; supermarketContext.Expenses.Add(newExpense); } supermarketContext.SaveChanges(); } }
private static void MoveVendors() { var client = new SupermarketModel(); using (client) { var vendors = client.Vendors; using (SupermarketContext context = new SupermarketContext()) { foreach (var item in vendors) { var vendor = new SupermarketSQL.Models.Vendor { VendorName = item.VendorName }; context.Vendors.Add(vendor); } context.SaveChanges(); } } }
public static void MoveData() { var sqliteData = new SupermarketSQLite(); using (sqliteData) { var taxes = sqliteData.Taxes; using (SupermarketContext context = new SupermarketContext()) { foreach (var item in taxes) { var tax = new SupermarketSQL.Models.TaxTable { ProductId = item.ProductId, Tax = item.Tax }; context.Taxes.Add(tax); sqliteData.SaveChanges(); } context.SaveChanges(); } } }
private static void ReplicateProducts(SupermarketContext context) { var oracleDb = new OracleEntities(); var products = oracleDb.PRODUCTS.ToList(); foreach (var product in products) { if (context.Products.Any(p => p.Name == product.NAME) == false) { context.Products.Add(new Product { Name = product.NAME, Price = product.PRICE, SupplierId = (int)product.SUPPLIER_ID, CategoryId = (int)product.CATEGORY_ID, MeasureId = (int)product.MEASURE_ID }); } } context.SaveChanges(); }
public bool Save() { return(_context.SaveChanges() >= 0); }
public static void ReadExcelData() { var dbContext = new SupermarketContext(); using (dbContext) { string[] subDirs; subDirs = Directory.GetDirectories(@"../../../../Reports/Sample-Sales-Extracted Files"); foreach (var dir in subDirs) { string[] files = Directory.GetFiles(dir); //Console.WriteLine("Directory: " + dir); //Console.WriteLine("Files:"); for (int i = 0; i < files.Length; i++) { string fileName = new FileInfo(files[i]).Name; int length = fileName.Length; DateTime date = Convert.ToDateTime(fileName.Substring(0 + length - 15, 11)); //Console.WriteLine("{0:dd-MM-yyyy}",date); string currentFilePath = (dir + @"\" + new FileInfo(files[i]).Name); string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + currentFilePath + ";Persist Security Info=False"; OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder(connectionString); conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES"); OleDbConnection dbConn = new OleDbConnection(conString.ConnectionString); dbConn.Open(); using (dbConn) { DataTable dataSet = new DataTable(); string selectSql = @"SELECT * FROM [Sales$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, dbConn); adapter.Fill(dataSet); int rows = dataSet.Rows.Count; string location = dataSet.Rows[0].ItemArray[0].ToString(); // Console.WriteLine("Company name: " + location); for (int row = 2; row < rows - 2; row++) { Sale currentSale = new Sale(); Location currentLocation = new Location(); // currentLocation.Name = location; dbContext.Locations.Add(currentLocation); currentSale.Location = currentLocation; int productID = Convert.ToInt32(dataSet.Rows[row].ItemArray[0]); var product = from p in dbContext.Products where p.ID == productID select p; Product currentProduct = product.First(); // Console.WriteLine("productID = "+productID); currentSale.Quantity = Convert.ToInt32(dataSet.Rows[row].ItemArray[1]); currentSale.UnitPrice = Convert.ToDecimal(dataSet.Rows[row].ItemArray[2]); currentSale.Sum = Convert.ToDecimal(dataSet.Rows[row].ItemArray[3]); currentSale.Date = date; currentSale.Product = currentProduct; //Console.WriteLine(currentSale.Location.Name + " " + currentSale.Sum); dbContext.Sales.Add(currentSale); // dbContext.Products.Add(currentProduct); dbContext.SaveChanges(); } } } } } }
private static void ReplicateSuppliers(SupermarketContext context) { var oracleDb = new OracleEntities(); var suppliers = oracleDb.SUPPLIERS.ToList(); foreach (var supplier in suppliers) { if (context.Suppliers.Any(s => s.Name == supplier.NAME) == false) { context.Suppliers.Add(new Supplier { Name = supplier.NAME, Address = supplier.ADDRESS, Phone = supplier.PHONE }); } } context.SaveChanges(); }
private ICollection <Models.Sale> GetSalesFromExcelFiles( string directory, SupermarketContext supermarketContext) { IList <Models.Sale> sales = new List <Models.Sale>(); string[] excelFilesPaths = Directory.GetFiles(directory, "*.xls"); foreach (var excelFilePath in excelFilesPaths) { string excelConnectionString = string.Format( Settings.Default.ExcelReadConnectionString, excelFilePath); OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataSet dataSet = new DataSet(); using (excelConnection) { string selectAllRowsCommandString = "SELECT * FROM [Sales$]"; OleDbCommand selectAllRowsCommand = new OleDbCommand(selectAllRowsCommandString, excelConnection); OleDbDataAdapter excelAdapter = new OleDbDataAdapter(selectAllRowsCommand); excelAdapter.Fill(dataSet, "Sales"); } DataRowCollection excelRows = dataSet.Tables["Sales"].Rows; string supermarketName = excelRows[0][0].ToString(); if (!supermarketContext.Supermarkets.Any(s => s.Name == supermarketName)) { supermarketContext.Supermarkets.Add(new Models.SupermarketBranch() { Name = supermarketName }); supermarketContext.SaveChanges(); } for (int i = 2; i < excelRows.Count - 2; i++) { int productID = 0; int.TryParse(excelRows[i][0].ToString(), out productID); if (productID != 0) { int quantity = 0; int.TryParse(excelRows[i][1].ToString(), out quantity); decimal unitPrice = 0; decimal.TryParse(excelRows[i][2].ToString(), out unitPrice); decimal sum = 0; decimal.TryParse(excelRows[i][3].ToString(), out sum); int supermarketID = supermarketContext.Supermarkets.First(s => s.Name == supermarketName).ID; string saleDateString = Path.GetFileName(Path.GetDirectoryName(excelFilePath)); sales.Add(new Models.Sale() { Date = DateTime.Parse(saleDateString), Product = supermarketContext.Products.Find(productID), Quantity = quantity, Sum = sum, Supermarket = supermarketContext.Supermarkets.Find(supermarketID), UnitPrice = unitPrice }); } } } return(sales); }
private static void ReplicateOrders(SupermarketContext context) { var oracleDb = new OracleEntities(); var orders = oracleDb.ORDERS.ToList(); foreach (var order in orders) { if (context.Orders.Any(o => o.Id == order.ID) == false) { context.Orders.Add(new Order { Quantity = order.QUANTITY, ProductId = (int)order.PRODUCT_ID, Discount = order.DISCOUNT, Date = order.ORDER_DATE, CustomerId = (int)order.CUSTOMER_ID }); } } context.SaveChanges(); }