Querying data in a DataTable without SQL statements

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

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 = "Commodity name";

MYDATATABLE.COLUMNS.ADD (Mydatacolumn);



Mydatacolumn = new DataColumn ();

Mydatacolumn.datatype =system.type.gettype ("System.Decimal");

Mydatacolumn.columnname = "commodity price";

MYDATATABLE.COLUMNS.ADD (Mydatacolumn);



Add data rows to a datasheet

MyDataRow = Mydatatable.newrow ();

mydatarow["id"] = 1;

mydatarow["Commodity name" = "Football";

mydatarow["commodity price" = 57.5;

MYDATATABLE.ROWS.ADD (MyDataRow);



MyDataRow = Mydatatable.newrow ();

mydatarow["id"] = 2;

mydatarow["Commodity Name" = "basketball";

mydatarow["commodity price" = 64.5;

MYDATATABLE.ROWS.ADD (MyDataRow);



MyDataRow = Mydatatable.newrow ();

mydatarow["id"] = 3;

mydatarow["Commodity name" = "Tennis";

mydatarow["commodity price" = 6.5;

MYDATATABLE.ROWS.ADD (MyDataRow);



MyDataRow = Mydatatable.newrow ();

mydatarow["id"] = 4;

mydatarow["Product Name" = "Tennis Racket";

mydatarow["commodity price" = 388.5;

MYDATATABLE.ROWS.ADD (MyDataRow);



Return data table

return mydatatable;

}






Step Two: Add a DataGrid1 to the ASPX foreground page and write the following code in the Page_Load of the background code:

if (!this. IsPostBack)

{

session["Table"]=maketable ();

Datagrid1.datasource= (DataTable) session["Table"];

Datagrid1.databind ();

}




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:

DataTable dt= (DataTable) session["Table"];

DataView dv=new DataView (DT);

Dv. rowfilter= "Commodity price >=" +textbox2.text.trim () + "and commodity price <=" +textbox3.text.trim ();

DATAGRID1.DATASOURCE=DV;

Datagrid1.databind ();




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:



Sum (Price)