private static void MigrateProducts() { SupermarketSystemData data = new SupermarketSystemData(); con = new OracleConnection { ConnectionString = ConnectionString }; con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT P_ID, " + "VENDOR_ID," + "PRODUCT_NAME, " + "MEASURE_ID, " + "PRICE, " + "PRODUCT_TYPE " + "FROM PRODUCTS"; using (OracleDataReader reader = cmd.ExecuteReader()) { var lastProduct = data.Products.All().OrderByDescending(p => p.Id).FirstOrDefault(); int dataId = 0; if (lastProduct != null) { dataId = lastProduct.Id; } while (reader.Read()) { int productId = int.Parse(reader["P_ID"].ToString()); if (dataId < productId) { Product product = new Product(); // for debugging product.Id = productId; product.VendorId = int.Parse(reader["VENDOR_ID"].ToString()); product.ProductName = reader["PRODUCT_NAME"].ToString(); product.MeasureId = int.Parse(reader["MEASURE_ID"].ToString()); product.Price = decimal.Parse(reader["PRICE"].ToString()); product.ProductType = (ProductType)Enum.Parse(typeof(ProductType), reader["PRODUCT_TYPE"].ToString()); data.Products.Add(product); } } data.SaveChanges(); } Close(); }
private static void MigrateMeasures() { SupermarketSystemData data = new SupermarketSystemData(); con = new OracleConnection { ConnectionString = ConnectionString }; con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT M_ID, MEASURE_NAME FROM MEASURE_UNITS"; using (OracleDataReader reader = cmd.ExecuteReader()) { var lastMeasure = data.Measures.All().OrderByDescending(v => v.Id).FirstOrDefault(); int dataId = 0; if (lastMeasure != null) { dataId = lastMeasure.Id; } while (reader.Read()) { int measureId = int.Parse(reader["M_ID"].ToString()); if (dataId < measureId) { Measure measure = new Measure(); measure.Id = measureId; measure.MeasureName = (string)reader["MEASURE_NAME"]; data.Measures.Add(measure); } } data.SaveChanges(); } Close(); }
private static void MigrateVendors() { SupermarketSystemData data = new SupermarketSystemData(); con = new OracleConnection { ConnectionString = ConnectionString }; con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT V_ID,VENDOR_NAME FROM VENDORS"; using (OracleDataReader reader = cmd.ExecuteReader()) { var lastVendor = data.Vendors.All().OrderByDescending(v => v.Id).FirstOrDefault(); int dataId = 0; if (lastVendor != null) { dataId = lastVendor.Id; } while (reader.Read()) { int vendorId = int.Parse(reader["V_ID"].ToString()); if (dataId < vendorId) { Vendor vendor = new Vendor(); vendor.Id = vendorId; vendor.VendorName = (string)reader["VENDOR_NAME"]; data.Vendors.Add(vendor); } } data.SaveChanges(); } Close(); }
private void FillReportsInDatabase() { foreach (var connectionString in this.connectionStrings) { OleDbConnection excelConnection = new OleDbConnection(connectionString); excelConnection.Open(); try { using (excelConnection) { OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [Sales$]", excelConnection); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); Sale newSale = new Sale(); string[] conArgs = connectionString.Split('\\'); if (!string.IsNullOrEmpty(conArgs[1])) { DateTime date = this.ParseDate(conArgs[1]); // Date of sale newSale.Date = date; } DataRow headerRow = dataTable.Rows[0]; // Name of the store string storeName = headerRow.ItemArray[0].ToString(); Store currentStore = data.Stores.Search(s => s.Name == storeName).FirstOrDefault(); if (currentStore == null) { var newStore = new Store { Name = storeName }; data.Stores.Add(newStore); data.SaveChanges(); // TODO: To optimized !!! currentStore = data.Stores.Search(s => s.Name == storeName).FirstOrDefault(); } newSale.StoreId = currentStore.Id; int reportsLength = dataTable.Rows.Count - 1; for (int row = 2; row < reportsLength; row++) { DataRow dataRow = dataTable.Rows[row]; // Product name string productName = dataRow.ItemArray[0].ToString(); if (!string.IsNullOrEmpty(productName)) { var product = data.Products.Search(p => p.ProductName == productName).FirstOrDefault(); if (product != null) { newSale.ProductId = product.Id; } else { throw new ArgumentNullException("Product", "Not existing product!"); } } // Add sale product quantity string quantityValue = dataRow.ItemArray[1].ToString(); if (!string.IsNullOrEmpty(quantityValue) && char.IsDigit(quantityValue[0])) { newSale.Quantity = int.Parse(quantityValue); } // Add sale product single price string unitPriceValue = dataRow.ItemArray[2].ToString(); if (!string.IsNullOrEmpty(unitPriceValue) && char.IsDigit(unitPriceValue[0])) { newSale.SinglePrice = decimal.Parse(unitPriceValue); } // Add sale sum string sumValue = dataRow.ItemArray[3].ToString(); if (!string.IsNullOrEmpty(sumValue) && char.IsDigit(sumValue[0])) { newSale.Sum = decimal.Parse(sumValue); } data.Sales.Add(newSale); data.SaveChanges(); } } excelConnection.Close(); } catch (Exception ex) { Console.WriteLine("Read excel exception! {0}", ex); } } }