The ADO.NET object model is rich, but at its heart it is a fairly straightforward set of classes. The most important of these is the DataSet. The DataSet represents a subset of the entire database, cached on your machine without a continuous connection to the database.
Periodically, you’ll reconnect the DataSet to its parent database, update the database with changes you’ve made to the DataSet, and update the DataSet with changes in the database made by other users or processes. That’s how ADO.NET maintains its disconnected nature that we mentioned at the start of the chapter.
This is highly efficient, but to be effective, the DataSet must be a robust subset of the database, capturing not just a few rows from a single table, but also a set of tables with all the metadata necessary to represent the relationships and constraints of the original database. This is, not surprisingly, what ADO.NET provides.
The DataSet is composed of DataTable objects as well as DataRelation objects. These are accessed as properties of the DataSet object. The Tables property returns a DataTableCollection, which in turn contains all the DataTable objects.
You can create a DataTable programmatically or as a result of a query against the database. The DataTable has a number of public properties, including the Columns collection, which returns the DataColumnCollection object, which in turn consists of DataColumn objects. Each DataColumn object represents a column in a table.
In addition to the Tables collection, the DataSet has a Relations property, which returns a DataRelationCollection consisting of DataRelation objects. Each DataRelation represents a relationship between two tables through DataColumn objects. For example, in the Northwind database, the Customers table is in a relationship with the Orders table through the CustomerID column.
The nature of this relationship is one-to-many, or parent-to-child. For any given order, there will be exactly one customer, but any given customer might be represented in any number of orders.
The Rows collection of the DataTable returns a set of rows for that table. You use this collection to examine the results of queries against the database, iterating through the rows to examine each record in turn, typically with a foreach loop. You’ll see this in the example in this chapter.
The DataSet is an abstraction of a relational database. ADO.NET uses a DataAdapter as a bridge between the DataSet and the data source, which is the underlying database. DataAdapter provides the Fill( ) method to retrieve data from the database and populate the DataSet.
Instead of tying the DataSet object too closely to your database architecture, ADO.NET uses a DataAdapter object to mediate between the DataSet object and the database. This decouples the DataSet from the database and allows a single DataSet to represent more than one database or other data source.
The DbConnection object represents a connection to a data source. This connection can be shared among different command objects. The DbCommand object allows you to send a command (typically, a SQL statement or a stored procedure) to the database. Often, these objects are implicitly created when you create a DataAdapter, but you can explicitly access these objects; for example, you can declare a connection string as follows:
string connectionString = "server=.\sqlexpress;" + "Trusted_Connection=yes; database=Northwind";
You can then use this connection string to create a connection object or to create a DataAdapter object.
An alternative to creating a DataSet (and a DataAdapter) is to create a DataReader. The DataReader provides connected, forward-only, read-only access to a collection of tables by executing either a SQL statement or stored procedures. DataReaders are lightweight objects that are ideally suited for filling controls with data and then breaking the connection to the backend database.