Net view and filter (defaultview and rowfilter)

Ado. Net has an object used to create an abstract model of any data source. These include dataset, datatable, datarow, dataview, and datarelation.
All these objects are defined in the system. Data namespace. They form an abstract model so that the same programming interface can be used for both Windows form, web form, and Web Service programming.
In practical applications, most of these objects operate on data in relational databases such as SQL Server. However, they can process various types of data regardless of their physical storage media.
You can use a DataSet object to package and associate data in each table, and use the able class to Process Table-type data, while the datarow object can process data in a row in a table.
All three objects are packaged with different logical aggregation layers. Dataset is a combination of datatable and others. Datatable is a combination of datarow and others. Datarow is a combination of fields and other fields. However, these objects do not have built-in filtering and sorting functions.
ADO. NET provides some classes to handle important aspects of this database application. In. Net beta2, the two most important objects are dataview and dataviewmanager.
Note: dataviewmanager is unique in beta2. In beta1, the corresponding functions are completed by datasetview.
Custom Data View
The dataview class is used to represent the views of a custom datatable. The relationship between able and dataview follows the famous design pattern-document/view pattern, where datatable is a document and dataview is a view.
At any time, you can have multiple views based on the same data. More importantly, you can process each view with its own set of attributes, methods, and events as an independent object. This also represents a huge leap over ADO.
Ado recordset can define filter strings. Once you set up this character, only data that matches a specific standard can be read and written. The filter attribute works similarly to the dynamic where clause. It simply hides some records in the same recordset object view.
In ADO, you never have an independent view object. A filtered recordset is always the same object, except that the number of records displayed is smaller than the actual number of records.
If you do not need to process different views at the same time, the above problem does not matter. The programming interface provides A recordset function that can be a table or a view. However, this cannot happen at the same time during creation. At a specific time point, the recordset can only be a table without a filter string or a view with a filter string activated.
The clone of recordset provides a better solution to this structure restriction. As clonation and the case of table Dolly, as described in Part 1, cloning A recordset is less costly because it does not copy data, but only copies the basic structure of A recordset. To process two or more views of the same data, you can use two or more clones to separate a set of filter strings.

Figure 1 process different views of the same recordset In ADO
In ADO. net, you can use the dataview object provided by the new object model. The dataview object of ADO. NET is used to represent a custom view of a given data table, but you can process it like a separate object. The dataview object retains a reference to the table and allows updates to it.

