Database Component hxj. Data (16) (queried field)

When no query field is set for the query, the component returns all field information by default, that is, select * from table

In the query, how do I set the field information to be returned?

In the previous examples, the query field already exists and is set using the select method.

Example 1:

DbSession.Default.From<Products>()                .Select(Products._.ProductID, Products._.ProductName)                .ToDataTable();

Here, only the productid and productname fields are returned.

Generated SQL:

Text: 
SELECT [Products].[ProductID],[Products].[ProductName] FROM [Products] 

 

Sometimes we need to set the alias of this field, such as select productname as pname from products

Alias settings are also very simple.

DbSession.Default.From<Products>()                .Select(Products._.ProductID, Products._.ProductName.As("pname"))                .ToDataTable();

Use the as method of the field to set.

Generated SQL

Text: 
SELECT [Products].[ProductID],[Products].[ProductName] AS [pname] FROM [Products] 

Is it easy. In fact, products. _. productid is a field class that represents field information. Field also provides some simple auxiliary methods.

Method	SQL
Field. Count ()	Count
Field. sum ()	Sum Total
Field. AVG ()	AVG average
Field. Len ()	LEN Length
Field. Trim ()	Remove leading and trailing Spaces
Field. Max ()	Maximum Value
Field. Min ()	Minimum value
Field. Left (INT length)	Extract the corresponding length from the left
Field. Right (INT length)	Extract the corresponding length from the right
Field. substring (INT startindex, int endindex)	Intercept the field content in the corresponding range

Let's continue with the example above.

DbSession.Default.From<Products>()               .Select(Products._.UnitPrice.Max())               .ToScalar();

The maximum unitprice in the products table is queried.

Generated SQL

Text: 
SELECT max([Products].[UnitPrice]) AS [UnitPrice] FROM [Products] 

Other methods are similar. Sometimes we need two fields to add, subtract, multiply, and divide. For example, the unit price is multiplied by the quantity to obtain the total price. Here is an example:

DbSession.Default.From<Order_Details>()                .Select((Order_Details._.UnitPrice * Order_Details._.Quantity).As("totalprice"))                .Top(10)                .ToDataTable();

The generated SQL statement is as follows:

Text: 
SELECT TOP 10 [Order Details].[UnitPrice] * [Order Details].[Quantity] AS [totalprice] FROM [Order Details] 

The component reloads the field's +-*/operators. This section describes the transactions in addition, subtraction, multiplication, and division.