ERP/MIS开发 30道ORM问题与解答 LLBL Gen 3.x Adapter
做基于LLBL Gen的项目经常会用到的内容，忘记的时候用来查询，分享出来。
1 如何读取指定的表的内容到集合中
DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection allCustomers = new EntityCollection(new CustomerEntityFactory());
adapter.FetchEntityCollection(allCustomers, null);
2 如何添加一笔数据库记录
ProductEntity newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.UnitsOnOrder=0;
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.SaveEntity(newProduct);
3 如何删除一笔数据库记录
1）数据库已经加载到内存中
CustomerEntity customer = new CustomerEntity("FISSA");
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(customer);
2）设置实体主键，直接从数据库中删除
CustomerEntity customer = new CustomerEntity("FISSA");
adapter.DeleteEntity(customer);
3) 使用断言表达式(predicate expression)直接从数据库中删除
IRelationPredicateBucket bucket = new RelationPredicateBucket((CustomerFields.CustomerID == "FISSA"));
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.DeleteEntitiesDirectly("CustomerEntity", bucket);
4 如何传递数据库连接字符串
DataAccessAdapter adapter = new DataAccessAdapter("data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret");
5 如何测试一个实体是从数据库中加载的
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter();
bool loadedCorrectly = adapter.FetchEntity(customer);
读取FetchEntity的返回值，true表示是从数据库中加载
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(customer);
bool loadedCorrectly = (customer.Fields.State == EntityState.Fetched);
查看Fields.State的值，Fetched表示是已经从数据库中加载
6 如何在运行时查看一个实体的主键字段
二个方法：查询实体的属性PrimaryKeyFields(类型是ArrayList),也可以遍历对象的属性，检查它的IsPrimaryKey的值
7 如何给一个新实体的属性null值
不给该字段指定任何值可以保证数据库的值是NULL,也可以通过指定null来设为NULL
CustomerEntity customer=new CustomerEntity(“CDC”);
customer.ContactTitle=null;
这会导致ContactTitle的数据库字段值是NULL,这一句也可以不写，常常会省略
8 如何给一个已经存在的实体设置null属性
CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue((int)CustomerFieldIndex.ContactTitle, null);
也可以这样写
CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue("ContactTitle", null);
说明：新实体，表示在内存中新创建的，没有与数据库发生关联或没有保存到数据库中
已经存在的实体，常常是从为数据库中读取记录值，并填充到实体的属性值中
9 如何更新直接更新一系列数据库记录的值
OrderEntity newValues = new OrderEntity();
newValues.EmployeeID = 5;
IRelationPredicateBucket filter = new RelationPredicateBucket((OrderFields.EmployeeID == 2)); DataAccessAdapter adapter = new DataAccessAdapter();
adapter.UpdateEntitiesDirectly(newValues, filter);
这几句，把OrderEntity 中所有EmployeeID 为2的值，都更新为5
10 如何给读取的经过排序的查询types list
SortExpression sorter = (OrderFields.OrderDate | SortOperator.Descending) & (CustomerFields.CompanyName | SortOperator.Ascending);
11 如何写WHERE FIELD=3的过滤语句
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == 3);
12 如何写WHRE FIELDA=FIELDB的过滤语句
IPredicateExpression filter = new PredicateExpression();
filter.Add(OrderFields.OrderDate == OrderFields.ShippingDate);
13 如何写WHERE field LIKE '%foo%'的过滤语句
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field % "%Foo%");
14 如何写WHERE field BETWEEN 1 AND 10的过滤语句
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(YourEntityFields.Field, Null, 1, 10));
15 如何写WHERE field IN (1, 2, 5)的过滤语句
IPredicateExpression filter = new PredicateExpression();
int[] values = new int[3] {1, 2, 5};
filter.Add(YourEntityFields.Field == values);
也可以用ArrayList来实现
IPredicateExpression filter = new PredicateExpression();
ArrayList values = new ArrayList();
values.Add(1); values.Add(2); values.Add(5);
filter.Add(YourEntityFields.Field == values);
这种办法更直接
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareRangePredicate(YourEntityFields.Field, Nothing, 1, 2, 5));
16 如何写WHERE field IN (SELECT fieldb FROM foo)的过滤语句
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate( YourEntityFields.Field, null, FooFields.Fieldb, null, SetOperator.In, null));
17 如何写WHERE field IS NULL的过滤语句
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == DBNull.Value));
18 如何给查询dynamics list分组
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count); IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
DataAccessAdapter adapter = new DataAccessAdapter();
DataTable tlist = new DataTable();
adapter.FetchTypedList(fields, tlist, bucket, 0, null, true, groupByClause);
19 如何给获取collection的排序后的值
EntityView2 view = new EntityView2(customers);
view.Sorter = (new EntityProperty("propertyName") | SortOperator.Ascending);
20 如何获取采购订单中，送货日期与下单日期相差最大的值，即MAX(Order.ShippingDate - Order.OrderDate)
DataAccessAdapter adapter = new DataAccessAdapter();
int maxValue = (int)adapter.GetScalar(OrderFields.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate), AggregateFunction.Max, (OrderFields.CustomerId == _customerId));
21 如何给查询添加聚合函数
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Country, 0, "Country");
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers");
fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;
22 如何获取聚合函数的计算值
DataAccessAdapter adapter = new DataAccessAdapter();
decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId, (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, (OrderDetailsFIelds.OrderId == 10254));
22 如何在查询中使用表达式
-- SQL 查询列RowTotal 是表达式
SELECT OrderID, ProductID, (UnitPrice * Quantity) AS RowTotal
FROM [Order Details]
写法如下
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);
23 如何读取自定义属性
Dictionary<string, string> customProperties = CustomerEntity.CustomProperties;
string description = customProperties["Description"];
也可以用CustomPropertiesOfType
Dictionary<string, string> customProperties = customer.CustomPropertiesOfType;
string description = customProperties["Description"];
如果要取特定的自定义属性而不是全部属性，可以这样写
Dictionary<string, string>fieldCustomProperties = CustomerEntity.FieldsCustomProperties["CustomerID"];
string description = fieldCustomProperties["Description"];
24 在实体保存之前添加验证
给实体设置验证类，并重写ValidateEntityBeforeSave
public override void ValidateEntityBeforeSave( IEntityCore involvedEntity )
{ CustomerEntity toValidate = (CustomerEntity)involvedEntity;
}
25 如何验证实休的每个属性值
protected override bool ValidateFieldValue( IEntityCore involvedEntity, int fieldIndex, object value )
{
bool toReturn = true; switch((OrderFieldIndex)fieldIndex)
{
case OrderFieldIndex.OrderId: // id is valid if the value is > 0
toReturn = ((int)value > 0); break;
default:
toReturn = true; break;
}
return toReturn;
}
26 如何调用存储过程
int outputValue;
DataTable resultSet = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, ref outputValue);
27 如何只查询实体指定的需要的字段，没有指定的其余的都不要查询？
例如，Customer(CustomerNo,Name,Address,Telephone,ContactName,Country), 现在只想写这样的查询
SELECT ContactName,Country FROM Customer,即只查客户的名称和所属的国家
ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList(false);
excludedFields.Add(CustomerFields.ContactName);
excludedFields.Add(CustomerFields.Country);
CustomerCollection customers = new CustomerCollection();
SortExpression sorter = new SortExpression(CustomerFields.CustomerId | SortOperator.Descending); customers.GetMulti(null, 0, sorter, null, null, excludedFields, 0, 0);
这样，LLBL Gen会产生SQL：SELECT ContactName,Country FROM Customer，而不是查询所有字段
如果反过来，ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList();
用这一句替换开头的一句，则是表示不查询ContactName和Country这两个字段。
28 在collection中查找符合条件的记录
IPredicate filter = (CustomerFields.Country == "UK");
List<int> indexes = myCustomers.FindMatches(filter);
29 如何调用数据库函数
CREATE FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit) RETURNS DECIMAL AS BEGIN …… END
ResultsetFields fields = new ResultsetFields(1);
fields.DefineField( OrderFields.OrderId, 0, "OrderID" );
fields[4].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 1 } );
DataTable results = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter() )
{ adapter.FetchTypedList( fields, results, null ); }
29 序列化与反序列化自定义属性
序列化
protected override void OnGetObjectData(SerializationInfo info, StreamingContext context)
{
info.Add("_orderTotal", _orderTotal);
}
反序列化
protected override void OnDeserialized(SerializationInfo info, StreamingContext context)
{
_orderTotal = info.GetDecimal("_orderTotal");
}
数据库字段属性默认是序列化，如果给entity加入了自定义属性，则需要像上面的例子一样实样序列化。
30 获取数据库记录的总数
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(CustomerFields.Country == "France");
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);
DataAccessAdapter adapter = new DataAccessAdapter();
int amount = (int)adapter.GetDbCount(new OrderEntityFactory().CreateFields(), filter, null, false);
小技巧：LLBL Gen已经提供了Debugger Visualizers，在Debug时，可以查看到一段ORM语句的伪SQL，很方便调试问题。请把Frameworks\LLBLGen Pro\RuntimeLibraries\DebuggerVisualizers目录中的SD.LLBLGen.Pro.DebugVisualizers.dll拷贝到My Documents\Visual Studio xxyy\Visualizers目录中。
