本文共 6891 字，大约阅读时间需要 22 分钟。

前一篇文章中提到，LLBL Gen对单个表的查询，这里再来分析一下LLBL Gen对多表的操作。

Adapter模式,用到的变量adapter定义为DataAccessAdapter adapter=new DataAccessAdapter ();

主从表的查询，保存和删除

SalesOrder是采购单主表，它下面有明细表SalesOrderDetail,用于记录采购单的物料信息。

查询编号为OE20100821的采购单及其物料明细 SalesOrderEntity salesOrder=new SalesOrderEntity (“OE20100821”); IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SalesOrderEntity);

prefetchPath.Add(SalesOrderEntity.PrefetchPathSalesOrderDetails);

bool found = adapter.FetchEntity(salesOrder, prefetchPath, null, null); PrefetchPath为因深度不同，一次可读取多个表的记录到内存中。LLBL Gen要求明确指定需要读取的从表深度。 保存采购单及其物料明细。先保存采购单主表，再保存从表采购单明细 SalesOrderEntity salesOrder=new SalesOrderEntity (“OE20100821”); adapter.SaveEntity(salesOrder, true, false); foreach (SalesOrderDetailEntity detail in salesOrder.SalesOrderDetails) {        adapter.SaveEntity(detail);   

}

删除采购单。先删除从表采购单明细，再删除采购单主表 SalesOrderEntity salesOrder;//OrderNo=”OE20100821” foreach (SalesOrderDetailEntity detail in salesOrder.SalesOrderDetails) {        adapter.DeleteEntity(detail);   

}

adapter.DeleteEntity(salesOrder);

LLBL Gen框架可以区分INSERT/UPDATE命令，统一用方法SaveEntity即可。

判断实体是新增加的还是从数据库中读取的，可用如下的方法 bool loadedCorrectly = (salesOrder.Fields.State == EntityState.Fetched);

 

自定义查询TypedList,可用于从多个表中读取任意的字段数据

SQL语句如下，这个例子也演示了表达式的用法。

SELECT OrderID,ProductID,(UnitPrice * Quantity) AS RowTotal FROm [Order Details] 对应的LLBL Gen的写法如下 ResultsetFields fields = new ResultsetFields(3);

fields.DefineField(OrderDetailsFields.OrderId, 0);

fields.DefineField(OrderDetailsFields.ProductId, 1);

fields.DefineField(new EntityField2("RowTotal",(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2);

DataTable results = new DataTable();

DataAccessAdapter adapter = new DataAccessAdapter();

adapter.FetchTypedList(fields, results, null);

再来看一个复杂的从多表中查询结果的例子 先定义存放结果的DataTable,根据字段需要设计对应的表结构

DataTable inventoryBalance= new FastSerializableDataTable("InventoryBalance");

inventoryBalance.Columns.Add("Loc", typeof(string)); inventoryBalance.Columns.Add("Description", typeof(string)); inventoryBalance.Columns.Add("QtyOnHand", typeof(decimal)); inventoryBalance.Columns.Add("QtyOnInspect", typeof(decimal)); 定义数据项的来源

ResultsetFields inventoryBalanceFields = new ResultsetFields(4);

inventoryBalanceFields .DefineField(InventoryBalanceFields.Loc, 0); inventoryBalanceFields .DefineField(LocationFields.Description, 1); inventoryBalanceFields .DefineField(InventoryBalanceFields.QtyOnHand, 2); inventoryBalanceFields .DefineField(InventoryBalanceFields.QtyOnInspect, 3); 定义查询过滤条件 IRelationPredicateBucket filterBucket = new RelationPredicateBucket(); filterBucket.PredicateExpression.Add(InventoryBalanceFields.ItemNo == itemNo); 设定以上数据来源项的表之间的关系 filterBucket.Relations.Add(InventoryBalanceEntity.Relations.LocationEntityUsingLoc);

对结果排序

ISortExpression sorter = new SortExpression(InventoryBalanceFields.ItemNo | SortOperator.Ascending)

最后加载数据到内存表中

adapter.FetchTypedList(inventoryBalanceFields, inventoryBalance, filterBucket, 0, sorter, false, null); 仓库LocationEntity与库存余额InventoryBalanceEntity的对应关系是一对一，使用Loc仓库编码来关联。             这个查询还可以复杂一些，要查询的字段不一定是数据库有的，比如聚合(求和，求平均值)函数的结果。 我们在为上面的查询添加一个字段，ORM写法如下,表示已经下采购单的物料合计数量 DbFunctionCall dbFunQtyUnused = new DbFunctionCall("ISNULL", new object[]  { (SalesOrderDetailFields.QtyOrdered), 0 }); EntityField2 eQtyOrder = new EntityField2("QtyOrdered", dbFunQtyUnused); inventoryBalanceFields.DefineField(eQtyOrder , 4, "QtyOrdered"); inventoryBalanceFields[4].AggregateFunctionToApply = AggregateFunction.Sum;

因为新加入了表SalesOrderEntity，需要增加关系,与仓库关联为采购单的每一笔物料的进仓仓库。

filterBucket.Relations.Add(InventoryBalanceEntity.Relations.SalesOrderDetailEntityUsingLoc);

 

子查询 Scalar query expressions

先看一个例子，SQL语句如下，查询客户表中的客户编号及其所有的订单合计数量

SELECT CustomerID, ( SELECT COUNT(*) FROM Orders WHERE CustomerID = Customers.CustomerID ) AS NumberOfOrders FROM Customers

Adapter模式的ORM的写法如下

ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CustomerFields.CustomerID, 0); fields.DefineField(new EntityField2("NumberOfOrders", new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), (CustomerFields.CustomerId == OrderFields.CustomerId))), 1); DataTable results = new DataTable(); adapter.FetchTypedList(fields, results, null); 如果只需要查询单个值，则DataAccessAdapter有更直接的支持，请看下面的例子 IRelationPredicateBucket filterBucket = new RelationPredicateBucket();

