DevExpress看明白代码--Northwind完整案例解读

#line 39 //DEMO_REMOVE
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.Linq;
using System.Linq;
using System.Web;
namespace DevExpress.Web.Demos.Mvc {
    public static class NorthwindDataProvider {    //数据供应
        const string NorthwindDataContextKey = "DXNorthwindDataContext";   //数据库contextKey
        public static NorthwindContext DB {   //实例化context方法
            get {
                if(HttpContext.Current.Items[NorthwindDataContextKey] == null)
                    HttpContext.Current.Items[NorthwindDataContextKey] = new NorthwindContext();
                return (NorthwindContext)HttpContext.Current.Items[NorthwindDataContextKey];
            }

        }

//思路:定义一个DB的使用方法,定义一个类来配合,在测试

//item--获得一个键/值集合，可用于在system.web之间组织和共享数据。IHttpModule接口和system.web。HTTP请求期间的IHttpHandler接口。

//https://www.cnblogs.com/fish-li/archive/2013/04/06/3002940.html

        static double CalculateAveragePrice(int categoryID) 

{   //计算平均价格,返回double

return (double)(from product in DB.Products where product.CategoryID == categoryID 

select product).Average(s => s.UnitPrice);    //根据分类ID查询数据,并取单价的平均值

        }

        static double CalculateMinPrice(int categoryID) {  //计算最低价格

return (double)(from product in DB.Products where product.CategoryID == categoryID 

select product).Min(s => s.UnitPrice);

        }
        static double CalculateMaxPrice(int categoryID) {        //计算最高价格

            return (double)(from product in DB.Products where product.CategoryID == categoryID 

select product).Max(s => s.UnitPrice);

        }
        public static IEnumerable GetCategories() {                //获得分类
            var query = from category in DB.Categories
                        select new {                    //生成一个匿名可枚举数据集
                            CategoryID = category.CategoryID,
                            CategoryName = category.CategoryName,
                            Description = category.Description,
                            Picture = category.Picture
                        };
            return query.ToList();    //转换为集合返回给前台
        }
        public static Category GetCategoryByID(int categoryID) {    //根据ID查询出一条分类信息
            return (from category in DB.Categories where category.CategoryID == categoryID select category).SingleOrDefault<Category>();
        }
        public static string GetCategoryNameById(int id) {
            Category category = GetCategoryByID(id);
            return category != null ? category.CategoryName : null;  //根据ID查询分类名称,三元表达式判断空值
        }
        public static IEnumerable GetCategoriesNames() {
            return from category in DB.Categories select category.CategoryName;  //查询所有的分类名称
        }
        public static IEnumerable GetCategoriesAverage() {
     var query = DB.Categories.ToList().Select(category => new { //查询分类的平均价格,组成两个字段的列表返回
                category.CategoryName,
                AvgPrice = CalculateAveragePrice(category.CategoryID)  //这里调用了前面刚定义的方法,学习了
            });
            return query.ToList();
        }
        public static IEnumerable GetCategoriesMin() {   //最小值 同上
            var query = DB.Categories.ToList().Select(category => new {
                category.CategoryName,
                AvgPrice = CalculateMinPrice(category.CategoryID)
            });
            return query.ToList();
        }
        public static IEnumerable GetCategoriesMax() {        //最大值  同上
            var query = DB.Categories.ToList().Select(category => new {
                category.CategoryName,
                AvgPrice = CalculateMaxPrice(category.CategoryID)
            });
            return query.ToList();
        }
        public static IEnumerable GetSuppliers() {        //获取供应商列表
            return DB.Suppliers.ToList();
        }
        public static IEnumerable GetCustomers() {        //获取客户所有数据
            return DB.Customers.ToList();
        }
        public static Customer GetCustomerByID(string customerID) {   //根据id获取Customer数据的第一行
            return (from customer in DB.Customers
                    where customer.CustomerID == customerID
                    select new Customer {            //注意新的数据放在这个类里面
                        CompanyName = customer.CompanyName,
                        ContactName = customer.ContactName,
                        ContactTitle = customer.ContactTitle,
                        Address = customer.Address,
                        City = customer.City,
                        Country = customer.Country,
                        Fax = customer.Fax,
                        Phone = customer.Phone,
                    }).SingleOrDefault();                //选择唯一值
        }
        public static string GetFirstCustomerID() {                //获取第一个客户ID
            return (from customer in NorthwindDataProvider.DB.Customers
                    select customer.CustomerID).First<string>();    //此处的id是string的
        }
        public static IEnumerable GetProducts() {                //获取产品列表
            return DB.Products.ToList();
        }
        public static IEnumerable GetProducts(string categoryName) {    //根据分类名称获取产品列表
     var query = from product in DB.Products
            join category in DB.Categories on product.CategoryID equals category.CategoryID  //合并两张表
            where category.CategoryName == categoryName    //合并表中的种类名等于参数
            select product;                    //选择产品
            return query.ToList();
        }
        public static IEnumerable GetProducts(int? categoryID) {    //根据种类id获取产品列表
            return DB.Products.Where(p => p.CategoryID == categoryID).ToList();
        }
        public static IEnumerable GetEmployees() {     //获取员工总表
            return DB.Employees.ToList();
        }
        public static IEnumerable GetEmployeesOrders() {        //获取订单列表
            var query = from orders in DB.Orders
           join employee in DB.Employees on orders.EmployeeID equals employee.EmployeeID   //合并表
           select new EmployeeOrder {
                            OrderDate = orders.OrderDate,
                            Freight = orders.Freight,
                            LastName = employee.LastName,
                            FirstName = employee.FirstName,
                            Photo = employee.Photo,
                            Id = employee.EmployeeID
                        };
            return query.ToList();
        }
        public static Binary GetEmployeePhoto(string lastName) {    //根据名查询图像,返回二进制图像给前台
            return (from employee in DB.Employees
                    where employee.LastName == lastName
                    select employee.Photo).SingleOrDefault();   //唯一值
        }
        public static Binary GetEmployeePhoto(int employeeId) {    //根据员工id获取二进制图像
            return (from employee in DB.Employees
                    where employee.EmployeeID == employeeId
                    select employee.Photo).SingleOrDefault();
        }
        public static string GetEmployeeNotes(int employeeId) {        //根据员工Id获取注释
            return (from employee in DB.Employees
                    where employee.EmployeeID == employeeId
                    select employee.Notes).Single();
        }
        public static IList<EditableEmployee> GetEditableEmployees() {   
            const string SessionKey = "DXDemoEmployees";

IList<EditableEmployee> employeesList = HttpContext.Current.Session[SessionKey] as IList<EditableEmployee>;

//从session读取可编辑的员工列表,这行代码最主要的功能是减少查询数据库的次数

            if(employeesList == null) {  //如果里面没有内容则赋值
HttpContext.Current.Session[SessionKey] = employeesList = (from employee in DB.Employees                                            select new EditableEmployee {
              EmployeeID= employee.EmployeeID,
              FirstName =employee.FirstName,
              LastName =employee.LastName,
              Title =employee.Title,
              HomePhone =employee.HomePhone,
              BirthDate =employee.BirthDate,
              HireDate =employee.HireDate,
               Notes =employee.Notes,
               ReportsTo =employee.ReportsTo,
              Photo =employee.Photo
            }).ToList();
            }
            return employeesList;   //保证了里面有值
        }
        public static EditableEmployee GetEditableEmployeeByID(int employeeID) {

            return GetEditableEmployees().Where(e => e.EmployeeID == employeeID).SingleOrDefault();

            //调用方法,然后根据员工ID筛选出一条记录,使用Session避免多次查询

        }
        public static int GetNewEditableEmployeeID() {   //获取新的可编辑员工表的最后一个id
            IEnumerable<EditableEmployee> editableEmployees = GetEditableEmployees();  //获取表

            return (editableEmployees.Count() > 0) ? editableEmployees.Last().EmployeeID + 1 : 0;

                                    //如果这个集合里面有数据,查询出最后一条数据并取id+1就是新的id,否则返回0

        }

