解析大型.NET ERP系统数据访问 对象关系映射框架LLBL Gen Pro

LLBL Gen Pro是一个为.NET开发人员设计的的对象关系映射(ORM)框架,与NHibernate,Entity Framework等框架一样,通过实体与数据表的映射,实现关系数据库持久化。

1  LLBL Gen Pro 入门  LLBL Gen Pro Basic

打开LLBL Gen Pro程序,在右边的数据库浏览器(Catelog Explorer)中根结点右键选择从关系数据库创建关系模型(

Add Relational Model Data from a Database),然后根据SQL Server,并且填入登录帐号和密码。

最终的界面如下图的所示,点击工具栏按钮生成.NET项目文件和实体映射文件。

解析大型.NET ERP系统数据访问 对象关系映射框架LLBL Gen Pro

1.1 持久化类 Persistent classes

以数据库表销售合同为例,它的数据库表结构定义如下。

CREATE TABLE [dbo].[SLORDR]
(
[RECNUM] [decimal] (18, 0) NOT NULL IDENTITY(1, 1),
[CONTRACT_NO] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CUSTOMER_NO] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUSTOMER_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CONTRACT_DATE] [datetime] NULL,
[REMARK] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATED_DATE] [datetime] NULL,
[CREATED_BY] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REVISED_DATE] [datetime] NULL,
[REVISED_BY] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CLOSED] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SLORDR] ADD CONSTRAINT [PK_SLORDR] PRIMARY KEY CLUSTERED ([CONTRACT_NO]) ON [PRIMARY]
GO

通过LLBL Gen Pro,生成的实体的代码量比较多,上面的销售合同数据表生成的实体类型定义源代码有600多行。作为入门的例子,先了解LLBL Gen Pro生成的构造方法和字段映射。先看销售合同实体的构造方法:

/// <summary> Static CTor for setting up custom property hashtables. Is executed before the first instance of this entity class or derived classes is constructed. </summary>
static SalesContractEntity()
{
SetupCustomPropertyHashtables();
} /// <summary> CTor</summary>
public SalesContractEntity():base("SalesContractEntity")
{
InitClassEmpty(null, null);
} /// <summary> CTor</summary>
/// <remarks>For framework usage.</remarks>
/// <param name="fields">Fields object to set as the fields for this entity.</param>
public SalesContractEntity(IEntityFields2 fields):base("SalesContractEntity")
{
InitClassEmpty(null, fields);
} /// <summary> CTor</summary>
/// <param name="validator">The custom validator object for this SalesContractEntity</param>
public SalesContractEntity(IValidator validator):base("SalesContractEntity")
{
InitClassEmpty(validator, null);
} /// <summary> CTor</summary>
/// <param name="contractNo">PK value for SalesContract which data should be fetched into this SalesContract object</param>
/// <remarks>The entity is not fetched by this constructor. Use a DataAccessAdapter for that.</remarks>
public SalesContractEntity(System.String contractNo):base("SalesContractEntity")
{
InitClassEmpty(null, null);
this.Fields[(int)SalesContractFieldIndex.ContractNo].CurrentValue = contractNo;
} /// <summary> CTor</summary>
/// <param name="contractNo">PK value for SalesContract which data should be fetched into this SalesContract object</param>
/// <param name="validator">The custom validator object for this SalesContractEntity</param>
/// <remarks>The entity is not fetched by this constructor. Use a DataAccessAdapter for that.</remarks>
public SalesContractEntity(System.String contractNo, IValidator validator):base("SalesContractEntity")
{
InitClassEmpty(validator, null);
this.Fields[(int)SalesContractFieldIndex.ContractNo].CurrentValue = contractNo;
} /// <summary> Protected CTor for deserialization</summary>
/// <param name="info"></param>
/// <param name="context"></param>
[EditorBrowsable(EditorBrowsableState.Never)]
protected SalesContractEntity(SerializationInfo info, StreamingContext context) : base(info, context)
{
if(SerializationHelper.Optimization != SerializationOptimization.Fast)
{
_salesContractDetail = (EntityCollection<SalesContractDetailEntity>)info.GetValue("_salesContractDetail", typeof(EntityCollection<SalesContractDetailEntity>));
this.FixupDeserialization(FieldInfoProviderSingleton.GetInstance());
}
// __LLBLGENPRO_USER_CODE_REGION_START DeserializationConstructor
// __LLBLGENPRO_USER_CODE_REGION_END
}

