》對於應用程序行業領域來說，涉及到Net框架的，在眾多支持大型項目的商用ORM框架中，使用最多的目前了解的主要有三款：
1、NHibernate(從Java版移植來的Net版)、
2、微軟的EF、
3、還有就是今天提到的LLBLGen
》ORM之間的性能比較
從上圖的測試結果可以看出，傳統的手動編寫代碼訪問數據庫，例如手動編寫的ADO.NET，仍然是從數據庫中取值的最快捷的方式，性能依然最好。而相比於三大ORM框架來說由於NH的功能龐大EF的次之，LLBLGen相較於來說性能最好。當然對比ORM訪問數據庫的性能這一個側面並不能完全說明一切，緩存機制、延遲加載機制的配合使用已經可以大部分避免數據庫訪問性能損耗的影響。(題外話:既然傳統如Ado.net的訪問方式依然性能優異為何還要選用ORM的話題不做擴展)
》LLBLGen Pro是LLBLGen的數據庫訪問解決方案，她包含兩個部分:設計器(LLBLGen Pro designer)和運行時框架(LLBLGen Pro runtime framework).
》設計器生成的源碼除支持自身的運行時框架外，還支持另外三種數據庫映射框架產品:
》LLBLGen Pro支持的數據庫種類眾多：諸如MS Access 2000/XP/2003/2007/2010、SQL Server 2000/2005/2008/2008R2/Express/MSDE/SQL Server CE Desktop、Oracle 9i/10g/11g、PostgreSql 7.4+/8.x+、Firebird 1.5.x/2.x、IBM DB2 7.x/8.x/9.x（非AS/400）、MySql 4.x，5.x (使用Core Lab/DevArt MySql .NET提供器）、Sybase Adaptive Server Enterprise（ASE）/SQL iAnywhere（ASA）。主流的幾大數據庫基本都包含在內，所有其框架適應數據庫的兼容能力自然可不必太擔心，同一套源碼可以同時支持多種數據庫，而不需要額外花太多精力考慮代碼數據庫兼容問題。
》LLBLGen的詳細了解可訪問官網 http://www.llblgen.com/ 各種介紹資料、文檔和代碼示例均有
》在建立完業務模型並轉化為規范的數據庫實體後，LLBLGen框架的使用可以基本使你完全脫離繁瑣的數據存儲及展現查詢的各種操作過程，而專心於業務邏輯的開發。
》以LLBLGen Pro runtime framework自身的運行時框架為例，設計器的代碼生成自動將各種表視圖存儲過程等對於的視圖模型Entidy、接口Interface、操作類等完整生成。業務中僅關心調用即可.
常用代碼整理如下:
using SD.LLBLGen.Pro.ORMSupportClasses;
using FactoryClasses；
using HelperClasses；
using RelationClasses；
using DBAdapter;
using EntityClasses；
using ViewClasses；
namespace ORM_Demo{
public class BLLCFUser：IBLLCFUser{
按照規則自動生成的方法代碼,如下示例...(借鑒內部資料的整理)
}
}
1、視圖查詢
例: Select * From View_CF_User_All Where RecordStatus<>'InActive' Order By UserID Desc
public ViewCFUserAllTypedView FetchAllUser()
{
DataAccessAdapter adapter = new DataAccessAdapter();
try
{
ViewCFUserAllTypedView user = new ViewCFUserAllTypedView();
RelationPredicateBucket bucket = new RelationPredicateBucket();
SortExpression sorter = new SortExpression();
//加過濾條件
bucket.PredicateExpression.Add(ViewCFUserAllFields.RecordStatus != "InActive");
//加排序條件
sorter.Add(ViewCFUserAllFields.UserID | SortOperator.Descending);
adapter.FetchTypedView(user.GetFieldsInfo(), user, bucket, 0, sorter, true);
return user;
}
catch
{
return null;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
ViewCFUserAllTypedView user = orm.FetchAllUser();
if (user != null)
{
gridView.DataSource = user;
gridView.DataBind();
}
2、表（記錄集合）查詢
例: Select * From CF_User
public EntityCollection<DerivedCFUserEntity> FetchUser()
{
DataAccessAdapter adapter = new DataAccessAdapter();
try
{
EntityCollection<DerivedCFUserEntity> user = new EntityCollection<DerivedCFUserEntity>(new DerivedCFUserEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
SortExpression sorter = new SortExpression();
adapter.FetchEntityCollection(user, bucket, 0, sorter);
return user;
}
catch
{
return null;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
EntityCollection<DerivedCFUserEntity> user = orm.FetchUser();
if (user != null)
{
gridView.DataSource = user;
gridView.DataBind();
}
3、表（某一記錄）查詢
例: Select * From CF_User Where LoginName='Admin' And RecordStatus<>'Inactive'
public DerivedCFUserEntity FetchUserInfo(string loginName)
{
DataAccessAdapter adapter = new DataAccessAdapter();
try
{
DerivedCFUserEntity user = new DerivedCFUserEntity();
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CFUserFields.LoginName == loginName);
bucket.PredicateExpression.AddWithAnd(CFUserFields.RecordStatus != "Inactive");
user = (DerivedCFUserEntity)adapter.FetchNewEntity(new DerivedCFUserEntityFactory(), bucket);
return user;
}
catch
{
return null;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
DerivedCFUserEntity user = orm.FetchUserInfo("Admin");
if (user.UserID>0)
{
lblLoginName.Text = user.LoginName;
}
3-2、多表(多表查詢 Linq實現)
》》
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
LinqMetaData metaData = new LinqMetaData(adapter);
var q = from t1 in metaData.CFEmployee
join t2 in metaData.CFCompany on t1.CompanyID equals t2.CompanyID
where t1.CompanyID < 100
select new { Addr = (t2.IRDAddress ?? "No Address"), t2.CreateUserID };
}
相當於
select IsNull(t2.IRDAddress,"No Address"),t2.CreateUserID
from CFEmployee as t1
inner join CFCompany as t2 on t1.CompanyID = t2.CompanyID
where t1.CompanyID < 100
》》
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
List<int> ids = new List<int>() { 1, 2, 3 };
LinqMetaData metaData = new LinqMetaData(adapter);
var q = from t1 in metaData.CFUser
where ids.Contains(t1.UserID)
select t1;
}
等同於SELECT * FROM CF_USER WHERE USERID IN (1, 2, 3)
》》
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
LinqMetaData metaData = new LinqMetaData(adapter);
var q = from t1 in metaData.CFUser
join t2 in metaData.CFEmployee on
new { C = t1.RefRecordID, B = t1.CreateUserID }
equals new { C = t2.EmployeeID, B = t2.CreateUserID }
join t3 in metaData.CFAccount on
new { D = t1.CreateUserID, E = t1.ModifyUserID }
equals new { D = t3.CreateUserID, E = t3.ModifyUserID }
select t1;
}
等同於
SELECT LPA_L1.* FROM [CF_User] as [LPA_L1]
INNER JOIN [CF_Employee] as [LPA_L2]
ON ( [LPA_L1].[RefRecordID] = [LPA_L2].[EmployeeID] AND [LPA_L1].[CreateUserID] = [LPA_L2].[CreateUserID]))
INNER JOIN [CFAccount] as [LPA_L3]
ON ( [LPA_L1].[CreateUserID] = [LPA_L3].[CreateUserID]) AND [LPA_L1].[ModifyUserID] = [LPA_L3].[ModifyUserID])
4、Insert操作（單表記錄新增）
例: Insert CF_User(A,B,C) Values(……)
public bool Add(string loginName, string password)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
DerivedCFUserEntity user = new DerivedCFUserEntity();
user.PrivClassID = 8;
user.RefTable = "CF_Employee";
user.RefRecordID = 1;
user.LoginName = loginName;
user.Password = password;
user.LanguageSelect = "CN";
user.IsManager = "N";
user.RegionalSetting = "zh-CN";
user.RecordStatus = "Active";
user.CreateUserID = 1;
user.CreateDate = DateTime.Now;
user.ModifyUserID = 1;
user.ModifyDate = DateTime.Now;
adapter.SaveEntity(user);
adapter.Commit();
return true;
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
string loginName = "Amdin";
string password = "111111";
if (orm.Add(loginName, password))
{
Alert("Success");
}
else
{
Alert("Fail");
}
5、Update操作（單表記錄修改）
例: Update CF_User Set RecordStatus='Inactive' Where UserID=100 //主鍵條件
public bool Modify(int userID)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
DerivedCFUserEntity user = new DerivedCFUserEntity(userID);
if (user.UserID < 1)
{
return false;
}
else
{
user.IsNew = false;
adapter.FetchEntity(user);
user.RecordStatus = "Inactive";
adapter.SaveEntity(user);
adapter.Commit();
return true;
}
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
int userID = 100;
if (orm. Modify(userID))
{
Alert("Success");
}
else
{
Alert("Fail");
}
Update操作（單表按條件多條記錄修改）
例: Update CF_User Set RecordStatus='Inactive'，ModifyDate=getdate() Where LoginName='Admin' and IsManager='N' //非主鍵條件
public bool Modify(string LoginName，string IsManager)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(DerivedCFUserFields.LoginName == LoginName);
bucket.PredicateExpression.Add(DerivedCFUserFields.IsManager == IsManager);
DerivedCFUserEntity updateValues = new DerivedCFUserEntity();
updateValues.RecordStatus = HRRecordStatus.Inactive;
updateValues.ModifyDate = DateTime.Now;
int AmountUpdated = adapter.UpdateEntitiesDirectly(updateValues, bucket);
adapter.Commit();
return true;
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
6、Delete操作（單表記錄刪除）
例: Delete From CF_User Where UserID=100
public bool Delete(int userID)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
DerivedCFUserEntity user = new DerivedCFUserEntity(userID);
if (user.UserID < 1)
{
return false;
}
else
{
adapter.FetchEntity(user);
//進行刪除操作
adapter.DeleteEntity(user);
adapter.SaveEntity(user);
adapter.Commit();
return true;
}
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
int userID = 100;
if (orm.Delete(userID))
{
Alert("Success");
}
else
{
Alert("Fail");
}
Delete操作（單表按條件多條記錄刪除）
例: Delete From CF_User Where CompanyID=100 and DeptID=34
public bool Delete(int CompanyID,int DeptID)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(DerivedCFUserFields.CompanyID == CompanyID);
bucket.PredicateExpression.Add(DerivedCFUserFields.DeptID == DeptID);
int AmountUpdated = adapter.DeleteEntitiesDirectly(typeof(DerivedCFUserEntity), bucket);
adapter.Commit();
return true;
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
7、Insert操作（多表記錄新增）
例: Insert CF_Employee(…………………………) Values(……………………………………)
Insert CF_User(…………………………) Values(……………………………………)
public bool Add(string loginName, string password, string firstName, string lastName, string employeeNo)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
int employeeID;
//再保存CF_Employee的信息
DerivedCFEmployeeEntity employee = new DerivedCFEmployeeEntity();
employee.FirstName = firstName;
employee.LastName = lastName;
employee.EmployeeNo = employeeNo;
employee.ActingSupervisor = "N";
employee.IsAllowProxy = "N";
employee.DefaultLanguage = "CN";
employee.RecordStatus = "Active";
employee.CreateUserID = 1;
employee.CreateDate = DateTime.Now;
employee.ModifyUserID = 1;
employee.ModifyDate = DateTime.Now;
adapter.SaveEntity(employee, true);
//保存CF_Employee後獲取出EmployeeID
employeeID = employee.EmployeeID;
//再保存CF_User的信息
DerivedCFUserEntity user = new DerivedCFUserEntity();
user.PrivClassID = 8;
user.RefTable = "CF_Employee";
//用到CF_Employee表中的EmployeeID
user.RefRecordID = employeeID;
user.LoginName = loginName;
user.Password = password;
user.LanguageSelect = "CN";
user.IsManager = "N";
user.RegionalSetting = "zh-CN";
user.RecordStatus = "Active";
user.CreateUserID = 1;
user.CreateDate = DateTime.Now;
user.ModifyUserID = 1;
user.ModifyDate = DateTime.Now;
adapter.SaveEntity(user);
adapter.Commit();
return true;
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
string loginName = "Amdin";
string password = "111111";
string firstName = "李";
string lastName = "小龍";
string employeeNo = "0532";
if (orm.Add(loginName, password, firstName, lastName, employeeNo))
{
Alert("Success");
}
else
{
Alert("Fail");
}
8、Update操作（多表記錄修改）
例: Update CF_User Set RecordStatus='Inactive' Where UserID=100
Update CF_Employee Set RecordStatus='Inactive' Where EmployeeID=200
public bool Modify(int userID, int employeeID)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
DerivedCFUserEntity user = new DerivedCFUserEntity(userID);
DerivedCFEmployeeEntity employee = new DerivedCFEmployeeEntity(employeeID);
if (user.UserID < 1 || employeeID < 0)
{
return false;
}
else
{
//更新CF_User的記錄
user.IsNew = false;
adapter.FetchEntity(user);
user.RecordStatus = "Inactive";
adapter.SaveEntity(user);
//更新CF_Employee的記錄
employee.IsNew = false;
adapter.FetchEntity(employee);
employee.RecordStatus = "Inactive";
adapter.SaveEntity(employee);
adapter.Commit();
return true;
}
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
int userID = 100;
int employeeID = 200;
if (orm. Modify(userID,employeeID))
{
Alert("Success");
}
else
{
Alert("Fail");
}
9、Delete操作（多表記錄刪除）
例: Delete From CF_User Where UserID=100
Delete From CF_Employee Where EmployeeID=200
public bool Delete(int userID, int employeeID)
{
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.StartTransaction(IsolationLevel.ReadCommitted, "");
try
{
DerivedCFUserEntity user = new DerivedCFUserEntity(userID);
DerivedCFEmployeeEntity employee = new DerivedCFEmployeeEntity(employeeID);
if (user.UserID < 1 || employeeID < 0)
{
return false;
}
else
{
//刪除CF_User中的記錄
adapter.FetchEntity(user);
adapter.DeleteEntity(user);
//刪除CF_Employee中的記錄
adapter.FetchEntity(employee);
adapter.DeleteEntity(employee);
adapter.Commit();
return true;
}
}
catch
{
adapter.Rollback();
return false;
}
finally
{
adapter.Dispose();
}
}
對生成代碼的調用e.g
ORM_Demo orm = new ORM_Demo();
int userID = 100;
int employeeID = 200;
if (orm.Delete(userID,employeeID))
{
Alert("Success");
}
else
{
Alert("Fail");
}
示例二:
1、
CustomerEntity customer = new CustomerEntity();定義一個空新實體
CustomerEntity customer = new CustomerEntity("SOLDES");定義一個主鍵值為"SOLDES"的新實體
2、
DataAccessAdapter adapter = new DataAccessAdapter();
CustomerEntity customer = new CustomerEntity("CHOPS");
adapter.FetchEntity(customer);
return customer;
直接從數據庫中取一條主鍵為"CHOPS"的記錄。它不會存在cache中。
相當於SQL: select * from customers where customerID='CHOPS'
根據主鍵來返回一條記錄
3、
DataAccessAdapter adapter = new DataAccessAdapter(true);
OrderEntity order = new OrderEntity(10254);
adapter.FetchEntity(order);
order.Customer = (CustomerEntity)adapter.FetchNewEntity(new CustomerEntityFactory(),order.GetRelationInfoCustomer());
adapter.CloseConnection();
相當於SQL: Select * from customers where CustomerID= (select CustomerID from orders where OrderID=10254)
在這裡，order為customer的子表，先從order表中取一條OrderID=10254的記錄，然後再取與這一條記錄相關聯的Customer的記錄。
4、
DataAccessAdapter adapter = new DataAccessAdapter();
CustomerEntity customer = new CustomerEntity();
customer.CompanyName = "Chop-suey Chinese";
adapter.FetchEntityUsingUniqueConstraint(customer, customer.ConstructFilterForUCCompanyName());
相當於SQL: select * from customerS where CompanyName ='Chop-suey Chinese'但是這裡CompanyName必須也是唯一的
5、
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter(true);
adapter.FetchEntity(customer);
customer.Phone = "(605)555-4321";
adapter.SaveEntity(customer);
adapter.CloseConnection();
相當於SQL: update customerS set phone='(605)555-4321' where customerID='Chop-suey Chinese'
//更新一條記錄的一個字段
6、
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(ProductFields.CategoryId == 3);
ProductEntity updateValuesProduct = new ProductEntity();
updateValuesProduct.Discontinued=true;
DataAccessAdapter adapter = new DataAccessAdapter();
int amountUpdated =adapter.UpdateEntitiesDirectly(updateValuesProduct, bucket);
相當於SQL: update products set Discontinued=1 where CategoryId = 3
更新CategoryId = 3的所有記錄
7、
PrefetchPath2 path = new PrefetchPath2((int)EntityType.OrderEntity);
path.Add(OrderEntity.PrefetchPathCustomer);
OrderEntity myOrder = new OrderEntity(10254);
adapter.FetchEntity(myOrder, path);
myOrder.CustomerID = null;
adapter.save(myOrder);
相當於SQL: Update Orders set CustomerID=NULL where OrderID=10254
8、DataAccessAdapter adapter = new DataAccessAdapter(true);
CustomerEntity customer = new CustomerEntity("CHOPS");
adapter.DeleteEntity(customer);
adapter.CloseConnection();
相當於SQL: Delete customers where customerID='CHOPS'
9、
OrderEntity order = new OrderEntity(10254);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(order);
order.SetNewFieldValue((int)OrderFieldIndex.ShippingDate, null);
adapter.SaveEntity(order);
相當於SQL: Update Orders Set ShippedDate=null Where OrderID=10254
這種寫法將不會執行數據驗證，使用時要注意這個問題！
也可以這樣寫：
OrderEntity order = new OrderEntity(10254);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(order);
order.ShippingDate = null;
adapter.SaveEntity(order);
10、CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue((int)CustomerFieldIndex.ContactTitle, null);
customer.TestCurrentFieldValueForNull(CustomerFieldIndex.ContactTitle);
// returns true
檢測一個字段值是否為NULL
11、CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter Adapter = new DataAccessAdapter();
EntityCollection orders = customer.Orders;
Adapter.FetchEntityCollection(orders, customer.GetRelationInfoOrders());
相當於SQL: Select * from Orders where CustomerID='CHOPS'
通過主表的一個主鍵值查找與這個主鍵相關聯的子表的記錄
以下這種辦法也可以：
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter Adapter = new DataAccessAdapter();
Adapter.FetchEntityCollection(customer.orders, customer.GetRelationInfoOrders());
12、
EntityCollection<CustomerEntity> customers =new EntityCollection<CustomerEntity>(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, null);
customers.Sort((int)CustomerFieldIndex.CompanyName, ListSortDirection.Descending);
相當於SQL: Select * From Customers Order By CompanyName desc
IPredicate filter = (CustomerFields.Country == "UK");
ArrayList indexes = myCustomers.FindMatches(filter);
這樣只在前面的myCustomers 中查找Country == "UK"的記錄，不會去數據庫中查找。
13、
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathEmployees);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, null, path);
}
這裡一共取了三個表的數據，主表為customers及其的子表Orders及Orders的主表Employess。
14、using (DataAccessAdapter adapter = new DataAccessAdapter())
{
DataTable table = new DataTable();
table.Columns.Add("CustomerId", typeof(string));
table.Columns.Add("CompanyName", typeof(string));
table.Columns.Add("OrderId", typeof(string));
ResultsetFields fields = new ResultsetFields(3);
fields[0] = CustomersFields.CustomerId;
fields[1] = CustomersFields.CompanyName;
fields[2] = OrdersFields.OrderId;
RelationPredicateBucket filter = new RelationPredicateBucket(CustomersFields.Country == "Germany");
filter.Relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);
IDataReader reader = adapter.FetchDataReader(fields, filter, CommandBehavior.CloseConnection, 0, true);
while (reader.Read())
{
table.Rows.Add(reader.GetValue(0).ToString(), reader.GetValue(1).ToString(), reader.GetValue(2).ToString());
}
reader.Close();
}
這是同時從兩個表中查找記錄的辦法
相當於SQL: select customers.customerid,customers.CompanyName from customers where customers.Country = 'USA'
15、
EntityCollection employees = new EntityCollection(new EmployeesEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeesEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrdersFields.OrderDate | SortOperator.Descending);
prefetchPath.Add(EmployeesEntity.PrefetchPathOrders, 10, null, null, sorter);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(employees,null, prefetchPath);
相當於SQL: select * from employees 假設第一條記錄的employeeid=1
select top 10 * from orders where employeeid=1 order by orderid desc
16、
ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList();
excludedFields.Add(CustomerFields.ContactName);
excludedFields.Add(CustomerFields.Country);
EntityCollection customers = new EntityCollection(new EmployeesEntityFactory());
SortExpression sorter = new SortExpression(CustomersFields.CustomerId | SortOperator.Descending);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, null, 0, sorter, null, excludedFields);
}
相當於SQL: Select ContactName,Country from customers order by CustomerId desc
CustomersEntity c = new CustomersEntity("CHOPS");
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntity(c, null, null, excludedFields);
}
相當於SQL:
Select ContactName,Country from customers Where CustomerId ='CHOPS'
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchExcludedFields(customers, excludedFields);
adapter.FetchExcludedFields(c, excludedFields);
}
也可以這樣寫
17、
ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList();
excludedFields.Add(OrdersFields.OrderDate);
PrefetchPath2 path = new PrefetchPath2(EntityType.CustomersEntity);
path.Add(CustomersEntity.PrefetchPathOrders, excludedFields);
EntityCollection customers = new EntityCollection(new CustomersEntityFactory());
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, null, 25, null, path);
}
18、 DataAccessAdapter adapter = new DataAccessAdapter();
// 開始一個事務
adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpates");
try
{
CustomersEntity customer = new CustomersEntity("CHOPS");
OrdersEntity order = new OrdersEntity(10254);
adapter.FetchEntity(customer);
adapter.FetchEntity(order);
// 修改字段
customer.Fax = "12345678";
order.Freight = 12;
// 保存
adapter.SaveEntity(customer);
adapter.SaveEntity(order);
// 提交
adapter.Commit();
}
catch
{
//出錯回滾
adapter.Rollback();
throw;
}
finally
{
// 銷毀
adapter.Dispose();
}
19、以下是一個更好的方法
DataAccessAdapter adapter = new DataAccessAdapter();
try
{
adapter.StartTransaction(IsolationLevel.ReadCommitted, "SavepointRollback");
AddressEntity newAddress = new AddressEntity();
adapter.SaveEntity(newAddress, true);
adapter.SaveTransaction("SavepointAddress");
CustomersEntity newCustomer = new CustomersEntity();
newCustomer.VisitingAddress = newAddress;
newCustomer.BillingAddress = newAddress;
try
{
adapter.SaveEntity(newCustomer, true);
}
catch (Exception ex)
{
adapter.Rollback("SavepointAddress");
}
adapter.Commit();
}
catch
{
adapter.Rollback();
throw;
}
finally
{
adapter.Dispose();
}
21、
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(CustomersFields.Country == "France");
filter.Relations.Add(OrdersEntity.Relations.CustomersEntityUsingCustomerId);
DataAccessAdapter adapter = new DataAccessAdapter();
int amount = (int)adapter.GetDbCount(new
OrdersEntityFactory().CreateFields(), filter, null, false);
相當於SQL:
Select count (*) from orders,customers Where Country = 'France' and customers.customerID=ORDERS.customerID
22、protected override void SerializeOwnedData(SerializationWriter writer, object context)
{
base.SerializeOwnedData(writer, context);
writer.WriteOptimized(this.OrderId);
}
protected override void DeserializeOwnedData(SerializationReader reader,object context)
{
base.DeserializeOwnedData(reader, context);
this.OrderId = reader.ReadOptimizedInt32();
}
23、
PredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(OrdersFields.OrderDate,null, new DateTime(1996, 8, 1), new DateTime(1996, 8, 12)));
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(filter);
EntityCollection customers = new EntityCollection(new OrdersEntityFactory());
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, bucket);
}
相當於SQL:
Select * from orders where orderdate between '1996-08-01' and '1996-08-12'
24、
EntityCollection Orders= new EntityCollection(new OrdersEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(OrdersFields.ShippedDate==System.DBNull.Value);
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(Orders, bucket);
}
相當於SQL: select * from orders where shippeddate is null
也可以這樣寫：
bucket.PredicateExpression.Add(new FieldCompareNullPredicate(OrdersFields.ShippedDate,null ,false));
25、
EntityCollection customers = new EntityCollection(new OrdersEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
DateTime[] values = new DateTime[3] { new DateTime(1998,04,8), new DateTime(1998,4,13), new DateTime(1998,4,21)};
bucket.PredicateExpression.Add(new FieldCompareRangePredicate(OrdersFields.OrderDate,null, values));
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, bucket);
}
相當於SQL:
select * from orders where OrderDate in ('1998-04-08' ,'1998-04-13','1998-04-21')
也可以這樣寫：
bucket.PredicateExpression.Add(OrderFields.OrderDate == values);//裡面是數組
26、
EntityCollection customers = new EntityCollection(new CustomersEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
CustomersFields.CustomerId,null, OrdersFields.CustomerId,null,
SetOperator.In, (OrdersFields.EmployeeId == 9)));
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, bucket);
}
相當於SQL:
select * from customers where Customers.CustomerID IN (SELECT CustomerID FROM Orders WHERE Employeeid=9)
27、
EntityCollection customers = new EntityCollection(new CustomersEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new FieldLikePredicate (CustomersFields.CompanyName ,null, "%n"));
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, bucket);
}
相當於SQL:
select * from customers where companyname like '%n'
也可以這樣寫：
bucket.PredicateExpression.Add(CustomersFields.CompanyName % "%n");
28、
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomersFieldIndex.Country, 0, "Country");
fields.DefineField(CustomersFieldIndex.CustomerId, 1, "AmountCustomers");
fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;
DataAccessAdapter adp = new DataAccessAdapter();
DataTable table = new DataTable();
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
adp.FetchTypedList(fields, table, null, 0, null, false, groupByClause);
29、
DataAccessAdapter adapter = new DataAccessAdapter();
decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId,(OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, (OrderDetailsFields.OrderId == 10254));
相當於SQL:
select sum(unitprice*quantity) from [Order Details] where orderid=10254
30、
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);
相當於SQL:
SELECT OrderID,ProductID,(UnitPrice * Quantity) AS RowTotal FROm [Order Details]
31、
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomersFields.CustomerId, 0);
fields.DefineField(new EntityField2("NumberOfOrders", new
ScalarQueryExpression(OrdersFields.OrderId.SetAggregateFunction(AggregateFunction.Count),(CustomersFields.CustomerId == OrdersFields.CustomerId))), 1);
DataTable results = new DataTable();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, results, null);
相當於SQL:
SELECT CustomerID,(SELECT COUNT(*) FROM Orders WHERE CustomerID = Customers.CustomerID) AS NumberOfOrders FROM Customers
