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 data becomes () {// generates DataTable system.data.DataTable myDataTable = new DataTable ("Local Data Table"); Datacolumn MyDatacolumn; DataRow MyDataRow; // Generate column data ID, an item name, commodity prices 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 = "trade name"; myDataTable.Columns.Add (myDataColumn); myDataColumn = new DataColumn (); myDataColumn.DataType = System.Type.GetType ( "System.Decimal"); myDataColumn.ColumnName = "commodity price"; myDataTable.Columns.Add (myDataColumn); // data Table Add Data Row MyDataRow = MyDataTable.NewRow (); MyDataRow ["ID"] = 1; MyDataRow [Product Name "] =" Football "; MyDataRow [" Product Price "] = 57.5; MyDataTable.Rows.Add (MyDataRow ); mydatarow = mydataable.newrow (); mydatarow ["id"] = 2; MyDATAROW ["Product Name"] = "Basketball "; mydatarow [" commodity price "] = 64.5; mydataable.Rows.add (MyDATAROW); MyDataRow = mydataable.newrow (); MyDataRow [" ID "] = 3; MyDataRow [Product Name" = "Tennis"; MyDATAROW ["Product Price"] = 6.5; MyDataBLE.Rows.Add (MyDATAROW); MyDataRow = myDataTable.NewRow (); MyDataRow ["ID"] = 4; MyDataRow [Product Name "] =" Tennishot "; MyDatarow ["Commodity Price"] = 388.5; MyDATATABLE.ROWS.ADD (MyDATAROW); // Return to Data Table Return MyDATABLE;} Step 2: Add a DataGrid1 in the ASPX front page,
Write the following code in the back_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 Product Price 1 Football 57.5 2 Basketball 64.5 3 Tennis 6.5 4 Tennis Shooting 388.5 Step 3: Add an ASPX Front Desk page to query Label1, TextBox1, and Button1. As shown in the following figure: Step 4: Add the following code in 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 property to make a fuzzy query DV.RowFilter = "Product Name Like '%" TextBox1.Text.trim () "%'"; DataGrid1.datasource = DV; DataGrid1.Database (); after running the page After TextBox1, after the query button, the query results displayed by the DataGrid are as follows: ID Product Name Product Price 3 Tennis 6.5 4 Tenniser 388.5 Step 5: Add a Label2 Control on the ASPX front page, two TextBox controls - TxtBox1 And TextBox2, and a "query" button, button2, as shown in the following image, used to query the price range of the user input in the goods. Step Six: Click the following code in the background code: DataTable DT = (DataTable) session ["Table"]; DataView DV = New DataView (dt); dt); dv.rowfilter = "Product price> =" TextBox2.text.trim () "AND commodity price <=" textBox3.text.trim (); dataGrid1.datasource = DV; dataGrid1.database (); user Input price range, 10, After 70, the query results shown in DataGrid1 are as follows: ID Product Name Product Price 1 Football 57.5 2 Basketball 64.5 Visible The syntax and role of statements are extremely similar, and the following is a syntax description of the RowFilter query statement in MSDN: / User-defined values can be used in the 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. For example: "firstname = 'john'" "price <= 50.00" "Birthdate <# 1/31/82 #" For columns including enumeration values, forced values to integer data types.
For example: "EnumColumn = 5" operator uses the Boolean and the OR and NOT operators to allow in series. Brackets can be used to combine clauses and mandatory priorities. The And operator takes precedence over other operators. For example: (LastName = 'smith' or lastname = 'Jones') and firstname = 'john' When you create a comparison expression, the following operators are allowed: <> <=> = <> = in Like is also Support the following arithmetic operators: (plus) - (reduction) * (multiply) / (divided) string Operator To connect the 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. In the LIKE comparison, both * and% can be exchanged 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. For example: "ItemName Like '* product *'" "ItemName Like '* product'" "ItemName Like 'Product *'" is not allowed using wildcards in the middle of the string. For example, 'TE * XT' is not allowed. The parent / sub-relational reference can be referenced in the expression by adding Parent in front of the column name. 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, the reference will be: avg (Custom (Customers2RDERS) .quantity) aggregate supports the following aggregation types: SUM And) AVG (average) min (minimum) Max (maximum) count (count) STDEV (statistical criteria) 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. For example: AVG (Child.price) AVG (Child.Price) .price) aggregate can also be performed on a single table.
