public static void SeedDatabase() { using (SupermarketDbModel mysql = new SupermarketDbModel()) using (SupermarketDBContext sql = new SupermarketDBContext()) { //sql.Database.Delete(); foreach (var measure in mysql.Measures) { if (!sql.Measures.Any(x=> x.MeasureId == measure.MeasureId)) { SupermarketEF.Model.Measure measureToAdd = new SupermarketEF.Model.Measure { MeasureId = measure.MeasureId, MeasureName = measure.MeasureName }; sql.Measures.Add(measureToAdd); } } sql.SaveChanges(); foreach (var vendor in mysql.Vendors) { if (!sql.Vendors.Any(x => x.VendorId == vendor.VendorId)) { SupermarketEF.Model.Vendor vendorToAdd = new SupermarketEF.Model.Vendor { //Products = new HashSet<SupermarketEF.Model.Product>(), VendorId = vendor.VendorId, VendorName = vendor.VendorName }; sql.Vendors.Add(vendorToAdd); } sql.SaveChanges(); } foreach (var product in mysql.Products) { if (!sql.Products.Any(x => x.ProductId == product.ProductId)) { SupermarketEF.Model.Product productToAdd = new SupermarketEF.Model.Product { VendorId = product.VendorId, ProductName = product.ProductName, MeasureId = product.MeasureId, BasePrice = product.BasePrice }; sql.Products.Add(productToAdd); } sql.SaveChanges(); } } }
public static void ParseExcelDirectory(SupermarketDBContext sqldb, string directoryPath) { DirectoryInfo dir = new DirectoryInfo(directoryPath); foreach (var subDirectory in dir.GetDirectories()) { FileInfo[] files = subDirectory.GetFiles(); //DateTime folderDate = DateTime.Parse(subDirectory.Name); foreach (var item in files) { Console.WriteLine(item.FullName); ParseExcelFile(sqldb, subDirectory.Name, item.FullName); sqldb.SaveChanges(); Console.WriteLine(); } } }
public static void ParseExcelFile(SupermarketDBContext sqlDb, string folderDate, string file) { string connectStr = @"Provider=Microsoft.ACE.OLEDB.12.0; " + @"Data Source=" + file + "; " + @"Extended Properties='Excel 12.0 Xml; HDR=YES'"; OleDbConnection dbCon = new OleDbConnection(connectStr); string sqlQuery = "select * from [Sales$]"; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlQuery, dbCon); DataSet currentData = new DataSet(); myDataAdapter.Fill(currentData, "Sales"); DataRowCollection excelRows = currentData.Tables["Sales"].Rows; string location = string.Empty; DateTime saleDate = DateTime.Parse(folderDate); location = excelRows[0][0].ToString(); for (int row = 2; row < excelRows.Count - 1; row++) { int productId = 0; int quantity = 0; decimal unitPrice = 0; decimal sum = 0; int.TryParse(excelRows[row][0].ToString(), out productId); int.TryParse(excelRows[row][1].ToString(), out quantity); decimal.TryParse(excelRows[row][2].ToString(), out unitPrice); decimal.TryParse(excelRows[row][3].ToString(), out sum); if (productId != 0) { SalesReport currentReport = new SalesReport { Location = location, ProductId = productId, Quantity = quantity, Sum = sum, UnitPrice = unitPrice, ReportDate = saleDate }; sqlDb.SalesReports.Add(currentReport); sqlDb.SaveChanges(); } } }