Views and filters in ADO. net

Source: Internet
Author: User
Tags arithmetic operators
Views and filters in ADO. net
Author: Unknown, ADO. NET has an object used to create an abstract model for any data source. Including Dataset, datatable, datarow, dataview, datarelationAnd so on.

All these objects are defined inSystem. DataNamespace. 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 useDatasetObject To package and associate data in each table, and use the able class to Process Table-type data.DatarowAn object can process data of a row in a table.
All three objects are packaged with different logical aggregation layers.DatasetYesDatatableAnd other combinations. WhileDatatableYesDatarowAnd other combinations.DatarowIs 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 in this respect areDataviewAndDataviewmanager.
Note:: Dataviewmanager is unique in beta2. In beta1, the corresponding functions are completed by datasetview.

Custom Data View
The dataview class is used to indicate customDatatable.DatatableAndDataviewThe relationship follows the famous design pattern-document/view pattern, whereDatatableIs a document, andDataviewIs 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.FilterThe working principle of attributes is similar to that of dynamic where clauses. 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 useDataviewObject. Ado. netDataviewAn object is used to represent a custom view of a given data table, but you can process it like a single object.DataviewThe 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 haveDataviewObject, you can use its attributes to create the data row set you want the user 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.

Set Filter RowfilterIs 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. 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 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 (employeeid <6, Len (lastname) % 2 = 0, Len (lastname) % 2> 0)

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 throughDatasourceProperty to obtain data binding controls. Dataview is a data binding class that can be builtDatasourceAttribute content.

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?

DataviewTableAttribute pointing to the corresponding data table, but dAtatableThe filter information is not saved. Therefore, the data in the pre-arranged table is destined to be unfeasible. Although DAtatableAndDataviewIs closely related, but they are independent and execute independent functions.

The following Visual Basic. Net code snippet shows how to traverse all data rows in the view and add them to ListBox.

Dim DV as new dataview ()
DV = Ds. Tables ("employees"). defaultview
DV. rowfilter = "employeeid> 5"

Listbox1.items. Clear ()
Dim Buf as string
Dim Dr as datarowview
For each DR in DV
Buf = ""
Buf & = DR ("lastname"). tostring () & "," & DR ("firstname"). tostring ()
Listbox1.items. Add (BUF)
Next

As mentioned above,DataviewIs an enumerable 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 useDatarowviewClass.DatarowviewRepresentationDatarowView, just likeDataviewThe same is true for views customized by datatable.
In general, datarow can have up to four States: Default, original, current, and proposed. These statuses are composedDatarowversionEnumeration type settingsRowversionAttribute expression.
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 refers toAcceptchangesThen, you can obtain the data row or snapshot from the data 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.
You can accessDatarowAccess with the same syntaxDatarowview. 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.
RowstatefilterIt is another interesting property 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
Deleted All data rows deleted after the last acceptchanges call
Modifiedcurrent All data rows modified since the last call of acceptchanges
Modifiedoriginal All data lines of the original version after the last acceptchanges call
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 areDatatableCallAcceptchanges. Updates to a single row are calledDatarowOfAcceptchanges. Similarly, these updates can be calledDatatableOrDatarowObjectRejectchangesTo cancel.
DataviewThe object also has some attributes, suchAllowedit, allowdeleteAndAllownewTo get or set whether to allow updates. 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
DatatableObjectDefaultviewAttribute is used to returnDataviewObject as the default view of the data table content. 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"

DataviewsettingThe 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.