public void loadCustomer()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var cust = from c in dc.Customers
orderby c.CustomerID
select c;
dataGridView1.DataSource = cust;
}
public static Customer GetCustomerById(string cusId)
{
NorthWindDataClassesDataContext northwind =
new NorthWindDataClassesDataContext();
var query = (from c in northwind.Customers
where (c.CustomerID == cusId)
select c).SingleOrDefault<Customer>();
return query;
}
/// <summary>
/// Delete a customer by customer ID
/// </summary>
/// <param name="customerID"></param>
public static void DeleteCustomer(string customerID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable<Customer>()
where c.CustomerID == customerID
select c).SingleOrDefault();
try
{
dc.Customers.DeleteOnSubmit(matchedCustomer);
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Displays the full Category table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table<Category> GetCategoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable<Category>();
}
/// <summary>
/// Example: Return an ordered list
///
/// Converts the returned value to a List
/// of type Employee; the list is ordered
/// by hire date
/// </summary>
/// <returns></returns>
public static List<Employee> GetEmployeesByHireDate()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from emp in dc.GetTable<Employee>()
orderby emp.HireDate ascending
select emp).ToList<Employee>();
}
private void updateCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
NorthWindDataClassesDataContext dbNorthwind
= new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dbNorthwind.GetTable<Customer>()
where c.CustomerID == "ABABA"
select c).SingleOrDefault();
if (matchedCustomer!=null)
{
matchedCustomer.ContactName = "Hoa";
matchedCustomer.ContactTitle = "Hoa.PHAN";
dbNorthwind.SubmitChanges();
MessageBox.Show("Customer successfully changed!");
loadCustomer();
}}
catch (Exception)
{
throw;
}
}
private void insertCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
NorthWindDataClassesDataContext dbNorthwind
= new NorthWindDataClassesDataContext();
Customer cust = new Customer();
cust.CustomerID = "ABABA";
cust.CompanyName = "Enclave";
cust.ContactName = "Hue";
cust.ContactTitle = "Hue.NGUEN";
cust.Address = "127-Huynh Thuc Khang-Hai Chau";
cust.City = "Da Nang";
cust.Region = "Middle";
cust.PostalCode = "70000";
cust.Country = "Viet Nam";
cust.Phone = "0975544516";
cust.Fax = "i don't know :D";
dbNorthwind.Customers.InsertOnSubmit(cust);
dbNorthwind.SubmitChanges();
MessageBox.Show("Customer successfully inserted!");
loadCustomer();
}
catch (Exception)
{
throw;
}
}
private void getProductByCategoryToolStripMenuItem_Click(object sender, EventArgs e)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
List<Product> prd = dc.GetProductsByCategory(1).ToList();
dataGridView1.DataSource = prd;
}
/// <summary>
/// Example: Select to a typed List
/// using a Where Clause
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public static List<Order> GetOrdersById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable<Order>()
where (ord.OrderID == orderId)
select ord).ToList<Order>();
}
/// <summary>
/// Displays the full Order_Detail table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table<Order_Detail> GetOrderDetailsTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable<Order_Detail>();
}
/// <summary>
/// Example: Select to a single returned object
/// using a Where Clause
///
/// Returns the first matching order
/// </summary>
/// <param name="orderId"></param>
/// <returns>The single matching or default value</returns>
public static Order GetOrderById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable<Order>()
where (ord.OrderID == orderId)
select ord).SingleOrDefault<Order>();
}
/// <summary>
/// Example: Query across entity ref with Where class
/// Same as previous function with added where clause
///
/// An entity is a representation in the model of a table
/// in the database, foreign key relationships are maintained
/// as entity references to the related tables in the model.
/// It is possible to query across tables through this
/// relationship in LINQ to SQL
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public static List<OrderandPricingResult> GetOrderAndPricingInformationByOrderId(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
where ords.OrderID == orderId
select new OrderandPricingResult
{
OrderID = ords.OrderID,
Company = ords.Customer.CompanyName,
OrderCountry = ords.Customer.Country,
ProductName = dets.Product.ProductName,
UnitPrice = dets.Product.UnitPrice,
UnitsOrder = dets.Quantity,
ShipperName = ords.Shipper.CompanyName,
SalesFirstName = ords.Employee.FirstName,
SalesLastName = ords.Employee.LastName,
SalesTitle = ords.Employee.Title
}).ToList<OrderandPricingResult>();
}
/// <summary>
/// Displays the full EmployeeTerritory table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table<EmployeeTerritory> GetEmployeeTerritoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable<EmployeeTerritory>();
}
/// <summary>
/// Stored Procedure: Sales By Year
/// </summary>
/// <param name="beginningYear"></param>
/// <param name="endingYear"></param>
/// <returns></returns>
/// Thomas
public static List<Sales_by_YearResult> SalesByYear(DateTime? beginningYear, DateTime? endingYear)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Sales_by_Year(beginningYear, endingYear).ToList<Sales_by_YearResult>();
}
/// <summary>
/// Example: Aggregation
///
/// Returns the total sum of the order
/// selected by order ID by selecting
/// unit price multiplied by quantity
/// ordered and then calling sum for
/// the total
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public static decimal? GetOrderValueByOrderId(int orderID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matches =
(from od in dc.GetTable<Order_Detail>()
where od.OrderID == orderID
select od.Product.UnitPrice * od.Quantity).Sum();
return matches;
}
/// <summary>
/// Stored Procedure: Ten Most Expenisve Products
/// </summary>
/// <returns></returns>
public static List<Ten_Most_Expensive_ProductsResult> TenMostExpensiveProducts()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Ten_Most_Expensive_Products().ToList<Ten_Most_Expensive_ProductsResult>();
}
/// <summary>
/// Displays the full Product table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table<Product> GetProductTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable<Product>();
}
/// <summary>
/// Displays the full Customer table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table<Customer> GetCustomerTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable<Customer>();
}
/// <summary>
/// Displays the full Region table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table<Region> GetRegionTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable<Region>();
}
private void handlelingToolStripMenuItem_Click(object sender, EventArgs e)
{
int? count = 0;
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
List<GetProductNameByCategoryIDResult> prd
= dc.GetProductNameByCategoryID(1, ref count).ToList();
dataGridView1.DataSource = prd;
MessageBox.Show("Records found: "+ count);
}
/// <summary>
/// Example: Using Take to get a limited
/// number of returned values for display and
/// using Skip to sequence to a different
/// starting point within the returned values -
/// can be used to navigate through a large
/// list
/// </summary>
/// <param name="SkipNumber"></param>
/// <returns></returns>
public static List<Order> GetTopFiveOrdersById(int SkipNumber)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable<Order>()
orderby ord.OrderID ascending
select ord).Skip(SkipNumber).Take(5).ToList<Order>();
}
private void insertMultilFieldsToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
NorthWindDataClassesDataContext dbNorthwind
= new NorthWindDataClassesDataContext();
List<Customer> cus = new List<Customer>();
cus.Add(
new Customer
{
CustomerID = "ABBBB",CompanyName = "Enclave",
ContactName = "Linh",ContactTitle = "Linh.DINH",
Address = "127-Huynh Thuc Khang-Hai Chau",
City = "Da Nang",Region = "Middle",
PostalCode = "70000",Country = "Viet Nam",
Phone = "0975544516",Fax = "i don't know :D"
}
);
cus.Add(
new Customer
{
CustomerID = "ABCCC",CompanyName = "Enclave",
ContactName = "Hoa",ContactTitle = "Hoa.PHAN",
Address = "127-Huynh Thuc Khang-Hai Chau",
City = "Da Nang",Region = "Middle",
PostalCode = "70000",Country = "Viet Nam",
Phone = "0975544516",Fax = "i don't know :D"
}
);
dbNorthwind.Customers.InsertAllOnSubmit(cus);
dbNorthwind.SubmitChanges();
MessageBox.Show("Customers successfully inserted!");
loadCustomer();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Insert or Update a Customer Record
///
/// If the customer ID exists, the existing
/// customer record is updated.
///
/// If the customer ID does not exist, the
/// new customer record is inserted into
/// the database
/// </summary>
/// <param name="customerId"></param>
/// <param name="companyName"></param>
/// <param name="contactName"></param>
/// <param name="contactTitle"></param>
/// <param name="address"></param>
/// <param name="city"></param>
/// <param name="region"></param>
/// <param name="postalCode"></param>
/// <param name="country"></param>
/// <param name="phone"></param>
/// <param name="fax"></param>
public static void InsertOrUpdateCustomer(string customerId, string companyName,
string contactName, string contactTitle, string address, string city,
string region, string postalCode, string country, string phone, string fax)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable<Customer>()
where c.CustomerID == customerId
select c).SingleOrDefault();
if(matchedCustomer == null)
{
try
{
// create new customer record since customer ID
// does not exist
Table<Customer> customers = Accessor.GetCustomerTable();
Customer cust = new Customer();
cust.CustomerID = customerId;
cust.CompanyName = companyName;
cust.ContactName = contactName;
cust.ContactTitle = contactTitle;
cust.Address = address;
cust.City = city;
cust.Region = region;
cust.PostalCode = postalCode;
cust.Country = country;
cust.Phone = phone;
cust.Fax = fax;
customers.InsertOnSubmit(cust);
customers.Context.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
else
{
try
{
matchedCustomer.CompanyName = companyName;
matchedCustomer.ContactName = contactName;
matchedCustomer.ContactTitle = contactTitle;
matchedCustomer.Address = address;
matchedCustomer.City = city;
matchedCustomer.Region = region;
matchedCustomer.PostalCode = postalCode;
matchedCustomer.Country = country;
matchedCustomer.Phone = phone;
matchedCustomer.Fax = fax;
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
private void supportingUserDefinedFunctionsToolStripMenuItem_Click(object sender, EventArgs e)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var products = from p in dc.Products
select new
{
NAME = dc.MyUpperFunction(p.ProductName)
};
dataGridView1.DataSource = products;
}
/// <summary>
/// Example: Joins
/// Joining using the join keyword
///
/// The values are set to each of the
/// properties contained in the
/// OrdersAndDetailsResult class
///
/// The value returned is converted
/// to a list of the specified type
/// </summary>
/// <returns></returns>
public static List<OrdersAndDetailsResult> OrdersAndDetails()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.GetTable<Order>()
join dets in dc.GetTable<Order_Detail>()
on ords.OrderID equals dets.OrderID
orderby ords.CustomerID ascending
select new OrdersAndDetailsResult
{
CustomerID = ords.CustomerID,
OrderDate = ords.OrderDate,
RequiredDate = ords.RequiredDate,
ShipAddress = ords.ShipAddress,
ShipCity = ords.ShipCity,
ShipCountry = ords.ShipCountry,
ShipZip = ords.ShipPostalCode,
ShippedTo = ords.ShipName,
OrderID = ords.OrderID,
NameOfProduct = dets.Product.ProductName,
QtyPerUnit = dets.Product.QuantityPerUnit,
Price = dets.Product.UnitPrice,
QtyOrdered = dets.Quantity,
Discount = dets.Discount
}
).ToList <OrdersAndDetailsResult>();
}
private void deleteCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
NorthWindDataClassesDataContext dbNorthwind
= new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dbNorthwind.GetTable<Customer>()
where c.CustomerID == "ABABA"
select c).Single();
dbNorthwind.Customers.DeleteOnSubmit(matchedCustomer);
dbNorthwind.SubmitChanges();
MessageBox.Show("Customer successfully deleted!");
loadCustomer();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
// This region contains examples of some
// of the sorts of queries that can be
// executed using LINQ to SQL
/// <summary>
/// Example: Where Clause
/// Returns an employee where the
/// employee ID matches the value
/// passed in as empID
/// </summary>
/// <param name="empId"></param>
/// <returns>The single matching or default value</returns>
public static Employee GetEmployeeById(int empId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from e in dc.GetTable<Employee>()
where (e.EmployeeID == empId)
select e).SingleOrDefault<Employee>();
}
