In actual programming engineering, it is often encountered: DataTable is not in the database, or DataTable has not been written to the database, or the DataTable read from the database is localized locally, and there is no writing database (may also Be other changes), in these cases, to query the data in DataTable, the powerful SQL language is not used to use.
Some .NET programmers take a program to establish a temporary table in the database to solve such query problems. And I think this method is not feasible. In fact, as long as the powerful function of the DataView class provided in the .NET class library (mainly using its RowFilter property), you can easily solve such query problems. Let's take a specific example to explain how to query with the SQL statement to query with the DataView RowFilter property.
Step 1: Create a C # ASP.NET project. First write a function MakeDataTable (), the code is as follows:
Private DataTable maketable ()
{
/ / Generate DataTable
System.Data.DataTable mydataable = new dataable ("Local Data Table");
Datacolumn mydatacolumn;
DataRow MyDataRow;
/ / Generate data column ID, product name, commodity price
MyDatacolumn = new datacolumn ();
MyDataColumn.Datatype = system.type.gettype ("System.Int32");
mydatacolumn.columnname = "id";
mydatacolumn.readonly = true;
mydatacolumn.unique = true;
MyDataTable.columns.add (MyDatacolumn);
MyDatacolumn = new datacolumn ();
MyDatacolumn.DataType = system.type.gettype ("system.string");
MyDataColumn.columnname = "Product Name";
MyDataTable.columns.add (MyDatacolumn);
MyDatacolumn = new datacolumn ();
MyDataColumn.DataType = system.type.gettype ("System.Decimal");
MyDataColumn.columnname = "commodity price";
MyDataTable.columns.add (MyDatacolumn);
/ / Add a data line for the data sheet
mydatarow = myDataBLE.NEWROW ();
MyDaTarow ["ID"] = 1;
MyDATAROW ["Product Name"] = "Football";
MyDATAROW ["commodity price"] = 57.5;
MyDataTable.Rows.Add (MyDATAROW);
mydatarow = myDataBLE.NEWROW ();
MyDATAROW ["ID"] = 2;
MyDATAROW ["Product Name"] = "Basketball";
MyDATAROW ["product price"] = 64.5;
MyDATABLE.ROWS.ADD (MyDATAROW); MyDATAROW = myDataTable.newrow ();
MyDATAROW ["ID"] = 3;
MyDATAROW ["Product Name"] = "Tennis";
MyDATAROW ["commodity price"] = 6.5;
MyDataTable.Rows.Add (MyDATAROW);
mydatarow = myDataBLE.NEWROW ();
MyDATAROW ["ID"] = 4;
MyDATAROW ["product name"] = "tennis racket";
MyDATAROW ["Product Price"] = 388.5;
MyDataTable.Rows.Add (MyDATAROW);
// Return to the data sheet
Return mydataable;
}
Step 2: Add a DataGrid1 in the ASPX front page, and write the following code in the bottom_load of the background code:
IF (! this.ispostback)
{
Session ["Table"] = MakeTable ();
DataGrid1.datasource = (data) session ["Table"];
DataGrid1.databind ();
}
At this point, you can see the data in the table shown in the DataGrid.
Id
product name
Commodity price
1
football
57.5
2
basketball
64.5
3
tennis
6.5
4
Tennis racket
388.5
Step 3: Add an ASPX front page to query Label1, TextBox1, and Button1. As shown below:
Step 4: Add the following code to the query button Button1 Click the following code:
DataTable DT = (DATATABLE) Session ["Table"];
/ / Create a data view for the data table
DataView DV = New DataView (DT);
// Use the ROWFILTER attribute to make a fuzzy query
DV.ROWFILTER = "Product Name Like '%" TextBox1.Text.trim () "%'";
DataGrid1.datasource = DV;
DataGrid1.databind ();
After running the page, enter "Net" in TextBox1. After the query button, the query results displayed by the DataGrid are as follows:
Id
product name
Commodity price
3
tennis
6.5
4
Tennis racket
388.5
Step 5: Add a Label2 control on the ASPX front page, two TextBox control -TextBox1 and TextBox 2, and a "query" button, button2, as shown in the following image, used to query the price range in the price of the user input in the product.
Step Six: Click the following code in the background code to add the following code:
DataTable DT = (DATATABLE) Session ["Table"];
DataView DV = New DataView (DT);
DV.ROWFILTER = "Product price> =" textBox2.text.trim () "AND commodity price <=" textBox3.Text.trim ();
DataGrid1.datasource = DV;
DataGrid1.Database (); User Enter the price range in the two text boxes, 10, 70, the query shown in DataGrid1 is as follows:
Id
product name
Commodity price
1
football
57.5
2
basketball
64.5
It can be seen that the functionality implemented by the SQL statement SELECT statement with DataView's ROWFILTER attribute
The syntax and role of the query statement in the ROWFILTER is very similar to the syntax and role of the SELECT statement in the SQL statement. The following is a syntax description of the RowFilter query statement in MSDN:
/
User-defined values can be used in an expression that compares the column value. The string value should be placed in single quotes. The date value should be placed in the pound symbol (#). For values, the decimal and scientific counting is allowed. E.g:
"Firstname = 'john'"
"Price <= 50.00"
Birthdate <#
1/31/82
# "
For columns containing enumeration values, enforce values to integer data types. E.g:
"Enumcolumn = 5"
Operator
Allow in series when using Boolean and NOT operators. Brackets can be used to combine clauses and mandatory priorities. The And operator takes precedence over other operators. E.g:
(LastName = 'smith' or lastname = 'Jones') And firstname = 'john'
When you create a comparison expression, the following operators are allowed:
<
>
<=
> =
<>
=
In
Like
The following arithmetic operators are also supported in the expression:
(Plus)
-(Less)
* (Multiplied)
/(except)
% (Modulus)
String operator
To connect a string, use characters. String compares whether to case case in case case is determined by the value of the CaseSensitive property of the DataSet class. However, this value can be rewritten with the CaseSensitive property of the DataTable class.
Wildcard
In the LIKE comparison, both * and% can be interchangeably as a wildcard. If the string in the Like clause contains * or%, these character applications are escaping them. If there is a middle bracket in the clause, the bracket character is used to escape (for example, [[[] or []]). At the beginning and end of the mode, or at the end of the mode, or the wildcard is allowed at the beginning of the mode. E.g:
"ItemName Like '* Product *'"
"ItemName Like '* Product'"
"Itemname like 'product *'"
The wildcard is not allowed in the middle of the string. For example, 'TE * XT' is not allowed.
Father / sub-relations reference
By adding a Parent in front of the column name, you can reference the parent table in the expression. For example, a column of a parent table named price.
By adding a child in front of the column name, you can reference the columns in the subthech in the expression. However, since the child relationship can return multiple lines, it must include references to sublining in the aggregation function. For example, SUM (Child.price) will be the sum of the columns named Price in the returned sub table.
If a table has multiple sub-tables, the syntax is: Child (Relationname). For example, if a table has two sub-tables, their names are Customers and Orders, and the DataRelelation object is named Customers 2RDERS, references to: avg (Customers2RDers) .quantity)
polymerization
Support the following aggregate types:
Sum (seeking)
AVG (average)
MIN (minimum)
Max (max)
Count (count)
StDEV (statistical deviation)
VAR (statistical variance).
The polymerization is usually performed along the relationship. Create a polymeric expression by using one of the functions listed above and the subtraces detailed in the "Father / Sub Refrection Reference" above. E.g:
AVG (child.price)
AVG (Orders2details) .price) .price
Aggregation can also be performed on a single table. For example, if you want to create a summary of numbers in columns called "Price", use:
SUM (Price)
