public static void UploadVendorsToSQL(ICollection<Vendor> vendors, SupermarketChainContext context) { foreach (var vendor in vendors) { var vendorSQL = new SuperMarketChain.Model.Vendor() { VendorName = vendor.VendorName }; if (context.Vendors.Where(v => v.VendorName.Equals(vendorSQL.VendorName)).Count() == 0) { context.Vendors.Add(vendorSQL); Console.WriteLine("Vendor " + vendorSQL.VendorName + " will be uploaded to SQL db." + vendorSQL.VendorName.Count()); } else { Console.WriteLine("Vendor " + vendorSQL.VendorName + " is already in SQL db and will not be uploaded."); } } if (context.ChangeTracker.HasChanges()) { context.SaveChanges(); } }
public static void UploadMeasuresToSQL(ICollection<Measure> measures, SupermarketChainContext context) { foreach (var measure in measures) { var measureSQL = new SuperMarketChain.Model.Measure() { ID = measure.Id, MeasureName = measure.MeasureName }; if (context.Measures.Where(m => m.MeasureName.Equals(measureSQL.MeasureName)).Count() == 0) { context.Measures.Add(measureSQL); Console.WriteLine("Measure " + measureSQL.MeasureName + " will be uploaded to SQL db."); } else { Console.WriteLine("Measure " + measureSQL.MeasureName + " is already in SQL db and will not be uploaded."); } } if (context.ChangeTracker.HasChanges()) { context.SaveChanges(); } }
public static void UploadProductsToSQL(ICollection<Product> products, SupermarketChainContext context) { foreach (var product in products) { var vendorName = GetVendorName("EVGENI-PC", "admin", "1111", product.VendorId); var vendorId = context.Vendors.Where(v => v.VendorName.Equals(vendorName)).Select(v => v.ID).FirstOrDefault(); var productSQL = new SuperMarketChain.Model.Product() { VendorId = vendorId, ProductName = product.ProductName, MeasureID = product.MeasureId, Price = product.Price }; if (context.Products.Where(p => p.ProductName.Equals(productSQL.ProductName)).Count() == 0) { context.Products.Add(productSQL); Console.WriteLine("Product " + productSQL.ProductName + " will be uploaded to SQL db"); } else { Console.WriteLine("Product " + productSQL.ProductName + " is already in SQL db and will not be uploaded."); } } if (context.ChangeTracker.HasChanges()) { context.SaveChanges(); } }
private void readExcell(string path) { decimal decNumber; double doubNumber; var context = new SupermarketChainContext(); string title = ""; bool isTitle = false; bool isProd = false; string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" + @"Extended Properties='Excel 8.0;HDR=Yes;'"; using (OleDbConnection connection = new OleDbConnection(conString)) { connection.Open(); OleDbCommand command = new OleDbCommand("select * from [Sales$]", connection); using (var dbContextTransaction = context.Database.BeginTransaction()) { try { using (OleDbDataReader dr = command.ExecuteReader()) { isTitle = false; isProd = false; int counter = 0; while (dr.Read()) { var row1Col0 = dr[counter]; var row1Col1 = dr[counter + 1]; var row1Col2 = dr[counter + 2]; var row1Col3 = dr[counter + 3]; if (isTitle == false) { title = dr[counter].ToString(); isTitle = true; if (vendorExist(dr[counter].ToString(), context) == false) { context.Vendors.Add(new Vendor { VendorName = title }); context.SaveChanges(); } continue; } else if (dr[counter].ToString() == "Total sum:") { continue; } if (isProd == false) { isProd = true; continue; } Decimal.TryParse(dr[counter + 2].ToString(), out decNumber); int id = vendorId(title, context); if (productExist(dr[counter].ToString(), decNumber, id) == false) { context.Products.Add(new Product { ProductName = dr[counter].ToString(), MeasureID = 1, Price = decNumber, VendorId = id }); } Double.TryParse(dr[counter + 1].ToString(), out doubNumber); if (saleExist(productId(dr[counter].ToString(), context), doubNumber, date, id) == false) { context.SaleReports.Add(new SaleReport { ProductId = productId(dr[counter].ToString(), context), Quantity = doubNumber, SaleTime = date, VendorId = id }); } context.SaveChanges(); } } dbContextTransaction.Commit(); } catch (Exception ex) { dbContextTransaction.Rollback(); } } } }