        public static void UpdateEditableEmployee(EditableEmployee employee) {  

//更新自定类的列表,参数是前台传过来的赋值后的自定义类

var editableEmployee = GetEditableEmployees().Where(e => e.EmployeeID == employee.EmployeeID).SingleOrDefault();

//查询出员工表根据id筛选出一条记录

            if(editableEmployee == null) 

 //如果为空,直接返回,否则把参数中传过来的值赋给定义的变量editableEmployee,此处有疑问,没有看到写入数据库的方法

               return;
            editableEmployee.FirstName = employee.FirstName;
            editableEmployee.LastName = employee.LastName;
            editableEmployee.Title = employee.Title;
            editableEmployee.BirthDate = employee.BirthDate;
            editableEmployee.HireDate = employee.HireDate;
            editableEmployee.Notes = employee.Notes;
            editableEmployee.ReportsTo = employee.ReportsTo;
            editableEmployee.HomePhone = employee.HomePhone;
            editableEmployee.Photo = employee.Photo;

        }

        public static void InsertEditableEmployee(EditableEmployee employee) {   //插入一条员工数据
            EditableEmployee editEmployee = new EditableEmployee();
            editEmployee.EmployeeID = GetNewEditableEmployeeID();
            editEmployee.FirstName = employee.FirstName;
            editEmployee.LastName = employee.LastName;
            editEmployee.BirthDate = employee.BirthDate;
            editEmployee.HireDate = employee.HireDate;
            editEmployee.Title = employee.Title;
            editEmployee.Notes = employee.Notes;
            editEmployee.ReportsTo = employee.ReportsTo;
            editEmployee.Photo = employee.Photo;
            GetEditableEmployees().Add(editEmployee);        //此处应该是定义了插入的方法

        }

