Visual Studio 2008开发新特性系列课程(4)：VS2008新特性之LINQ实战 [兼

张义先开发工程师开发程师中达金桥技术服务有限公司微软微软中文技术论坛中文技术论坛————精彩生活每一天精彩生活每一天://本周活动更新本周活动更新：9Top10论坛英雄！9畅谈我的2007畅谈我的与众不同：9版主：50+微软最有价值专家（MVP）9涵盖微软几乎所有产品线和知识库930+适合开发人员和IT专业人员技术板块适合开发人员和专人员技术板块Today’sProblem:Data!=ObjectsData!=ObjectsLINQ架构LINQ架构LINQ架构LINQ架构VBVBOthers…Others…C#C#.NetLanguageIntegratedQuery(LINQ).NetLanguageIntegratedQuery(LINQ)LINQenableddatasourcesLINQenableddatasourcesLINQenabledADONETLINQenabledADONETLINQLINQLINQLINQLINQenabledADO.NETLINQenabledADO.NETLINQLINQLINQLINQLINQLINQLINQLINQToObjectsToObjectsLINQLINQToXMLToXMLLINQLINQToEntitiesToEntitiesLINQLINQToSQLToSQLLINQLINQToDatasetsToDatasetsbooktitle/author/ObjectsObjectsprice//bookXMLXMLRelationalRelationalLINQforSQLLINQforSQLLINQforSQLLINQforSQL把类数过系把类数过系•把.NET类和SQL数据通过关系进行映射•把LINQ查询转化为SQL语言进行执行•把.NET类和SQL数据通过关系进行映射•把LINQ查询转化为SQL语言进行执行把LINQ查询转化为SQL语言进行执行•支持对插入,更新,删除操作进行跟踪.把LINQ查询转化为SQL语言进行执行•支持对插入,更新,删除操作进行跟踪.•支持实体级别的验证规则•构建于ADONET之上并且集成连接池和事•支持实体级别的验证规则•构建于ADONET之上并且集成连接池和事•构建于ADO.NET之上并且集成连接池和事务处理•构建于ADO.NET之上并且集成连接池和事务处理LINQforSQLBasicsLINQforSQLBasicspublicpartialclassProduct{bliitPdtIDpublicintProductID;publicstringProductName;publicNullableintSupplierID;publicNullableintCategoryID;publicNullableintCategoryID;}publicclassNorthwindDataContext:DataContext{bliTblPdtPdtpublicTableProductProducts;}hiddbhid()NorthwindDataContextdb=newNorthwindDataContext();DataList1.DataSource=fromproductindb.Productswhereproduct.UnitsInStock0pselectproduct;DataList1.DataBind();CreatingandusingaLINQCreatingandusingaLINQCreatingandusingaLINQforSQLDataModelCreatingandusingaLINQforSQLDataModel数据表映射数据表映射数据表映射数据表映射映射数据表和实体类之间的关系映射数据表和实体类之间的关系•映射数据表和实体类之间的关系使用数据库中典型的主/外键进行表示•映射数据表和实体类之间的关系使用数据库中典型的主/外键进行表示•使用数据库中典型的主/外键进行表示支持灵活的关系查询并且不用写任务的SQL•使用数据库中典型的主/外键进行表示支持灵活的关系查询并且不用写任务的SQL•支持灵活的关系查询并且不用写任务的SQL代码就可以执行处理过程•支持灵活的关系查询并且不用写任务的SQL代码就可以执行处理过程PK/FXRelationshipsinDatabasePK/FXRelationshipsinDatabaseProductsTableSuppliersTableCategoriesTableForeignKeyRelationshipstotheSuppliersandtotheSuppliersandCategoriesTablesLINQ设计器中的关系映射LINQ设计器中的关系映射模型中的代码关联模型中的代码关联publicpartialclassProduct{publicintProductID;publicstringProductName;publicNullableintSupplierID;publicNullableintCategoryID;publicSupplierSupplier;publicCategoryCategory;}}publicpartialclassSupplier{publicintSupplierID;publicstringCompanyName;publicEntitySetProductProducts;}publicpartialclassCategory{publicintCategoryID;publicEntitySetProductProducts;publicEntitySetProductProducts;}CreatingandusingAssociationsCreatingandusingAssociationsCreatingandusingAssociationswiththeDLINQDesignerCreatingandusingAssociationswiththeDLINQDesignerggListSuppliersandTheirProductsListSuppliersandTheirProductsstSuppesadeoductsstSuppesadeoductsSupplier.CompanyNameProduct.ProductNameListSuppliersandProductsListSuppliersandProductsListSuppliersandProductsListSuppliersandProductsNorthwindDataContextdb=newNorthwindDataContext();();varsuppliers=fromsindb.Supplierswheres.Products.Count2selects;foreach(Suppliersupplierinsuppliers){Response.Write(h3+supplier.CompanyName+/h3);fh(ddilid){foreach(Productproductinsupplier.Products){Response.Write(--);ResponseWrite(productProductName);Response.Write(product.ProductName);Response.Write(br/);}}}ListSuppliersandProductsListSuppliersandProductsListSuppliersandProductsListSuppliersandProductsNorthwindDataContextdb=newNorthwindDataContext();SupplierList.DataSource=fromsindb.Supplierswheres.Products.Count2selects;liiid()SupplierList.DataBind();ListSuppliersandProductsListSuppliersandProductsListSuppliersandProductsListSuppliersandProductsasp:RepeaterIDSupplierListrunatserverasp:RepeaterID=SupplierListrunat=serverItemTemplateh3%#Eval(CompanyName)%/h3pyasp:RepeaterDataSource='%#Eval(Products)%'runat=serverItemTemplate%#Eval(ProductName)%br/--%#Eval(ProductName)%br//ItemTemplate/asp:Repeater/ItemTemplate/asp:RepeaterListSuppliersandTheirProductsListSuppliersandTheirProductsppppReducingQuerieswiththeReducingQuerieswiththeIncludingmethodIncludingmethodNorthwindDataContextdb=newNorthwindDataContext();GridView1.DataSource=(fromsindb.Supplierswheres.Products.Count4selects)Including(s=sProducts)selects).Including(s=s.Products)GridView1.DataBind();CustomerOrderHistoryJoinCustomerOrderHistoryJoinCustomerOrderHistoryJoinCustomerOrderHistoryJoinNorthwindDataContextdb=newNorthwindDataContext();varresults=fromcindb.Customersjoinoindb.OrdersoncCstomerIDeqalsoCstomerIDintocstOrdersonc.CustomerIDequalso.CustomerIDintocustOrdersfromoincustOrdersselectnew{Customer=cCompanyNameCustomer=c.CompanyName,OrderDate=o.OrderDate,OrderTotal=o.OrderDetails.Sum(d=d.UnitPrice)};GridView1.DataSource=results;GridView1.DataBind();CustomerOrderHistoryJoinCustomerOrderHistoryJoinCustomerOrderHistoryJoinCustomerOrderHistoryJoinPagingDataw/Skip()andTake()PagingDataw/Skip()andTake()ggp()()ggp()()PagingDataw/Skip()andTake()PagingDataw/Skip()andTake()PagingDataw/Ski