// Copy data from auto-generates MySql classes to code first made classes for MS SQL Database public static void TransferData(SupermarketModel mySQL, SupermarketContext msSQL) { foreach (var measure in mySQL.Measures) { var newMeasure = new SupermarketDB.Models.Measure { MeasureID = measure.MeasureID, MeasureName = measure.MeasureName }; msSQL.Measures.Add(newMeasure); msSQL.SaveChanges(); } foreach (var vendor in mySQL.Vendors) { var newVendor = new SupermarketDB.Models.Vendor { VendorID = vendor.VendorID, VendorName = vendor.VendorName }; msSQL.Vendors.Add(newVendor); msSQL.SaveChanges(); } }
public static void TransferDataConnTable(SupermarketModel mySQL, SupermarketContext msSQL) { foreach (var product in mySQL.Products) { var newProduct = new SupermarketDB.Models.Product { ProductID = product.ProductID, VendorID = product.VendorID, MeasureID = product.MeasureID, ProductName = product.ProductName, BasePrice = product.BasePrice }; msSQL.Products.Add(newProduct); msSQL.SaveChanges(); } }
public static void TransferDataFromExcelToDB(SupermarketContext DBconn, string directory) { DirectoryInfo dir = new DirectoryInfo(directory); var directories = dir.GetDirectories(); foreach (var d in directories) { var date = DateTime.Parse(d.Name); var files = d.GetFiles(); foreach (var f in files) { string excelFile = f.FullName; string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + excelFile + ";" + @"Extended Properties='Excel 12.0 Xml;HDR=YES'"; OleDbConnection conn = new OleDbConnection(connectionString); conn.Open(); DataSet newDataSet = new DataSet(); using (conn) { OleDbCommand cmd = new OleDbCommand("SELECT * from [Sales$]", conn); OleDbDataAdapter newDataAdapter = new OleDbDataAdapter(cmd); newDataAdapter.Fill(newDataSet, "Sales"); } DataTable table = newDataSet.Tables[0]; // Get supermarket name DataRow row = table.Rows[0]; DataColumn col = table.Columns[0]; string line = row[col].ToString(); int spaceIndex = line.IndexOf(' '); string supermarketName = line.Substring(spaceIndex + 1); decimal sum = decimal.Parse(table.Rows[table.Rows.Count - 1][table.Columns.Count - 1].ToString()); List<decimal[]> allData = new List<decimal[]>(); for (int r = 2; r < table.Rows.Count - 1; r++) { DataRow dataRow = table.Rows[r]; var productData = new decimal[4]; decimal result = 0; for (int c = 0; c < 4; c++) { DataColumn dataCol = table.Columns[c]; decimal.TryParse(dataRow[dataCol].ToString(), out result); productData[c] = result; } if (result != 0) { allData.Add(productData); } } foreach (var report in allData) { var productId = (int)report[0]; var quantity = (int)report[1]; var unitPrice = report[2]; var reportSum = report[3]; var saleReport = new SaleReport { ProductID = productId, Quantity = quantity, UnitPrice = unitPrice, Sum = reportSum, SupermarketName = supermarketName, ReportDate = date }; DBconn.SalesReports.Add(saleReport); DBconn.SaveChanges(); } } } }