一共是7个构造方法,它们的作用说明如下:

1 静态构造方法 调用SetupCustomPropertyHashtables方法以初始化属性的自定义元数据。

2 默认无参数构造方法,以主键为参数的构造方法应用于实际开发过程。

3 以IEntityFields2为参数的构造方法,被框架使用。

4 以IValidator为参数的构造方法,用于自定义验证类型。

5 以SerializationInfo为参数的方法用于序列化传输。

1.2 映射 Mapping

LLBL Gen Pro框架工具会生成2个项目文件,实体定义放在项目DatabaseGeneric中,实体与数据表的映射放在DatabaseSpecific,也就是前者是数据库无关的,后者与数据库的具体特性相关联。比如生成存储过程调用,则代码生成会放到DatabaseSpecific项目中。来看一下上面的销售合同表是如何与实体映射的。

/// <summary>Inits SalesContractEntity's mappings</summary>
private void InitSalesContractEntityMappings()
{
this.AddElementMapping( "SalesContractEntity", "dbEnterprise", @"dbo", "SLORDR", 11 );
this.AddElementFieldMapping( "SalesContractEntity", "Recnum", "RECNUM", false, "Decimal", 0, 0, 18, true, "SCOPE_IDENTITY()", null, typeof(System.Decimal), 0 );
this.AddElementFieldMapping( "SalesContractEntity", "ContractNo", "CONTRACT_NO", false, "NVarChar", 20, 0, 0, false, "", null, typeof(System.String), 1 );
this.AddElementFieldMapping( "SalesContractEntity", "CustomerNo", "CUSTOMER_NO", true, "NVarChar", 8, 0, 0, false, "", null, typeof(System.String), 2 );
this.AddElementFieldMapping( "SalesContractEntity", "CustomerName", "CUSTOMER_NAME", true, "NVarChar", 50, 0, 0, false, "", null, typeof(System.String), 3 );
this.AddElementFieldMapping( "SalesContractEntity", "ContractDate", "CONTRACT_DATE", true, "DateTime", 0, 0, 0, false, "", null, typeof(System.DateTime), 4 );
this.AddElementFieldMapping( "SalesContractEntity", "Remark", "REMARK", true, "NVarChar", 4000, 0, 0, false, "", null, typeof(System.String), 5 );
this.AddElementFieldMapping( "SalesContractEntity", "CreatedDate", "CREATED_DATE", true, "DateTime", 0, 0, 0, false, "", null, typeof(System.DateTime), 6 );
this.AddElementFieldMapping( "SalesContractEntity", "CreatedBy", "CREATED_BY", true, "NVarChar", 8, 0, 0, false, "", null, typeof(System.String), 7 );
this.AddElementFieldMapping( "SalesContractEntity", "RevisedDate", "REVISED_DATE", true, "DateTime", 0, 0, 0, false, "", null, typeof(System.DateTime), 8 );
this.AddElementFieldMapping( "SalesContractEntity", "RevisedBy", "REVISED_BY", true, "NVarChar", 8, 0, 0, false, "", null, typeof(System.String), 9 );
this.AddElementFieldMapping( "SalesContractEntity", "Closed", "CLOSED", true, "NVarChar", 1, 0, 0, false, "", new ISL.TypeConverters.BooleanStringConverter(), typeof(System.String), 10 );
}
 

与NHiberate不同,LLBL Gen Pro将对象关系映射直接存储在源代码中,数据表字段与实体属性的映射由代码生成工具维护。

1.3 数据读写 Data access

能过以下几行简单的代码例子,了解LLBL Gen Pro提供的数据访问接口,下面的代码适用于Adapter模式。

//读取实体
DataAccessAdapter adapter...
SalesContractEntity salesContract = new SalesContractEntity("SC201507260001");
adapter.FetchEntity(salesContract, prefetchPath, null, fieldList); //保存实体
DataAccessAdapter adapter...
SalesContractEntity salesContract =new SalesContractEntity("SC201507260001");
salesContract .Customer="FLEXTRONICS";
adapter.SaveEntity(salesContract , true, false); //删除实体
DataAccessAdapter adapter...
SalesContractEntity salesContract...
adapter.DeleteEntity(salesContract);

