private static void Load()
{
foreach (FreightByShipper shipper in _freightByShipperList)
{
using (var db = new NorthwindDataContext("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"))
{
var shipperToDelete = from shippers in db.FreightSummaries
where
shippers.ShipperName == shipper.ShipperName &&
shippers.RunDate == DateTime.Today
select shippers;
db.FreightSummaries.DeleteOnSubmit(shipperToDelete.First());
db.SubmitChanges();
var shipperToInsert = new FreightSummary()
{
Freight = shipper.Freight,
ShipperName = shipper.ShipperName,
RunDate = DateTime.Today
};
db.FreightSummaries.InsertOnSubmit(shipperToInsert);
db.SubmitChanges();
}
}
foreach (Employee employee in _employees)
{
using (var myConnection = new SqlConnection())
{
myConnection.ConnectionString =
"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";
string myQuery = "delete EmployeeBonus where Date = @RunDate and EmployeeName = @EmployeeName";
using (var myCommand = new SqlCommand(myQuery, myConnection))
{
myCommand.Parameters.Add(new SqlParameter("RunDate", DateTime.Today));
myCommand.Parameters.Add(new SqlParameter("EmployeeName", employee.Name));
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
myQuery =
"insert EmployeeBonus (Date,EmployeeName,Bonus) VALUES (@RunDate, @EmployeeName, @Bonus)";
using (var myCommand = new SqlCommand(myQuery, myConnection))
{
myCommand.Parameters.Add(new SqlParameter("RunDate", DateTime.Today));
myCommand.Parameters.Add(new SqlParameter("EmployeeName", employee.Name));
myCommand.Parameters.Add(new SqlParameter("Bonus", employee.Bonus));
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
string result = String.Format("{0}:{1}:{2:#.##}", employee.Name,
employee.IsManager ? "Manager" : "Employee",
employee.Bonus);
Console.WriteLine("Added Row to summary: " + result);
}
}
}
}
public void DeleteAndInsert(DateTime runDate, FreightByShipper freightByShipper)
{
using (var context = new NorthwindEntities())
using (var scope = new TransactionScope())
{
var summaryToDelete = (from freightSummary in context.FreightSummaries
where
freightSummary.RunDate == runDate &&
freightSummary.ShipperName == freightByShipper.ShipperName
select freightSummary).FirstOrDefault();
if (summaryToDelete != null)
{
context.DeleteObject(summaryToDelete);
}
var newFreightSummary = new FreightSummary()
{
Freight = freightByShipper.Freight,
RunDate = runDate,
ShipperName = freightByShipper.ShipperName
};
context.FreightSummaries.AddObject(newFreightSummary);
context.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
scope.Complete();
}
}
