这一节追踪LLBL Gen是如何把实体映射到为数据库操作的SQL语句的。
先来看读取数据库的程序片段[TestMethod]
public void TestFetchSalesOrderHeader() { DataAccessAdapter adapter = new DataAccessAdapter(ConnectionString); SalesOrderHeaderEntity salesOrder = new SalesOrderHeaderEntity(43659); adapter.FetchEntity(salesOrder); DateTime orderDate = salesOrder.OrderDate; }进入DataAccessAdapterBase的FetchEntity方法
public bool FetchEntity(IEntity2 entityToFetch)
{ return FetchEntity(entityToFetch, null, entityToFetch.ActiveContext, null); }它有一系列的overload方法
public bool FetchEntity(IEntity2 entityToFetch, Context contextToUse) public bool FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath) public bool FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse) public virtual bool FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields)在读取记录时，根据实体的主键来构造filter
RelationPredicateBucket filter = new RelationPredicateBucket(); IPredicateExpression pkFilter = CreatePrimaryKeyFilter(entityToFetch.Fields.PrimaryKeyFields); if(pkFilter==null) { throw new ORMQueryConstructionException("The entity '" + entityToFetch.LLBLGenProEntityName + "' doesn't have a PK defined, and FetchEntity therefore can't create a query to fetch it. Please define a PK on the entity"); } filter.PredicateExpression.Add(pkFilter); bool fetchResult = FetchEntityUsingFilter(entityToFetch, prefetchPath, contextToUse, filter, excludedIncludedFields); 从FetchEntityUsingFilter方法的参数可以看出，API的中各种用法，都最终会变成对这个方法的调用。以下举例 1) 读取一笔数据库记录的所有字段值给实体 SELECT * FROM CUSTOMERS WHERE CUSTOMERID='CHOPS' DataAccessAdapter adapter = new DataAccessAdapter();CustomerEntity customer = new CustomerEntity("CHOPS");
adapter.FetchEntity(customer);
2) 只读取指定的字段值，如下所示，只读取ContactName和Country
SELECT ContactName ,Country FROM customers WHERE CustomerId = 'CHOPS'
CustomerEntity customer = new CustomerEntity("CHOPS");
ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList(false);
excludedFields.Add(CustomerFields.ContactName);
excludedFields.Add(CustomerFields.Country);
adapter.FetchEntityCollection(customers, null, 0, sorter, null, excludedFields);
3) 读取指定的字段以外的数据库字段。比如，读取数据库字段ContactName和Country之外的所有字段
CustomerEntity customer = new CustomerEntity("CHOPS");ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList(true);
excludedFields.Add(CustomerFields.ContactName);
excludedFields.Add(CustomerFields.Country);
adapter.FetchEntityCollection(customers, null, 0, sorter, null, excludedFields);
用英语的原文来说，就是To specify which fields to exclude, you have two options:
- Specify the fields to exclude
- Specify the fields to fetch (i.e. include) which means the rest is excluded.
4) 使用PrefetchPath来读取关系记录中的主从明细的子表
需要一次读取客户表和它的子表订单表，SQL语句如下 读取CustomerSELECT CustomerID, CompanyName, ...FROM Customers WHERE Country = ‘Germany’
读取Order SELECT OrderID, CustomerID, OrderDate, ... FROM Orders WHERE CustomerID IN ( SELECT CustomerID FROM Customers WHERE Country = ‘Germany’) LLBL Gen的写法 CustomerCollection customers = new CustomerCollection(); IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.CustomerEntity); prefetchPath.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails); prefetchPath.Add(CustomerEntity.PrefetchPathVisitingAddress); IPredicateExpression filter = new PredicateExpression(); filter.Add(CustomerFields.Country == "Germany"); adapter.FetchEntity(filter, prefetchPath); 继续分析方法bool fetchResult = FetchEntityUsingFilter(entityToFetch, prefetchPath, contextToUse, filter, excludedIncludedFields); 在这个方法里面，会处理掉不需要读取的字段，同时也会读取实体对应的数据库字段的映射IFieldPersistenceInfo[] persistenceInfos = GetFieldPersistenceInfos(entityToFetch.Fields);
PersistenceCore.ExcludeFieldsFromPersistenceInfos(entityToFetch.Fields, persistenceInfos, excludedIncludedFields, hierarchyType, discriminatorFieldIndex); fetchResult = FetchEntityUsingFilter(entityToFetch.Fields, persistenceInfos, filter) 先看GetFieldPersistenceInfos方法的实现 protected virtual IFieldPersistenceInfo[] GetFieldPersistenceInfos(IEntityFields2 fields) { IFieldPersistenceInfo[] persistenceInfo = new FieldPersistenceInfo[fields.Count]; for (int i = 0; i < fields.Count; i++) { persistenceInfo[i] = GetFieldPersistenceInfo(fields[i]); } return persistenceInfo; }注意方法GetFieldPersistenceInfo，它会根据实体属性，构造成IFieldPersistenceInfo返回
继续读取方法，落入到fetchResult = FetchEntityUsingFilter(entityToFetch.Fields, persistenceInfos, filter);
它的主体结构是 IRetrievalQuery selectQuery = CreateSelectDQ(fieldsToFetch, persistenceInfos, predicateExpressionToUse, 0, null, filter.Relations, true, null, 0,0); try { OnFetchEntity( selectQuery, fieldsToFetch ); ExecuteSingleRowRetrievalQuery( selectQuery, fieldsToFetch, persistenceInfos ); OnFetchEntityComplete( selectQuery, fieldsToFetch ); } CreateSelectDQ调用DynamicQueryEngine来生成IRetrievalQuery查询 DynamicQueryEngineBase engine = CreateDynamicQueryEngine(); return engine.CreateSelectDQ(fieldsToFetch.GetAsEntityFieldCoreArray(), persistenceInfoObjects, GetActiveConnection(), filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize); DataAccessAdapter的一个最主要的作用是，绑定DQE,dynamic query enginee。 protected override DynamicQueryEngineBase CreateDynamicQueryEngine() { return this.PostProcessNewDynamicQueryEngine(new DynamicQueryEngine()); } DynamicQueryEngineBase 是ORM Support类库的一部分，是基础类型。如果需要添加SQL Server的支持，则 派生于它，重写若干方法。各数据库的DQE与DynamicQueryEngineBase 的关系如下图至些，对以上堆栈的分析，得出结论 1）无论哪种数据库，DatabaseGeneric部分都是相同的，它是数据库schema在程序语言中的实现。 2）DatabaseSpecific的作用：绑定特定数据库的实现，绑定数据库schema与.NET object的映射关系。 各种SQL方言会表现出稍微的不同，程序集SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll目的就是为实现这种差异
举例说明，SQL方言的细微不同
a) 字符串连接 MySQL的写法，使用concat函数SELECT concat(ename,'works as a',job) as msg FROM employee where deptno=10
SQL Server的写法，使用+SELECT ename+'works as a' + job as meg FROM employee where deptno=10
b) 限制返回的行数DB2: SELECT * FROM emp fetch first 5 rows only
MySQL和PostgresSQL : SELECT * FROM emp limit 5
Oracle : SELECT * FROM emp WHERE rownum<=5
SQL Server:SELECT top 5 * FROM emp
关于更多的SQL方言的不同，请参考电子书《SQL.Cookbook》,有详细的讲解。继续进入DQE中，看看SQL Server如何生成查询语句
engine.CreateSelectDQ(fieldsToFetch.GetAsEntityFieldCoreArray(), persistenceInfoObjects, GetActiveConnection(), filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);先看看DQE的方法成员,常见的SELECT,UPDATE,INSERT,DELETE命令由以下几个方法生成
在构造SELECT的语句过程中，用到QueryFragments 这个类型来辅助生成SQL语句 QueryFragments fragments = new QueryFragments(); fragments.AddFragment("SELECT"); StringPlaceHolder distinctPlaceholder = fragments.AddPlaceHolder(); StringPlaceHolder topPlaceholder = fragments.AddPlaceHolder(); DelimitedStringList projection = fragments.AddCommaFragmentList(false); if(relationsSpecified) { fragments.AddFormatted("FROM {0}", relationsToWalk.ToQueryText()); query.AddParameters(((RelationCollection)relationsToWalk).CustomFilterParameters); }AppendWhereClause(selectFilter, fragments, query);
AppendGroupByClause(groupByClause, fragments, query); AppendOrderByClause(sortClauses, fragments, query); query.SetCommandText(fragments.ToString()); 最后，把构造好的QueryFragments 转换在字符串，传递给IRetrievalQuery 要读懂这段代码，可参考一个最完整的SQL Server 的SELECT语句，包含TOP,DISTINCT,WHERE,Group by和order by来一块块的分析各个方法的作用。回到堆栈的DataAccessAdapterBase的FetchEntityUsingFilter方法
执行从DQE中获取的IRetrievalQuery查询， DataAccessAdapterBase中的OnFetchEntity是虚拟方法，在读取数据前执行
protected virtual void OnFetchEntity(IRetrievalQuery selectQuery, IEntityFields2 fieldsToFetch)
{ }ExecuteSingleRowRetrievalQuery的方法主体内容如下
{IDataReader dataSource = null;
try { PrepareQueryExecution(queryToExecute, true); dataSource = PerformExecuteSingleRowRetrievalQuery(queryToExecute, CommandBehavior.SingleRow); FetchOneRow(dataSource, fieldsToFill, fieldsPersistenceInfo); }这里，还有一个疑问没有解决。当前如何判断数据库是SQL Server,而不是DB2，它是如何发送SQL语句到服务器的？
DataAccessAdapterBase还有几个用于数据库命令的字段private string _transactionName, _connectionString;
private DbConnection _activeConnection; private DbTransaction _physicalTransaction; 这里用到了通用的DbConnection接口，而不是SqlConnection,OleDbConnection,OracleConnection. protected virtual IDataReader PerformExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, CommandBehavior behavior) { return queryToExecute.Execute(behavior); } PerformExecuteSingleRowRetrievalQuery方法用把流程交给了参数IRetrievalQuery的Execute方法。 要追踪到Execute方法，在非调试状态，需要找到它实际指向的对象类型，回到DQE中，生成IRetrievalQuery的方法 IRetrievalQuery toReturn = new RetrievalQuery(connectionToUse, CreateCommand()); CreateSelectDQ(fieldsOfSelectList.ToArray(), persistenceInfosOfFields.ToArray(), toReturn, filterToUse, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, relationsSpecified, sortClausesSpecified); 原来是RetrievalQuery这个类型，找到它的Execute方法,方法内容如下 public DbDataReader Execute(CommandBehavior behavior) { DbDataReader toReturn = this.Command.ExecuteReader(behavior); 也就是执行Command命令，返回快递只读向前的游标变量DbDataReader 接下来的步骤，应该就是绑定DbDataReader中的值到实体的属性中. 进入方法FetchOneRow(dataSource, fieldsToFill, fieldsPersistenceInfo); private void FetchOneRow(IDataReader dataSource, IEntityFields2 rowDestination, IFieldPersistenceInfo[] fieldsPersistenceInfo) {if(dataSource.Read())
{IFieldPersistenceInfo[] persistenceInfosForRowReader = fieldsPersistenceInfo;
if(hasExcludedFields) { persistenceInfosForRowReader = FieldUtilities.RemoveExcludedFieldsPersistenceInfos(persistenceInfosForRowReader); }ReadRowIntoFields(values, rowDestination, fieldNameToOrdinal, persistenceInfosForRowReader);
rowDestination.State = EntityState.Fetched;
方法ReadRowIntoFields把值放到属性中，并且State设置为EntityState.Fetched。 在这里，还有一个type converter的应用。比如采购单的状态，在数据库字段Finish，1表示是已经完成的采购单，0表示正在采购(收货或是验货入仓，没有完成). SalesOrderHeaderEntity salesOrder=new SalesOrderEntity(4098); 读取状态 bool finish=salesOrder.Finish; //1 或是设置 salesOrder.Finish=true; //0 有时候，也会用Y表示完成，N表示正在进行中的采购单。这种true/false与数据为记录的1/0的映射之前的转换，就需要用到type converter来转换。 结论1 ORM中访问数据库的方法是使用通用的DbConnection,DbCommand,DbDataReader等接口,此方法已经内置到ORM Support类库中，区别于我们的这种写法
swith (databasetype){
case DbType.SqlServer: _command=new SqlCommand(text); break; case DbType.MySql:_command=new MySqlCommand(text);
break;
唯一一个外接的与数据库方言相关的类型库DQE，是用来构造命令，而不用来执行命令。 2 实体的属性表示IEntityField2与实体的数据库记录的字段表示IFieldPersistenceInfo是分开的，也就是实体中不包含任何执久化的信息，在执行实体的新增，删除，修改命令时，会通过PersistenceInfoProviderCore接口构造它的属性对应的字段信息，通过些信息，构造IRetrievalQuery,最后调用IRetrievalQuery.ExecuteReader读取数据。