        public static IList<EditableCustomer> GetEditableCustomers() {   //此处用法与员工表一样
            const string SessionKey = "DXDemoCustomers";
IList<EditableCustomer> customersList = HttpContext.Current.Session[SessionKey] as IList<EditableCustomer>;
            if(customersList == null) {
HttpContext.Current.Session[SessionKey] = customersList = (from customer in DB.Customers
select new EditableCustomer {
               CustomerID= customer.CustomerID,
               CompanyName= customer.CompanyName,
              ContactName= customer.ContactName,
               City =customer.City,
               Region =customer.Region,
               Country =customer.Country                                                            }
                ).ToList();
            }
            return customersList;

        }

        public static EditableCustomer GetEditableCustomerByID(string customerID) {    //同员工表
            return GetEditableCustomers().Where(c => c.CustomerID.Equals(customerID)).SingleOrDefault();
        }
        public static void UpdateCustomer(EditableCustomer customer) {  //同员工表
            EditableCustomer editableCustomer = GetEditableCustomerByID(customer.CustomerID);
            if(editableCustomer == null)
                return;
            editableCustomer.CompanyName = customer.CompanyName;
            editableCustomer.ContactName = customer.ContactName;
            editableCustomer.City = customer.City;
            editableCustomer.Country = customer.Country;
            editableCustomer.Region = customer.Region;

        }

        public static IEnumerable GetOrders() {        //订单表
            return DB.Orders.ToList();
        }
        public static IEnumerable GetInvoices() {   //获取发票信息

            var query = from invoice in DB.Invoices    

        //invoices是放在视图里面的,在视图中多表连接查询得到一个结果集

            join customer in DB.Customers on invoice.CustomerID equals customer.CustomerID

        //连接顾客表与发票信息

            select new Mvc.Invoice() {   //赋值给自定义类
            CompanyName = customer.CompanyName,        //公司名
            City = invoice.City,                //城市
            Region = invoice.Region,            //地区
            Country = invoice.Country,            //国家
            UnitPrice = invoice.UnitPrice,            //单价
            Quantity = invoice.Quantity,            //总价
            Discount = invoice.Discount            //折扣
                        };
            return query.ToList();
        }
        public static IEnumerable GetFullInvoices() {            //获取完整的发票信息
            var query = from invoice in DB.Invoices
            join customer in DB.Customers on invoice.CustomerID equals customer.CustomerID

            join order in DB.Orders on invoice.OrderID equals order.OrderID

            //连接三张表

            select new {
        SalesPerson = order.Employee.FirstName + " " + order.Employee.LastName,   //销售员
                            customer.CompanyName,            //公司名
                            invoice.Address,
                            invoice.City,
                            invoice.Country,
                            invoice.CustomerName,
                            invoice.Discount,
                            invoice.ExtendedPrice,        //总价
                            invoice.Freight,          //货运
                            invoice.OrderDate,        //定单日期
                            invoice.ProductName,        //产品名称
                            invoice.Quantity,        //定单数量
                            invoice.Region,
                            invoice.UnitPrice,
                        };
            return query.ToList();
        }
        public static IEnumerable GetInvoices(string customerID) {   //根据顾客id获取发票
            return DB.Invoices.Where(i => i.CustomerID == customerID).ToList();
        }
        public static IList<EditableProduct> GetEditableProducts() {    //允许编辑产品,这里是查出数据给前台

            IList<EditableProduct> products = (IList<EditableProduct>)HttpContext.Current.Session["Products"];

            //如果Session有值就减少查询,没有就查询后赋值

            if(products == null) {
                products = (from product in DB.Products
                            select new EditableProduct {
                                ProductID = product.ProductID,
                                ProductName = product.ProductName,
                                CategoryID = product.CategoryID,
                                SupplierID = product.SupplierID,
                                QuantityPerUnit = product.QuantityPerUnit,
                                UnitPrice = product.UnitPrice,
                                UnitsInStock = product.UnitsInStock,
                                Discontinued = product.Discontinued
                            }).ToList();
                HttpContext.Current.Session["Products"] = products;
            }
            return products;
        }
        public static EditableProduct GetEditableProduct(int productID) {

return (from product in GetEditableProducts() where product.ProductID == productID select product).FirstOrDefault();}

