ADO.NET Data Containers: An Explanation

This is not actually code for anything but rather an explanation of the ADO.NET classes that store data and how they relate to each other. I think a lot of people don't really understand fully so I thought this was in order.

DataSet

The DataSet is basically an in-memory representation of a database. It doesn't have to match the schema of any actual database and the data it contains doesn't have to even come from a database, but that's its basic purpose.

Just as a database contains tables and relations between them, a DataSet contains DataTables and DataRelations between them. The DataSet's Tables property is a DataTableCollection that contains the DataTables and the Relations property is a DataRelationCollection that contains the DataRelations.

A lot of people use a DataSet without thinking when they don't really need to. A DataSet is a component, which means it can be added to a form in the designer. It's also a convenient way to store multiple DataTables and is essential if you want DataRelations between the tables. If you're just populating a single DataTable in code though, a DataSet is pointless. The Fill method of a DataAdapter can populate a DataTable in a DataSet or a loose DataTable. Use the loose DataTable unless there's a good reason to use a containing DataSet.

DataTable

Just as the DataSet represents a database, a DataTable represents a database table. It doesn't have to match the schema of an actual table though. It basically contains the result set of a query. That query might get all records and all columns of a single table or it might get some columns and some rows from several tables.

The DataTable itself is a container too. Its Columns property is a DataColumnCollection that contains DataColumns, which describe the data in the table, while its Rows property is a DataRowCollection that contains DataRows, whose fields contain the actual data.

Just like a database table, a DataTable has a PrimaryKey. This is an array containing a subset of its DataColumns whose values uniquely identify each record.

The DataTable also has a DefaultView property that contains a DataView. This is similar to the Rows property but not the same. A DataView allows sorting, filtering and transactional editing of the data in the DataTable and will be discussed later.

DataRelation

A DataRelation defines a relationship between two DataTables. It contains a reference to each table and also the columns that are related. A DataRelation can be used to navigate data from a parent table to a child or from a child table to a parent. It can also be used to enforce foreign key constraints on child data and propagate changes from parent to child.

DataColumn

A DataColumn corresponds to a column in a database table. It doesn't actually contain any data itself but rather describes the data in the corresponding field of the DataRows of the same DataTable. It describes the type, size, etc. of the data.

DataRow

A DataRow corresponds to a row in a database table. The fields of a DataRow contain the data for that row of the DataTable. Each field can be accessed by numeric index, column name or by the DataColumn itself.

The DataRow also contains two versions of the data: the original and the current. If a field has been edited those two values will be different. A newly added row has no original data while a deleted row has no current data.

DataView

A DataView contains no data of its own, but rather provides a view of the data in a DataTable. That view can be sorted and filtered, via the Sort and RowFilter properties, without affecting the data in the DataTable in any way. These properties are a user-friendly interface to the DataView's implementation of the IBindingListView interface.

While you can create a DataView explicitly, generally you should use the DefaultView property of a DataTable. The only reason to create a DataView explicitly is if you need more than one veiw of the same DataTable. The DataView is much like the DataRowCollection class, although it contains DataRowViews rather than DataRows and provides extra functionality. The DataRowCollection contains the raw data while the DataView processes it.

A DataView can also be used to create a new DataTable, via its ToTable method, that will reflect the current sorting and filter of the DataView. It can also be used to select only specific columns from the original DataTable.

DataRowView

Just as a DataView provides a view of the data in a DataTable, so a DataRowView provides a view of the data in a DataRow. Just as the Table property of a DataView returns the DataTable it provides a view of, so the Row property of a DataRowView returns the DataRow it provides a view of.

A DataRowView is very similar to a DataRow and can be treated in much the same way. It's main benefit is transactional processing, through its implementation of the IEditableObject interface. You can add a DataRowView to a DataView and then discard it without the underlying DataRow ever being added to the underlying DataTable. You can also edit a DataRowView and then discard the changes without them ever being pushed to the underlying DataRow.

Generally speaking, data-binding requires an object that implements either the IList or the IListSource interface. As the name suggests, an IListSource is a source of an IList, i.e. an IListSource is not a list itself but it is the source of an object that is. I would generally suggest binding through a BindingSource but, whether you do so or bind directly, you can bind a DataSet, a DataTable or a DataView.

When you bind a DataSet it's really only the data from a single DataTable that gets displayed so, unless binding at design time, you may as well just bind that DataTable directly.

The only time that you should bind a DataView is if you want to bind the contents of the same DataTable to multiple controls and be able to sort, filter and select independently. In that case you should create multiple DataViews on the DataTable and bind each of those. Otherwise you should just bind the DataTable directly. There's no point binding the DefaultView of a DataTable unless you're also binding another DataView from the same DataTable, but in that case I'd suggest not using the DefaultView at all. For consistency, if you need multiple views then create each one explicitly and don't use the DefaultView at all. As such you should NEVER be binding the DefaultView.

Getting back to the IList and IListSource interfaces, the DataView class implements the IList interface while the DataTable implements the IListSource interface. What does this mean for data-binding? Well, when you bind a DataTable the bound control doesn't get the data from the DataTable itself because it isn't a list. It calls GetList on the DataTable to get an IList object that it can get items from. What do you get when you call GetList on a DataTable? You get its DefaultView. That's why there's no point ever binding the DefaultView directly. It's also why, when you bind a DataTable, the items in the bound control turn out to be DataRowViews rather than DataRows.

Now, you may be wondering why it happens this way. The answers are in my previous posts. The DataView and DataRowView classes provide sorting, filtering and transactional editing while the DataTable (and DataRowCollection) and DataRow classes do not. When you bind your DataTable to a DataGridView and then click a column header to sort the data, that's the DataView that's allowing you to do that, through its implementation of the IBindingList interface. The IBindingList interface provides simple sorting, i.e. by one column or property, while the IBindingListView interface, which the DataView also implements, provides complex sorting and filtering too. When you edit a cell in the grid and then press Escape to cancel the change, that's the DataRowView that's allowing you to do that through its implementation of the IEditableObject interface.

Well-put! Very informative.

Added to my list of important bookmarks from VBF.

-tg

Good explanation!