[C #] dataview usage --
In actual programming projects, we often encounter the following situation: datatable is not in the database, or datatable has not been written to the database, or the able read from the database has been changed locally, no data is written back to the database (other changes may need to be made). In these cases, to query the data in the datatable, a powerful SQL language is useless.
Some. net Program This type of query problem can be solved by creating temporary tables in the database. In my opinion, this method is not feasible, as long as it is used. the powerful functions of the dataview class provided by the net class library (mainly using its rowfilter attribute) can easily solve such query problems. The following is a specific example to illustrate how to use the rowfilter attribute of dataview to query data without using SQL statements.
Step 1: create a C # ASP. NET project. Compile a function makedatatable () to generate a datatable (), Code As follows:
Private datatable maketable ()
{
// Generate a able
System. Data. datatable mydatatable = new datatable ("local data table ");
Datacolumn mydatacolumn;
Datarow mydatarow;
// Generate the data column ID, product name, and product 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 = "item name ";
Mydatatable. Columns. Add (mydatacolumn );
Mydatacolumn = new datacolumn ();
Mydatacolumn. datatype = system. type. GetType ("system. Decimal ");
Mydatacolumn. columnname = "product price ";
Mydatatable. Columns. Add (mydatacolumn );
// Add data rows to a data table
Mydatarow = mydatatable. newrow ();
Mydatarow ["ID"] = 1;
Mydatarow ["item name"] = "football ";
Mydatarow ["product price"] = 57.5;
Mydatatable. Rows. Add (mydatarow );
Mydatarow = mydatatable. newrow ();
Mydatarow ["ID"] = 2;
Mydatarow ["Product Name"] = "basketball ";
Mydatarow ["product price"] = 64.5;
Mydatatable. Rows. Add (mydatarow );
Mydatarow = mydatatable. newrow ();
Mydatarow ["ID"] = 3;
Mydatarow ["item name"] = "tennis ";
Mydatarow ["product price"] = 6.5;
Mydatatable. Rows. Add (mydatarow );
Mydatarow = mydatatable. newrow ();
Mydatarow ["ID"] = 4;
Mydatarow ["Product Name"] = "tennis rackets ";
Mydatarow ["product price"] = 388.5;
Mydatatable. Rows. Add (mydatarow );
// Return the data table
Return mydatatable;
}
Step 2: Add a datagrid1 on the aspx front-end page and write the following code in page_load of the background code:
If (! This. ispostback)
{
Session ["table"] = maketable ();
Datagrid1.datasource = (datatable) session ["table"];
Datagrid1.databind ();
}
The data in the table displayed in the DataGrid is displayed on the page.
Id product name product price
1 football 57.5
2 basketball 64.5
3 tennis 6.5
4 tennis rackets 388.5
Step 3: Add label1, textbox1, and button1.
Step 4: Add the following code to the query button button1 click event:
Datatable dt = (datatable) session ["table"];
// Create a data view for the data table
Dataview DV = new dataview (DT );
// Use the rowfilter attribute for fuzzy search
DV. rowfilter = "product name like '%" + textbox1.text. Trim () + "% '";
Datagrid1.datasource = DV;
Datagrid1.databind ();
After running the page, enter "Net" in textbox1 and press the query button. The query result displayed in the DataGrid is as follows:
Id product name product price
3 tennis 6.5
4 tennis rackets 388.5
Step 5: Add a label2 control, two textbox controls-textbox1 and textbox2, and a "query" button, button2, as shown in, it is used to query the price range entered by the user in the product.
Step 6: Add the following code for the button2 button in the background code by clicking the event:
Datatable dt = (datatable) session ["table"];
Dataview DV = new dataview (DT );
DV. rowfilter = "product price >=" + textbox2.text. Trim () +" and product price <= "+ textbox3.text. Trim ();
Datagrid1.datasource = DV;
Datagrid1.databind ();
The user enters the price range in the two text boxes. After 10, 70, the query results displayed in datagrid1 are as follows:
Id product name product price
1 football 57.5
2 basketball 64.5
It can be seen that the rowfilter attribute of dataview can fully implement the functions implemented by the SELECT statement of SQL statements.
The query statement in rowfilter is similar to the SELECT statement syntax and function in SQL statements. The following is a description of the rowfilter query statement syntax in msdn:
//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// /////////////////
User-Defined values can be used in expressions that compare column values. The string value should be enclosed in single quotes. The date value should be placed in the pound sign. Decimal and scientific notation are allowed for numeric values. For example:
"Firstname = 'john '"
"Price <= 50.00"
"Birthdate <#1/31/82 #"
For columns that contain enumerated values, convert the values to integer data types. For example:
"Enumcolumn = 5"
Operator
Concatenation is allowed when the and, or, and not operators are used. You can use parentheses to combine clauses and force priorities. The and operator takes precedence over other operators. For example:
(Lastname = 'Smith 'or lastname = 'Jones') and firstname = 'john'
When creating a comparison expression, you can use the following operators:
<
>
<=
> =
<>
=
In
Like
The following Arithmetic Operators are also supported in expressions:
+ (Plus)
-(Minus)
* (Multiplication)
/()
% (Module)
String Operators
To connect strings, use + characters. Whether string comparison is case sensitive is determined by the casesensitive attribute value of the dataset class. However, you can use the casesensitive attribute of the datatable class to override this value.
Wildcard
In the like comparison, * and % can be exchanged as wildcards. If the string in the like clause contains * or %, these characters are escaped using brackets. If the clause contains brackets, the brackets are used to escape the brackets (for example, [[] or []). Wildcard characters are allowed at the beginning and end of a pattern, at the end of a pattern, or at the beginning of a pattern. For example:
"Itemname like '* product *'"
"Itemname like '* product '"
"Itemname like 'product *'"
Wildcards are not allowed between strings. For example, 'TE * XT 'is not allowed '.
Parent/child relationship reference
By adding parent to the column name, you can reference the parent table in the expression. For example, parent. price references the column named Price in the parent table.
By adding a child to the column name, you can reference columns in the child table in the expression. However, because the child relationship can return multiple rows, the reference to the Child column must be included in the aggregate function. For example, sum (child. Price) returns the sum of the columns named Price in the subtable.
If a table has multiple sub-tables, the syntax is child (relationname ). For example, if a table has two sub-tables named MERs and orders respectively, The datarelation object is named customers2orders and the reference is:
AVG (Child (customers2orders). Quantity)
Aggregation
The following aggregation types are supported:
Sum (SUM)
AVG (average)
Min (minimum)
Max (maximum)
Count)
STDev (statistical standard deviation)
VaR (statistical variance ).
Aggregation is usually executed along the link. Create an aggregate expression by using one of the functions listed above and the child table column detailed in the parent/child relationship reference above. For example:
AVG (child. Price)
AVG (Child (orders2details). Price)
Aggregation can also be performed on a single table. For example, to create a summary for a number in a column named "price", use:
Sum (price)
