Esempio n. 1
0
 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));
     }
 }
Esempio n. 2
0
        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>
        }
Esempio n. 3
0
        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>
        }
Esempio n. 4
0
        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>
        }
Esempio n. 5
0
        // 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();
        }
Esempio n. 6
0
        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));
            }
        }
Esempio n. 7
0
        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());
            }
        }
Esempio n. 8
0
        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>
        }
Esempio n. 9
0
        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);
            }
        }
Esempio n. 10
0
        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>
        }
Esempio n. 11
0
        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();
        }
Esempio n. 12
0
        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>
        }
Esempio n. 13
0
        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>
        }
Esempio n. 14
0
        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>
        }
Esempio n. 15
0
        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>
        }
Esempio n. 16
0
        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));
                }
            }
        }
Esempio n. 17
0
        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>
        }
Esempio n. 18
0
        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>
        }
Esempio n. 19
0
        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>
        }
Esempio n. 20
0
        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>
        }
Esempio n. 21
0
        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>
        }
Esempio n. 22
0
        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>
        }
Esempio n. 23
0
        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>
        }
Esempio n. 24
0
        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>
        }
Esempio n. 25
0
        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>
        }
Esempio n. 26
0
        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>
        }
Esempio n. 27
0
        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>
        }
Esempio n. 28
0
        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));
            }
        }
Esempio n. 29
0
 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));
         }
     }
 }
Esempio n. 30
0
        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>
        }