private static void InsertDataFromXls(string filePath, string dateString)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = filePath;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
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[] dateParts = dateString.Split('-');
int day = int.Parse(dateParts[0]);
int month = GetMonthAsInt(dateParts[1]);
int year = int.Parse(dateParts[2]);
DateTime reportDate = new DateTime(year, month, day);
int rowsCount = dt.Rows.Count - 1;
for (int i = 2; i < rowsCount; i++)
{
Report report = new Report
{
ProductId = Convert.ToInt32(dt.Rows[i].ItemArray[0]),
Quantity = Convert.ToInt32(dt.Rows[i].ItemArray[1]),
UnitPrice = Convert.ToDecimal(dt.Rows[i].ItemArray[2]),
Sum = Convert.ToDecimal(dt.Rows[i].ItemArray[3]),
Date = reportDate
};
string locationName = dt.Rows[0].ItemArray[0].ToString();
InsertInSqlServer(report, locationName);
}
}
private static void InsertInSqlServer(Report report, string locationName)
{
using (var context = new SupermarketContext())
{
var reports = context.Reports
.Where(x => x.Date == report.Date &&
x.Location.Name == locationName &&
x.ProductId == report.ProductId);
if (reports.Count() > 0)
{
return;
}
var locations = context.Locations.Where(x => x.Name == locationName);
if (locations.Count() > 0)
{
report.Location = locations.First();
}
else
{
report.Location = new Location { Name = locationName };
}
context.Reports.Add(report);
context.SaveChanges();
}
}