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();
= "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), 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] =
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, 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);
// 提交
();
}
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");
}
();
}
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 ( ,null, "%n"));
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, bucket);
}
相当于SQL:
select * from customers where companyname like '%n'
也可以这样写：
bucket.PredicateExpression.Add( % "%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
