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();
}
}
}
}
}
}