【C#技术】一篇文章搞掂:LLBL
公司代码速查
ParameterBase.CurrentUser.UserId
ICustomerDeskDetailManager customerDeskDetailManager = ClientProxyFactory.CreateProxyInstance<ICustomerDeskDetailManager>();
使用主键查询一个实体
CustomerEntity _Customer = new CustomerEntity(CustomerNo); DataAccessAdapterBase adapter = GetCompanyDataAccessAdapter(); { bool found = adapter.FetchEntity(_Customer, prefetchPath, null, fieldList); if (!found) throw new Foundation.Common.RecordNotFoundException(CustomerNo, "Invalid customer"); } return _Customer;
根据条件查找实体集
EntityCollection CustomerCollection = new EntityCollection(new CustomerEntityFactory()); using (DataAccessAdapterBase adapter = GetCompanyDataAccessAdapter()) { adapter.FetchEntityCollection(CustomerCollection, filterBucket, 0, sortExpression, prefetchPath, fieldList); } return CustomerCollection;
实体集内存过滤
EntityView2<SalesOrderDetailEntity> orderDetail = new EntityView2<SalesOrderDetailEntity>(salesOrder.SalesOrderDetails); ISortExpression sorter = new SortExpression(SalesOrderDetailFields.EntryNo | SortOperator.Ascending); orderDetail.Sorter = sorter;
保存实体
using (DataAccessAdapterBase adapter = GetCompanyDataAccessAdapter()) { try { adapter.StartTransaction(IsolationLevel.ReadCommitted, "SaveCustomerGroup"); adapter.SaveEntity(CustomerGroup, true, false); adapter.Commit(); } catch { adapter.Rollback(); throw; } } return CustomerGroup;
删除实体
using (DataAccessAdapterBase adapter = GetCompanyDataAccessAdapter()) { try { adapter.StartTransaction(IsolationLevel.ReadCommitted, "DeleteCustomerGroup"); adapter.DeleteEntity(CustomerGroup); adapter.Commit(); } catch { adapter.Rollback(); throw; } }
Count(*)写法
using (DataAccessAdapterBase adapter = GetCompanyDataAccessAdapter()) { if (filterBucket == null) filterBucket = new RelationPredicateBucket(); return (int)adapter.GetScalar(CustomerGroupFields.CustomerGroup, null, AggregateFunction.Count, filterBucket.PredicateExpression); }
过滤条件写法
IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.PredicateExpression.Add(CustomerDeskDetailFields.DeskName == list[0].SysDeskName);
判断是否为空
bucket.PredicateExpression.Add(JobOrderMaterialPickDetailFields.SourceEntryNo == DBNull.Value);
关联过滤
//系统有外键,直接调用 bucket.Relations.Add(InventoryMovementEntity.Relations.InventoryMovementDetailEntityUsingRefNo); //系统无外键,通过这种方式创建;JoinHint与写的顺序相反 bucket.Relations.Add(new EntityRelation(ItemFields.ItemNo, InventoryMovementDetailFields.ItemNo, RelationType.OneToMany), JoinHint.Left);
使用别名关联
//主实体必须放在后面 bucket.Relations.Add(new EntityRelation(LocationFields.Loc,JobOrderMaterialPickFields.LocFrom, RelationType.OneToMany),"LocFrom", JoinHint.Left);
多键关联
IEntityRelation mulKeyRelation = new EntityRelation(JobOrderMaterialPickDetailFields.SourceRefNo, PurchaseOrderDetailFields.OrderNo, RelationType.ManyToOne); mulKeyRelation.AddEntityFieldPair(JobOrderMaterialPickDetailFields.SourceEntryNo, PurchaseOrderDetailFields.EntryNo); bucket.Relations.Add(mulKeyRelation, JoinHint.Right);//系统无外键,通过这种方式创建
在结果集中搜索
List<int> ids = customerDeskCollection.FindMatches(CustomerDeskDetailFields.TableName == item.TableName);
搜索出新的结果集
IPredicate filterBucket1 = (GcBomAmendmentFields.Change == true); sortExpression = new SortExpression(); sortExpression.Add(GcBomAmendmentFields.EntryNo | SortOperator.Ascending); EntityView2<GcBomAmendmentEntity> entityView1 = new EntityView2<GcBomAmendmentEntity>(collection); entityView1.Filter = filterBucket1; entityView1.Sorter = sortExpression; EntityCollection<GcBomAmendmentEntity> changeCollection = (EntityCollection<GcBomAmendmentEntity>)entityView1.ToEntityCollection();
关联读取实体
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.PurchaseOrderEntity); IPrefetchPathElement2 element = prefetchPath.Add(PurchaseOrderEntity.PrefetchPathPurchaseOrderDetails); element.SubPath.Add(PurchaseOrderDetailEntity.PrefetchPathPurchaseOrderBomDetails);
排序条件
ISortExpression sortExpression = new SortExpression(); sortExpression.Add(CustomerDeskFields.SortNo | SortOperator.Ascending);
复杂的公式(ISNULL(QtyReceipt,0)>ISNULL(QtyCosting,0))
DbFunctionCall dbFunQtyReceipt = new DbFunctionCall("ISNULL", new object[] { (PurchaseReceiptsDetailFields.QtyReceipt), 0 }); DbFunctionCall dbFunQtyCosting = new DbFunctionCall("ISNULL", new object[] { (PurchaseReceiptsDetailFields.QtyCosting), 0 }); EntityField2 eQtyReceipt = new EntityField2("QtyReceipt", dbFunQtyReceipt); EntityField2 eQtyCosting = new EntityField2("QtyCosting", dbFunQtyCosting); bucketInspection.PredicateExpression.Add(new FieldCompareExpressionPredicate(eQtyReceipt, null, ComparisonOperator.GreaterThan, new Expression(eQtyCosting)));
复杂的公式(SUM(ISNULL(eQtyOnHand,0)-ISNULL(QtyReserved,0)))
DbFunctionCall dbFunQtyOnHand = new DbFunctionCall("ISNULL", new object[] { (InventoryBalanceFields.QtyOnHand), 0 }); DbFunctionCall dbFunQtyReserved = new DbFunctionCall("ISNULL", new object[] { (InventoryBalanceFields.QtyReserved), 0 }); EntityField2 eQtyOnHand = new EntityField2("QtyOnHand", dbFunQtyOnHand); EntityField2 eQtyReserved = new EntityField2("QtyReserved", dbFunQtyReserved); IExpression expression = new Expression(eQtyOnHand, ExOp.Sub, eQtyReserved); object qtyBalance = adapter.GetScalar(InventoryBalanceFields.QtyOnHand.SetExpression(expression), null, AggregateFunction.Sum, filterBucket.PredicateExpression);
qtyBalance == DBNull.Value ? 0 : (decimal)qtyBalance;
保存时不验证
entity.Validator = null; adapter.SaveEntity(entity, true, false);
选择读取的列
ExcludeIncludeFieldsList fields = new ExcludeIncludeFieldsList(false); fields.Add(FiscalPeriodFields.EndDate);
自定义读取表数据
ResultsetFields fields = new ResultsetFields(4); fields.DefineField(InventoryMovementFields.RefNo, 0); fields.DefineField(InventoryMovementFields.LocFrom, 1); fields.DefineField(ProductionInspectionFields.JobNo, 3, "InspectionJobNo");//关联表的数据,以及创建别名 //获取 IUserDefinedQueryManager userDefinedQueryManager = ClientProxyFactory.CreateProxyInstance<IUserDefinedQueryManager>(); _productionIssueTable = userDefinedQueryManager.GetQueryResult(fields, bucket, sortExpression, null, true, 0, false);
直接删除 Delete From A Where
IRelationPredicateBucket bucket = new RelationPredicateBucket((CustomerFields.CustomerID == "FISSA")); DataAccessAdapter adapter = new DataAccessAdapter(); adapter.DeleteEntitiesDirectly("CustomerEntity", bucket);
直接更新 Update A set Where
var ShipmentImportTmpChangedEntity = new ShipmentImportTmpEntity(); ShipmentImportTmpChangedEntity.TransactionType = "FeedBack";//直接赋值 ShipmentImportTmpChangedEntity.Fields[(int)ShipmentImportTmpFieldIndex.TransactionType].ExpressionToApply = new Expression(ShipmentImportTmpFields.PaymentType);//等于另外一个字段 ShipmentImportTmpChangedEntity.Fields[(int)ShipmentImportTmpFieldIndex.TransactionType].ExpressionToApply = (ShipmentImportTmpFields.PaymentType + ShipmentImportTmpFields.TransactionType);//等于表达式 IRelationPredicateBucket bucket2 = new RelationPredicateBucket(); bucket2.PredicateExpression.Add(ShipmentImportTmpFields.PaymentType == "Amazon fees"); adapter.UpdateEntitiesDirectly(ShipmentImportTmpChangedEntity, bucket2); //实际应该把其他的值设置为false,防止同时更新,然后使用currentValue赋值,防止发生连带赋值 InventoryMovementDetailEntity updateMovDetails = new InventoryMovementDetailEntity(); foreach (IEntityField2 field in updateMovDetails.Fields) { field.IsChanged = false; } updateMovDetails.Fields[(int)InventoryMovementDetailFieldIndex.CreatedDate].CurrentValue = DateTime.Now; adapter.UpdateEntitiesDirectly(updateMovDetails, bucket);
事务
using (DataAccessAdapterBase adapter = GetCompanyDataAccessAdapter())
{
try{
adapter.StartTransaction(IsolationLevel.ReadCommitted, "SalesShipmentImportOrder");
使用LLBL基本的语句写
adapter.Commit();
}
catch
{
adapter.Rollback();
throw;
}
select * from A where A.xx in (select yy from B)
bucket.PredicateExpression.Add(new FieldCompareSetPredicate( JobOrderMaterialPickDetailFields.SourceRefNo, null, JobOrderFields.JobNo, null, SetOperator.In, bucket.PredicateExpression, bucket.Relations));