DataAccessAdapter 是LLBL Gen Pro生成的项目DatabaseSpecific中的一个类型定义,是Adapter模式下标准的数据访问接口。

2  数据访问接口 Data access adapter

DataAccessAdapter  对Adapter模式,这个类型是数据访问接口的全部。如果是自治(SelfServicing)模式,则数据的增删改方法会直接附加到实体类型定义中。

EntityCollection 对象集合 实体的容器,一般用于数据设计时绑定和实体集的表示。有泛型和非泛型两个版本。

泛型版本的创建例子如下代码所示:

EntityCollection collection = new EntityCollection(new SalesContractEntityFactory());

EntityState 对象的状态 参考下面的枚举类型定义,对象可处于4种状态,New表示对象在内存中刚刚创建,Fetched表示对象从数据库中刚刚取到内存,OutOfSync表示对象的值与数据库中的值不一致,Deleted表示对象已经被删除。

public enum EntityState
{
New,
Fetched,
OutOfSync,
Deleted,
}

事务 为保证数据操作中发生异常的回滚操作,事务的例子代码如下所示。

try
{
adapter.StartTransaction(IsolationLevel.ReadCommitted, "Post SalesContract");
adapter.DeleteEntity(SalesContract);
adapter.Commit();
}
catch
{
adapter.Rollback();
throw;
}

存储过程 LLBL Gen Pro支持两种存储过程,一种需要返回值的定义为RetrievalProcedures,另一种是执行数据操作不返回结果的ActionProcedures。

数据库连接  类型DataAccessAdapter封装了数据访问接口,比如打开与关闭数据库连接,开启事务。

adapter.OpenConnection();
adapter.CloseConnection();


3  对象关系映射 Object relational mapping

3.1 属性映射 property mapping

以销售合同表(SLORDR)的客户编号(Customer_No),客户名称(Customer_Name),合同日期(Contract_Date)三个字段为例子,参考下面的属性与数据表字段定义的映射。

/// <summary> The CustomerNo property of the Entity SalesContract<br/>
/// Mapped on table field: "SLORDR"."CUSTOMER_NO"<br/>
/// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 8<br/>
/// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false<br/><br/></summary>
/// <remarks>Mapped on table field: "SLORDR"."CUSTOMER_NO"<br/>
/// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 8<br/>
/// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks>
public virtual System.String CustomerNo
{
get { return (System.String)GetValue((int)SalesContractFieldIndex.CustomerNo, true); }
set { SetValue((int)SalesContractFieldIndex.CustomerNo, value); }
} /// <summary> The CustomerName property of the Entity SalesContract<br/>
/// Mapped on table field: "SLORDR"."CUSTOMER_NAME"<br/>
/// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 50<br/>
/// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false<br/><br/></summary>
/// <remarks>Mapped on table field: "SLORDR"."CUSTOMER_NAME"<br/>
/// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 50<br/>
/// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks>
public virtual System.String CustomerName
{
get { return (System.String)GetValue((int)SalesContractFieldIndex.CustomerName, true); }
set { SetValue((int)SalesContractFieldIndex.CustomerName, value); }
} /// <summary> The ContractDate property of the Entity SalesContract<br/>
/// Mapped on table field: "SLORDR"."CONTRACT_DATE"<br/>
/// Table field type characteristics (type, precision, scale, length): DateTime, 0, 0, 0<br/>
/// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false<br/><br/></summary>
/// <remarks>Mapped on table field: "SLORDR"."CONTRACT_DATE"<br/>
/// Table field type characteristics (type, precision, scale, length): DateTime, 0, 0, 0<br/>
/// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks>
public virtual System.DateTime ContractDate
{
get { return (System.DateTime)GetValue((int)SalesContractFieldIndex.ContractDate, true); }
set { SetValue((int)SalesContractFieldIndex.ContractDate, value); }
}

LLBL Gen Pro框架依据表的主键生成实体的构造方法,设置主键字段的元数据,而不像NHibernate那样,为每个实体生成一个Id主键,参考下面的代码,重写对象的ToString方法,返回主键值。

public override string ToString()
{
List<string> builder = new List<string>();
foreach (IEntityField2 field2 in Fields)
{
if (field2.IsPrimaryKey)
builder.Add(Convert.ToString(field2.CurrentValue));
}
return string.Join(",", builder.ToArray());
}

