View and filters in ADO.NET
Author: Unknown
There is a layer of object in ADO.NET to create an abstract model of any data source. Including
DataSet, DataTable, DataRow, DataView, DataRelation, etc.
All of these objects are defined
System.data namespace. They form an abstract model that allows both Windows Form, Web Form or Web Service to program the same programming interface.
In practical applications, these objects are mostly operated on data in relational databases such as SQL Server. However, they can handle various data regardless of its physical storage medium.
you can use
DataSet objects to package and associate data in each table, with DataTable classes to handle data of table types, and
The DataRow object can handle data of a certain row in the table.
These three objects are packaged in data, but there are different logical aggregation hierarchies.
DataSet is
DataTable and other combinations. and
DataBole is
DataRow and other combinations.
DataRow is a field and other combinations. However, there is no built-in filtering and sorting in these objects.
ADO.NET provides some classes to handle important aspects in this database application. In .NET Beta2, the most important objects in this regard is
DataView and
DataViewManager.
Note: DataViewManager is in Beta2. In Beta1, the corresponding function is done by DataSetView.
Custom data view
DataView class is used to represent customized
View of DataTable.
DataTable and
The relationship between DataView is to follow the famous design pattern - document / view mode, where
DataBole is a document, and
DataView is a view.
At any time, you can have multiple different views based on the same data. More importantly, you can process each view with your own properties, methods, events as a stand-alone object. This also represents a huge leap relative to ADO.
ADO RECORDSET can define a filter string. Once you have built this character, only data that matches a specific standard can be read and written.
The working principle of the Filter property is similar to the dynamic WHERE clause. It is just a simple hidden record on the same Recordset object view.
In ADO, you never have a separate view object. A filtered Recordset always the same object, but only shows less than its actual number.
If you don't need to process some different views, the above problem is not tight. The programming interface gives the Recordset or both a table or a view. But when you create it, this cannot happen at the same time. At a particular moment, the Recordset can only be a table without a string or a view that activates a filter string.
Recordset's cloning provides a better way to solve this structural restriction. As CLONATION AND THE CASE OF TABLE DOLLY, PART 1 said, cloning Recordset is relatively overhead, because it does not copy data, just copying the basic construction of Recordset. To handle two or more views of the same data, you can use two or more clones, each with a set of corresponding filter strings.
Figure 1 Different views of the same Recordset in ADO
In ADO.NET, you can use the new object model
DataView object. ADO.NET
The DataView object is used to represent a customized view of a given data sheet, but you can handle it like processing a separate object.
The DataView object retains a reference to the table and allows you to update it.
Figure 2 Operates different views of the same data table in ADO.NET
Functionally, using the ADO Recordset clone to complete the same feature using a special view object, it is to make you implement filtering, operate the selected data line, and handle multiple views at the same time. Deep DataView object
The DataView object inherits MarshalByValueComponent and implements a set of interfaces available in the data binding control.
Public Class DataViewinherits MarshalbyValueComponentimplements IbindingList, Ilist, Icollection, IEnumerable, _ITYPEDLIST, ISUPPORTINITIRIZE
The class derived from the MarshalByValueComponent is the .NET remote component, can be columnized by a value-ie serialized object to the target application domain. (For details, please see more details about .NET components)
The content in the DataView can be operated by many programming interfaces, including collection, list, and enumerators. The iBindingList interface ensures that the class provides all the necessary features that support complex and simple data bindings.
In general, the DataView object can be used to reach two purposes. First, the view is important for the DataSource domain in association DataTable objects and data binding controls. Second, it also provides a layer of packaging to the connected DataTable, allowing you to filter, sort, edit, and browse.
DataView is not the only data driver class that can remotely operate through the pass value. DataSet and DataTable also have the same capabilities, especially under interoperability scenes.
Create DataView
Public DataView (); Public DataView (DataTable);
DataView is only available in the same existing, it is likely to be available after a non-empty DATATABLE object. Typically, this connection is specified in the configuration.
DataView DV; DV = New DataView (THEDATASET.TABLES ["Employees"]);
However, you can also create a new view first, then use the Table property to be associated with the same table.
DataView DV = New DataView (); DV.TABLE = theseDataSet.tables ["Employees"]; DataView constructor allows you to get a DataView object by DataTable. If necessary, vice versa. In fact, the DEFAULTVIEW attribute of the DataTable object returns a DataView object of the table. DataView DV = DT.DEFAULTVIEW;
Once you have
DataView object, you can use its properties to create the data rows you want users to see. General, you can use the following properties:
The former of RowFilter sort can customize the rules that can be seen in the view. The latter is sorted by expressions. Of course, you can use any combination of both.
Set filtering
RowFilter is a readable and writable property that is used to read and set the expression of the table filtered.
Public Virtual String RowFilter {Get; set;}
You can make an expression in any legal combination of column names, logic, and digital operators and constants. Here are some examples:
DV.ROWFILTER = "country = 'usa'"; dv.rowfilter = "Employeeid> 5 and birthdate <# 1/31/82 #" dv.rowfilter = "Description Like '* product *'"
Let's take a look at the basic rules and operators of the filter. The filter string is a logical connection of an expression. You can connect to a shorter expression with both, or, not, or using parentheses to form a clause, specify a priority operation.
Typically, the clauses contain the column names are compared to the letters, numbers, date, or another column name. Here, a relational operator and an arithmetic operator can be used, such as> =, <,>, , *,% (molding), and the like.
If the row to select does not conveniently express by arithmetic or logical operator, you can use the IN operator. The following code shows how to choose a random row:
DV.ROWFILTER = "Employeeid in (2, 4, 5)"
You can also use wildcards * and%, and they look more useful when using the Like operator. They all represent any number of characters that can be used instead of using each other.
Please note that if there is a * or% character in the Like clause, you must enclose it with square brackets to avoid ambiguity. If it is very unfortunate, the brackets in the string also exist, then it must also be enclosed in itself. In this way, the matching statement will be as follows:
Dv.rowfilter = "description like '[[] * []] products [[] * []]"
Wildcards only allow for use at the beginning or end of the filter string, and cannot appear in the middle of the string. For example, the following statements generate runtime errors:
DV.RowFilter = "Description Like 'Prod * CT"
The string must be enclosed in single quotes, and the date type must be enclosed in a # symbol. Character value can use decimal points and scientific counting.
RowFilter also supports aggregate functions such as SUM, Count, Min, Max, and AVG. If there is no data line in the table, the function will return NULL.
Introducing the RowFilter expression, let us discuss three convenient functions: Len, IIF and Substring.
As it is named, len () returns the length of a particular expression. The expression can be a column name or other legal expression.
SubString () Returns the specified expression from a specific location, a character string of a specific length.
I like it favorite is IIF (), which is one to two values according to the value of logical expressions. The IIF is a compact expression of the IF-THEN-ELSE statement. The syntax is as follows:
IIF (Expression, IF_TRUE, IF_FALSE)
Through this function, a very complex filter string can be established. For example, assuming that you get an Employees table from the Northwind database of SQL Server, the following expressions can be selected from the EmployeeID less than 6 and lastname is an even number of characters and EmployeeIDs greater than 6 and lastname is an employee for odd characters.
IIF (Employeeid <6, Len (Lastname)% 2 = 0, Len (Lastname)% 2> 0)
The following figure shows the result (sample application will be discussed later)
Figure three on the table in Northwind to filter
The example program is a Windows® Form application, which uses two DataGrid
Controls to implement Master / Detail structure. A GRID is generated when loading, ie in SQL
Server Data Adapter completed data reading work. Please note that DATA
Adapter is introduced in beta 2, and the corresponding SqlDataSetCommand class is in the beta 1.
Row
In the above example, DataGrid must be responsible for the data line in the prevention view to refresh the user interface. This automatic mechanism is .NET
The product binds. DataGrid is a data binding control for data by DataSource properties. DataView is a data binding class that can be built
DataSource properties content.
What should I do if you want to use another control other than DataGrid? And if you don't want to use automatic data binding? How should I preprange the data line selected in the view?
DataView
Table attribute points to the corresponding data sheet, but D
Atatable does not save filter information. Therefore, the data in the pre-spectrum is not feasible. Although D
ATATABLE and
DataView is closely coupled, but they each maintain independent and perform independent features.
The following Visual Basic .NET code segment displays all the data rows in the view and adds to listbox.
Dim dv As New DataView () dv = ds.Tables ( "Employees"). DefaultViewdv.RowFilter = "employeeid> 5" ListBox1.Items.Clear () Dim buf As StringDim dr As DataRowViewFor Each dr In dvbuf = "" buf & = DR ("Lastname"). Tostring () & "& DR (" firstname "). Tostring () listbox1.items.add (buf) Next
As mentioned earlier,
DataView is an enumerable class, so you can pass it safely to the for ...each statement. The count property stores the number of data lines in the view to use in the for ....
To access a line in the view, you can use
DataRowView class.
DataRowView can be represented
DataRow's view, like
DataView expresses the DataTable custom view.
Overall, DataRow has four states: default, original, current, and proposed. These states
DataRowVersion enumeration type setting, by
RowVersion property is expressed.
The view of the DataRow can only be one of the states.
The default version of the data line is only available when the default value is set when the column is set. And the original version refers to the last call table
After AcceptChanges, data lines or snapshots are obtained from the number of numbers. The current version refers to the current data line, including all updates that occurred at the time. The proposed state exists only during the editing process of calling beginedit and endedit.
Can be accessed
DataRow's same syntax access
DataRowView. The most important attribute here is Item.
Sort and other convenient features
DataView supports the sort property that can be used to sort the contents in the view. The sort is sorted by a comma-separated column expression. By adding ASC or DESC qualified words after any column name, the fields can be arranged in the order of rising or falling. If there is no direction limit word, the default order is ASC.
DataView is an object in memory, so sorting locally without calling the database server.
RowStateFilter is another interesting attribute of DataView. It can filter content in DataTable with any predefined criteria. The following table is all the values of the DataViewRowState enumeration type:
Currentrows include all uninomited, new and modified data line deleted, all self-call acceptchange deleted data line ModifiedCurrent All Since the last call acceptchange, modifiedoriginal all self-call acceptchange post-original version of the Original version Data Row NEW All Since the last call AcceptChanges newly added line ORIGINALROWS Returns the initial data line, including unchanged and deleted unchanged all unrecognized data rows If you want to operate unconnected data, all updates are right
DataTable call
After AcceptChanges take effect. Update to a single line in call
DataRow
After AcceptChanges take effect. Similarly, these updates can be called
DataTable or
DataRow object
RejectChanges Cancel.
There are still some properties of the DataView object, such as
Allowedit, AllowDelete and
ALLOWNEW, is used to get or set the value that is allowed to update. The default values are set to TRUE, allowing any types of updates. If you want to complete the corresponding update operation when you are set to FALSE, there will be a runtime error.
DataViewManager class
Dataable object
The defaultview attribute is used to return one
DataView object, the default view of the content in the data table. It reads data in natural sequence and displays all rows in the table without using any filtering.
Themastergrid.dataSource = m_ds.tables ("employees"). DefaultView
If you need a specific view, you can sort and / or filter the DefaultView object.
m_ds.tables ("Employees"). DefaultView.Sort = "LastName" themastergrid.dataSource = m_ds.tables ("Employees"). The DefaultViewDataViewManager class is the view settings for all tables in the DataSet.
DataViewManager can be created by passing a legitimate non-empty Dataset to class.
DIM DVM AS DATAVIEWMANAGERDVM = New DataViewManager (m_ds)
You can also get directly from the DEFAULTVIEWMANAGER attribute of the DataSet object:
DIM DVM AS DataViewManager = m_ds.defaultViewManager
Important is the DataViewManager class is associated with the same DataSet. Below is another feasible method:
DIM DVM AS New DataViewManager () DVM.DataSet = m_ds
The most important attribute of DataViewManager is DataViewSettings, a collection of DataViewSetting objects.
DIM DVS AS DATAVIEWSETTINGDVS = DVM.DataViewSettings ("Employees") DVS.Sort = "Lastname"
The DataViewSetting object contains the parameter information of the table view. When binding data to data-sensitive controls, use DataViewManager instead of DataSet or DataTable to keep your view settings (filtering and sorting fields)
Themastergrid.DataSource = DVMTHEMASTERGRID.DATAMEMBER = "EMPLOYEES" is here, and the view is sorted and filtered by the Employees table in DataViewSetting. In other words, the DataViewSetting class is a cache for views of a particular table.
Next step
The above example program implements the Master / Detail structure with the filter. If you use the data binding control (such as DataGrid) in .NET, you can better achieve this. In future columns, I will discuss data relationships in memory, and how they affect the design of Master / Detail structure.
Dialogue: Do you need controls or components? There are many terms in .NET, there are many terms that can often be used. It is specifically pointed to: class, components, objects, and controls. Here, I provide a table to express the proper meaning of each term. We often treat them as synonyms.
It is necessary to keep in mind that the whole .NET architecture is composed of classes. So anything you get from it, first, it is a class. In the .NET environment, the controls and components are not the same class. As for objects, it can be considered an example of running .NET class.
Components are a special class that implements the IComponent interface or derived from a class that implements the IComponent interface.
The control is a component that provides a user interface function. In the .NET architecture, you can find two types of controls: the client's Windows Forms control and ASP.NET Server controls.
The IComponent interface is included in the IDisposable interface and provides a certain way to clear resources.
Public Interface IComponentinherits IDisposable
This method and standard .NET garbage collector can be selected by this release resource. You define a Dispose method by implementing IDisposable. This is programmed, you can explicitly release the object without waiting for the garbage collector to process.
The .NET component knows how to connect in series in the application domain. This has two methods: Basic functions are built in the MarshalByValueComponent class by reference or via values. .NET Component class, in fact, Idisposable, but directly or indirectly inherits one of the two classes described above.
The application domain is a lightweight process. Column set objects are column set objects means that the Proxy / Stub entity pair is created and processes remote calls. The value means that the serialization of the object is passed through the boundaries of the domain.
The control is a more special object, which also provides user interface elements. Of course, a control is always a Component, but it is not necessarily true.
