public static decimal? UseFindIncomesForSupplierInPeriod()
{
decimal? income;
using (var dbContext = new NorthwindEntities())
{
income = dbContext.uspTotalIncomesForSupplierInPeriod("Tokyo Traders", new DateTime(1994, 01, 01), new DateTime(2014, 09, 09)).First();
}
return income;
}
public static void GenerateTwinDb()
{
var northwindEntities = new NorthwindEntities();
string connectionString = northwindEntities.Database.Connection.ConnectionString;
connectionString = connectionString.Replace("Northwind", "NorthwindTwin");
//Console.WriteLine(connectionString);
DbContext context = new DbContext(connectionString);
var dbScript = CreateDatabaseScript(northwindEntities);
dbScript = RenameDb(dbScript);
//Console.WriteLine(dbScript);
context.Database.ExecuteSqlCommand(dbScript);
}
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 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 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 IEnumerable<Customer> GetCustomersToList()
{
var result = new List<Customer>();
using (var northwindEntities = new NorthwindEntities())
{
result = northwindEntities.Customers.ToList();
}
return result;
}
/*Write a method that finds all the sales by specified
region and period (start / end dates). */
public static IEnumerable<Order> SalesByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
var result = new List<Order>();
using (var northwindEntities = new NorthwindEntities())
{
result = northwindEntities
.Orders
.Where(
o =>
(o.ShippedDate.Value >= startDate && o.ShippedDate.Value <= endDate) &&
(o.ShipRegion == region)
).ToList();
}
return result;
}
/*Implement previous by using native SQL query and
executing it through the DbContext.*/
public static IEnumerable<Customer> GetCustomersByOrderMadeIn1997SShippedToCanadaSqlQuery()
{
IEnumerable<Customer> result;
using (var northwindEntities = new NorthwindEntities())
{
string sqlQuery = "SELECT DISTINCT c.* FROM Customers c " +
"INNER JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE (1997 = (DATEPART (year, o.OrderDate)))" +
"AND (o.ShipCountry = 'Canada')";
result = northwindEntities.Customers.SqlQuery(sqlQuery).ToList();
}
return result;
}
/*Write a method that finds all customers who have
orders made in 1997 and shipped to Canada.*/
public static IEnumerable<Customer> GetCustomersByOrderMadeIn1997SShippedToCanada()
{
var result = new List<Customer>();
using (var northwindEntities = new NorthwindEntities())
{
result = northwindEntities.Customers.Where(c => c.Orders.Any(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada")).ToList();
}
return result;
}
private static Customer FindCustomerById(NorthwindEntities entities, string customerId)
{
Customer foundCustomer = entities.Customers.FirstOrDefault(
c => c.CustomerID == customerId);
return foundCustomer;
}
public static void DeleteCustomer(string customerId)
{
using (var northwindEntities = new NorthwindEntities())
{
var customerToDelete = FindCustomerById(northwindEntities, customerId);
northwindEntities.Customers.Remove(customerToDelete);
northwindEntities.SaveChanges();
}
}
