The ADO.NET DataSet for example does not ever communicate directly with the database. To fetch data from the database into a DataSet you must pass the DataSet into the Fill method of a connected object (The DataAdapter).
DataSet Class
DataSets are a container class for a number of DataTable objects (stored in the Tables collection). You can think of this as an in memory representation of multiple tables, their constraints, relationships, and the actual rows.
The data stored in the DataSet is a disconnected copy of the actual database. Any changes you make to a DataSet only exist in RAM. You can quickly get a list of just the changes by calling the GetChanges method to return only modified DataRows. This allows you to submit smaller change sets back to the database.
The Merge method also lets to combine multiple change sets into a single DataSet. This is very useful in multi tier scenarios where you need to receive partial updates from lots of different systems and merge them into a single database change.
The DataSet is also unique in that you can read and write this disconnected data cache to disk. A common use is to put the DataSet into an XML document for transmission to multiple systems to merge into their local database.
DataTable Class
The DataTable allows you to examine the actual rows of a DataSet through rows and columns collections. You can store the results of a query in a DataTable by calling the Adapters Fill method. Once the DataTable is filled the database connection is released and operates disconnected only. You can then continue to examine the data without any further communication between the DataTable and the database.
This is important for scenarios where you want to work across a network share. Caching the data on the local machine is vital to performance of the application. Whenever possible you should not be using connected objects against a network shared database, the traffic is very expensive.
DataColumn Class
Each DataTable has a collection of DataColumns associated with it. This represents a single Column in the database. But the object doesn't actually contain any of the data stored in the DataTable. This is just a metadata storage class about the column, its constraints, types, etc. It is quite useful for finding when a single column allows nulls, has a unique constraint, etc. The autoincrement property is also implemented at the DataColumn level since you want to ensure each row has a unique value.
One interesting usage for this class is the Expression property. This allows you to define how the data for this column is calculated. This would enable you to compute the value of an items total price based upon units sold and price per unit at the DataColumn level rather than through a SQL command. The added benefit to using this scenario is that updates to the Price and Quantity columns will recalculate the total price column in RAM without having to return to the database to rerun the SQL.
DataRow Class
Each row in a DataTable is represented in the Rows collection as a DataRow. To examine the data in a specific column use the Item property of the appropriate DataRow object. You can lookup the columns in a row through an integer index, or by the name of the Column.
The DataRow class is also where you perform updates to a DataSet. When your preparing to edit a row the BeginEdit method should be called on the DataRow object. The EndEdit method then can be used to save the changes back to the DataSet, CancelEdit provides an undo facility. This is key to how the DataGrid and other grid controls allow users to edit, commit, and cancel their changes in memory.
A key concept often missed by developers is that changes to the DataRow even when they are "AcceptChanges" calls are only committing them to the RAM copy of the database (The DataSet). You must still commit the changes back to the database in order to store them permanently.
Constraints Class
Each DataColumn may have multiple Constraints. Conditions such as unique are applied through this class. Constraint objects are maintained through the DataTables Constraints collection.
DataRelation Class
Most tables in a single DataSet will be related in some way. The DataSet can handle mapping things like Orders to OrderDetails with a little help from the DataRelation class. This is a way to enforce referential integrity from the database, without having to make a complete round trip to the database.
DataView Class
Although excluded in the diagram above, this is an important class. You can use multiple DataView objects to example the same DataTable in a DataSet. This can result in massive memory savings since only one copy of the data needs to be maintained in the DataSet. You can do a lot of view type operations with the in memory DataView object.