        //根据id查出一条数据

        public static int GetNewEditableProductID() {   //获取最新的产品id
            IEnumerable<EditableProduct> editableProducts = GetEditableProducts();
            return (editableProducts.Count() > 0) ? editableProducts.Last().ProductID + 1 : 0;
        }
        public static void DeleteProduct(int productID) {  //根据产品id删除产品信息
            EditableProduct product = GetEditableProduct(productID);
            if(product != null)
            GetEditableProducts().Remove(product);   //这个地方要根据访问数据库的方式更改
        }
        public static void InsertProduct(EditableProduct product) {
            EditableProduct editProduct = new EditableProduct();
            editProduct.ProductID = GetNewEditableProductID();
            editProduct.ProductName = product.ProductName;
            editProduct.CategoryID = product.CategoryID;
            editProduct.SupplierID = product.SupplierID;
            editProduct.QuantityPerUnit = product.QuantityPerUnit;
            editProduct.UnitPrice = product.UnitPrice;
            editProduct.UnitsInStock = product.UnitsInStock;
            editProduct.Discontinued = product.Discontinued;
            GetEditableProducts().Add(editProduct);            //与员工表的处理方式相同
        }
        public static void UpdateProduct(EditableProduct product) {
            EditableProduct editProduct = GetEditableProduct(product.ProductID);
            if(editProduct != null) {
                editProduct.ProductName = product.ProductName;
                editProduct.CategoryID = product.CategoryID;
                editProduct.SupplierID = product.SupplierID;
                editProduct.QuantityPerUnit = product.QuantityPerUnit;
                editProduct.UnitPrice = product.UnitPrice;
                editProduct.UnitsInStock = product.UnitsInStock;
                editProduct.Discontinued = product.Discontinued;
            }         //与员工表的处理方式相同
        }
        public static IEnumerable GetEmployeesList() {   //获取员工的姓名称id号,应该是给下拉框准备的
            return DB.Employees.ToList().Select(e => new {
                ID = e.EmployeeID,
                Name = e.LastName + " " + e.FirstName
            });
        }

        public static int GetFirstEmployeeID() {     

 //获取员工表的第一条id,不明白要做什么,这个值虽然是一个数字,但记录本身是无序的,如果没有排序,有可能会有错误

            return DB.Employees.First().EmployeeID;
        }
        public static Employee GetEmployee(int employeeId) {     //根据ID选择一条记录
            return DB.Employees.Single(e => employeeId == e.EmployeeID);        //
        }

        public static IEnumerable GetOrders(int employeeID) {  

 //连接三张表生成一个匿名类转为list给前台,这种方式只能用来展示数据

            var query = from order in DB.Orders
            where order.EmployeeID == employeeID
            join order_detail in DB.Order_Details on order.OrderID equals order_detail.OrderID
            join customer in DB.Customers on order.CustomerID equals customer.CustomerID
            select new {
                            order.OrderID,
                            order.OrderDate,
                            order.ShipName,
                            order.ShipCountry,
                            order.ShipCity,
                            order.ShipAddress,
                            order.ShippedDate,
                            order_detail.Quantity,
                            order_detail.UnitPrice,
                            customer.CustomerID,
                            customer.ContactName,
                            customer.CompanyName,
                            customer.City,
                            customer.Address,
                            customer.Phone,
                            customer.Fax
                        };
            return query.ToList();
        }
        public static IEnumerable GetProductReports() {   //销售大类合计表连接发票表,生成一张带有发货时间的表
            var query = from sale in DB.Sales_by_Categories
                        join invoice in DB.Invoices on sale.ProductName equals invoice.ProductName
                        where invoice.ShippedDate != null
                        select new {
                            sale.CategoryName,
                            sale.ProductName,
                            sale.ProductSales,
                            invoice.ShippedDate,    //发货时间
                        };
            return query.ToList();
        }
        public static IEnumerable GetCustomerReports() {
            var query = from customer in DB.Customers    //顾客表
            join order in DB.Orders on customer.CustomerID equals order.CustomerID   //订单表
            join order_detail in DB.Order_Details on order.OrderID equals order_detail.OrderID  //订单详情表
            join product in DB.Products on order_detail.ProductID equals product.ProductID  //产品表
            select new {   //匿名类转列表给前台展示
                    product.ProductName,        //产品名称
                   customer.CompanyName,       //公司名称
                   order.OrderDate,            //订单日期 
ProductAmount = (float)(order_detail.UnitPrice * order_detail.Quantity) * (1 - order_detail.Discount / 100) *100
                        };  //总金额
            return query.ToList();

        }

