SMARTPERSISTENCELAYER 2.0 Query Advanced Query Article
Overview
After reading the front function, everyone will take into account the query between multi tables. Here, I would like to talk about the complexity / importance / feasibility of query in the application system.
Importance: Query statistics is one of the targets of system maintenance, is an essential part of the system, so in most systems, there will be query statistics.
Complexity: Inquiry is a most complex part in the system development, no one can expect the statistics of the complexity, even in a small system, so in the SPL is also unable to implement so many query statistics, in order to be compatible, SPL also strives to achieve what it can do is, the "Joint Query" function will be completed through the Query class.
Feasibility: To quickly and effectively deal with complex and variable queries and statistics, in my opinion, use views to process is the most effective way. One is the high speed of the view: The view query of the database is more fast than performing SQL, because the database will cache the view. Second, it is quite complicated to write SQL statements, and query aggregated statements are quite complicated, so it is easy to make mistakes, and the view will make the problem very simple, and the development speed is greatly improved. The third is maintenance: Due to the complexity of the query, the frequent changes will be the most obvious feature, so the strain ability of the SQL statement has no view.
Therefore, my suggestion is for some queries and statistics, we should use views as much as possible. Simple queries can of course use SQL. Now introduce Query joint queries in the SPL.
Joint inquiry
Example: The ID of the table A is associated with the AID of the B table. We want to associate a / b table, select the Name and the COMPANY value of the table.
Query Q1 = New query (TypeOf (a)); // Built a Query of a table
Q1. addttribute (a.name); // Add a field to select
Condition c = q1. GetQueryCondition (); // a Table Generate a query
C.ADDEQUALTO ("Name", "Tintown"); // Add conditions to A, please refer to Condition
Q1.ORDERBY ("name"); // Add a sort, please refer to Retrievecriteria
Query Q2 = New query (TypeOf (b)); // Built a Query of a B table
Q2.Addattribute (B.Company, "Company Name"); // Add another selection field to output
Q1. AddJoinQuery (A.ID, Q2, B.AID); // Associate Q1 with Q2
DataSable DT = Q1. Execute (); // Execute Query
Step analysis:
1. Query's creation is almost the same as criteira, to specify a physical type TypeOf (a)
2. Since it is a multi-table query, which fields are required to select which fields are available in the current SPL:
1. addttribute (field name): This is the name of the field directly in the field name
2. Addttribute (field name, alias): This is to output a field name to alias 3. AddAttribute (AttributeType.all): This means all displayed in *
3. Return to the DataTable type after using Execute (), can be binded directly.
When using this output field, be careful not to rename the output, especially after using the *, because it is some unpredictable field rename, it is recommended to use alias as much as possible.
Through the above way, the joint query between most tables can be achieved. There is a limitations above: the relationship between the table-write table is inner Join. That is, the left connection effect cannot be achieved, for this kind of query, I still Recommended views.
Other Condition and Orderby are similar to RetrieVecriteria, and will not be described again.
D
For the summary query itself is also very complicated, SPL can only achieve simple summary, and the summary of complexity is still recommended to use views or SQL.
Query Q1 = New query (TypeOf (a));
Q1.Addattribute (a.name): // Display Name
Q1.Selectmax (A.Price, "MaxPrice"); // Statistics the highest price, alias MaxPrice
Q1.SelectSum (a.price, "allprice"); // Statistical price sum, alias Allprice
Q1.Selectavg (A.Price, "AvgPrice"); // Statistics Average Price, Alias Avgprice
Q1.SelectCount (a.name, "count"); // Count statistics, alias count
Q1.Groupby (a.name); // Pick up NAME
Q1. EXECUTE (); //
Simple summarization using SELECTMAX, SELECTSUM, SELECTAVG, SelectCount, the above example is the case where the multi-sum value is displayed, so it is an Execute () method.
SPL also provides a single return method:
Query Q = New Query (TypeOf (a);
Q.SelectCount (A.ID);
INT REscalar ();
This can return the number of strips, in fact, in the SPL is the first column value of the first record.
SQL statement execution
To extend compatibility, or provide the most basic SQL statement execution interface, for some complex statements, you can use the SQL statement to solve, and the implementation is also very simple:
String Sqlstring = "SELECT NAME FROM a Where Price> 1000";
Query. ProcessSQL (Sqlstring, "DBNAME");
The dbname here means which data source is operated. Of course, it is important to pay attention to the field name that returns to SQL, which is the actual field name of the database, not the entity's attribute name.
Distinct function
In the SPL, you can use the isDistinct property to use the Distinct function, which will eventually generate the "Select Distinct ..." statement.
Stored procedure
The stored procedure is also supported in the SPL, and we can also complete the query static class:
IdataParameter Para = query.getParameter ("northwind"); // Create a parameter
Para.ParameterName = "@ name1"; // Define parameter name
Para.value = "f"; // Define Parameter IdataParater [] Paras = New IdataParameter [1]; // Define Arrange Arrse
Paras [0] = para; // assigns a parameter to the array
DataTable DT = query.runprocedure ("test", parac, "northwind"); // Execute a stored procedure
Step analysis:
1. Optional setting stored procedure parameters, you can use Query's getParameter () to create a parameter, define parameter names and parameter values.
2. GetParameter The corresponding Parameter is generated according to the type of data source, such as: The data source is SQL, generates SQLParameter, if it is other, generate oleDbParameter; can also pass GetParameter ("data Source Name "," Parameter ", parameter value) to directly assign parameter names and parameter values.
3. Define the array of parameters and add the parameters to an array.
4. Execute Query's RunProcedure ("Stored Procedure Name", parameter array, data source name), so you can get DataTable.
If it is not returning a DataTable stored procedure, we can adopt:
INT Query. Runprocedure ("Stored Procedure Name", parameter array, OUT affects the number of lines, data source names);
This method returns the number of rows that affects, out of operation; the INT directly returned directly, the return value of the stored procedure.
to sum up
For Query, his joint query function is also very limited, so if it is a simple joint query can be implemented by Query, if it is too complicated, create a view in the database, then use Query's execution SQL statement to implement the function of the execution SQL statement . Because of the complex thing, if the SPL is implemented, the process will also become complex, and this complicated is not as transparent as SQL.
Listen to
November 2004
MSN: TINTOWN_LIU@hotmail.com