实体对象继承于CommonEntityBase,CommonEntityBase继承于EntityBase2,EntityBase2又继承于IEntity2。

public abstract class EntityBase2 : IEntity2, IEntityCore, IEditableObject, IActiveContextParticipant, ITransactionalElement, ISerializable, IXmlSerializable, INotifyPropertyChanged, IDataErrorInfo, IEntityCoreInternal
{
}

这里需要注意一下为什么有此类型的定义后面会加数字2,这是为了支持两种模式的实体类型定义。

SelfServicing 模式的实体定义接口是IEntity,Adapter模式的实体定义接口是IEntity2。在整个LLBL Gen Pro框架中,凡是与Adapter模式相关的基类会在类型后面加一个数字2,比如上面定义的EntityBase2 。

LLBL Gen Pro不支持实体对象的继承,数据库表生成的实体之间都是平行的关系。当然也可以用面向对象的方式编程,增加一个类型继承于实体类型定义,但不推荐这样的写法。

3.2 对象生命周期  Object lifecycle

事件 Events

  • Entity: Initializing 实体初始化前
  • Entity: Initialized 实体初始化完成
  • Entity collection: EntityRemoving 实体集合中的实体删除前
  • Entity collection: EntityRemoved 实体集合中的实体删除完成(Remove或RemoveAt)
  • Entity collection: EntityAdding 实体集合增加实体前
  • Entity collection: EntityAdded   实体集合增加实体完成

重写方法 Overridable methods

  • Entity: OnFieldsCreated(仅适用于SelfServicing) 字段创建完成
  • Entity: OnFieldValueChanged 实体值更改完成。
  • Entity: OnGetValue 获取值。
  • Entity: OnGetValueComplete 获取值完成 。
  • Entity: OnInitializing 实体初始化
  • Entity: OnInitialized 初始化完成.
  • Entity: OnInitClassMembersComplete 初始化类成员完成。
  • Entity: OnRelatedEntitySet 相关实体设置.
  • Entity: OnRelatedEntityUnset 相关实体完成
  • Entity: OnSetValue 属性设置值
  • Entity: OnSetValueComplete 设置值完成.
  • Entity (adapter): OnBeforeEntitySave 实体保存前
  • Entity / entity collection: OnGetObjectData 序列化时
  • Entity / entity collection: OnDeserialized 反序列化完成。
  • Entity collection: OnEntityRemoving 被删除时。
  • Entity collection: OnEntityRemoved 实体集合中的实体删除后。
  • Entity collection: OnEntityAdding 实体集合中增加实体前。
  • Entity collection: OnEntityAdded 实体集合中增加实体后.
  • DataAccessAdapter: OnInsertPersistenceInfoObjects
  • TypedList: OnResultsetBuilt
  • TypedList: OnRelationSetBuilt
  • TypedList: OnInitialized
  • TypedView: OnInitialized

3.3 实体验证 Entity validate

在实体初始化类型定义中,指定验证类型,参考下面的代码。

this.Validator =new  SalesContractValidator();

SalesContractValidator验证类型的完整代码,主要重写了保存前验证和客户编号值赋值验证。

