Data | Statements in actual programming projects, it is often the case that the DataTable is not in the database, or that the DataTable has not been written to the database, or that the DataTable read from the database has been locally altered and not written back to the database (possibly with other changes), In these cases, a powerful SQL language is not useful for querying data in a DataTable.
Some. NET programmers to solve such query problems by establishing temporary tables in the database. And I think this method is not feasible, in fact, as long as the use. NET class library, the powerful functionality of the DataView class, mainly with its RowFilter attribute, makes it easy to solve such query problems. Here is a concrete example to illustrate how to use the DataView RowFilter property to query without SQL statements.
Step one: Create a asp.net project for C #. First write a function makedatatable () that generates a DataTable, and the code is as follows:
Private DataTable maketable ()
{
Generating a DataTable
System.Data.DataTable mydatatable = new DataTable ("Local data Table");
DataColumn Mydatacolumn;
DataRow MyDataRow;
Generate data column ID, commodity name, commodity price
At this point, you can see the data in the table displayed in the DataGrid by browsing the page.
Id
Product Name
Commodity price
1
Football
57.5
2
Basketball
64.5
3
Tennis
6.5
4
Tennis Racket
388.5
Step three: Add a query to Label1,textbox1 and Button1 on the ASPX foreground page. As shown in the following illustration:
Step Four: Add the following code to the query button Button1 Click event:
DataTable dt= (DataTable) session["Table"];
Create a Data view for a datasheet
DataView dv=new DataView (DT);
Fuzzy query using the RowFilter attribute
Dv. Rowfilter= "Product name like '%" +textbox1.text.trim () + "%";
DATAGRID1.DATASOURCE=DV;
Datagrid1.databind ();
After you run the page, enter "NET" in TextBox1, and after pressing the query button, the DataGrid displays the following query results:
Id
Product Name
Commodity price
3
Tennis
6.5
4
Tennis Racket
388.5
Step five: Add a Label2 control to the ASPX foreground page, two TextBox controls-textbox1 and TextBox2, and a "query" button, Button2, as shown in the following figure, to query the price range entered by the user in the product.
Step Six: Add the following code to the Button2 button click event in the background code:
The user enters a price range in two text boxes, and after 10,70, the query results shown in DATAGRID1 are as follows:
Id
Product Name
Commodity price
1
Football
57.5
2
Basketball
64.5
It can be seen that the DataView RowFilter attribute is fully capable of achieving the function of the SQL statement SELECT statement.
The query statement in RowFilter is very similar to the syntax and function of the SELECT statement in the SQL statement, as illustrated by the syntax for the RowFilter query statement in MSDN:
User-defined values can be used in expressions that will be compared to column values. String values should be enclosed in single quotes. Date values should be placed in the pound sign (#). For numeric values, decimal and scientific notation are allowed. For example:
"FirstName = ' John '"
"Price <= 50.00"
"Birthdate < #1/31/82#"
Casts a value to an integer data type for the column that contains the enumeration value. For example:
"Enumcolumn = 5"
Operator
Concatenation is allowed when Boolean and, or, and not operators are used. You can use parentheses to combine clauses and force precedence. The AND operator takes precedence over other operators. For example:
(LastName = ' Smith ' OR LastName = ' Jones ') and FirstName = ' John '
The following operators are allowed when you create a comparison expression:
<
>
<=
>=
<>
=
In
Like
The following arithmetic operators are also supported in an expression:
+ (plus)
-(minus)
* (multiply)
/(except)
% (modulus)
String operators
To connect strings, use the + character. Whether string comparisons are case-sensitive is determined by the value of the CaseSensitive property of the DataSet class. However, you can override this value with the CaseSensitive property of the DataTable class.
Wildcard characters
In like comparisons, both * and% are interchangeable as wildcard characters. If the string in the LIKE clause contains * or%, the characters are escaped using brackets ([]). If there is a bracket in the clause, the bracket character is used to escape the brackets (for example, [[] or []]). Wildcard characters are allowed at the beginning and end of the pattern, or at the end of the pattern, or at the beginning of the pattern. For example:
"ItemName like ' *product* '"
"ItemName like ' *product '"
"ItemName like ' product* '"
Wildcard characters are not allowed in the middle of a string. For example, ' te*xt ' is not allowed.
Parent/child Relationship Reference
By adding parent to the column name, you can refer to the parent table in an expression. For example, Parent.price refers to a column named Price in the parent table.
By adding a child to the column name, you can refer to the columns in the Children table in an expression. However, because a child relationship can return multiple rows, you must include a reference to the subreport in the aggregate function. For example, SUM (child.price) returns the sum of the columns named price in the child table.
If a table has more than one table, the syntax is: Child (RelationName). For example, if a table has two child tables with their names Customers and Orders, the DataRelation object is named Customers2orders, and the reference will be:
AVG (customers2orders). Quantity)
Polymerization
The following aggregation types are supported:
SUM (SUM)
AVG (average)
MIN (minimum value)
Max (max)
Count (Count)
StDev (statistical standard deviation)
Var (statistical variance).
Aggregations are usually executed along a relationship. Create an aggregate expression by using one of the functions listed above and the child table columns detailed in the parent/child relationship reference above. For example:
AVG (Child.price)
AVG (orders2details). Price)
Aggregations can also be executed on a single table. For example, to create a rollup for a number in a column named "Price", you would use:
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.