This article provides a roadmap to learn and to master the ADO.NET DataSet
, and DataViewManager
Roadmap articles provide links to useful information, including onlinedocumentation, Microsoft Knowledge Base articles, and Microsoft white papers, to help you learn about a Microsoft product or technology. Microsoft Knowledge Base How To articles and walkthroughs provide step-by-step instructions to accomplish specific tasks. QuickStart sample files are ready-made programs that illustrate a technique.
For additional ADO.NET technology roadmap articles, click the article number below to view the article in the Microsoft Knowledge Base:
313590back to the top Overview
INFO: Roadmap for ADO.NET
is a disconnected data store that provides consistent functionality regardless of the data source. A DataSet
also provides a bridge between relational organization of data (for example, a database) and hierarchical organization of data (for example, Extensible Markup Language).
is similar to an array of disconnected Recordset
objects, but the DataSet
offers more powerful functionality. The DataSet
can maintain local constraints, cascade updates and deletions, and provide hierarchical navigation along relations. The DataSet
also has a more powerful expression evaluator than Microsoft ActiveX Data Objects (ADO). This provides more flexibility when you search for and filter records. The DataSet
also allows low-level control over row versioning and error state.
provides a mechanism for sorting and filtering a single DataTable
. A DataViewManager
provides a mechanism for sorting and filtering multiple DataTable
objects when you perform hierarchical navigation.
For more information about the ADO.NET DataSet
object, refer to the following Microsoft Web site:back to the top DataSetDataSet
objects include the following elements:
- DataTable objects
- DataColumn objects
- DataRow objects
- DataRelation objects
- ExtendedProperties collection
is a collection of DataTable
objects. A DataSet
also contains a collection of DataRelation
objects that allow for referential integrity, cascading updates, hierarchical navigation, and aggregate expressions.
provides the functionality to read and to write Extensible Markup Language (XML) data and schema through the following methods:
: The DataTable
and the DataRow
objects cannot read or write XML independently. The Clone
method returns an empty DataSet
with the same schema as the source DataSet
. The Copy
method returns a copy that includes the data.
The following list describes some of the methods and the properties of the DataSet
Microsoft Knowledge Base How To ArticlesVisual Studio .NET Help DocumentationMSDN Articles
- HasChanges property. This property returns True if any of the DataTable objects contain modified records.
- GetChanges method. This method returns a copy of the DataSet that contains only changed elements.
You can combine DataRowState flags to control whether you get added, deleted, or modified records (or a combination thereof). By default, all changes are returned. If no changes exist, null (in Microsoft Visual C# .NET, Microsoft Visual C++ .NET, Microsoft JScript .NET) or Nothing (in Microsoft Visual Basic .NET) is returned.
- Merge method. This method combines two DataSet objects. The MissingSchemaAction argument controls whether the target DataSet schema is extended to match the source DataSet schema, whether extended schema is ignored, or whether it causes an exception.
- If a primary key on a DataTable object exists, this key is used to update the target record from a record with the same key in the source DataSet. If no primary key exists, the records are appended, which results in duplicate records.
- If the PreserveChanges argument is True, only added records are merged (that is, any changes in the target DataSet are preserved). If the source DataSet contains modified records, the original key values are used to match records in the target DataSet.
- CaseSensitive property. This property determines whether data is case-sensitive, though this can also be set on a table-by-table basis. Schema is both case-sensitive and not case-sensitive. When ADO.NET accesses an item, ADO.NET uses a case-sensitive lookup. If this fails, ADO.NET uses a lookup that is not case-sensitive. If there is more than one item with the same name (that is not case-sensitive), you receive an exception if the case-sensitive lookup fails.
- AcceptChanges and RejectChanges methods. These methods locally commit or roll back data changes in the DataSet. These methods do not affect schema changes.
- Locale property. This property allows you to set a CultureInfo object to govern sorting and searching.
- DefaultViewManager property. This property returns a DataViewManager object that the DataSet maintains. You can manipulate this object for sorting and for filtering, or you can create one or more independent DataViewManager objects of your own.
These articles are from the Diving Into Data Access
: If you installed the QuickStart samples on your computer, these samples are available in the \QuickStart\Howto\Samples\Xml folder.Microsoft Knowledge Base How To Articles
HOW TO: Copy DataRows Between DataTables by Using Visual Basic .NET
HOW TO: Copy DataRows Between DataTables by Using Visual C# .NET
HOW TO: Obtain Extended Error Information in the DataSet by Using Visual Basic .NET
For a list of articles that contain helper functions to handle common DataSet
manipulations (such as CREATE TABLE and SELECT DISTINCT equivalents, joins, and grouping) in a single function call, click the following link:back to the top DataTable
is a collection of metadata and data, where the metadata is described by a collection of DataColumn
objects and Constraint
objects, and the data is contained in a collection of DataRow
objects. A DataTable
may exist by itself or as part of a DataSet
Unlike the ADO Recordset
object, which manipulates a Provider
, the DataTable
is a passive object. The DataAdapter
object, the XmlDataDocument
object, and the user code manipulate the DataTable
. The DataTable
does not know where its own data comes from. This data can come from multiple sources.
The following list describes some of the methods and the properties of the DataTable
- AcceptChanges method, RejectChanges method, Clone method, Copy method, GetChanges method, and HasChanges property. These methods and the HasChanges property operate similarly to their equivalents in the DataSet, except that they affect a single DataTable.
- PrimaryKey property. You can use the PrimaryKey property to indicate which column or columns make up the primary key.
- ImportRow method. This method adds a copy of a DataRow from another DataTable that has identical schema. You can use this method in conjunction with the Clone method to copy records more selectively than the Copy method.
- Select method. This method returns an array of DataRow objects that are sorted and filtered according to the arguments that you supply. You can also filter by row state.
- Constraints property. This property is a collection of unique constraints and foreign key constraints on the DataTable.
: The DataTable
does not have an equivalent method for the Merge
method. However, the Merge
method of the DataSet
can take a DataTable
or array of DataRow
objects to produce the same effect.back to the top DataColumn
You can use the DataColumn
to define the attributes for a column in a DataTable
or a DataRow
(mainly the ColumnName
and the DataType
attributes). The DataColumn
contains AutoNumber and Null support.
The following list describes some of the properties of the DataColumn
- ColumnMapping property. This property controls whether the DataColumn object maps to an XML element or to an attribute when the XmlDataDocument manipulates the DataColumn or when the DataSet serializes the DataColumn.
- Unique property. This property allows you to set a unique constraint on a non-primary key column.
can contain an expression that is based on other DataColumn
objects within the DataTable
. In addition, the DataColumn
can use a DataRelation
object to reference a field in a parent DataTable
or to aggregate fields in a child DataTable
. However, when you reference fields that are outside of the current DataTable
, you must add the expression columns after you fill the tables. If you try to update a DataTable
with an expression that references another DataTable
, you receive an exception.back to the top DataRow
Although a DataRow
may exist by itself, the DataTable
or the DataRowBuilder
must create the DataRow
. For example, you can use the NewRow
method of the DataTable
to create the DataRow
, or you can use a DataTable
for schema of the DataRowBuilder
to create the DataRow
can have multiple states, including:
If a DataRow
is not part of a DataTable
, the value of its RowState
property is Detached
, which changes to Added
when you incorporate the DataRow
in a DataTable
can have multiple versions, including:
- Proposed (when you are editing)
The following methods control the row versions:
collection has an overload that allows you to specify the DataRowVersion
as well as the column that you want to access. If you try to reference a version that does not exist, you receive an exception.
The following list describes some of the methods and the properties of the DataRow
back to the top DataRelation
- HasVersion method. You can test for a particular DataRowVersion value by using the HasVersion method.
- Deleted rows do not have a Current version.
- Added rows and detached rows do not have an Original version.
- If you are not within a BeginEdit, EndEdit, and CancelEdit block, the row does not have a Proposed version.
- GetParentRow, GetParentRows, GetChildRow, and GetChildRows methods. These methods return a DataRow object or an array of DataRow objects that contain parent or child rows based on the DataRelation that is named. These methods allow for hierarchical access between tables.
NOTE: The DataRelation object can allow many relations between tiers (that is, the DataRelation does not have to use unique columns). Therefore, the DataRow includes the GetParentRows method.
- HasErrors and RowError properties. These properties indicate whether the DataRow has an error. Although the DataAdapter usually sets these properties after an update fails, you can also set these properties manually.
object defines the parent/child relation between two DataTable
The following list describes some of the properties of the DataRelation
back to the top ExtendedProperties
- ParentKeyConstraint and ChildKeyConstraint properties. These properties determine whether referential integrity is enforced.
- Nested property. This property determines whether child table elements are nested within the parent table when the DataSet is serialized to XML.
, the DataTable
, the DataRelation
, and the Constraint
objects all support an ExtendedProperties
collection where you can store user-defined attributes for that object.back to the top DataView
allows you to sort and filter records that you view. Each DataTable
has a DefaultView
object that you can access and set properties on. In addition, you can create a number of independent DataView
objects on a DataTable
. The DataView
creates an index that is based on the sorted columns, which provides fast search capabilities through the Find
method. This Find
method only searches the current, sorted column. If the DataView
is sorted on multiple columns, you must provide an array of values in the Find
You can edit the Current
value of fields in a DataTable
row by simple assignment. The change is immediate. Alternately, you can use the BeginEdit
method to change Proposed
row versions and use the EndEdit
or the CancelEdit
method to commit or roll back your changes.
You cannot directly edit data rows in the DataView
. You must use the BeginEdit
, the EndEdit
, and the CancelEdit
methods instead. After you call the EndEdit
method, the changes are written to the DataTable
and are available immediately. Other DataView
objects on the same DataTable
are notified of the changes.
By default, a DataView
binds to the Current
rows. You can pass a DataViewRowState
to the constructor to bind to other row versions (for example, to access Deleted
: You can only bind a DataView
to a single DataTable
. You cannot make a DataView
filter another DataTable
after you create the DataView
event is raised whenever the underlying filtered or sorted data is changed.Visual Studio .NET Help DocumentationMSDN Article
The following article is from the Diving into Data Access
column.Microsoft Knowledge Base Article
325682back to the top DataViewManager
HOW TO: Implement a Custom DataView Class in Visual Basic .NET
If you navigate to child records through a DataView
, the child records are unfiltered (except with regard to the parent records). The DataViewManager
allows you to specify the RowFilter
and the Sort
properties that are applied when you perform hierarchical navigation.
For example, if you bind a Windows Form DataGrid
control to a DataSet
that contains a list of customers and orders, when you navigate to the orders for a particular customer, all orders are displayed. However, if you bind the DataGrid
to a DataViewManager
, and if you add a DataViewSetting
object to the DataViewManager
collection, you can filter the list of orders to display only those orders that exceed a certain monetary amount. In addition, you can filter the list of orders and sort them in descending order according to the order amount.Visual Studio .NET Help DocumentationMSDN Article
This article is from the Diving into Data Access
column.back to the top Troubleshooting
If you encounter problems and need answers to your questions, consult the MSDN newsgroups or the Microsoft Knowledge Base. In the MSDN newsgroups, you can share your experiences with your peers.back to the top