A DataSet is an in-memory data store that can hold the numerous tables. DataSets only hold data and do not interact with any data source. It is the SqlDataAdapter that manages connections with the data source and gives us the disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task.

The SqlDataAdapter performs the following tasks when filling a DataSet with data:

1. Open connection
2. Retrieve data into DataSet
3. Close connection

1. Open connection
2. Write changes from DataSet to data source
3. Close connection

A couple scenarios illustrate why you would want to work with disconnected data: people working without network connectivity and making Web sites more scalable. Consider sales people who need customer data as they travel. At the beginning of the day, they'll need to sync up with the main data base to have the latest information available. During the day, they'll make modifications to existing customer data, add new customers, and input new orders. This is okay because they have a given region or customer base where other people won't be changing the same records. At the end of the day, the sales person will connect to the network and update changes for overnight processing.

Another scenario is making a Web site more scalable. With a SqlDataReader, you have to go back to the data base for records every time you show a page. This requires a new connection for each page load, which will hurt scalability as the number of users increase. One way to relieve this is to use a DataSet that is updated one time and stored in cache.

Exceptions to the scenario above include situations where you need to update data. You then have to make a decision, based on the nature of how the data will be used as to your strategy. Use disconnected data when your information is primarily read only, but consider other alternatives (such as using SqlCommand object for immediate update) when your requirements call for something more dynamic.

There isn't anything special about instantiating a DataSet. You just create a new instance, just like any other object:

The SqlDataAdapter holds the SQL commands and connection object for reading and writing data. You initialize it with a SQL select statement and connection object:

Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet. Here's how to do it, by using the Fill method of the SqlDataAdapter:

A DataSet will bind with both ASP.NET and Windows forms DataGrids. Here's an example that assigns the DataSet to a Windows forms DataGrid: