public JsonResult YearlySalesData()
{
using (Northwnd db = new Northwnd())
{
var yearlySalesInfo =
from o in db.Orders
join od in db.Order_Details on o.OrderID equals od.OrderID
group new { o, od } by new { o.OrderDate.Value.Year } into f
orderby f.Key.Year
select new
{
Year = f.Key.Year,
TotalSales = f.Sum(s => s.od.UnitPrice * s.od.Quantity * (1 - s.od.Discount))
};
var yearSaleInfo = yearlySalesInfo.ToList();
return(Json(yearSaleInfo, JsonRequestBehavior.AllowGet));
}
}
void method2()
{
// <Snippet2>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
db.ObjectTrackingEnabled = false;
IOrderedQueryable <Employee> hireQuery =
from emp in db.Employees
orderby emp.HireDate
select emp;
foreach (Employee empObj in hireQuery)
{
Console.WriteLine("EmpID = {0}, Date Hired = {1}",
empObj.EmployeeID, empObj.HireDate);
}
// </Snippet2>
}
static void Main(string[] args)
{
// <Snippet6>
Northwnd db = new Northwnd(@"c\northwnd.mdf");
Customer cust = db.Customers.Where(c => c.CustomerID ==
"ALFKI").Single();
DataContractSerializer dcs =
new DataContractSerializer(typeof(Customer));
StringBuilder sb = new StringBuilder();
XmlWriter writer = XmlWriter.Create(sb);
dcs.WriteObject(writer, cust);
writer.Close();
string xml = sb.ToString();
// </Snippet6>
}
static void Main(string[] args)
{
Northwnd db = new Northwnd(@"");
// <Snippet1>
// Query 1.
var q1 =
from ord in db.Orders
where ord.EmployeeID == 9
select ord;
foreach (var ordObj in q1)
{
Console.WriteLine("{0}, {1}", ordObj.OrderID,
ordObj.ShipVia.Value);
}
// </Snippet1>
}
// GET: Product/Product/1
public ActionResult Product(int?id)
{
// if there is no "category" id, return Http Bad Request
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
using (Northwnd db = new Northwnd())
{ // save the selected category name to the ViewBag
ViewBag.Filter = db.Categories.Find(id).CategoryName;
// retrieve list of products
return(View(db.Products.Where(p => p.CategoryID == id && p.Discontinued == false).OrderBy(p => p.ProductName).ToList()));
}
//return View();
}
public ActionResult Account([Bind(Include = "CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,Email")] CustomerEdit UpdatedCustomer)
{
// For future version, make sure that an authenticated user is a customer
if (Request.Cookies["role"].Value != "customer")
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
using (Northwnd db = new Northwnd())
{
if (ModelState.IsValid)
{
Customer customer = db.Customers.Find(UserAccount.GetUserID());
//customer.CompanyName = UpdatedCustomer.CompanyName;
// if the customer is changing their CompanyName
if (customer.CompanyName.ToLower() != UpdatedCustomer.CompanyName.ToLower())
{
// Ensure that the CompanyName is unique
if (db.Customers.Any(c => c.CompanyName == UpdatedCustomer.CompanyName))
{
// duplicate CompanyName
ModelState.AddModelError("CompanyName", "Duplicate Company Name");
return(View(UpdatedCustomer));
}
customer.CompanyName = UpdatedCustomer.CompanyName;
}
customer.Address = UpdatedCustomer.Address;
customer.City = UpdatedCustomer.City;
customer.ContactName = UpdatedCustomer.ContactName;
customer.ContactTitle = UpdatedCustomer.ContactTitle;
customer.Country = UpdatedCustomer.Country;
customer.Email = UpdatedCustomer.Email;
customer.Fax = UpdatedCustomer.Fax;
customer.Phone = UpdatedCustomer.Phone;
customer.PostalCode = UpdatedCustomer.PostalCode;
customer.Region = UpdatedCustomer.Region;
db.SaveChanges();
return(RedirectToAction(actionName: "Index", controllerName: "Home"));
}
//validation error
return(View(UpdatedCustomer));
}
}
public ActionResult SignIn([Bind(Include = "CustomerId,Password")] CustomerSignIn customerSignIn, string ReturnUrl)
{
using (Northwnd db = new Northwnd())
{
if (ModelState.IsValid)
{
// find customer by CustomerId
Customer customer = db.Customers.Find(customerSignIn.CustomerId);
// hash & salt the posted password
string str = UserAccount.HashSHA1(customerSignIn.Password + customer.UserGuid);
// Compared posted Password to customer password
if (str == customer.Password)
{
// Passwords match
// authenticate user (this stores the CustomerID in an encrypted cookie)
// normally, you would require HTTPS
FormsAuthentication.SetAuthCookie(customer.CustomerID.ToString(), false);
// send a cookie to the client to indicate that this is a customer
HttpCookie myCookie = new HttpCookie("role");
myCookie.Value = "customer";
Response.Cookies.Add(myCookie);
// if there is a return url, redirect to the url
if (ReturnUrl != null)
{
return(Redirect(ReturnUrl));
}
// Redirect to Home page
return(RedirectToAction(actionName: "Index", controllerName: "Home"));
}
else
{
// Passwords do not match
// Passwords do not match
ModelState.AddModelError("Password", "Incorrect password");
}
}
var companies = db.Customers.OrderBy(x => x.CompanyName).ToList();
ViewBag.CustomerID = new SelectList(companies, "CustomerID", "CompanyName");
// create drop-down list box for company name
//ViewBag.CustomerID = new SelectList(db.Customers.OrderBy(c => c.CompanyName), "CustomerID", "CompanyName").ToList();
return(View());
}
}
static void Main(string[] args)
{
Northwnd db = new Northwnd(@"northwnd.mdf");
// <Snippet1>
var query =
from cust in db.Customers
group cust.ContactName by new { City = cust.City, Region = cust.Region };
foreach (var grp in query)
{
Console.WriteLine("\nLocation Key: {0}", grp.Key);
foreach (var listing in grp)
{
Console.WriteLine("\t{0}", listing);
}
}
// </Snippet1>
}
void method1()
{
// <Snippet1>
Northwnd db = new Northwnd(@"northwnd.mdf");
IQueryable <Order> notificationQuery =
from ord in db.Orders
where ord.ShipVia == 3
select ord;
foreach (Order ordObj in notificationQuery)
{
if (ordObj.Freight > 200)
{
SendCustomerNotification(ordObj.Customer);
}
ProcessOrder(ordObj);
}
}
static void Main(string[] args)
{
// <Snippet1>
// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");
// or, if you are not using SQL Server Express
// Northwnd nw = new Northwnd("Database=Northwind;Server=server_name;Integrated Security=SSPI");
var companyNameQuery =
from cust in nw.Customers
where cust.City == "London"
select cust.CompanyName;
foreach (var customer in companyNameQuery)
{
Console.WriteLine(customer);
}
// </Snippet1>
}
void method1()
{
Northwnd db = new Northwnd(@"c:\northwnd.mdf)");
// <Snippet2>
db.Log = Console.Out;
var custQuery =
from cust in db.Customers
where cust.City == "London"
select cust;
foreach (var custObj in custQuery)
{
Console.WriteLine(custObj.ContactName);
}
// </Snippet2>
Console.ReadLine();
}
void method50()
{
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
// <Snippet50>
var infoQuery =
from emp1 in db.Employees
from emp2 in emp1.Employees
where emp1.City == emp2.City
select new
{
FirstName1 = emp1.FirstName,
LastName1 = emp1.LastName,
FirstName2 = emp2.FirstName,
LastName2 = emp2.LastName,
emp1.City
};
// </Snippet50>
}
public void method2()
{
// <Snippet2>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith <Customer>(c => c.Orders);
db.LoadOptions = dlo;
var londonCustomers =
from cust in db.Customers
where cust.City == "London"
select cust;
foreach (var custObj in londonCustomers)
{
Console.WriteLine(custObj.CustomerID);
}
// </Snippet2>
}
static void Main(string[] args)
{
// <Snippet1>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
// Make changes here.
try
{
db.SubmitChanges();
}
catch (ChangeConflictException e)
{
Console.WriteLine(e.Message);
// Make some adjustments.
// ...
// Try again.
db.SubmitChanges();
}
// </Snippet1>
}
void method3()
{
// <Snippet3>
Northwnd nw = new Northwnd(@"northwnd.mdf");
var cityNameQuery =
from cust in nw.Customers
where cust.City.Contains("London")
select cust;
foreach (var customer in cityNameQuery)
{
if (customer.City == "London")
{
customer.City = "London - Metro";
}
}
nw.SubmitChanges();
// </Snippet3>
}
public JsonResult FilterChart(string CategoryName)
{
using (Northwnd db = new Northwnd())
{
if (CategoryName == null)
{
var categoryInfo =
from p in db.Products
join c in db.Categories on p.CategoryID equals c.CategoryID
join od in db.Order_Details on p.ProductID equals od.ProductID
group new { p, c, od } by new { c.CategoryName } into f
select new
{
Name = f.Key.CategoryName,
Sum = f.Sum(s =>
s.od.Quantity * s.od.UnitPrice * (1 - s.od.Discount))
}; //Sum = (od.UnitPrice * od.Quantity * (1 - od.Discount)) };
var catInfo = categoryInfo.ToList();
return(Json(catInfo, JsonRequestBehavior.AllowGet));
}
else
{
var productInfo =
from p in db.Products
join c in db.Categories on p.CategoryID equals c.CategoryID
join od in db.Order_Details on p.ProductID equals od.ProductID
where c.CategoryName == CategoryName
group new { p, c, od } by new { p.ProductName } into f
select new
{
Name = f.Key.ProductName,
Sum = f.Sum(s =>
s.od.Quantity * s.od.UnitPrice * (1 - s.od.Discount))
};
var prodInfo = productInfo.ToList();
return(Json(prodInfo, JsonRequestBehavior.AllowGet));
}
}
}
static void Main(string[] args)
{
// <Snippet2>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
db.DeferredLoadingEnabled = false;
IQueryable <Customer> custQuery =
from cust in db.Customers
where cust.City == "London"
select cust;
foreach (Customer custObj in custQuery)
{
foreach (Order ordObj in custObj.Orders)
{
ProcessCustomerOrder(ordObj);
}
}
// </Snippet2>
}
void method30()
{
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
// <Snippet30>
var priceQuery =
from prod in db.Products
group prod by prod.CategoryID into grouping
select new
{
grouping.Key,
TotalPrice = grouping.Sum(p => p.UnitPrice)
};
foreach (var grp in priceQuery)
{
Console.WriteLine("Category = {0}, Total price = {1}",
grp.Key, grp.TotalPrice);
}
// </Snippet30>
}
void method7seq()
{
// <Snippet7>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IMultipleResults sprocResults =
db.MultipleResultTypesSequentially();
// First read products.
foreach (Product prod in sprocResults.GetResult <Product>())
{
Console.WriteLine(prod.ProductID);
}
// Next read customers.
foreach (Customer cust in sprocResults.GetResult <Customer>())
{
Console.WriteLine(cust.CustomerID);
}
// </Snippet7>
}
void method2()
{
// <Snippet2>
// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");
Customer cust = new Customer();
cust.CompanyName = "SomeCompany";
cust.City = "London";
cust.CustomerID = "98128";
cust.PostalCode = "55555";
cust.Phone = "555-555-5555";
nw.Customers.InsertOnSubmit(cust);
// At this point, the new Customer object is added in the object model.
// In LINQ to SQL, the change is not sent to the database until
// SubmitChanges is called.
nw.SubmitChanges();
// </Snippet2>
}
void method39()
{
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
// <Snippet39>
IQueryable <String> custQuery =
(from cust in db.Customers
select cust.Phone)
.Concat
(from cust in db.Customers
select cust.Fax)
.Concat
(from emp in db.Employees
select emp.HomePhone)
;
foreach (var custData in custQuery)
{
Console.WriteLine(custData);
}
// </Snippet39>
}
static void Main(string[] args)
{
// <Snippet1>
using (Northwnd db = new Northwnd(@"c:\northwnd.mdf"))
{
// Get original Customer from deserialization.
var q1 = db.Orders.First();
string serializedQ = SerializeHelper.Serialize(q1);
var q2 = SerializeHelper.Deserialize(serializedQ, q1);
// Track this object for an update (not insert).
db.Orders.Attach(q2, false);
// Replay the changes.
q2.ShipRegion = "King";
q2.ShipAddress = "1 Microsoft Way";
// DataContext knows how to update the order.
db.SubmitChanges();
}
// </Snippet1>
}
static void Main(string[] args)
{
// <Snippet1>
// Add 'using System.Reflection' for this section.
Northwnd db = new Northwnd("...");
try
{
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException e)
{
Console.WriteLine("Optimistic concurrency error.");
Console.WriteLine(e.Message);
foreach (ObjectChangeConflict occ in db.ChangeConflicts)
{
MetaTable metatable = db.Mapping.GetTable(occ.Object.GetType());
Customer entityInConflict = (Customer)occ.Object;
Console.WriteLine("Table name: {0}", metatable.TableName);
Console.Write("Customer ID: ");
Console.WriteLine(entityInConflict.CustomerID);
foreach (MemberChangeConflict mcc in occ.MemberConflicts)
{
object currVal = mcc.CurrentValue;
object origVal = mcc.OriginalValue;
object databaseVal = mcc.DatabaseValue;
MemberInfo mi = mcc.Member;
Console.WriteLine("Member: {0}", mi.Name);
Console.WriteLine("current value: {0}", currVal);
Console.WriteLine("original value: {0}", origVal);
Console.WriteLine("database value: {0}", databaseVal);
Console.ReadLine();
}
}
}
// </Snippet1>
}
static void Main(string[] args)
{
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
// <Snippet1>
DataGrid dataGrid1 = new DataGrid();
DataGrid dataGrid2 = new DataGrid();
DataGrid dataGrid3 = new DataGrid();
var custQuery =
from cust in db.Customers
select cust;
dataGrid1.DataSource = custQuery;
dataGrid2.DataSource = custQuery;
dataGrid2.DataMember = "Orders";
BindingSource bs = new BindingSource();
bs.DataSource = custQuery;
dataGrid3.DataSource = bs;
// </Snippet1>
}
void method3()
{
// <Snippet3>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
using (TransactionScope ts = new TransactionScope())
{
try
{
Product prod1 = db.Products.First(p => p.ProductID == 4);
Product prod2 = db.Products.First(p => p.ProductID == 5);
prod1.UnitsInStock -= 3;
prod2.UnitsInStock -= 5;
db.SubmitChanges();
ts.Complete();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
// </Snippet3>
}
void method3()
{
Northwnd db = new Northwnd(@"c:\linqtest6\northwnd.mdf");
// <Snippet3>
// Create the new Customer object.
Customer newCust = new Customer();
newCust.CompanyName = "AdventureWorks Cafe";
newCust.CustomerID = "ADVCA";
// Add the customer to the Customers table.
db.Customers.InsertOnSubmit(newCust);
Console.WriteLine("\nCustomers matching CA before insert");
foreach (var c in db.Customers.Where(cust => cust.CustomerID.Contains("CA")))
{
Console.WriteLine("{0}, {1}, {2}",
c.CustomerID, c.CompanyName, c.Orders.Count);
}
// </Snippet3>
}
static void Main(string[] args)
{
Northwnd db = new Northwnd(@"");
// <Snippet1>
db.Connection.Close();
// </Snippet1>
// <Snippet2>
using (TransactionScope ts = new TransactionScope())
{
db.SubmitChanges();
ts.Complete();
}
// </Snippet2>
// <Snippet3>
IEnumerable <Customer> results = db.ExecuteQuery <Customer>(
@"select c1.custid as CustomerID, c2.custName as ContactName
from customer1 as c1, customer2 as c2
where c1.custid = c2.custid"
);
// </Snippet3>
}
public ActionResult Account()
{
// For future version, make sure that an authenticated user is a customer
if (Request.Cookies["role"].Value != "customer")
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
//ViewBag.CustomerID = UserAccount.GetUserID();
using (Northwnd db = new Northwnd())
{
// find customer using CustomerID (stored in authentication ticket)
Customer customer = db.Customers.Find(UserAccount.GetUserID());
// display original values in textboxes when customer is editing data
/*CustomerEdit EditCustomer = new CustomerEdit()
* {
* CompanyName = customer.CompanyName,
* ContactName = customer.ContactName,
* ContactTitle = customer.ContactTitle,
* Address = customer.Address,
* City = customer.City,
* Region = customer.Region,
* PostalCode = customer.PostalCode,
* Country = customer.Country,
* Phone = customer.Phone,
* Fax = customer.Fax,
* Email = customer.Email
* };*/
//using AutoLoader
var EditCustomer = Mapper.Map <CustomerEdit>(customer);
return(View(EditCustomer));
}
}
public JsonResult YearlySalesByCountryData(int?year)
{
using (Northwnd db = new Northwnd())
{
if (year == null)
{
var yearlySalesInfo =
from o in db.Orders
join od in db.Order_Details on o.OrderID equals od.OrderID
group new { o, od } by new { o.ShipCountry } into f
orderby f.Key
select new
{
Country = f.Key.ShipCountry,
TotalSales = f.Sum(s => s.od.UnitPrice * s.od.Quantity * (1 - s.od.Discount))
};
var yearSaleInfo = yearlySalesInfo.ToList();
return(Json(yearSaleInfo, JsonRequestBehavior.AllowGet));
}
else
{
var yearlySalesInfo =
from o in db.Orders
join od in db.Order_Details on o.OrderID equals od.OrderID
where o.OrderDate.Value.Year == year
group new { o, od } by new { o.ShipCountry } into f
select new
{
Country = f.Key.ShipCountry,
TotalSales = f.Sum(s => s.od.UnitPrice * s.od.Quantity * (1 - s.od.Discount))
};
var yearSaleInfo = yearlySalesInfo.ToList();
return(Json(yearSaleInfo, JsonRequestBehavior.AllowGet));
}
}
}
static void Main(string[] args)
{
// <Snippet1>
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
db.Log = Console.Out;
// Specify order to be removed from database
int reqOrder = 10250;
// Fetch OrderDetails for requested order.
var ordDetailQuery =
from odq in db.OrderDetails
where odq.OrderID == reqOrder
select odq;
foreach (var selectedDetail in ordDetailQuery)
{
Console.WriteLine(selectedDetail.Product.ProductID);
db.OrderDetails.DeleteOnSubmit(selectedDetail);
}
// Display progress.
Console.WriteLine("detail section finished.");
Console.ReadLine();
// Determine from Detail collection whether parent exists.
if (ordDetailQuery.Any())
{
Console.WriteLine("The parent is presesnt in the Orders collection.");
// Fetch Order.
try
{
var ordFetch =
(from ofetch in db.Orders
where ofetch.OrderID == reqOrder
select ofetch).First();
db.Orders.DeleteOnSubmit(ordFetch);
Console.WriteLine("{0} OrderID is marked for deletion.", ordFetch.OrderID);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
else
{
Console.WriteLine("There was no parent in the Orders collection.");
}
// Display progress.
Console.WriteLine("Order section finished.");
Console.ReadLine();
try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
// Display progress.
Console.WriteLine("Submit finished.");
Console.ReadLine();
// </Snippet1>
}