Figure 2 operations on different views of the same data table in ADO. net
In terms of function, using ADO recordset To clone and using special view objects is the same function, allowing you to filter and operate the selected data rows, and process multiple views at the same time.
In-depth dataview object
The dataview object inherits the marshalbyvaluecomponent and implements a set of interfaces to make it available in the data binding control. Public class dataview
Inherits extends albyvaluecomponent
Implements ibindinglist, ilist, icollection, ienumerable ,_
Itypedlist, isupportinitialize
The class derived from marshalbyvaluecomponent is A. NET remote component. You can use values to set columns, that is, serialize objects to the target application domain. (For more information about the. NET Component, see the following section)
Content in dataview can be operated through many programming interfaces, including collections, lists, and enumerators. The ibindinglist interface ensures that this class provides all the necessary features to support complex and simple data binding.
In general, the dataview object can be used for two purposes. First, the view is very important for the datasource field in the associated able object and data binding control. Second, it also provides a layer of packaging for the connected datatable, allowing you to filter, sort, edit, and browse.
Dataview is not the only data driver class that can be remotely operated by passing values. Dataset and datatable have the same capabilities, especially in interoperability scenarios.
Create dataview public dataview ();
Public dataview (datatable );
Dataview is available only when it is connected to an existing, possibly non-empty able object. Generally, this connection is specified during construction.
Dataview DV;
DV = new dataview (thedataset. Tables ["employees"]);
However, you can create a new view and associate it with the table by using the table attribute.
Dataview DV = new dataview ();
DV. Table = thedataset. Tables ["employees"];
The dataview constructor allows you to get a dataview object from the able. If needed, or vice versa. In fact, the defaultview attribute of the datatable object returns a dataview object for the table.
Dataview DV = DT. defaultview;
Once you have a dataview object, you can use its attributes to create a data row set that you want users to see. Generally, you can use the following attributes:
Rowfilter
Sort
The former allows you to customize rules for matching visible data in the view. The latter uses expressions for sorting. Of course, you can use any combination of the two.
Rowfilter is a read/write attribute used to read and set table filter expressions.
Public Virtual string rowfilter {Get; set ;}
You can use any legal combination of column names, logical and numeric operators, and constants to form an expression. The following are examples: DV. rowfilter = "Country = 'usa '";
DV. rowfilter = "employeeid> 5 and birthdate DV. rowfilter =" description like '* product *'"
Let's take a look at the basic filter rules and operators.
The filter string is the logical connection of the expression. You can use and, or, not to connect to a short expression, or you can use parentheses to form a clause to specify a priority operation.
The clauses that usually contain column names are compared with letters, numbers, dates, or other column names. Here, you can use Relational operators and arithmetic operators, such as> =, +, *, % (Modulo), and so on.
If the row to be selected cannot be easily expressed by arithmetic or logical operators, you can use the in operator. The following code selects a random row:
DV. rowfilter = "employeeid in (2, 4, 5 )"
You can also use wildcards * and %, which are more useful when used together with the like operator. They all represent any number of characters and can be used with each other.
Note that if the like clause already contains * or % characters, you must enclose it in square brackets to avoid ambiguity. If, unfortunately, the Chinese brackets of the string also exist, they must also be enclosed. In this way, the matching statement is as follows:
DV. rowfilter = "description like '[[] * [] product [[] * []"
Wildcard characters can only be used at the beginning or end of a filter string, but cannot appear in the middle of a string. For example, the following statement produces a runtime error:
DV. rowfilter = "description like 'prod * CT"
The string must start with single quotes, and the date type must start with the # symbol. Numeric values can use decimal points and scientific notation.
Rowfilter also supports aggregate functions, such as sum, Count, Min, Max, and AVG. If there are no data rows in the table, the function returns NULL.
At the end of introducing the rowfilter expression, let's discuss three convenient functions: Len, IIF, and substring.
As its name, Len () returns the length of a specific expression. This expression can be a column name or another legal expression.
Substring () returns the character substring of a specified expression starting from a specific position.
I like IIF () most, which has one or two values according to the logical expression value. IIF is a compact expression of the if-then-else statement. Syntax:
IIF (expression, if_true, if_false)
This function can be used to create complex filter strings. For example, assume that you obtain the Employees table from the SQL server's northwind database, the following expressions can be used to select employees whose employeeid is less than 6 and whose lastname is an even number of characters and whose employeeid is greater than 6 and whose lastname is an odd number of characters.
IIF (employeeid0)
Result displayed (the sample application will be discussed later)

Figure 3 filter the tables in northwind
The example program is a Windows form application, which uses two DataGrid
Control to implement the master/detail structure. A grid is generated when it is loaded, that is
After the server data adapter completes Data Reading. Please note that data
The adapter is introduced in Beta 2 and corresponds to the sqldatasetcommand class in Beta 1.

