public static void UpdateCustomer(string customerID, Customer newCustomer)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.Where(cus => cus.CustomerID == customerID)
.FirstOrDefault();
customer.Address = newCustomer.Address ?? customer.Address;
customer.City = newCustomer.City ?? customer.City;
customer.CompanyName = newCustomer.CompanyName ?? customer.CompanyName;
customer.ContactName = newCustomer.ContactName ?? customer.ContactName;
customer.ContactTitle = newCustomer.ContactTitle ?? customer.ContactTitle;
customer.Country = newCustomer.Country ?? customer.Country;
customer.CustomerID = newCustomer.CustomerID ?? customer.CustomerID;
customer.Fax = newCustomer.Fax ?? customer.Fax;
customer.Phone = newCustomer.Phone ?? customer.Phone;
customer.PostalCode = newCustomer.PostalCode ?? customer.PostalCode;
customer.Region = newCustomer.Region ?? customer.Region;
db.SaveChanges();
}
Console.WriteLine("Customer updated!");
}
public static void Main(string[] args)
{
IObjectContextAdapter northwindEntities = new NorthwindEntities();
var cloneNorthwind = northwindEntities.ObjectContext.CreateDatabaseScript();
var createNorthwindTwin = "CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, " +
"FILENAME = 'C:\\NorthwindTwin.mdf', " +
"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwinLog, " +
"FILENAME = 'C:\\NorthwindTwin.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
var connectionStringForCreating = "Server=LOCALHOST;Database=master;Integrated Security=true";
var connectionForCreating = new SqlConnection(connectionStringForCreating);
connectionForCreating.Open();
var createDB = new SqlCommand(createNorthwindTwin, connectionForCreating);
createDB.ExecuteNonQuery();
var connectionStringForClosing = "Server=LOCALHOST;Database=NorthwindTwin;Integrated Security=true";
var connectionForClosing = new SqlConnection(connectionStringForClosing);
connectionForClosing.Open();
var cloneDB = new SqlCommand(cloneNorthwind, connectionForClosing);
cloneDB.ExecuteNonQuery();
Console.WriteLine("The database NorthwindTwin was created!");
}
private static void InsertOrder(string shipName = null, string shipAddress = null, string shipCity = null, string shipRegionm = null,
string shipPostalCode = null, string shipCountry = null, string customerID = null, int? employeeID = null, DateTime? orderDate = null,
DateTime? requiredDate = null, DateTime? shippedDate = null, int? shipVia = null, decimal? freight = null)
{
using (var context = new NorthwindEntities())
{
Order newOrder = new Order
{
ShipAddress = shipAddress,
ShipCity = shipCity,
ShipCountry = shipCountry,
ShipName = shipName,
ShippedDate = shippedDate,
ShipPostalCode = shipPostalCode,
ShipRegion = shipRegionm,
ShipVia = shipVia,
EmployeeID = employeeID,
OrderDate = orderDate,
RequiredDate = requiredDate,
Freight = freight,
CustomerID = customerID
};
context.Orders.Add(newOrder);
context.SaveChanges();
Console.WriteLine("Row is inserted.");
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var context = new NorthwindEntities())
{
this.GridViewEmployees.DataSource = context.Employees.ToList();
Page.DataBind();
}
}
public static void InsertCustomer(Customer customer)
{
using(var db = new NorthwindEntities())
{
db.Customers.Add(customer);
db.SaveChanges();
}
Console.WriteLine("New customer added!");
}
public static List<Order> GetSalesByReagionAndPeriod(string region, DateTime startDate, DateTime endDate, NorthwindEntities context)
{
var result = context.Orders
.Where(order => order.ShipRegion.Equals(region)
&& ((DateTime)order.OrderDate) > startDate
&& ((DateTime)order.OrderDate) < endDate)
.ToList();
return result;
}
public static void Main(string[] args)
{
var northwindEntities = new NorthwindEntities();
var employee = northwindEntities.Employees.FirstOrDefault();
foreach (var territory in employee.EntityTerritories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
protected void Page_Init(object sender, EventArgs e)
{
var db = new NorthwindEntities();
employees = db.Employees.Select(emp => new EmployeeModel
{
Id = emp.EmployeeID,
FirstName = emp.FirstName,
LastName = emp.LastName
})
.ToList();
}
protected void Page_Load(object sender, EventArgs e)
{
int id = int.Parse(Request.QueryString["id"]);
using (var context = new NorthwindEntities())
{
var employee = context.Employees.Where(emp => emp.EmployeeID == id).ToList();
this.DetailsViewEmployee.DataSource = employee;
}
Page.DataBind();
}
public static Product GetProductByID(int productID)
{
Product product;
using (var ctx = new NorthwindEntities())
{
var q = from p in ctx.Products.Include("Category")
where p.ProductID == productID
select p;
product = q.SingleOrDefault<Product>();
}
return product;
}
public static List<Product> GetNewProducts()
{
// List<Product> products;
List<Product> products;
using (var ctx = new NorthwindEntities())
{
products = ctx.Products.Include("Category").OrderByDescending(p => p.ProductID).Take(5).ToList<Product>();
}
return products;
}
private static List<Customer> FindAllCustomersByOrdersIn1997AndShippedToCanada()
{
List<Customer> result;
using(var db = new NorthwindEntities())
{
result = db.Orders
.Where(ord => ord.OrderDate.Value.Year == 1997 && ord.ShipCountry == "Canada")
.Select(cus => cus.Customer)
.ToList();
}
return result;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
using (var context = new NorthwindEntities())
{
var employees = context.Employees.ToList();
this.ListViewEmployees.DataSource = employees;
this.ListViewEmployees.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
var employees = new NorthwindEntities().Employees.ToList();
this.GridViewEmployees.DataSource = employees;
if (Request.QueryString["id"] != null)
{
this.FormViewEmployeeDetails.DataSource = employees;
int id = int.Parse(Request.QueryString["id"]) - 1;
this.FormViewEmployeeDetails.PageIndex = id;
}
Page.DataBind();
}
public static byte[] GetCategoryPictureByID(int categoryID)
{
byte[] byteArray;
using (var ctx = new NorthwindEntities())
{
var q = from c in ctx.Categories
where c.CategoryID == categoryID
select c.Picture;
byteArray = q.SingleOrDefault<byte[]>();
}
return byteArray;
}
private static List<Order> FindAllOrdersRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
List<Order> result;
using (var db = new NorthwindEntities())
{
result = db.Orders
.Where(ord => ord.OrderDate.Value == startDate &&
ord.RequiredDate.Value == endDate && ord.ShipRegion == region)
.Select(order => order)
.ToList();
}
return result;
}
public static void DeleteCustomer(string customerID)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.Where(cus => cus.CustomerID == customerID)
.FirstOrDefault();
db.Customers.Remove(customer);
db.SaveChanges();
}
Console.WriteLine("The customer is removed!");
}
public static List<Category> GetCategories()
{
List<Category> categories;
using (var ctx = new NorthwindEntities())
{
var q = from category in ctx.Categories
select category;
categories = q.ToList<Category>();
}
return categories;
}
private static decimal? GetTotalIncomes(string supplierName, DateTime? startDate, DateTime? endDate)
{
using (var northwindEntites = new NorthwindEntities())
{
var totalIncomeSet = northwindEntites
.usp_GetTotalIncomeForSupplier(supplierName, startDate, endDate);
foreach (var totalIncome in totalIncomeSet)
{
return totalIncome;
}
}
return null;
}
protected void Page_Load(object sender, EventArgs e)
{
this.ButtonBack.Visible = false;
this.FormViewDetails.Visible = false;
if (!Page.IsPostBack)
{
using (var context = new NorthwindEntities())
{
var employees = context.Employees.Select(emp => new { Id = emp.EmployeeID, FullName = emp.FirstName + " " + emp.LastName }).ToList();
this.GridViewEmployees.DataSource = employees;
this.GridViewEmployees.DataBind();
}
}
}
protected void GridViewEmployees_SelectedIndexChanged(object sender, EventArgs e)
{
int employeeId = Convert.ToInt32(this.GridViewEmployees.SelectedDataKey.Value);
using (var context = new NorthwindEntities())
{
var employees = context.Employees.Where(emp => emp.EmployeeID == employeeId).ToList();
this.FormViewDetails.DataSource = employees;
this.FormViewDetails.DataBind();
this.GridViewEmployees.Visible = false;
this.FormViewDetails.Visible = true;
this.ButtonBack.Visible = true;
}
}
/// <summary>
/// Implement previous by using native SQL query and executing it through the DbContext.
/// </summary>
/// <param name="date">Date of the order</param>
/// <param name="country">Country of the order</param>
private static void FindCustomersOrders(DateTime date, string country)
{
var northwindEntities = new NorthwindEntities();
string nativeSqlQuery = "SELECT c.ContactName, o.OrderDate, o.ShipName, o.ShipCountry FROM dbo.Customers AS c " +
"INNER JOIN dbo.orders AS o ON c.CustomerID = o.CustomerID " +
"WHERE year(o.OrderDate) = '" + date.Year + "' AND o.ShipCountry = '" + country + "'";
var customersOrders = northwindEntities.Database.SqlQuery<CustomerOrder>(nativeSqlQuery);
var format = "ContactName Name: {0} \nOrder Date: {1} \nShip Name: {2} \nShipCountry: {3} \n";
foreach (var order in customersOrders)
{
Console.WriteLine(format, order.ContactName, order.OrderDate, order.ShipName, order.ShipCountry);
}
}
private static void FindAllCustomersByOrdersIn1997AndShippedToCanadaNative()
{
NorthwindEntities context = new NorthwindEntities();
string nativeSQLQuery =
"SELECT * FROM Customers c " +
"INNER JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada' ";
var customersNative = context.Database.SqlQuery<Customer>(nativeSQLQuery);
foreach (var custNative in customersNative)
{
Console.WriteLine(custNative.ContactName);
}
context.Dispose();
}
public static List<Product> GetFeaturedProducts()
{
List<Product> products = new List<Product>() ;
using (var ctx = new NorthwindEntities())
{
var q = ctx.Products.Include("Category");
int skip = (DateTime.Now.Month - 1) * 5;
// Select 5 random products
products = q.OrderBy(p => p.ProductID).Skip(skip).Take(5).ToList<Product>();
}
return products;
}
/// <summary>
/// Write a method that finds all the sales by specified region and period (start / end dates).
/// </summary>
/// <param name="region">Sale's region</param>
/// <param name="startDate">Sales from date</param>
/// <param name="endDate">Sales to date</param>
private static void FindSalesBySpecifiedRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
var northwindEntities = new NorthwindEntities();
var sales = northwindEntities.Orders.Where(o => (startDate < o.OrderDate && o.OrderDate < endDate) && o.ShipRegion == region);
var pattern = "OrderID: {0} \nCustomerID: {1} \nEmployeeID: {2} \nOrderDate: {3} \nRequiredDate: {4} " +
"\nShippedDate: {5} \nShipVia: {6} \nFreight: {7} \nShipName: {8} \nShipAddress: {9} \nShipCity: {10} " +
"\nShipRegion: {11} \nShipPostalCode: {12} \nShipCountry: {13} \n";
foreach (var sale in sales)
{
Console.WriteLine(pattern, sale.OrderID, sale.CustomerID, sale.EmployeeID, sale.OrderDate, sale.RequiredDate, sale.ShippedDate,
sale.ShipVia, sale.Freight, sale.ShipName, sale.ShipAddress, sale.ShipCity, sale.ShipRegion, sale.ShipPostalCode, sale.ShipCountry);
}
}
protected void Page_Init(object sender, EventArgs e)
{
var db = new NorthwindEntities();
employees = db.Employees.Select(emp => new EmployeeModel
{
Id = emp.EmployeeID,
FirstName = emp.FirstName,
LastName = emp.LastName,
Title = emp.Title,
TitleOfCourtesy = emp.TitleOfCourtesy,
BirthDate = emp.BirthDate,
HireDate = emp.HireDate,
Address = emp.Address,
City = emp.City,
Country = emp.Country
})
.ToList();
}
protected void GridViewEmployees_SelectedIndexChanged(object sender, EventArgs e)
{
int employeeId = Convert.ToInt32(this.GridViewEmployees.SelectedDataKey.Value);
using (var context = new NorthwindEntities())
{
FormViewDetails.DataSource = new List<Employee>()
{
context.Employees.FirstOrDefault(x => x.EmployeeID == employeeId)
};
FormViewDetails.DataBind();
this.GridViewEmployees.Visible = false;
this.FormViewDetails.Visible = true;
this.ButtonBack.Visible = true;
}
}
public static Northwind.usp_GetTotalIncome_Result GetAllIncome(string companyName, DateTime startDate, DateTime endDate, NorthwindEntities context)
{
//NOTE!!!
//Make sure you execute this script only once and update your C# database model with the new prcedure
context.Database.ExecuteSqlCommand("CREATE PROCEDURE [usp_GetTotalIncome] " +
"(@companyName nvarchar(40), @dateStart datetime, @dateEnd datetime) " +
"AS " +
"SELECT s.CompanyName, " +
"SUM(p.UnitsInStock * p.UnitPrice) AS Reveniew " +
"FROM Suppliers s " +
"INNER JOIN Products p " +
"ON s.SupplierID = p.SupplierID " +
"WHERE s.CompanyName = @companyName " +
"GROUP BY s.SupplierID, s.CompanyName");
context.SaveChanges();
var result = context.usp_GetTotalIncome(companyName, startDate, endDate).FirstOrDefault();
return result;
}
public static List<Product> GetProducts()
{
List<Product> products;
using (var ctx = new NorthwindEntities())
{
//var q = from product in ctx.Products
// orderby product.ProductName
// select product;
products = ctx.Products.Include("Category").ToList<Product>();
//q.Include(b => b.Category).ToList<Product>();
}
return products;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) //check if the webpage is loaded for the first time.
{
ViewState["PreviousPage"] = Request.UrlReferrer;//Saves the Previous page url in ViewState
}
string id = this.Request.Params["id"];
if (!string.IsNullOrWhiteSpace(id))
{
int employeeId = int.Parse(id);
using (var context = new NorthwindEntities())
{
var employee = context.Employees.FirstOrDefault(emp => emp.EmployeeID == employeeId);
if (employee != null)
{
this.DetailsViewEmployee.DataSource = new List<Employee>() { employee };
this.DetailsViewEmployee.DataBind();
}
}
}
}
