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(); } }