public static void ExtractDataToSqlServer()
{
var sqlDb = new CupOfCoffeeContext();
using (sqlDb)
{
var productCollection = db.GetCollection <Product>("Products");
var products = productCollection.FindAll();
var categoryCollection = db.GetCollection <Category>("Categories");
var categories = categoryCollection.FindAll();
foreach (var category in categories)
{
category.Products = products.Where(product => product.CategoryId == category.Id).ToList();
sqlDb.Categories.Add(category);
}
sqlDb.SaveChanges();
var employeeCollection = db.GetCollection <Employee>("Employees");
var employees = employeeCollection.FindAll();
var positionCollection = db.GetCollection <Position>("Positions");
var positions = positionCollection.FindAll();
foreach (var position in positions)
{
position.Employees = employees.Where(employee => employee.PositionId == position.Id).ToList();
sqlDb.Positions.Add(position);
}
sqlDb.SaveChanges();
var customerCollection = db.GetCollection <Customer>("Customers");
var customers = customerCollection.FindAll();
var customerStatusCollection = db.GetCollection <CustomerStatus>("CustomerStatuses");
var customerStatuses = customerStatusCollection.FindAll();
foreach (var status in customerStatuses)
{
status.Customers = customers.Where(customer => customer.CustomerStatusId == status.Id).ToList();
sqlDb.CustomerStatuses.Add(status);
}
sqlDb.SaveChanges();
}
}
public static void Insert(IList <EmployeeSalary> salaries, CupOfCoffeeContext context)
{
foreach (var salary in salaries)
{
context.MonthlySalaries.Add(new MonthlySalary
{
EmployeeId = salary.EmployeeID,
Date = DateTime.Now,
Amount = salary.TotalSalary
});
}
context.SaveChanges();
}
private void btnFeedbackLoader_Click(object sender, RoutedEventArgs e)
{
if (this.filePath != string.Empty)
{
using (var context = new CupOfCoffeeContext())
{
try
{
var feedbacks = XmlParser.GenerateFeedbacksFromXml(this.filePath);
foreach (var feedback in feedbacks)
{
context.CustomerFeedbacks.Add(feedback);
}
context.SaveChanges();
foreach (var feedback in feedbacks)
{
string feedbackAsJson = JsonConvert.SerializeObject(feedback);
}
DatabasePopulator.ImportFeedback(feedbacks);
}
catch (Exception)
{
MessageBox.Show("Cannot load the xml! Please, make sure that the data in it is correct!",
"Error",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
}
else
{
MessageBox.Show("There is no xml file chosen!",
"Warning",
MessageBoxButton.OK,
MessageBoxImage.Warning);
}
}
static public bool Parse(string _filePath, string _productsSheet, string _orderSheet)
{
var reportSheet = _productsSheet + "$";
var reportDetail = _orderSheet + "$";
var connectionBuilder = new OleDbConnectionStringBuilder();
connectionBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
connectionBuilder.DataSource = _filePath;
connectionBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes");
var connectionExcel = new OleDbConnection(connectionBuilder.ConnectionString);
try
{
connectionExcel.Open();
}
catch (InvalidOperationException)
{
Console.WriteLine("Could not open excel file for parsing -> InvalidOperationException");
return(false);
//throw new InvalidOperationException("Could not open excel file for parsing -> InvalidOperationException");
}
catch (OleDbException)
{
Console.WriteLine("Could not open excel file for parsing -> OleDbException");
return(false);
//throw new InvalidOperationException("Could not open excel file for parsing -> OleDbException");
}
using (connectionExcel)
{
var query = "SELECT * FROM [{0}]";
var details = new OleDbCommand(string.Format(query, reportDetail), connectionExcel);
var orderId = 0;
using (var sqlConnection = new CupOfCoffeeContext())
{
var employeeId = 0;
int?custemerId = null;
var date = new DateTime();
using (var data = details.ExecuteReader())
{
data.Read();
employeeId = Convert.ToInt32(data["EmployeeId"]);
if (data["CustomerId"] != null)
{
custemerId = Convert.ToInt32(data["CustomerId"]);
}
date = DateTime.Parse((string)data["Date"]);
}
var order = sqlConnection.Orders.Add(
new Order()
{
EmployeeId = employeeId,
CustomerId = custemerId,
OrderDate = date
}
);
sqlConnection.SaveChanges();
orderId = order.Id;
var products = new OleDbCommand(string.Format(query, reportSheet), connectionExcel);
try
{
using (var data = products.ExecuteReader())
{
while (data.Read())
{
var productId = Convert.ToInt32(data["ProductId"]);
var quantity = Convert.ToInt32(data["Quantity"]);
var happyHour = Convert.ToBoolean(data["HappyHour"]);
sqlConnection.OrderDetails.Add(
new OrderDetail()
{
OrderId = orderId,
ProductId = productId,
Quantity = quantity,
HappyHour = happyHour
}
);
}
}
sqlConnection.SaveChanges();
return(true);
}
catch (Exception)
{
Console.WriteLine("Could not parse excel file for parsing -> InvalidOperationException");
return(false);
}
}
}
}