LLBLGen的数据库相对应SQL语句实现方法

 

1、CustomerEntity customer = new CustomerEntity();定义一个空新实体

CustomerEntity customer = new CustomerEntity("SOLDES");定义一个主键值为"SOLDES"的新实体

2、DataAccessAdapter adapter = new DataAccessAdapter();

CustomerEntity customer = new CustomerEntity("CHOPS");

adapter.FetchEntity(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.Customer = 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