Pre-arranged View
In the preceding example, the DataGrid must be used to pre-arrange data rows in the view to refresh the user interface. The automatic mechanism is. net.
The product of data binding. The DataGrid is a data binding control that obtains data through the datasource attribute. Dataview is a data binding class that can be used to construct the content of the datasource attribute.
What should you do if you want to use another control other than the DataGrid? What if you don't want to use automatic data binding? How should we pre-arrange the selected data rows in the view?
The table attribute of dataview points to the corresponding data table, but the datatable does not save the filtering information. Therefore, the data in the pre-arranged table is destined to be unfeasible. Although datatable and dataview are closely related, they are independent and execute independent functions.
The following code snippet shows how to traverse all data rows in the view and add them to ListBox.
Dataview DV = new dataview ();
DV = Ds. Tables ["employees"]. defaultview;
DV. rowfilter = "employeeid> 5 ";
Listbox1.items. Clear ();
String Buf = new string ();
Datarowview DR = new datarowview ();
Foreach (DR in DV)
{
Buf = "";
Buf & = DR ("lastname"). tostring () & "," & DR ("firstname"). tostring ();
Listbox1.items. Add (BUF );
}
Note: I am not familiar with this article in VB. NET,
I don't know how to change this sentence. How can I use the & = symbol? Please check it out later. Buf & = DR ("lastname"). tostring () & "," & DR ("firstname"). tostring ();
As mentioned above, dataview is an enumeration class, so you can safely pass it to the for... each statement. The Count attribute stores the number of rows in the view for use in the for... next loop.
To access a row in the view, you can use the datarowview class. Datarowview can be used to represent datarow views, just as dataview expresses datatable custom views.
In general, datarow can have up to four States: Default, original, current, and proposed. These statuses are set by the datarowversion Enumeration type and expressed by the rowversion attribute.
Datarow views can only be in one of the states.
The default version of a data row is available only when the default value is set during column construction. The initial (original) version means that after the acceptchanges of the last called table, the data row or snapshot is obtained from the number drama source. The current version refers to the current data row, including all updates that occurred at the time. The proposed status only exists when you call beginedit and endedit.
Datarowview can be accessed using the same datarow syntax. The most important attribute here is item.
Sorting and other convenient features
Dataview supports the sort attribute and can be used to sort the content in the view. Sort is sorted by column name expressions separated by commas. By adding ASC or DESC restrictions to any column name, you can arrange the fields in the ascending or descending order. If no direction limiting word exists, the default order is ASC.
Dataview is an object in memory, so it is sorted locally without calling the database server.
Rowstatefilter is another interesting attribute of dataview. It can use any predefined criteria to filter the content in the datatable. The following table lists all values of the dataviewrowstate Enumeration type: currentrows includes all unupdated, new, and modified data rows.
All data rows deleted after the last call of acceptchanges
Modifiedcurrent: All data rows modified since the last call of acceptchanges
Modifiedoriginal: All data rows of the original version after the last call of acceptchanges
New all newly added rows since the last acceptchanges call
Originalrows returns the initial data row, including the unchanged and deleted
Unchanged all unupdated data rows
If you want to operate on non-connected data, all updates take effect after you call acceptchanges for the datatable. Updates to a single row take effect after the datarow acceptchanges is called. Similarly, these updates can be canceled by calling the rejectchanges of the able or datarow object.
The dataview object also has some attributes, such as allowedit, allowdelete, and allownew, to get or set whether to allow update. They are set to true by default and allow updates of any type. If you want to complete the update operation when the flag is set to false, a runtime error will occur.
Dataviewmanager class
The defaultview attribute of the datatable object is used to return a dataview object, which is used as the default view of the content in the data table. It reads data in a natural order and displays all rows in the table without any filtering.
Themastergrid. datasource = m_ds.tables ("employees"). defaultview
If you need data-specific views, you can sort and/or filter defaview view objects directly.
M_ds.tables ("employees"). defaultview. Sort = "lastname"
Themastergrid. datasource = m_ds.tables ("employees"). defaultview
The dataviewmanager class is used to store view settings for all tables in the dataset.
You can create a dataviewmanager by passing a valid non-empty dataset to the class constructor.
Dim DVM as dataviewmanager
DVM = new dataviewmanager (m_ds)
You can also obtain the following information through the defaultviewmanager attribute of the DataSet object:
Dim DVM as dataviewmanager = m_ds.defaultviewmanager
The important thing is that the dataviewmanager class is associated with the same dataset. The following is another feasible method:
Dim DVM as new dataviewmanager ()
DVM. dataset = m_ds
The most important attribute of dataviewmanager is dataviewsettings, a set of dataviewsetting objects.
Dim DVS as dataviewsetting
DVS = DVM. dataviewsettings ("employees ")
DVS. Sort = "lastname"
The dataviewsetting object contains the parameter information of the table view. When you bind data to a data-sensitive control, you can use dataviewmanager instead of dataset or datatable to retain your view settings (filter and sort fields)
Themastergrid. datasource = DVM
Themastergrid. datamember = "employees"
Here, the view Automatically sorts and filters the Employees table specified by dataviewsetting. In other words, the dataviewsetting class is a cache for the view of a specific table.
Next step
In the preceding example, the program uses filter to implement the master/detail structure. If you use a special data binding control (such as DataGrid) in. net, this can be better achieved. In future columns, I will discuss the data relationships in the memory and how they affect the design of the master/detail structure.
Dialog: Do you need controls or components?
There are many terminologies in. Net that can be replaced frequently. It is particularly pointed out that: classes, components, objects and controls. Here, I provide a table to express the proper meaning of each term. We often treat them as synonyms.
Note that the entire. NET architecture is composed of classes. So what you get from it is first a class. In the. NET environment, controls and components are not of the same type. Objects can be considered as running. Net class instances.
A component is a special class that implements the icomponent interface or a class derived from the icomponent interface.
A widget is a component that provides user interface functions. In the. NET architecture, you can find two types of controls: the Windows Forms control of the client and the ASP. NET Server Control.
The icomponent interface is included in the idisposable interface and provides a definite method to clear resources.
Public interface icomponent
Inherits idisposable

