程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> 關於C# >> ERP/MIS開發 30道ORM問題與解答

ERP/MIS開發 30道ORM問題與解答

編輯：關於C#

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目錄中。