        public static IEnumerable GetSalesPerson() {   //获得销售员
            return DB.SalesPersons.ToList();
        }
    }
    public class EditableProduct {                //产品类
        public int ProductID { get; set; }            
        [Required(ErrorMessage = "Product Name is required")]     //标记  必需的
        [StringLength(50, ErrorMessage = "Must be under 50 characters")]        //字符串的长度50以内
        public string ProductName { get; set; }
        [Required(ErrorMessage = "Category is required")]
        public int? CategoryID { get; set; }
        public int? SupplierID { get; set; }
        [StringLength(100, ErrorMessage = "Must be under 100 characters")]
        public string QuantityPerUnit { get; set; }
        [Range(0, 10000, ErrorMessage = "Must be between 0 and 10000$")]   //范围限制
        public decimal? UnitPrice { get; set; }
        [Range(0, 1000, ErrorMessage = "Must be between 0 and 1000")]
        public short? UnitsInStock { get; set; }
        bool? discontinued;                            //可空类型
        public bool? Discontinued {            //显示的名字
            get {
                return discontinued;
            }
            set {
                discontinued = value == null ? false : value;        //注意用法:三元表达式
            }
        }
    }
    public class Invoice {
        public string CompanyName { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string Country { get; set; }
        public decimal UnitPrice { get; set; }
        public short Quantity { get; set; }
        public float Discount { get; set; }
    }
    public class EditableEmployee {
        public int EmployeeID { get; set; }
        [Required(ErrorMessage = "First Name is required")]
        [StringLength(10, ErrorMessage = "Must be under 10 characters")]
        [Display(Name = "First Name")]       //前台显示的名称
        public string FirstName { get; set; }
        [Required(ErrorMessage = "Last Name is required")]
        [StringLength(20, ErrorMessage = "Must be under 20 characters")]
        [Display(Name = "Last Name")]
        public string LastName { get; set; }
        [StringLength(30, ErrorMessage = "Must be under 30 characters")]
        [Display(Name = "Position")]
        public string Title { get; set; }
        [StringLength(24, ErrorMessage = "Must be under 24 characters")]
        [Display(Name = "Home Phone")]
        public string HomePhone { get; set; }
        [Display(Name = "Birth Date")]
        public DateTime? BirthDate { get; set; }
        [Display(Name = "Hire Date")]
        public DateTime? HireDate { get; set; }
        public string Notes { get; set; }
        public int? ReportsTo { get; set; }
        public byte[] Photo { get; set; }
    }
    public class EditableCustomer {
        public string CustomerID { get; set; }
        [Required(ErrorMessage = "Company Name is required")]
        [StringLength(40, ErrorMessage = "Must be under 40 characters")]
        public string CompanyName { get; set; }
        [StringLength(30, ErrorMessage = "Must be under 30 characters")]
        public string ContactName { get; set; }
        [StringLength(15, ErrorMessage = "Must be under 15 characters")]
        public string City { get; set; }
        [StringLength(15, ErrorMessage = "Must be under 15 characters")]
        public string Region { get; set; }
        [StringLength(15, ErrorMessage = "Must be under 15 characters")]
        public string Country { get; set; }
    }
    public class EmployeeOrder {
        public DateTime? OrderDate { get; set; }
        public decimal? Freight { get; set; }
        public decimal? Id { get; set; }            //可空的小数类型
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public byte[] Photo { get; set; }
    }
}