public static void TakeDataFromMySql() { SupermarketEntities sqlDb = new SupermarketEntities(); using (sqlDb) { SupermarketData mySql = new SupermarketData(); using (mySql) { List<string> measureNames = mySql.Measures.Select(x => x.Name).ToList(); foreach (string measureName in measureNames) { int measurementCount = sqlDb.Measures.Select(x => x.Name).Where(x => x == measureName).Count(); if (measurementCount == 0) { sqlDb.Measures.Add(new Measure { Name = measureName }); } } sqlDb.SaveChanges(); List<string> vendorNames = mySql.Vendors.Select(x => x.Name).ToList(); foreach (string vendorName in vendorNames) { int vendorNamesCount = sqlDb.Vendors.Select(x => x.Name).Where(x => x == vendorName).Count(); if (vendorNamesCount == 0) { sqlDb.Vendors.Add(new Vendor { Name = vendorName }); } } sqlDb.SaveChanges(); var products = mySql.Products.ToList(); foreach (Product mySqlProduct in products) { string productName = mySqlProduct.Name; string productVendorName = mySqlProduct.Vendor.Name; string productMeasureName = mySqlProduct.Measure.Name; decimal productBasePrice = mySqlProduct.BasePrice; var sqlProduct = sqlDb.Products.Where(x => x.Name == productName).ToList(); if (sqlProduct.Count==0) { Product newProduct = new Product { Name=productName }; sqlDb.Products.Add(newProduct); sqlProduct.Add(newProduct); } sqlProduct[0].BasePrice = productBasePrice; Vendor sqlVendor = sqlDb.Vendors.Where( x => x.Name == productVendorName).FirstOrDefault(); sqlProduct[0].Vendor= sqlVendor; Measure sqlMeasure = sqlDb.Measures.Where( x => x.Name == productMeasureName).FirstOrDefault(); sqlProduct[0].Measure = sqlMeasure; sqlDb.SaveChanges(); } } } }
private static void ReadExcelsFromDirectory(string filePath) { DataTable dt = new DataTable("newtable"); using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"")) { connection.Open(); string selectSql = @"SELECT * FROM [Sales$]"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection)) { adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } connection.Close(); } string location = dt.Rows[1][0].ToString(); SuperMarket newSupermarket = new SuperMarket() { Name = location, }; for (int i = 3; i < dt.Rows.Count - 1; i++) { int prodId = 0; string productId = dt.Rows[i][0].ToString(); int.TryParse(productId, out prodId); if (prodId > 0) { using (var ctx = new SupermarketEntities()) { if (ctx.Products.Find(prodId) != null) { var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList(); if (supermarket.Count == 0) { ctx.SuperMarkets.Add(newSupermarket); supermarket.Add(newSupermarket); } Sale newSale = new Sale() { ProductId = prodId, SuperMarketId = supermarket[0].Id, Date = DateTime.Now, Quantity = int.Parse(dt.Rows[i][1].ToString()), Price = decimal.Parse(dt.Rows[i][2].ToString()), Sum = decimal.Parse(dt.Rows[i][3].ToString()) }; ctx.Sales.Add(newSale); ctx.SaveChanges(); } } } } }