public List <CustomerEntity> AllCustomers()
{
using (var context = new NorthWindEntities())
{
var customerData = (
from customer in context.Customers
join contactType in context.ContactTypes on customer.ContactTypeIdentifier equals contactType.ContactTypeIdentifier
join contact in context.Contacts on customer.ContactId equals contact.ContactId
select new CustomerEntity
{
CustomerIdentifier = customer.CustomerIdentifier,
Company = customer.CompanyName,
ContactIdentifier = customer.ContactId,
FirstName = contact.FirstName,
LastName = contact.LastName,
ContactTypeIdentifier = contactType.ContactTypeIdentifier,
ContactTitle = contactType.ContactTitle,
Street = customer.Address,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentifier,
CountyName = customer.Country.Name
}).ToList();
return(customerData);
}
}
static void Main()
{
InsertCustomer("PENAD", "Kalin");
//UpdateCustomer("PENAD", "Dancho");
//DeleteCustomer("PENAD");
//Task3Metod(1997,"Canada");
//Task4Metod(1997,"Canada");
//Task5Metod("SP",new DateTime(1990,1,1),new DateTime(2014,4,16));
using (var db = new NorthWindEntities())
{
var log = db.usp_TotalIncome("Exotic Liquids", new DateTime(1990, 10, 12), new DateTime(2013, 12, 12));
foreach (var l in log)
{
Console.WriteLine("Name: {0}, Product name: {1}, Order date: {2}", l.CompanyName, l.ProductName, l.OrderDate);
Console.WriteLine("------------------------------------------------");
}
}
}
static void UpdateCustomer(string CustomerID, 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)
{
using (var db = new NorthWindEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == CustomerID).FirstOrDefault();
if (customer != null)
{
customer.CustomerID = CustomerID;
customer.CompanyName = CompanyName;
customer.ContactName = ContactName;
customer.ContactTitle = ContactTitle;
customer.Address = Address;
customer.City = City;
customer.Region = Region;
customer.PostalCode = PostalCode;
customer.Country = Country;
customer.Phone = Phone;
customer.Fax = Fax;
db.SaveChanges();
}
else
{
throw new ArgumentException("No such customer!");
}
}
}
/// <summary>
/// Get a single customer by primary key using user define class which
/// has a partial property/field list and a custom Comparer overriding Equals.
/// </summary>
/// <param name="pIdentifier">Valid key to locate</param>
/// <param name="pLog">true to log EF generation to the test output window</param>
/// <returns>Valid customer if found by primary key</returns>
public Company GetCompanyWithCountryByIdentifier(int pIdentifier, bool pLog = false)
{
using (var context = new NorthWindEntities())
{
if (pLog)
{
context.Database.Log = Console.Write;
}
// inspect this variable before hitting the line below with FirstOrDefault.
// talk about using var
IQueryable <Company> companyQuery =
from company in context.Customers
join contact in context.Contacts on company.ContactId equals contact.ContactId
join contactType in context.ContactTypes on contact.ContactId equals contactType.ContactTypeIdentifier
join country in context.Customers on company.CountryIdentifier equals country.CountryIdentifier
where company.CustomerIdentifier == pIdentifier
select new Company
{
Id = pIdentifier,
Name = company.CompanyName,
ContactIdentifier = company.ContactId.Value,
FirstName = company.Contact.FirstName,
LastName = company.Contact.LastName,
Title = company.ContactType.ContactTitle,
Country = company.Country.Name,
CountryId = company.CountryIdentifier.Value
};
// inspect this line before returning to the caller.
var result = companyQuery.FirstOrDefault();
return(result);
}
}
public string GetData(int cart)
{
string cartData = cart.ToString();
cartData += "test";
//return String.Format("Hello {0}", cartData);
int data = 0;
try { data = cart; }
catch (Exception ex) {
return("No data");
}
String returntable = "<table><tr>";
returntable += "<th>Product </th><th>Ammount </th><th>Price Per product </th><th>Total </th></tr>";
using (NorthWindEntities context = new NorthWindEntities())
{
IEnumerable <CartContent> Cart = context.spGetCart(cart);
foreach (CartContent product in Cart)
{
returntable += "<tr><td>" + product.ProductName + "</td><td>" + product.HowMany.ToString() + "</td><td>" + product.UnitPrice.ToString() + "</td><td>" + (product.UnitPrice * (Convert.ToDecimal(product.HowMany))).ToString() + "</td></tr>";
}
}
returntable += "<table>";
return(returntable);
}
/// <summary>
/// Provides a method to update an existing customer and check for validation errors.
/// If there are issues in validation the entry is reverted back to it's former state.
///
/// Also, the validation errors are reported back to the user interface.
///
/// </summary>
/// <param name="EditedCustomer"></param>
/// <returns></returns>
public bool UpdateCustomer(Customer EditedCustomer)
{
using (NorthWindEntities context = new NorthWindEntities())
{
try
{
context.Entry(EditedCustomer).State = EntityState.Modified;
context.SaveChanges();
ValidationMessage = "";
return(true);
}
catch (System.Data.Entity.Validation.DbEntityValidationException ef)
{
UndoPendingChanges(context);
ValidationMessage = ef.DbEntityValidationExceptionToString();
return(false);
}
catch (Exception ex)
{
UndoPendingChanges(context);
ValidationMessage = "Update failed";
return(false);
}
}
}
public List <Customer> GetCustomerEntity()
{
using (var db = new NorthWindEntities())
{
return(db.Customers.ToList());
}
}
/// <summary>
/// Provides a search for company name
/// </summary>
/// <param name="value">text to use for filter</param>
/// <param name="options">Type of search</param>
/// <remarks>
/// By default EF is case sensitive. Here we use ToLower to allow
/// case insensitive search but a fragile method is modifying the underlying table
/// https://milinaudara.wordpress.com/2015/02/04/case-sensitive-search-using-entity-framework-with-custom-annotation/
/// </remarks>
public void CompanyNameFilter(string value, FilterOptions options)
{
using (NorthWindEntities context = new NorthWindEntities())
{
switch (options)
{
case FilterOptions.StartsWith:
DataSource = context.Customers.Filter(cust => cust.CompanyName.ToLower().StartsWith(value.ToLower(), StringComparison.CurrentCulture)).ToList();
break;
case FilterOptions.Contains:
DataSource = context.Customers.Filter(cust => cust.CompanyName.ToLower().Contains(value.ToLower())).ToList();
break;
case FilterOptions.EndsWith:
DataSource = context.Customers.Filter(cust => cust.CompanyName.ToLower().EndsWith(value.ToLower(), StringComparison.CurrentCulture)).ToList();
break;
case FilterOptions.Equals:
DataSource = context.Customers.Filter(cust => cust.CompanyName.Equals(value, StringComparison.OrdinalIgnoreCase)).ToList();
break;
default:
break;
}
}
}
/// <summary>
/// Demonstrates
/// * loading navigation properties
/// * not loading navigation properties
///
/// done via
/// context.Configuration.LazyLoadingEnabled = false;
/// </summary>
/// <param name="pIdentifier"></param>
/// <returns></returns>
public Customer GetCompanyByCustomerIdentifierEager(int pIdentifier)
{
/*
* Country is not null for all - note .Include
*/
using (var context = new NorthWindEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var test = context.Customers.Include(cust => cust.Country);
var result1 = test.ToList();
Console.WriteLine();
}
/*
* NEVER DO THIS even though it works. Include in this case
* will fail if the entity Country is removed or renamed.
*/
using (var context = new NorthWindEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var test = context.Customers.Include("Country");
var result1a = test.ToList();
Console.WriteLine();
}
/*
* Country is not null for all
*/
using (var context = new NorthWindEntities())
{
context.Configuration.LazyLoadingEnabled = true;
var test = context.Customers;
var result2 = test.ToList();
Console.WriteLine();
}
/*
* Country is null for all
*/
using (var context = new NorthWindEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var test = context.Customers;
var result3 = test.ToList();
Console.WriteLine();
}
/*
* Uses default for LazyLoadingEnabled
*/
using (var context = new NorthWindEntities())
{
var test = context.Customers;
var result4 = test.ToList();
Console.WriteLine();
}
return(new Customer());
}
/// <summary>
/// Insert new Customer
/// </summary>
/// <param name="pCustomer"></param>
/// <returns></returns>
public bool InsertCustomer(Customer pCustomer)
{
using (var context = new NorthWindEntities())
{
context.Entry(pCustomer).State = EntityState.Added;
return(context.SaveChanges() == 1);
}
}
/// <summary>
/// Here the current customer displayed in the DataGridView is removed
/// from the database.
/// </summary>
/// <param name="sender"></param>
/// <remarks>
/// Make sure if working with child records that under table design for
/// these tables you have set delete action to cascade otherwise the remove
/// will fail and a runtime exception will be thrown.
/// </remarks>
public void Remove(Customer sender)
{
using (NorthWindEntities context = new NorthWindEntities())
{
context.Entry(sender).State = EntityState.Deleted;
context.SaveChanges();
}
}
public Customer GetCompanyByCustomerIdentifier(int pIdentifier)
{
using (var context = new NorthWindEntities())
{
var repository = new CustomerRepository(context);
return(repository.GetById(pIdentifier));
}
}
/// <summary>
/// Insert new Customer
/// </summary>
/// <param name="pCustomer"></param>
/// <returns></returns>
public int InsertCustomer(Customer pCustomer)
{
using (var context = new NorthWindEntities())
{
context.Entry(pCustomer).State = EntityState.Added;
context.SaveChanges();
return(pCustomer.CustomerIdentifier);
}
}
/// <summary>
/// - pCustomer has one property set, the primary key
/// - A variable is used to capture SaveChanges for debugging.
/// Could have been done same as <seealso cref="InsertCustomer"/>
/// </summary>
/// <param name="pCustomer"></param>
/// <returns>true if deleted, false if not deleted</returns>
public bool RemoveCustomer(Customer pCustomer)
{
using (var context = new NorthWindEntities())
{
context.Entry(pCustomer).State = EntityState.Deleted;
var results = context.SaveChanges();
return(results == 1);
}
}
public int CountryCountForCustomers(int pCountryCode)
{
using (var context = new NorthWindEntities())
{
return(context.Entry(context.Countries.Find(pCountryCode))
.Collection(item => item.Customers)
.Query()
.Count());
}
}
static void Task5Metod(string region, DateTime start, DateTime end)
{
using (var db = new NorthWindEntities())
{
foreach (var order in db.Orders.Where(x => (DateTime)(x.OrderDate) >= start && (DateTime)(x.OrderDate) <= end && x.ShipRegion == region))
{
Console.WriteLine("Ship name: {0}\nOrder date: {1}\nRegion: {2}", order.ShipName, order.OrderDate, order.ShipRegion);
Console.WriteLine("-------------------------------------");
}
}
}
static void InsertCustomer(string CustomerID, 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)
{
using (var db = new NorthWindEntities())
{
db.Customers.Add(new Customer(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax));
db.SaveChanges();
}
}
/// <summary>
/// Return a distinct list of contact titles
/// </summary>
/// <remarks>
/// In a normalized database the titles would be in a reference table.
/// </remarks>
public void GetContactTitles()
{
using (NorthWindEntities context = new NorthWindEntities())
{
ContactList = context
.Customers
.DistinctBy(cust => cust.ContactTitle)
.OrderBy(cust => cust.ContactTitle)
.Select(cust => cust.ContactTitle)
.ToList();
}
}
/// <summary>
/// Given the method <see cref="AnonymousCustomer"/>
/// </summary>
/// <param name="pIdentifier"></param>
/// <returns></returns>
public CustomerSpecial NotAnonymousCustomer(int pIdentifier)
{
using (var context = new NorthWindEntities())
{
// execute immediately
return(context.Customers
.Join(context.Contacts, customer => customer.CustomerIdentifier, contact => contact.ContactId,
(customer, ct) => new CustomerSpecial
{
Customer = customer,
Contact = ct
})
.FirstOrDefault(item => item.Customer.CustomerIdentifier == pIdentifier));
}
}
/// <summary>
/// Get customer by primary key, since result is an anonymous type
/// it can not be used outside of this method.
/// </summary>
/// <param name="pIdentifier"></param>
public void AnonymousCustomer(int pIdentifier)
{
using (var context = new NorthWindEntities())
{
// execute immediately
var results = context.Customers
.Join(context.Contacts, customer => customer.CustomerIdentifier, contact => contact.ContactId,
(customer, ct) => new
{
Customer = customer,
Contact = ct
})
.FirstOrDefault(item => item.Customer.CustomerIdentifier == pIdentifier);
}
}
/// <summary>
/// Save a detached customer
/// </summary>
/// <param name="pCompany">Company with contact id, contact first and last name</param>
/// <returns>true successfully updated false on failure</returns>
public bool UpdateCompanyContactFirstLastName(Company pCompany)
{
var contact = new Contact()
{
ContactId = pCompany.ContactIdentifier,
FirstName = pCompany.FirstName,
LastName = pCompany.LastName
};
using (var context = new NorthWindEntities())
{
context.Entry(contact).State = EntityState.Modified;
return(context.SaveChanges() == 1);
}
}
public ActionResult Orders(string customerId)
{
Order[] orders;
using (var db = new NorthWindEntities())
{
orders = db.Orders
.Include(o => o.Customer)
.Include(o => o.Employee)
.Include(o => o.Shipper)
.Where(c => c.CustomerID == customerId)
.ToArray();
}
ViewBag.SharePointContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
return(View(orders));
}
public ActionResult Orders(string customerId)
{
Order[] orders;
using (var db = new NorthWindEntities())
{
orders = db.Orders
.Include(o => o.Customer)
.Include(o => o.Employee)
.Include(o => o.Shipper)
.Where(c => c.CustomerID == customerId)
.ToArray();
}
ViewBag.SharePointContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
return View(orders);
}
/// <summary>
/// Get customer by primary key, get everything except a few properties defined
/// in partial classes
/// </summary>
/// <param name="pIdentifier"></param>
public void GetCompanyByCustomerIdentifierTemp(int pIdentifier)
{
using (var context = new NorthWindEntities())
{
// deferred execution
var query = from company in context.Customers
join contact in context.Contacts on company.ContactId equals contact.ContactId
join contactType in context.ContactTypes on contact.ContactId equals contactType.ContactTypeIdentifier
where company.CustomerIdentifier == pIdentifier
select company;
var results = query.FirstOrDefault();
Console.WriteLine();
}
}
static void DeleteCustomer(string CustomerID)
{
using (var db = new NorthWindEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == CustomerID).FirstOrDefault();
if (customer != null)
{
db.Customers.Remove(customer);
db.SaveChanges();
}
else
{
throw new ArgumentException("No such customer!");
}
}
}
public void RollbackExample()
{
var ops = new EntityFrameworkUtilities.EntityFrameworkUtilities();
using (var context = new NorthWindEntities())
{
Department dept = context.Departments.Create();
dept.Name = "New Added Department";
dept.Code = "AAA";
context.Departments.Add(dept);
context.SaveChanges();
var deptUpdate = context.Departments.Find(1);
deptUpdate.Code = "BBB";
ops.UndoingChangesDbEntityPropertyLevel(context, dept);
context.SaveChanges();
}
}
static void Task3Metod(int year, string country)
{
using (var db = new NorthWindEntities())
{
var customers = db.Customers.Join(db.Orders, (c => c.CustomerID), (o => o.CustomerID), (c, o) => new
{
Customer = c.CompanyName,
OrderDate = o.OrderDate,
Country = o.ShipCountry
}).Where(o => o.OrderDate.Value.Year == year && o.Country == country);
Console.WriteLine("----------------------------------");
foreach (var customer in customers)
{
Console.WriteLine("Name: {0}, Order date: {1}, Ship country: {2}", customer.Customer, customer.OrderDate, customer.Country);
Console.WriteLine("----------------------------------");
}
}
}
/// <summary>
/// 新增資料
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static MVCResult <string> Add(T model)
{
var result = new MVCResult <string>();
try
{
using (NorthWindEntities entity = new NorthWindEntities())
{
entity.Set <T>().Add(model);
result.SetSuccess(entity.SaveChanges(), SQLType.Insert);
return(result);
}
}
catch (Exception ex)
{
result.SetError(ex.Message);
return(result);
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
if(DropDownList1.SelectedIndex == 0)
{
infoLbl.Text = "";
return;
}
var supplier = DropDownList1.SelectedItem.Text;
int ddlSelected = Int32.Parse(DropDownList1.SelectedValue);
int itemCount = 0;
using (NorthWindEntities myEntities = new NorthWindEntities())
{
Decimal total_value = 0.0M;
var selectedSuppliersProducts =
from product in myEntities.Products
where product.SupplierID == ddlSelected
select product;
//int orderCount = selectedSuppliersProducts.Count();
//infoLbl.Text = string.Format("Customer {0} has {1} orders",
//supplier, orderCount);
foreach (Product product in selectedSuppliersProducts)
{
var order_details =
from order_detail in myEntities.Order_Details
where order_detail.ProductID == product.ProductID
select order_detail;
itemCount += order_details.Count();
foreach (var order_detail in order_details)
{
total_value += Order_Detail_Value(order_detail);
}
}
infoLbl.Text = string.Format("Supplier {0} has {1} order items worth {2}", supplier, itemCount, total_value.ToString("C2"));
}
}
/// <summary>
/// 修改資料
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static MVCResult <string> Update(T model)
{
var result = new MVCResult <string>();
try
{
using (NorthWindEntities entity = new NorthWindEntities())
{
entity.Entry(model).State = System.Data.Entity.EntityState.Modified;
result.SetSuccess(entity.SaveChanges(), SQLType.Update);
return(result);
}
}
catch (Exception ex)
{
result.SetError(ex.Message);
return(result);
}
}
static void Task4Metod(int year, string country)
{
using (var db = new NorthWindEntities())
{
string myQuery = @"SELECT 'Name: ' + c.CompanyName + ' Date: ' + CONVERT(VARCHAR, o.OrderDate, 120) + ' Country: ' + o.ShipCountry
FROM Customers c JOIN Orders o
ON c.CustomerID=o.CustomerID
WHERE YEAR(o.OrderDate)={0} AND o.ShipCountry={1}";
object[] parameters = { year, country };
Console.WriteLine("----------------------------------");
foreach (string rec in db.Database.SqlQuery <string>(myQuery, parameters))
{
Console.WriteLine(rec);
Console.WriteLine("----------------------------------");
}
}
}
public void GetCustomers()
{
using (NorthWindEntities context = new NorthWindEntities())
{
try
{
DataSource = context.Customers.Select(cust => cust)
.Distinct()
.OrderBy(cust => cust.CompanyName)
.ToList();
}
catch (Exception ex)
{
/*
* Of course for a production application we need to consider much better options
*/
Console.WriteLine(ex.Message);
}
}
}
