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(); } }
///<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!"); }
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); }
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(); } }
/// <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); } }
/// <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); } }
///<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 UpdateProductsFromOracle() { var oracleContext = new OracleEntities(); var msSqLcontext = new SupermarketContext(); var products = oracleContext.PRODUCTS .Where(p => p.ISCOPIED == false && p.ISDELETED == false) .Select(p => new { p.PRODUCTNAME, p.PRICE, p.VENDOR.VENDORNAME, p.MEASURE.MEASURENAME, p.PRODUCTSTYPE.TYPENAME }).ToList(); if (products.Count > 0) { var addedProductsList = new List<string>(); foreach (var product in products) { var productName = product.PRODUCTNAME; var price = product.PRICE; var vendorId = MsSqlManager.GetVendorIdByName(product.VENDORNAME); var measureId = MsSqlManager.GetMeasureIdByName(product.MEASURENAME); var typeId = MsSqlManager.GetTypeIdByName(product.TYPENAME); try { msSqLcontext.Products.AddOrUpdate( p => p.ProductName, new Product() { ProductName = productName, VendorId = vendorId, MeasureId = measureId, ProductTypeId = typeId, Price = (float)price }); msSqLcontext.SaveChanges(); addedProductsList.Add(productName); } catch (Exception ex) { throw new ArgumentException(); } } var productsToChange = oracleContext.PRODUCTS.Where(p => addedProductsList.Contains(p.PRODUCTNAME)).ToList(); productsToChange.ForEach(p => p.ISCOPIED = true); oracleContext.SaveChanges(); Console.WriteLine("\nAdded new Products from OracleBD into MS SQL Server:"); productsToChange.ForEach(p => Console.WriteLine("Added product name: {0}", p.PRODUCTNAME)); } else { Console.WriteLine("\nThere is no new records to import into PRODUCTS 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 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!"); } }
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 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(); } } }
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 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!"); } }
/// <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(); } }
/// <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(); } }
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(); } } } } } }