protected void Page_Load(object sender, EventArgs e)
{
NorthwindEntities context = new NorthwindEntities();
this.GridViewEmployees.DataSource = context.Employees.ToList();
this.GridViewEmployees.DataBind();
}
public static IEnumerable<string> GetSalesByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
var specifiedSales = northwindEntities.Invoices.Where(s => s.Region == region &&
s.ShippedDate.Value.CompareTo(startDate) >= 0 && s.ShippedDate.Value.CompareTo(endDate) <= 0)
.Select(s => new
{
Product = s.ProductName,
Customer = s.CustomerName,
Quantity = s.Quantity,
ShippedDate = s.ShippedDate,
});
IList<string> sales = new List<string>();
foreach (var sale in specifiedSales)
{
sales.Add(string.Format("Product: {0}{4}Customer: {1}{4}Quantity: {2}{4}Shipped date: {3}{4}",
sale.Product, sale.Customer, sale.Quantity,
sale.ShippedDate.Value.ToShortDateString(), Environment.NewLine));
}
return sales;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack)
{
return;
}
int employeeId;
bool tryParseId = int.TryParse(Request.Params["id"], out employeeId);
if (Request.Params["Id"] == null || !tryParseId)
{
Response.Redirect("~/Employees.aspx");
}
NorthwindEntities context = new NorthwindEntities();
var employee = context.Employees.Where(emp => emp.EmployeeID == employeeId).ToList();
if (employee.Count == 0)
{
Response.Redirect("~/Employees.aspx");
}
this.DetailsViewEmployee.DataSource = employee;
this.DetailsViewEmployee.DataBind();
}
static void AddNewOrders(IEnumerable<object[]> orderInfos)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
using (TransactionScope scope = new TransactionScope())
{
foreach (object[] orderInfo in orderInfos)
{
Order order = new Order()
{
CustomerID = (orderInfo[0] as string),
EmployeeID = (orderInfo[1] as int?),
OrderDate = (orderInfo[2] as DateTime?),
RequiredDate = (orderInfo[3] as DateTime?),
ShippedDate = (orderInfo[4] as DateTime?),
ShipVia = (orderInfo[5] as int?),
Freight = (orderInfo[6] as decimal?),
ShipName = (orderInfo[7] as string),
ShipAddress = (orderInfo[8] as string),
ShipCity = (orderInfo[9] as string),
ShipRegion = (orderInfo[10] as string),
ShipPostalCode = (orderInfo[11] as string),
ShipCountry = (orderInfo[12] as string),
};
northwindEntities.Orders.Add(order);
}
northwindEntities.SaveChanges();
scope.Complete();
}
}
}
static void Main()
{
string decorationLine = new string('-', Console.WindowWidth);
Console.Write(decorationLine);
Console.WriteLine("***Generating 'NorthwindTwin' database using the Northwind database context***");
Console.Write(decorationLine);
string createNorthwindCloneDBCommand = @"CREATE DATABASE NorthwindTwin ON PRIMARY
(NAME = NorthwindTwin, FILENAME = 'D:\NorthwindTwin.mdf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%)
LOG ON (NAME = NorthwindTwinLog, FILENAME = 'D:\NorthwindTwin.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)";
SqlConnection dbConnectionForCreatingDB =
new SqlConnection(Settings.Default.ConnectionStringForCreatingTwinDB);
dbConnectionForCreatingDB.Open();
using (dbConnectionForCreatingDB)
{
SqlCommand createDBCommand =
new SqlCommand(createNorthwindCloneDBCommand, dbConnectionForCreatingDB);
createDBCommand.ExecuteNonQuery();
}
IObjectContextAdapter northwindContext = new NorthwindEntities();
string cloneNorthwindScript = northwindContext.ObjectContext.CreateDatabaseScript();
SqlConnection dbConnectionForCloningDB = new SqlConnection(Settings.Default.ConnectionStringForCloningDB);
dbConnectionForCloningDB.Open();
using (dbConnectionForCloningDB)
{
SqlCommand cloneDBCommand = new SqlCommand(cloneNorthwindScript, dbConnectionForCloningDB);
cloneDBCommand.ExecuteNonQuery();
}
}
public static void AddCustomer(
string id,
string companyName,
string contactName,
string contactTitle,
string address,
string city,
string region,
string postalCode,
string country,
string phone,
string fax)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
Customer newCustomer = new Customer()
{
CustomerID = id,
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();
}
}
public static void DeleteCustomerById(string customerId)
{
NorthwindEntities context = new NorthwindEntities();
Customer customer = GetCustomerById(context, customerId);
context.Customers.Remove(customer);
context.SaveChanges();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack)
{
return;
}
NorthwindEntities context = new NorthwindEntities();
var employeesDataSource = from employee in context.Employees.ToList()
select new
{
FullName = employee.FirstName + " " + employee.LastName,
Id = employee.EmployeeID
};
this.GridViewEmployees.DataSource = employeesDataSource;
this.GridViewEmployees.DataBind();
if(Request.Params["Id"] != null)
{
int employeeId = int.Parse(Request.Params["Id"]);
var employee = context.Employees.Where(emp => emp.EmployeeID == employeeId).ToList();
this.FormViewEmployee.DataSource = employee;
this.FormViewEmployee.DataBind();
}
}
public static bool ModifyCustomer(
string id,
string newCompanyName,
string newContactName,
string newContactTitle,
string newAddress,
string newCity,
string newRegion,
string newPostalCode,
string newCountry,
string newPhone,
string newFax)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
Customer customerToModify = northwindEntities.Customers.Find(id);
if (customerToModify != null)
{
customerToModify.CompanyName = newCompanyName ?? customerToModify.CompanyName;
customerToModify.ContactName = newContactName ?? customerToModify.ContactName;
customerToModify.ContactTitle = newContactTitle ?? customerToModify.ContactTitle;
customerToModify.Address = newAddress ?? customerToModify.Address;
customerToModify.City = newCity ?? customerToModify.City;
customerToModify.Region = newRegion ?? customerToModify.Region;
customerToModify.PostalCode = newPostalCode ?? customerToModify.PostalCode;
customerToModify.Country = newCountry ?? customerToModify.Country;
customerToModify.Phone = newPhone ?? customerToModify.Phone;
customerToModify.Fax = newFax ?? customerToModify.Fax;
northwindEntities.SaveChanges();
return true;
}
return false;
}
}
// The id parameter should match the DataKeyNames value set on the control
// or be decorated with a value provider attribute, e.g. [QueryString]int id
public object EmployeesFormView_GetItem(int id)
{
var employee = new NorthwindEntities().Employees
.FirstOrDefault(empl => empl.EmployeeID == id);
return employee;
}
static void Main()
{
string decorationLine = new string('-', Console.WindowWidth);
Console.Write(decorationLine);
Console.WriteLine("***Performing concurrent changes over the same records");
Console.WriteLine("using two data contexts***");
Console.Write(decorationLine);
using (NorthwindEntities northwindEntities1 = new NorthwindEntities())
{
using (NorthwindEntities northwindEntities2 = new NorthwindEntities())
{
Customer customerByFirstDataContext = northwindEntities1.Customers.Find("CHOPS");
customerByFirstDataContext.Region = "SW";
Customer customerBySecondDataContext = northwindEntities2.Customers.Find("CHOPS");
customerBySecondDataContext.Region = "SSWW";
northwindEntities1.SaveChanges();
northwindEntities2.SaveChanges();
}
}
Console.WriteLine("Changes successfully made!");
}
internal static void Main()
{
var db = new NorthwindEntities();
using (db)
{
db.Database.CreateIfNotExists();
}
}
protected void GridViewEmployees_SelectedIndexChanged(object sender, EventArgs e)
{
var id = this.GridViewEmployees.SelectedDataKey.Value;
NorthwindEntities context = new NorthwindEntities();
this.FormViewEmployeeDetails.DataSource = context.Employees.Where(emp => emp.EmployeeID == (int)id).ToList();
this.FormViewEmployeeDetails.DataBind();
}
private static decimal? GetIncomeByPeriodAndSupplier(DateTime start, DateTime end, int supplierId)
{
var db = new NorthwindEntities();
using (db)
{
var result = db.IncomeFromSupplierAndPeriod(start, end, 1).Select(r => r.Value).First();
return result;
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var db = new NorthwindEntities())
{
var employees = db.Employees.ToList();
this.ListViewEmployees.DataSource = employees;
this.ListViewEmployees.DataBind();
}
}
public static IEnumerable<string> GetOrdersShippedToAndMadeFromYear(string country, int year)
{
var db = new NorthwindEntities();
using (db)
{
return db.Orders
.Where(o => o.ShipCountry == country && o.OrderDate.Value.Year == year)
.GroupBy(o => o.Customer.CompanyName)
.Select(g => g.Key)
.ToList();
}
}
// Task 03.
// Write a method that finds all customers who have orders made
// in 1997 and shipped to Canada.
public static IEnumerable<string> FindCustomersByOrder(int year, string shipCountry)
{
NorthwindEntities context = new NorthwindEntities();
IList<string> customer =
context.Orders
.Where(o => o.OrderDate.Value.Year == year)
.Where(o => o.ShipCountry == shipCountry)
.Select(c => c.Customer.ContactName).Distinct().ToList();
return customer;
}
// Task 05.
// Write a method that finds all the sales by specified region
// and period (start / end dates).
public static IEnumerable<Sales_by_Year_Result> FindSalesByRegionAndPeriod(string shipRegion, DateTime startYear, DateTime endYear)
{
NorthwindEntities context = new NorthwindEntities();
var salesByRegionAndYear =
from salesByYear in context.Sales_by_Year(startYear, endYear)
join order in context.Orders
.Where(o => o.ShipRegion == shipRegion)
on salesByYear.OrderID equals order.OrderID
select salesByYear;
return salesByRegionAndYear;
}
protected void Page_Load(object sender, EventArgs ev)
{
var employeeIdString = Request.Params["id"];
int employeID;
var isPassed = int.TryParse(employeeIdString, out employeID);
if (isPassed)
{
var context = new NorthwindEntities();
var selectedEmployees = context.Employees.Where(e => e.EmployeeID == employeID).ToList();
this.DetailsViewEmployee.DataSource = selectedEmployees;
this.DetailsViewEmployee.DataBind();
}
}
public static bool DeleteCustomer(string id)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
Customer customerToDelete = northwindEntities.Customers.Find(id);
if (customerToDelete != null)
{
northwindEntities.Customers.Remove(customerToDelete);
northwindEntities.SaveChanges();
return true;
}
return false;
}
}
static decimal? GetTotalIncomes(string supplierName, DateTime? startDate, DateTime? endDate)
{
using (NorthwindEntities northwindEntites = new NorthwindEntities())
{
var totalIncomesSet = northwindEntites
.usp_GetTotalIncomesBySupplierNameAndPeriod(supplierName, startDate, endDate);
foreach (var totalIncomes in totalIncomesSet)
{
return totalIncomes;
}
}
return null;
}
// Task 04.
// Implement previous by using native SQL query and executing it
// through the DbContext.
public static IEnumerable<string> FindCustomersByOrderNativeSQL(int year, string shippedCountry)
{
NorthwindEntities context = new NorthwindEntities();
string nativeSQLQuery =
"SELECT DISTINCT c.ContactName as CustomerName " +
"FROM Customers c JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE DATEPART(year, o.OrderDate) = {0} AND o.ShipCountry = {1} ";
object[] parameters = { year, shippedCountry };
var customer = context.Database.SqlQuery<string>(
nativeSQLQuery, parameters);
return customer;
}
internal static void Main()
{
var db = new NorthwindEntities();
using (db)
{
var dbCopy = new NorthwindEntities();
using (dbCopy)
{
var first = db.Categories.Where(c => c.CategoryID == 1).First();
first.CategoryName = "Pesho";
var second = dbCopy.Categories.Where(c => c.CategoryID == 1).First();
second.CategoryName = "Gosho";
db.SaveChanges();
dbCopy.SaveChanges();
}
}
}
public static IEnumerable<string> GetWithSQLOrdersShippedToAndMadeFromYear(string country, int year)
{
var db = new NorthwindEntities();
using (db)
{
string nativeSqlQuery =
"SELECT CompanyName FROM Customers c " +
"INNER JOIN Orders o ON o.CustomerID = c.CustomerID " +
"WHERE c.Country = '" +
country +
"' AND YEAR(o.OrderDate) = " +
year +
"GROUP BY CompanyName";
object[] parameters = { country, year };
var companies = db.Database.SqlQuery<string>(nativeSqlQuery, parameters);
return companies.ToList();
}
}
static void Main()
{
StringBuilder dbScript = new StringBuilder();
dbScript.Append("USE NorthwindTwin ");
NorthwindEntities objectContext = new NorthwindEntities();
string generatedScript =
((IObjectContextAdapter)objectContext).ObjectContext.CreateDatabaseScript();
dbScript.Append(generatedScript);
objectContext.Database.ExecuteSqlCommand("CREATE DATABASE NorthwindTwin");
objectContext.Database.ExecuteSqlCommand(dbScript.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack)
{
return;
}
NorthwindEntities context = new NorthwindEntities();
var employeesDataSource = from employee in context.Employees.ToList()
select new
{
FullName = employee.FirstName + " " + employee.LastName,
Id = employee.EmployeeID
};
this.GridViewEmployees.DataSource = employeesDataSource;
this.GridViewEmployees.DataBind();
}
static void Main()
{
string decorationLine = new string('-', Console.WindowWidth);
Console.Write(decorationLine);
Console.WriteLine("***Accessing an employee's corresponding territories through");
Console.WriteLine("a property of type EntitySet<T> in the Northwind database***");
Console.Write(decorationLine);
NorthwindEntities northwindEntities = new NorthwindEntities();
int employeeID = 1;
Employee employee = northwindEntities.Employees.Find(employeeID);
// Property added at Northwind.Data ExtendedEmployee file
EntitySet<Territory> territories = employee.EntityTerritories;
Console.WriteLine("All territories for employee with ID {0} are:", employeeID);
foreach (var territory in territories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
static void Main(string[] args)
{
var creator = new PDFCreator.Classes.PDFCreator(Directory.GetCurrentDirectory());
creator.CreatePDF("testPDF");
var dbCon = new NorthwindEntities();
using (dbCon)
{
var products = from product in dbCon.Products
join supplier in dbCon.Suppliers
on product.SupplierID equals supplier.SupplierID
where product.ProductID < 25
select product;
creator.CreateTable(5);
creator.AddTableHeader(DateTime.Now.ToString(), 5);
creator.AddColumnNames(new string[] { "ProductID", "ProductName", "Price", "Quantity", "SuplierName" });
foreach (var product in products)
{
creator.AddContent(new string[] { product.ProductID.ToString(), product.ProductName, product.UnitPrice.ToString(), product.QuantityPerUnit, product.Supplier.CompanyName });
}
creator.AddTable();
creator.CreateTable(5);
creator.AddTableHeader(DateTime.Now.ToString(), 5);
creator.AddColumnNames(new string[] { "ProductID", "ProductName", "Price", "Quantity", "SuplierName" });
foreach (var product in products)
{
creator.AddContent(new string[] { product.ProductID.ToString(), product.ProductName, product.UnitPrice.ToString(), product.QuantityPerUnit, product.Supplier.CompanyName });
}
creator.AddTable();
creator.CloseFile();
}
}
static void Main()
{
NorthwindEntities db = new NorthwindEntities();
using (db)
{
var customers = db.Orders.Where(x => x.ShippedDate.Value.Year == 1997 && x.ShipCountry == "Canada")
.Join(db.Customers, o => o.CustomerID, c => c.CustomerID, (o, c) => new
{
shipCountry = o.ShipCountry,
shipYear = o.ShippedDate.Value.Year,
customerName = c.ContactName
});
Console.WriteLine("CustomerName | ShipedCountry | ShipedYear");
foreach (var customer in customers)
{
Console.WriteLine(customer.customerName + " | " + customer.shipCountry + " | "+ customer.shipYear);
}
}
}
public SqlCustomersRepository()
{
this.context = new NorthwindEntities();
}
public static void InsertNewCustomer(
string id,
string company,
string name = null,
string title = null,
string address = null,
string city = null,
string region = null,
string code = null,
string country = null,
string phone = null,
string fax = null)
{
var db = new NorthwindEntities();
using (db)
{
var newcustomer = new Customer
{
CustomerID = id,
CompanyName = company,
ContactName = name,
ContactTitle = title,
Address = address,
City = city,
Region = region,
PostalCode = code,
Country = country,
Phone = phone,
Fax = fax
};
db.Customers.Add(newcustomer);
db.SaveChanges();
}
}