In the actual development process, we often face some queries or statistical sub-table information. For one level of processing, it is relatively simple. If there are two levels, three levels, etc. or more, the conditions written at this time will be more complex and prone to writing errors. situation. In addition to providing flexible comparison operation functions, the SQL Artisan field object also provides N-level condition nesting functions in the object's In method, allowing you to be more flexible in constructing query conditions.
Take MSSQL's NorthWind as an example:
query the sales amount of a specified employee during a certain period of time.
Processed tables: Employees, Orders, Order Details.
During the implementation process, you can query through table association or directly query the target table. To directly query the target table, you must add the conditions of the parent table to the current query conditions in a nested manner. .
Employee.Employee->Orders.OrderDate->Sum(Order Details) makes a reverse condition nesting.
The following is the implementation of the two query methods under SQL statements and SQL Artisa components.
(The code is only used to express the functions of the SQL Artisan component, and other issues existing in the SQL statement itself will not be discussed)
SQL method one:
SELECT Sum(UnitPrice*Quantity*(1-Discount)) FROM Employees INNER JOIN Orders ON Employees.EmployeeID= Orders.EmployeeID INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID where ( ( Employees.EmployeeID = 3 And Orders.OrderDate >= '1997-7-1' ) And Orders.OrderDate <= '1997 -8-1')
SQL Artisan method one:
query = session.CreateQuery(Employees.TBL.INNER(Orders.TBL,Orders._EmployeeID)&
Orders.TBL.INNER(OrderDetails.TBL,Orders._OrderID));
query.Selects= new FieldAdapter[] {SqlMath.Sum(OrderDetails._UnitPrice*OrderDetails._Quantity*(1-OrderDetails._Discount))};
query.Expreesion = Employees._EmployeeID.At(Employees.TBL) ==3 &
Orders._OrderDate.At(Orders.TBL ) >= DateTime.Parse("1997-7-1")&
Orders._OrderDate.At(Orders.TBL) <= DateTime.Parse("1997-8-1");
ds = query.ExecuteDataSet();
SQL Method 2:
SELECT Sum(UnitPrice*Quantity*(1-Discount)) FROM [Order Details] where OrderID in (select OrderID from Orders where ( ( ( OrderDate >= '1997-7-1'And OrderDate < '1997-8 -1' ) And EmployeeID in (select EmployeeID from Employees where ( EmployeeID = 3 )) ) ))
SQL Artisan method two:
IQuery query = session.CreateQuery(OrderDetails.TBL);
query.Selects= new FieldAdapter[]{SqlMath.Sum(OrderDetails._UnitPrice*OrderDetails._Quantity*(1-OrderDetails._Discount))};
query. Expreesion = OrderDetails._OrderID.In(Orders.TBL,Orders._OrderID,
Orders._OrderDate >= DateTime.Parse("1997-7-1") &
Orders._OrderDate < DateTime.Parse("1997-8-1") &
Orders._EmployeeID.In(Employees.TBL,Employees._EmployeeID,Employees._EmployeeID ==3));
System.Data.DataSet ds = query.ExecuteDataSet();
From the above code, you can see that the SQL Artisan component conveniently and flexibly nests multi-level conditions through the In method of the field object. It is not a bad idea for developers to use strings to spell out SQL statements when writing such cumbersome query conditions. Dream, but it is also a very headache to manually check its legitimacy; not to mention that many times we have to face more complex query conditions.
What SQL Artisan brings to you is a completely object-based way of writing conditions and provides compilation and testing of the development environment, which greatly improves the efficiency and quality of writing conditions.
In fact, the SQL statements of the two SQL methods are intercepted by running the SQL Artisan method.
.