[Serializable]
public partial class SalesContractValidator : ValidatorBase
{
// Add your own validation code between the two region markers below. You can also use a partial class and add your overrides in that partial class.
// __LLBLGENPRO_USER_CODE_REGION_START ValidationCode
public override void ValidateEntityBeforeSave(IEntityCore involvedEntity)
{
base.ValidateEntityBeforeSave(involvedEntity);
SalesContractEntity salesContract = (SalesContractEntity)involvedEntity; if (string.IsNullOrEmpty(salesContract.ContractNo))
throw new EntityValidationException("Contract No. is required");
if (string.IsNullOrEmpty(salesContract.CustomerNo))
throw new EntityValidationException("Customer No. is required"); if (salesContract.IsNew)
{
ISalesContractManager salesContractManager = ClientProxyFactory.CreateProxyInstance<ISalesContractManager>();
if (salesContractManager.IsSalesContractExist(Shared.CurrentUserSessionId, salesContract.ContractNo))
throw new RecordDuplicatedException(salesContract.ContractNo, "Cotract No. is already used");
}
} public override bool ValidateFieldValue(IEntityCore involvedEntity, int fieldIndex, object value)
{
bool result = base.ValidateFieldValue(involvedEntity, fieldIndex, value);
if (!result) return false; switch ((SalesContractFieldIndex) fieldIndex)
{
case SalesContractFieldIndex.CustomerNo:
return this.ValidateCustomerNo((string) value);
} return true;
} private bool ValidateCustomerNo(string value)
{
if (!string.IsNullOrEmpty(value))
{
ICustomerManager customerManager = ClientProxyFactory.CreateProxyInstance<ICustomerManager>();
customerManager.ValidateCustomerNo(Shared.CurrentUserSessionId, value);
} return true;
} // __LLBLGENPRO_USER_CODE_REGION_END

4  对象关系 Object relation

1  主从关系 Mater/Detail

LLBL Gen Pro根据表之间的主从关系,在生成源代码时已经构建好了对象之间的关系。从表会增加一个集合属性(EntityCollection)到主表所映射的类型中,同时从表也有一个主表的属性方便引用主表的字段(属性)。

以销售合同为例子,一个销售合同包含多个销售订单,销售合同与销售订单是一对多关系。销售合同的源代码定义文件:

[Serializable]
public partial class SalesContractEntity : CommonEntityBase
// __LLBLGENPRO_USER_CODE_REGION_START AdditionalInterfaces
// __LLBLGENPRO_USER_CODE_REGION_END
{
#region Class Member Declarations
private EntityCollection<SalesContractDetailEntity> _salesContractDetail; // __LLBLGENPRO_USER_CODE_REGION_START PrivateMembers
// __LLBLGENPRO_USER_CODE_REGION_END
#endregion

在查询时,要查询出销售合同和它的销售订单明细表,可参考下面的代码。

IPrefetchPath2 prefetchPath = new PrefetchPath2((int) EntityType.SalesContractEntity);
prefetchPath.Add(SalesContractEntity.PrefetchPathSalesContractDetail);
SalesContractEntity salesContract = _salesContractEntityManager.GetSalesContract("SC201507270001", prefetchPath);
 

IPrefetchPath2用于LLBL Gen Pro关系查询,再来看一个三层结构的查询例子:

IPrefetchPathElement2 prefetchElement = prefetchPath.Add(SalesOrderEntity.PrefetchPathSalesOrderDetails);
prefetchElement.SubPath.Add(SalesOrderDetailEntity.PrefetchPathSalesOrderOrderLots);

第一层表是销售订单SalesOrder,第二层是销售订单明细(物料编号,数量,单价),第三层是销售订单物料明细下的批号(Lot)。对于数据关系中已经建立好的关系,LLBL Gen Pro都会为我们生成关系的类型定义,也支持通过代码构建关系,参考下面的查询语句。

IEntityRelation entityRelation = new EntityRelation(OrderLinkFields.OrderNo, JobOrderFields.JobNo, RelationType.OneToMany);
IRelationPredicateBucket filterBucket.Relations.Add(entityRelation);

2  关系类型 Entity relation

先来看一下LLBL Gen Pro定义的关系类型,参考下面的枚举定义。

  public enum RelationType
{
OneToMany,
OneToOne,
ManyToOne,
ManyToMany,
}

实际应用中多对多关系的例子比较少,多对多的关系应该要考虑增加数据表,分解成一对多的关系。

5  数据查询  Query

5.1 SELECT子句

认识两个类型ExcludeFieldsList和IncludeFieldsList,及以ExcludeIncludeFieldsList。从名称中可以推测出类型的含义,IncludeFieldsList是包含要选择的字段,ExcludeFieldsList是排除不读取的字段,ExcludeIncludeFieldsList通过传入构造方法参数,简化以上两个类型的的使用。比如下面的代码,是读取指定的字段:

ExcludeIncludeFieldsList custSepcFieldList = new ExcludeIncludeFieldsList(false);
custSepcFieldList.Add(CustomerSpecificationFields.Description);
custSepcFieldList.Add(CustomerSpecificationFields.SalesUom);
custSepcFieldList.Add(CustomerSpecificationFields.LotSize);
custSepcFieldList.Add(CustomerSpecificationFields.CustItemNo);
custSepcFieldList.Add(CustomerSpecificationFields.Specifications);

翻译成SQL语句,要选择的字段(IncludeFieldsList),比如读取客户编号和客户名称:

SELECT CustomerNo,CustomerName

排除不读取的字段,比如物料主档中图片比较大,SELECT语句中不包含图片字段,就需要用这个类型的写法。

除非真的需要,尽量不要写SELECT * 读取所有字段。

5.2  FROM子句

LLBL Gen Pro根据读取的数据类型(Entity,TypeList) 自动生成FROM部分。参考读取一个实体的代码:

CustomerSpecificationEntity customerSpec = new CustomerSpecificationEntity(customerNo, itemNo);
adapter.FetchEntity(customerSpecificationEntity, prefetchPath, null, fieldList);

再看下面读取一个列表(TypeList)的写法,返回一个结果集DataTable。

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(JobOrderFields.JobNo, 0); IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(JobOrderFields.Posted == true);
bucket.PredicateExpression.Add(JobOrderFields.Finished == true);
bucket.PredicateExpression.Add(JobOrderFields.Closed == false); System.Data.DataTable table = queryManager.GetQueryResult(fields, bucket, null, null, true, false);

 

5.3  WHERE 子句与条件表达式

IRelationPredicateBucket类型是SQL中WHERE语句部分的面向对象封装,IRelationPredicateBucket包含条件和关系,IPredicateExpression 只包含条件,IEntityRelation只包含关系。

IPredicateExpression 举例如下,

IPredicateExpression B = ((Table1Fields.Foo == "One") & (Table1Fields.Bar == "Two")) // A
| (Table2Fields.Bar2 == "Three");
 

LLBL Gen Pro官方帮助文件中列举了一个详细的SQL语句与查询类型中的映射关系,摘要如下。

SQL 语句

Predicate派生类型

Field BETWEEN 3 AND 5
Field BETWEEN field2 AND 4

FieldBetweenPredicate

Field = Field2

Field < (Field2 * 4)

FieldCompareExpressionPredicate

Field Is NULL

FieldCompareNullPredicate

Field IN (1, 2, 3, 5)

FieldCompareRangePredicate

Field IN (

SELECT Field FROM Foo WHERE ...)

FieldCompareSetPredicate

Field = 3

Field != "Foo"

FieldCompareValuePredicate

Field LIKE "Foo%"

FieldLikePredicate

再来看一下关系,如果是在数据库中有建立表之间的主从关系,则可直接用LLBL Gen Pro生成的关系类型,比如一个客户可对应多个联系地址,客户与联系地址是一对多的关系:

CustomerEntity.Relations.CustomerContactEntityUsingCustomerNo

如果没有在数据库中建立关系,则需要通过IEntityRelation类型创建关系,参看下面的例子代码。

IEntityRelation entityRelation = new EntityRelation(JobOrderMaterialLedgerFields.JobNo, JobOrderMaterialFields.JobNo, RelationType.OneToMany);

5.4 数据排序 ORDER BY

ISortExpression接口用于封装排序相关的操作,参考下面的例子。

ISortExpression sortExpression = new SortExpression();
sortExpression.Add(SalesOrderFields.OrderNo | SortOperator.Ascending);

支持多个字段排序,排序优先级与排序字段的增加顺序有关。

5.5 数据分组 GROUP BY

IGroupByCollection接口封装分组相关的操作。来看一个例子代码,创建分组接口的实例。

IGroupByCollection groupBy = new GroupByCollection(SalesOrderFields.OrderNo);

支持多个字段分组,可以借用下面的代码来创建多个字段分组的查询。

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(JobOrderFields.JobNo);
groupByClause.Add(JobOrderFields.BomNo);
 

5.6 聚合函数  Aggregate functions

LLBL Gen Pro帮助文档中有一篇《Generated code - Field expressions and aggregates》是专门讲解聚合函数的,参考下面简单的例子。

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Country, 0, "Country");
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers");
fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;
 

也可以直接读取数据,求一个聚合函数的返回值,例子代码如下。

DataAccessAdapter adapter = new DataAccessAdapter();
decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId,
(OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum,
(OrderDetailsFIelds.OrderId == 10254));


5.7 子查询 Subquery

先看SQL语句,读取客户编号和它的订单数量。

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

用LLBL Gen Pro Adapter模式实现,代码如下:

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);

上一篇:Python----字符串截取(查找,替换)


下一篇:背水一战 Windows 10 (116) - 后台任务: 前台程序激活后台任务