}
//思路:定义一个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 . CustomerIDjoin 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 ; } } }
