Net框架下-ORM框架LLBLGen的简介

》对于应用程序行业领域来说，涉及到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).

》设计器生成的源码除支持自身的运行时框架外，还支持另外三种数据库映射框架产品:

• Entity Framework 1.0和4.0
• NHibernate 2.x：包括标准的Hibernate和Fluent NHibernate
• LINQ to SQL

》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