public static string InsertCustomerIntoNorthwind(string customerId, string companyName)
{
NorthwindEntities context = new NorthwindEntities();
Customer customerToInsert = new Customer
{
CustomerID = customerId,
CompanyName = companyName
};
context.Customers.Add(customerToInsert);
context.SaveChanges();
return(customerToInsert.CustomerID);
}
public static void CreateOrder(string customerId, int employeeId, DateTime orderDate, int shipVia, string shipCity)
{
NorthwindEntities context = new NorthwindEntities();
Order newOrder = new Order();
newOrder.CustomerID = customerId;
newOrder.EmployeeID = employeeId;
newOrder.OrderDate = orderDate;
newOrder.ShipVia = shipVia;
newOrder.ShipCity = shipCity;
context.Orders.Add(newOrder);
context.SaveChanges();
}
public static void Process()
{
using(var firstContext = new NorthwindEntities())
using (var secondContext = new NorthwindEntities())
{
var firstProdToModify = firstContext.Products.FirstOrDefault(pr => pr.ProductName == "Pavlova");
var secondProdToModify = secondContext.Products.FirstOrDefault(pr => pr.ProductName == "Pavlova");
firstProdToModify.ProductName = "Petrova";
secondProdToModify.ProductName = "Georgieva";
firstContext.SaveChanges();
secondContext.SaveChanges();
}
// To ensure smooth operation Async Query & Save could be used.
}
public static void CreateNestedContexts()
{
NorthwindEntities dbOne = new NorthwindEntities();
NorthwindEntities dbTwo = new NorthwindEntities();
Customer customerOne = dbOne.Customers.Where(c => c.ContactName.Contains("no")).FirstOrDefault();
Customer customerTwo = dbTwo.Customers.Where(c => c.ContactName.Contains("no")).FirstOrDefault();
Console.WriteLine(customerOne.CompanyName);
customerOne.CompanyName += "NEW1";
Console.WriteLine(customerOne.CompanyName);
dbOne.SaveChanges();
Console.WriteLine(customerTwo.CompanyName);
customerTwo.CompanyName += "NEW2";
Console.WriteLine(customerTwo.CompanyName);
dbTwo.SaveChanges();
Customer customerEdited = dbOne.Customers.Where(c => c.ContactName.Contains("no")).FirstOrDefault();
Console.WriteLine(customerEdited.CompanyName);
}
public static void EditCustomer(NorthwindEntities context, Customer customer)
{
var customerToUpdate = context.Customers
.Where(c => c.CustomerID == customer.CustomerID)
.FirstOrDefault();
customerToUpdate.Address = customer.Address;
customerToUpdate.City = customer.City;
customerToUpdate.CompanyName = customer.CompanyName;
customerToUpdate.ContactName = customer.ContactName;
customerToUpdate.ContactTitle = customer.ContactTitle;
customerToUpdate.Country = customer.Country;
customerToUpdate.CustomerDemographics = customer.CustomerDemographics;
customerToUpdate.Fax = customer.Fax;
customerToUpdate.Orders = customer.Orders;
customerToUpdate.Phone = customer.Phone;
customerToUpdate.PostalCode = customer.PostalCode;
customerToUpdate.Region = customer.Region;
context.SaveChanges();
}
public static void CreateFindIncomesForSupplierInPeriod()
{
string storedProcedure = "CREATE PROCEDURE [dbo].[uspTotalIncomesForSupplierInPeriod] (@supplierName nvarchar(50), @startDate date, @endDate date) " +
"AS " +
"BEGIN " +
"SELECT SUM(o.Freight) FROM [dbo].[Orders] o " +
"INNER JOIN [dbo].[Order Details] od " +
"ON o.OrderID = od.OrderID " +
"INNER JOIN [dbo].[Products] p " +
"ON od.ProductID = p.ProductID " +
"INNER JOIN [dbo].[Suppliers] s " +
"ON p.SupplierID = s.SupplierID " +
"WHERE @supplierName = s.[CompanyName] " +
"AND o.ShippedDate BETWEEN " +
"CAST(@startDate AS datetime) AND CAST(@endDate AS datetime)" +
"END;";
using (var dbContext = new NorthwindEntities())
{
dbContext.Database.ExecuteSqlCommand(storedProcedure);
dbContext.SaveChanges();
}
}
/*Create a DAO class with static methods which
provide functionality for inserting, modifying and
deleting customers. Write a testing class.*/
public static string InsertCustomer(
string companyName,
string contactName = null,
string contactTitle = null,
string address = null,
string city = null,
string region = null,
string postalCode = null,
string country = null,
string phone = null,
string fax = null)
{
var northwindEntities = new NorthwindEntities();
int indexOfWordSeparator = companyName.IndexOf(' ');
indexOfWordSeparator = indexOfWordSeparator > 0 ? indexOfWordSeparator + 1 : 4;
string customerId = (companyName.Substring(0, 4) + companyName.Substring(indexOfWordSeparator, 1)).ToUpperInvariant();
var newCustomer = new Customer()
{
CustomerID = customerId,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax,
};
northwindEntities.Customers.Add(newCustomer);
northwindEntities.SaveChanges();
return newCustomer.CustomerID;
}
public static void NewOrder(string customerId, int employeeId, DateTime orderDate, int shipVia, string shipName, string shipCity)
{
// using (TransactionScope scope = new TransactionScope())
// {
using (var dbContext = new NorthwindEntities())
{
using (var tran = dbContext.Database.BeginTransaction())
{
var newOrder = new Order();
newOrder.CustomerID = customerId;
newOrder.EmployeeID = employeeId;
newOrder.OrderDate = orderDate;
newOrder.ShipVia = shipVia;
newOrder.ShipName = shipName;
newOrder.ShipCity = shipCity;
dbContext.Orders.Add(newOrder);
dbContext.SaveChanges();
tran.Commit();
}
}
//scope.Complete();
//}
}
public static void ModifyCustomer(string customerId, CustomerProperty property, string newValue)
{
using (var northwindEntities = new NorthwindEntities())
{
var customerToModify = FindCustomerById(northwindEntities, customerId);
switch (property)
{
case CustomerProperty.CompanyName:
customerToModify.CompanyName = newValue;
break;
case CustomerProperty.ContactName:
customerToModify.ContactName = newValue;
break;
case CustomerProperty.ContactTitle:
customerToModify.ContactTitle = newValue;
break;
case CustomerProperty.Address:
customerToModify.Address = newValue;
break;
case CustomerProperty.City:
customerToModify.City = newValue;
break;
case CustomerProperty.Region:
customerToModify.Region = newValue;
break;
case CustomerProperty.PostalCode:
customerToModify.PostalCode = newValue;
break;
case CustomerProperty.Country:
customerToModify.Country = newValue;
break;
case CustomerProperty.Phone:
customerToModify.Phone = newValue;
break;
case CustomerProperty.Fax:
customerToModify.Fax = newValue;
break;
default:
throw new ApplicationException(string.Format("No such property found: {0}", property.ToString()));
}
northwindEntities.SaveChanges();
}
}
public static void DeleteCustomer(string customerId)
{
using (var northwindEntities = new NorthwindEntities())
{
var customerToDelete = FindCustomerById(northwindEntities, customerId);
northwindEntities.Customers.Remove(customerToDelete);
northwindEntities.SaveChanges();
}
}
public static void AddCustomer(NorthwindEntities context, Customer customer)
{
context.Customers.Add(customer);
context.SaveChanges();
}