filterBucket.PredicateExpression.Add(SalesOrderDetailFields.ItemNo == ‘PC’);

filterBucket.PredicateExpression.Add(SalesOrderFields.OrderNo== ‘OE20110921’); filterBucket.Relations.Add(SalesOrderEntity.Relations.SalesOrderDetailEntityUsingOrderNo); DbFunctionCall dbFunQtyOrdered = new DbFunctionCall("IsNull", new object[] { (SalesOrderDetailFields.QtyOrdered), 0 }); object result = adapter.GetScalar(SalesOrderDetailFields.QtyOrdered, dbFunQtyOrdered , AggregateFunction.Sum, filterBucket); 这段ORM语句是要查询采购单OE20110921中的物料为PC的物料合计数量，用SQL写出来就是 SELECT SUM(QtyOrdered) FROM SalesOrder a, SalesOrderDetail b WHERE a.OrderNo=b.OrderNo AND a.OrderNo=’OE20110921’ AND b.ItemNo=’PC’ 聚合函数应用的场景，常常如下所示，就是需要构建结果集ResultsetFields ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CustomerFieldIndex.Country, 0, "Country"); fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers"); fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct

 

EntityCollection的查询

要查询的数据如果来源于数据库，可以应用以上的几种方法，如果要查询的数据来源于内存集合，则应用下面的模式。

比如，要找出采购单明细表中未出货的物料，则应用以下的写法 List<int> indices = SalesOrderDetails.FindMatches(SalesOrderDetailFields.QtyPacked <= 0); for (int i = indices.Count - 1; i >= 0; i--) {        SalesOrderDetails unPackedOrder=SalesOrderDetails[indices[i]); } FindMatches方法也可以传入下面的类型

IPredicate filterBucket = (SalesOrderDetailFields.QtyPacked <= 0);

如果要操作的数据有先后之分，并且些时EntityCollection中的数据不是业务规则操作需要的顺序，则可以以下面的模式，用EntityView2来排序过滤数据，重新获取按照指定规则排序的数据。 IPredicate filterBucket = (SalesOrderDetailFields.ItemNo == “PC”); ISortExpression sortExpression = new SortExpression(); sortExpression.Add(SalesOrderDetailFields.ItemNo| SortOperator.Ascending);

EntityCollection<SalesOrderDetailEntity> detailEntities = SalesOrder.SalesOrderDetails;

EntityView2<SalesOrderDetailEntity> entityView = new EntityView2<SalesOrderDetailEntity>(detailEntities);

entityView.Filter = filterBucket; entityView.Sorter = sortExpression; 关键的三句,依据EntityCollection构造EntityView2,并且传入指定的排序和过滤条件。 EntityCollection<SalesOrderDetailEntity> tmpCollection = (EntityCollection<SalesOrderDetailEntity>)entityView.ToEntityCollection(); foreach (SalesOrderDetailEntity detail in tmpCollection) {    //在这里，采购单中的物料明细已经按照物料编号升序排序了 }

自从应用ORM后，很少写SELECT * 这样的查询语句，因为这样太耗费时间，影响Performance，这种模式如下

IncludeFieldsList fieldList = new IncludeFieldsList(); fieldList.Add(SalesOrderFields.DueDay); fieldList.Add(SalesOrderFields.OrdreNo); SalesOrderEntity sales=adapter.FetchEntity(salesOrder, prefetchPath, null, fieldList); 然后会应用读到的值DueDay和OrdreNo，如果SalesOrderEntity还有一个字段是SalesMan(string,varchar(50)), 在这种读取模式下，这个值永远是string.Empty.

转载地址：http://awgzz.baihongyu.com/