A Dataset is an in-memory data store that can hold numerous tables.  Datasets only hold data and do not interact with a data source.  It is the SqlDataAdapter that manages connections with the data source and gives us disconnected database behavior.  The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task.  For example, the SqlDataAdapter performs the following tasks when filling a Dataset with data:

•    Open connection
•    Retrieve data into Dataset
•    Close connection

And performs the following actions when updating data source with Dataset changes:

•    Open connection
•    Write changes from Dataset to data source
•    Close connection

In between the Fill and Update operations, data source connections are closed and we are free to read and write data with the Dataset as we need. 

Features of Dataset

There are six main features of dataset. They are:

•    Working with disconnected data
•    Scrolling, Sorting, Searching, and Filtering
•     Working with Hierarchical Data
•    Caching Changes
•    XML Integration
•    Uniform Functionality

Working with disconnected data

Data in DataSet are disconnected from database. Connection to the database is only established when the data from the database is to be fetched into the dataset using DataAdapter. Changes made in the dataset will not affect the database and changes made in the database will not be reflected in our dataset.

The main advantage of working with disconnected database is that there is no need of live connection to the database. Once the result of query is fetched from database into the DataSet, the connection to the database can be closed.

Disconnected data structures such as DataSets are also helpful when building multi-tiered applications. If the application uses business objects running on a middle-tier server to access database, business object needs to pass disconnected data structures to client application.

Scrolling, Sorting, Searching, and Filtering

We can check any row of data in DataSet at any time and any number of times. We can move from first record to last and from last record to middle any number of times, which means we can loop back and forth through the results of query as often as we like. We can sort the data in the DataSet according to column or number of columns. We can search for a row of data based on simple search criteria. We can also apply a filter to the data in DataSet so that only rows that satisfy the desired criteria are visible.

Working with Hierarchical Data

DataSet objects are designed to work with hierarchical data. The DataSet object lets us define relationships between the tables of data stored in the DataSet.

Caching Changes

Changes made in rows can be cached in the dataset and can be submitted to the database using DataAdapter after all changes made. Changes or modifications in row or table can be examined and can be compared with the original database before submitting it to the database. This can be helpful in multi-tier application where every user can change or modifications in rows or table, to determine how the rows are modified (insert, delete or modified).

XML Integration

Through ADO.NET DataSet we can save and load the contents of a DataSet to and from files as XML documents. The DataSet also allows separating the schema information (table, column, and constraint information) into an XML schema file. In ADO.NET, DataSet objects and XML documents are almost interchangeable. We can easy to move from one data structure to the other.

Uniform Functionality

Unlike Recordset, DataSet object supports features such as filtering, searching, sorting, and caching updates 

Creating a Dataset Object

DataSet ds = new DataSet();

The code above creates a new Dataset ‘ds’.

Creating A SqlDataAdapter

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

SqlDataAdapter da = new SqlDataAdapter(
    "select * from Employee", con);

The code above creates a new SqlDataAdapter  da.  The SQL select statement specifies what data will be read into a DataSet.  The connection object, con, should have already been instantiated, but not opened.  It is the SqlDataAdapter's responsibility to open and close the connection during Fill and Update method calls.

Filling the DataSet

Once we have a Dataset and SqlDataAdapter instances, we need to fill the Dataset. 

da.Fill(ds, "Employee");

The Fill method, in the code above, takes two parameters: a Dataset and a table name.  The Dataset must be instantiated before trying to fill it with data.  The second parameter is the name of the table that will be created in the Dataset. 

Updating Changes

After modifications are made to the data, we'll want to write the changes back to the database. 

System.Data.SqlClient.SqlCommandBuilder cb;

cb = new System.Data.SqlClient.SqlCommandBuilder(da);

da.Update(ds "Employee");

The table contains a list of records that have been modified and the Insert, Update, and Delete properties of the SqlDataAdapter contain the SQL statements used to make database modifications.