public static void SeedDatabase()
{
using (SupermarketDbModel mysql = new SupermarketDbModel())
using (SupermarketDBContext sql = new SupermarketDBContext())
{
//sql.Database.Delete();
foreach (var measure in mysql.Measures)
{
if (!sql.Measures.Any(x=> x.MeasureId == measure.MeasureId))
{
SupermarketEF.Model.Measure measureToAdd = new SupermarketEF.Model.Measure
{
MeasureId = measure.MeasureId,
MeasureName = measure.MeasureName
};
sql.Measures.Add(measureToAdd);
}
}
sql.SaveChanges();
foreach (var vendor in mysql.Vendors)
{
if (!sql.Vendors.Any(x => x.VendorId == vendor.VendorId))
{
SupermarketEF.Model.Vendor vendorToAdd = new SupermarketEF.Model.Vendor
{
//Products = new HashSet<SupermarketEF.Model.Product>(),
VendorId = vendor.VendorId,
VendorName = vendor.VendorName
};
sql.Vendors.Add(vendorToAdd);
}
sql.SaveChanges();
}
foreach (var product in mysql.Products)
{
if (!sql.Products.Any(x => x.ProductId == product.ProductId))
{
SupermarketEF.Model.Product productToAdd = new SupermarketEF.Model.Product
{
VendorId = product.VendorId,
ProductName = product.ProductName,
MeasureId = product.MeasureId,
BasePrice = product.BasePrice
};
sql.Products.Add(productToAdd);
}
sql.SaveChanges();
}
}
}
public static void ParseExcelDirectory(SupermarketDBContext sqldb, string directoryPath)
{
DirectoryInfo dir = new DirectoryInfo(directoryPath);
foreach (var subDirectory in dir.GetDirectories())
{
FileInfo[] files = subDirectory.GetFiles();
//DateTime folderDate = DateTime.Parse(subDirectory.Name);
foreach (var item in files)
{
Console.WriteLine(item.FullName);
ParseExcelFile(sqldb, subDirectory.Name, item.FullName);
sqldb.SaveChanges();
Console.WriteLine();
}
}
}
public static void ParseExcelFile(SupermarketDBContext sqlDb, string folderDate, string file)
{
string connectStr = @"Provider=Microsoft.ACE.OLEDB.12.0; " +
@"Data Source=" + file + "; " +
@"Extended Properties='Excel 12.0 Xml; HDR=YES'";
OleDbConnection dbCon = new OleDbConnection(connectStr);
string sqlQuery = "select * from [Sales$]";
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlQuery, dbCon);
DataSet currentData = new DataSet();
myDataAdapter.Fill(currentData, "Sales");
DataRowCollection excelRows = currentData.Tables["Sales"].Rows;
string location = string.Empty;
DateTime saleDate = DateTime.Parse(folderDate);
location = excelRows[0][0].ToString();
for (int row = 2; row < excelRows.Count - 1; row++)
{
int productId = 0;
int quantity = 0;
decimal unitPrice = 0;
decimal sum = 0;
int.TryParse(excelRows[row][0].ToString(), out productId);
int.TryParse(excelRows[row][1].ToString(), out quantity);
decimal.TryParse(excelRows[row][2].ToString(), out unitPrice);
decimal.TryParse(excelRows[row][3].ToString(), out sum);
if (productId != 0)
{
SalesReport currentReport = new SalesReport
{
Location = location,
ProductId = productId,
Quantity = quantity,
Sum = sum,
UnitPrice = unitPrice,
ReportDate = saleDate
};
sqlDb.SalesReports.Add(currentReport);
sqlDb.SaveChanges();
}
}
}