/// <summary>
/// Copy data from MySQL Database to MSSQL Sever Database
/// </summary>
/// <param name="spEntMySQL">MySQL entity</param>
/// <param name="spEntSQLServer">MSSQL Serve entity</param>
internal static void MigrateData(
MySQL.Models.OpenAccess.SupermarketEntitiesMySQL spEntMySQL,
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer)
{
if (spEntSQLServer.Database.Exists())
{
return;
}
// Insert Measures
foreach (var measure in spEntMySQL.Measures)
{
SQLServer.Models.EntityFramework.Measure measureToInsert =
new SQLServer.Models.EntityFramework.Measure();
measureToInsert.ID = measure.MeasureID;
measureToInsert.Name = measure.MeasureName;
spEntSQLServer.Measures.Add(measureToInsert);
}
// Inserting Vendors
foreach (var vendor in spEntMySQL.Vendors)
{
SQLServer.Models.EntityFramework.Vendor vendorToInsert =
new SQLServer.Models.EntityFramework.Vendor();
vendorToInsert.ID = vendor.VendorID;
vendorToInsert.Name = vendor.VendorName;
spEntSQLServer.Vendors.Add(vendorToInsert);
}
// Inserting Products
foreach (var product in spEntMySQL.Products)
{
SQLServer.Models.EntityFramework.Product productToInsert =
new SQLServer.Models.EntityFramework.Product();
productToInsert.ID = product.ProductID;
productToInsert.MeasureId = product.MeasureID;
productToInsert.ProductName = product.ProductName;
productToInsert.VendorId = product.VendorID;
productToInsert.BasePrice = product.BasePrice;
spEntSQLServer.Products.Add(productToInsert);
}
spEntSQLServer.SaveChanges();
}
private static int InsertSupermarket(SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer, ExcelData excelRow)
{
var existingSupermarket = spEntSQLServer.Supermarkets.FirstOrDefault(s => s.Name == excelRow.SupermarketName);
if (existingSupermarket == null)
{
SQLServer.Models.EntityFramework.Supermarket supermarketToInsert = new SQLServer.Models.EntityFramework.Supermarket();
supermarketToInsert.Name = excelRow.SupermarketName;
var result = spEntSQLServer.Supermarkets.Add(supermarketToInsert);
spEntSQLServer.SaveChanges();
return(result.SupermarketId);
}
else
{
return(existingSupermarket.SupermarketId);
}
}
private static int InsertSale(
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer,
ExcelData excelRow,
int dateID,
int supermarketID)
{
SQLServer.Models.EntityFramework.Sale saleToInsert = new SQLServer.Models.EntityFramework.Sale();
saleToInsert.ProductID = excelRow.ProductID;
saleToInsert.SupermarketID = supermarketID;
saleToInsert.DateID = dateID;
saleToInsert.Quantity = excelRow.Quantity;
saleToInsert.Sum = excelRow.Sum;
saleToInsert.UnitPrice = excelRow.UnitPrice;
var a = spEntSQLServer.Sales.Add(saleToInsert);
spEntSQLServer.SaveChanges();
return(a.ID);
}
private static int InsertDate(
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer,
ExcelData excelRow)
{
var existingDate = spEntSQLServer.Dates.FirstOrDefault(d => d.DateAndTime == excelRow.SaleDate);
if (existingDate == null)
{
SQLServer.Models.EntityFramework.Date dateToInsert = new SQLServer.Models.EntityFramework.Date();
dateToInsert.DateAndTime = excelRow.SaleDate;
var result = spEntSQLServer.Dates.Add(dateToInsert);
spEntSQLServer.SaveChanges();
return(result.ID);
}
else
{
return(existingDate.ID);
}
}