public static void TakeDataFromMySql()
{
SupermarketEntities sqlDb = new SupermarketEntities();
using (sqlDb)
{
SupermarketData mySql = new SupermarketData();
using (mySql)
{
List<string> measureNames = mySql.Measures.Select(x => x.Name).ToList();
foreach (string measureName in measureNames)
{
int measurementCount = sqlDb.Measures.Select(x => x.Name).Where(x => x == measureName).Count();
if (measurementCount == 0)
{
sqlDb.Measures.Add(new Measure { Name = measureName });
}
}
sqlDb.SaveChanges();
List<string> vendorNames = mySql.Vendors.Select(x => x.Name).ToList();
foreach (string vendorName in vendorNames)
{
int vendorNamesCount = sqlDb.Vendors.Select(x => x.Name).Where(x => x == vendorName).Count();
if (vendorNamesCount == 0)
{
sqlDb.Vendors.Add(new Vendor { Name = vendorName });
}
}
sqlDb.SaveChanges();
var products = mySql.Products.ToList();
foreach (Product mySqlProduct in products)
{
string productName = mySqlProduct.Name;
string productVendorName = mySqlProduct.Vendor.Name;
string productMeasureName = mySqlProduct.Measure.Name;
decimal productBasePrice = mySqlProduct.BasePrice;
var sqlProduct = sqlDb.Products.Where(x => x.Name == productName).ToList();
if (sqlProduct.Count==0)
{
Product newProduct = new Product
{
Name=productName
};
sqlDb.Products.Add(newProduct);
sqlProduct.Add(newProduct);
}
sqlProduct[0].BasePrice = productBasePrice;
Vendor sqlVendor = sqlDb.Vendors.Where(
x => x.Name == productVendorName).FirstOrDefault();
sqlProduct[0].Vendor= sqlVendor;
Measure sqlMeasure = sqlDb.Measures.Where(
x => x.Name == productMeasureName).FirstOrDefault();
sqlProduct[0].Measure = sqlMeasure;
sqlDb.SaveChanges();
}
}
}
}
private static void ReadExcelsFromDirectory(string filePath)
{
DataTable dt = new DataTable("newtable");
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\""))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
string location = dt.Rows[1][0].ToString();
SuperMarket newSupermarket = new SuperMarket()
{
Name = location,
};
for (int i = 3; i < dt.Rows.Count - 1; i++)
{
int prodId = 0;
string productId = dt.Rows[i][0].ToString();
int.TryParse(productId, out prodId);
if (prodId > 0)
{
using (var ctx = new SupermarketEntities())
{
if (ctx.Products.Find(prodId) != null)
{
var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList();
if (supermarket.Count == 0)
{
ctx.SuperMarkets.Add(newSupermarket);
supermarket.Add(newSupermarket);
}
Sale newSale = new Sale()
{
ProductId = prodId,
SuperMarketId = supermarket[0].Id,
Date = DateTime.Now,
Quantity = int.Parse(dt.Rows[i][1].ToString()),
Price = decimal.Parse(dt.Rows[i][2].ToString()),
Sum = decimal.Parse(dt.Rows[i][3].ToString())
};
ctx.Sales.Add(newSale);
ctx.SaveChanges();
}
}
}
}
}
