Database Component hxj. Data (28th) (queries in transactions, subqueries in query fields, and implicit conversion of whereclip)

This section describes the newly added features in the new version and how to use them.

 

Queries in transactions

In application scenarios, sometimes we need to update, add, or delete tables for transactions, and query these operations when submitting transactions (we do not want to lower the transaction level to make data changes in other connection queries ), queries performed within the transaction.

In fact, the Fromsection method is added to dbtrans.

The sample code is as follows:

Using (dbtrans trans = dbsession. default. begintransaction () {// modified the first record trans. update <Products> (products. _. productname, "productname", products. _. productid = 1); // query products product = trans in a transaction. from <Products> (). tofirst (); Trans. commit ();}

It is exactly the same as normal query.

 

Subquery in the query field

In the Application scenario, a column in the result needs to be obtained through a subquery.

For example, SQL: Select customerid, customername, (select count (*) from order where order. customerid = Customer. customerid) as ordercount from customer

The addselect method is added to the Fromsection query,

Addselect (Fromsection)

Addselect (Fromsection, string aliasname)

The second parameter is the alias after the subquery. Otherwise, there is no alias.

 

The sample code is as follows:

DbSession.Default.From<Customers>()                .Select(Customers._.CustomerID, Customers._.ContactName)                .AddSelect(DbSession.Default.From<Orders>().Select(Field.All.Count()).Where(Customers._.CustomerID == Orders._.CustomerID), "ordercount")                .Page(10, 6)                .ToDataTable();

 

Generated SQL statement:

Text: 
SELECT TOP 10 * FROM 
( SELECT TOP 41 [Customers].[CustomerID],[Customers].[ContactName],
( SELECT count(*) AS [cnt] FROM [Orders] WHERE ([Customers].[CustomerID] = [Orders].[CustomerID]) ) AS [ordercount] 
FROM [Customers] ORDER BY [Customers].[CustomerID] DESC ) AS tempIntable 
ORDER BY [CustomerID] ASC 

In this version, the page size has also changed, that is, for SQL Server2000 and access databases. That is, when the data in the page is greater than half of the total number of records, it is first queried in reverse order and then in positive order. The SQL output above is already like this.

 

It is also very easy to use. It is mainly used for the Fromsection query. For details, refer to the previous sections.

 

Implicit conversion of whereclip

The string type can be implicitly converted to the whereclip type.

Example:

DbSession.Default.From<Products>().Where("ProductID>10").ToList();

The where method parameter whereclip can also be directly passed here, and will be directly converted to whereclip.

This write method has the same effect as below.

DbSession.Default.From<Products>().Where(new WhereClip("ProductID>10")).ToList();

The generated SQL statements are:

Text: 
SELECT * FROM [Products] WHERE (ProductID>10)

With this implicit conversion, you can write the following code:

DbSession.Default.From<Products>().Where("ProductID>10" && Products._.CategoryID == 1).ToList();

Generated SQL:

Text: 
SELECT * FROM [Products] WHERE ((ProductID>10) AND ([Products].[CategoryID] = @fjipdlfqgppzhwet)) 

Parameters: 
@fjipdlfqgppzhwet[Int32] = 1 

 

However, this method is generally not recommended, but it is better to have a strong type of writing. As follows:

DbSession.Default.From<Products>().Where(Products._.ProductID > 10 && Products._.CategoryID == 1).ToList();

 

This section is complete.

 

Download