You can select either this method to release resources or the standard. Net garbage collector. By implementing idisposable, you define a dispose method. By programming, you can release objects explicitly without waiting for the garbage collector to process them.
The. NET Component knows how to concatenate in the application domain. There are two methods: By referencing or passing through a value, the basic functions are built in the marshalbyrefcomponet and marshalbyvaluecomponent classes respectively .. NET Component class, in fact, implements idisposable, but directly or indirectly inherits one of the above two classes.
The application domain is a lightweight process. By referencing a column set object, it means that the proxy/stub object pair will be created and processed for remote calls. The pass-through value means that the serialized copy of the object passes beyond the boundary of the domain.
Controls are more specialized objects and provide user interface elements. Of course, a control is always a component, but the opposite is not necessarily true.
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 programmers use methods such as creating temporary tables in the database to solve such queries. 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. The Code is 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. As shown in:
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 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" birthdate forcibly converts a value to an integer data type for a column containing enumerated values. 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)
Original article: http://www.xrss.cn/Dev/DotNet/20078715492.Html
The following is an example of csdn:
Datarow [] rows1 = DT. Select ("Password = 'admins '");
Datarow [] rows2 = DT. Select ("Password = 'admin '");
1. All data is selected and stored in the dataset variable a_ds at a time.
2. Define filter conditions
Dataview rowfilter = new dataview (a_ds.tables [0]);
Rowfilter. rowfilter = "Password = 'admins '";
Rowfilter. rowstatefilter = dataviewrowstate. originalrows;
3. Put the filtered data in a new able.
Datatable dt = rowfilter. totable ();
To process data in Dataset: You can define the dataset variable B _ds.
B _ds.tables.clear ();
B _ds.tables.add (DT );
If you want to bind the data source to the datagridview, you can change the data source of the datagridview to DT.
Datagridview. datasource = DT;
4. If you want to filter the results again, use the method 2. Change a_ds.tables [0] to DT.
5. In the filtering, it is best not to change the value of dataset variable a_ds. Each filtering changes the intermediate variable DT. After filtering, the data source of the datagridview is rebound.