Learn the advanced features of disconnected operations in ADO.NET, including the sortable/filterable DataView, the XML parsing capabilities of the DataSet, and the strongly typed DataSet, and how they contribute to the data access stack of .NET.

Dale Michalk is an application development consultant for Microsoft Premier Support in Charlotte, NC. He is a regular contributor to InformIT on new technology topics such as .NET and XML.

Learn the advanced features of disconnected operations in ADO.NET including
the sortable/filterable DataView, the XML parsing capabilities of the DataSet,
as well as the strong-typed DataSet and how they contribute to the data access
stack of .NET.

Dale Michalk is an application development consultant for Microsoft Premier
Support in Charlotte, NC. He is a regular contributor to InformIT on new
technology topics such as .NET and XML.

The disconnected capabilities of ADO.NET provide rich functionality beyond
the basic necessities of loading and storing information to and from a data
source. ADO.NET has the flexibility to work with XML natively via the
DataSet, create a dynamic viewing object called the DataView
that filters and sorts a DataTable, and build a strongly typed
DataSet that mimics the data domain it represents. This article
discusses each of these useful features and what they contribute to the data
access stack of .NET.

NOTE

Click here to
download a zip file containing the source files for this article.

A View to a DataSet

The DataView provides a changeable, dynamic view into the world of
data inside a DataSet. It's commonly used with data-binding
scenarios and GUI controls to provide a different look at the same collection of
data for analysis by the end user of a program. It has sorting and filtering
capabilities as well as supporting editing with the appropriate property
configuration that can synchronize its efforts with the single
DataTable that it's mapped to.

A DataView can be created through direct instantiation by passing a
DataTable reference to its constructor or by setting the
DataTable property of a newly minted DataView. The
DataTable has a factory property called DefaultView that
generates a default DataView on the DataTable. The
construction patterns reinforce the fact that a DataView can only
reference a single DataTable of the DataSet at a time. The
code in Listing 1 shows the various creation options.

Once the DataView is created, you can set the sorting and filtering
through the Sort, RowFilter, and RowStateFilter
properties. The Sort property takes a string containing the name of the
DataColumn or DataColumns, separated by commas, and the sort
type in the form of ASC (ascending) or DESC (descending), with
the default sort being ascending.

The RowFilter property takes a more complex string expression that
consists of an optionally repeating DataColumn name, an operator, and
the value to apply using the operator. The set of operators spans those familiar
to SQL programming: comparison operators (such as <, >,
=); a string-matching operator in the form of LIKE and the
% and * wildcards; as well as logical operators including
OR and AND to compose expressions. See the documentation for
the Expression class for more complete details on the exact operators
and values supported.

The final filtering property of interest in the DataView is the
RowStateFilter. This allows the programmer to view rows that meet state
criteria depending on the DataViewRowState enumeration. The state
changes as a result of data modification through row data changes, row
additions, and row deletions. In Listing 2, this enumeration takes on the values
listed in Table 1.

Table 1 DataViewRowState Enumeration Values

Value

Description

Added

New rows added

CurrentRows

All rows including unchanged, new, and modified rows

Deleted

Rows deleted from the DataTable

ModifiedCurrent

A current version, which is a modified version of original data

ModifiedOriginal

The original version, although it has since been modified

None

None of the rows

OriginalRows

All of the original rows, including unchanged and deleted rows

Unchanged

Rows that haven't changed

In addition to being a viewing tool, the DataView can
act as an input tool. The AllowEdit, AllowNew, and
AllowDelete properties control whether a row can be modified, added, or
deleted to the DataView and its related DataTable.
AllowNew affects the use of the DataViewAddRow
method, AllowDelete affects the Delete method, and
AllowEdit determines whether the DataRowView object modeling
the row of data can be modified.

With DataView preliminaries out of the way, we can discuss a working
implementation of the process. I've departed from the console applications
of the previous articles to use a .NET Windows forms application and its rich
set of controls. The DataGrid class in particular makes it easy to see
the results of our manipulation of a DataView and happily binds to
either the DataTable or DataView. Listing 2 shows the code for
the ViewDataSet form class. Listing 3 shows database utility code to
pull data from Northwind for the demos in this article.

The ViewDataSet forms class has a private member DataSet
named CustDS that stores the data loaded in from the Customers
table in the SQL Server Northwind sample database. The load event of the form
uses the DataAdapterUtility class from Listing 3 to fill the data into
the CustDSDataSet. After the data load, the
DataTable representing the customers is bound to the
MainDataGridDataGrid control via the DataView
construction property named DefaultView, as shown in Figure 1. The
DataGrid control has the ability to sort, update, delete, and add rows
to the DataSet to which it's bound.

The bottom portion of the form in Figure 1 is used to demonstrate the
DataView capabilities. The ViewDataGrid member of the
ViewDataSet form is a DataGrid control that's bound to a
custom-constructed DataView object created when the RefreshView button
is clicked. A set of text box controls provides input for the Sort and
RowFilter expressions, while the combo box represents the
RowStateFilter enumeration values. The Allow Changes check box is
linked to the AllowEdit, AllowDelete, and AllowNew
properties of the DataView and sets all of them to true or false by
virtue of the check box value.The DataGrid control is smart
enough to read the changes in these values and react to user input
accordingly.

Figure 2 shows the result of sorting the DataView using
Country and then Address as the columns in the Sort
property. The filter expression then does a LIKE operation for all
customers with a name that starts with B. Figure 3 shows the option for
adding a row that becomes available when the Allow Changes check box is checked.
We added the row with BALLY as the customer ID. (You have to scroll down
to the bottom of the MainDataGrid control to see the BALLY row.) Figure
4 shows the results of deleting the customer with the customer ID value of
BERGS, and how the changes are propagated to both DataGrid
controls.

Figure 2 Sorting using Country and Address while using
LIKE and Customers starting with B.