The DataSet class provides a means to access and manipulate a memory-resident representation of a database, including related tables, constraints, and relationships among the tables. Interaction between the DataSet and the underlying data source is done via the DataAdapter class.

DataAdapter objects are much like Command objects, but are used specifically to access and manipulate information stored in a DataSet. ADO.NET provides both the SqlDataAdapter and OleDbDataAdapter classes for use with the appropriate data store.

The DataSet is mainly used in scenarios wherein data must be updateable and should persist across multiple requests.

This document briefly describes the steps to retrieve information from a database into a DataSet.

NOTE: While this code walk-thru uses the SQL Server™ .NET Data Provider, the same concepts apply to the corresponding OLEDB classes as well.

1. First, set up the connection.

   SqlConnection myConn = new SqlConnection ( "server=(local)\\NetSDK; trusted_connection=yes; database=pubs" ); Dim myConn As New SqlConnection ( _ "server=(local)\NetSDK; trusted_connection=yes; database=pubs" )
   	C#	VB

2. Next, set up a DataAdapter with the given query and associate this with the current connection. This is done in the same way a command is constructed.

   String query = "SELECT * FROM Customers"; SqlDataAdapter myCmd = new SqlDataAdapter ( query, myConn ); Dim query As String = "SELECT * FROM Customers" Dim myCmd As SqlDataAdapter = New SqlDataAdapter ( query, myConn )
   	C#	VB

3. Set up a DataSet and populate it with the Fill method of the DataAdapter object.

   DataSet myData = new DataSet ( ); myCmd.Fill ( myData,"Customers" ); Dim myData as New DataSet ( ) myCmd.Fill ( myData,"Customers" )
   	C#	VB

The DataSet now contains a DataTable that holds the results of the query. In fact, the DataSet can hold results from multiple queries and even relate them, as described elsewhere in this workshop.

And because the data is stored as a collection of rows and columns in the table, you can iterate through and display the contents of the DataSet using simple foreach statements.

foreach ( DataTable table in myData.Tables ) {
   foreach ( DataRow row in table.Rows ) {
      foreach ( DataColumn col in table.Columns ) {
         Response.Write ( row [ col ].ToString ( ) );
      }
   }
}

foreach table as DataTable in myData.Tables
   foreach row as DataRow in table.Rows
      foreach col as DataColumn in table.Columns
         Response.Write ( row ( col ).ToString ( ) ) 
      next
   next
next

The method described above uses generic array notation ( ex. row [ col ] ) to access the data in the tables, rows, and columns collections of the DataSet. This makes the method applicable to any DataSet, and is particularly useful when the table and field names are unknown beforehand.

The below snippets show examples of using a DataAdapter to retrieve data into a Dataset, then iterate thru and display the contents of the DataSet using simple foreach loops as described above.

NOTE: This example uses one of the overloaded versions of the SqlDataAdapter constructor. For other examples that may be available, see the individual overload topics.

// set up the connection
SqlConnection myConn = new SqlConnection ( 
   "server=(local)\\NetSDK; trusted_connection=yes; database=northwind" );

// set up the query
SqlDataAdapter myCmd = new SqlDataAdapter ( 
   "SELECT * FROM Customers", myConn );

// initialize dataset object
DataSet myData = new DataSet ( );
// fill with query results
myCmd.Fill ( myData, "Customers" );

// display dataset contents
foreach ( DataTable table in myData.Tables ) {
   foreach ( DataRow row in table.Rows ) {
      foreach ( DataColumn col in table.Columns ) {
         Response.Write ( row [ col ].ToString ( ) );
      }
   }
}

' set up the connection
Dim myConn as new SqlConnection ( _
   "server=(local)\NetSDK; trusted_connection=yes; database=northwind" ) 

' set up the query
Dim myCmd as new SqlDataAdapter ( _
   "SELECT * FROM Customers", myConn ) 

' instantiate dataset object
Dim myData as new DataSet ( ) 
' fill with query results
myCmd.Fill ( myData, "Customers" ) 

' display dataset contents
foreach table as DataTable in myData.Tables
   foreach row as DataRow in table.Rows
      foreach col as DataColumn in table.Columns
         Response.Write ( row ( col ).ToString ( ) ) 
      next
   next
next

 Show me 

NOTE: This example uses one of the overloaded versions of the OleDbDataAdapter constructor. For other examples that may be available, see the individual overload topics.

// set up the connection
OleDbConnection myConn = new OleDbConnection ( 
   "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + 
   Server.MapPath ( "~/app_data/dbtutor.mdb" ) );

// set up the query
OleDbDataAdapter myCmd = new OleDbDataAdapter ( 
   "SELECT * FROM Products", myConn );

// initialize dataset object
DataSet myData = new DataSet ( );
// fill with query results
myCmd.Fill ( myData, "Products" );

// display dataset contents
foreach ( DataTable table in myData.Tables ) {
   foreach ( DataRow row in table.Rows ) {
      foreach ( DataColumn col in table.Columns ) {
         Response.Write ( row [ col ].ToString ( ) );
      }
   }
}

' set up the connection
Dim myConn as new OleDbConnection ( _
   "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
   Server.MapPath ( "~/app_data/dbtutor.mdb" ) ) 

' set up the query
Dim myCmd as new OleDbDataAdapter ( _
   "SELECT * FROM Products", myConn ) 

' instantiate dataset object
Dim myData as new DataSet ( ) 
' fill with query results
myCmd.Fill ( myData, "Products" ) 

' display dataset contents
foreach table as DataTable in myData.Tables
   foreach row as DataRow in table.Rows
      foreach col as DataColumn in table.Columns
         Response.Write ( row ( col ).ToString ( ) ) 
      next
   next
next

 Show me 

While the data in this sample is obtained from a database, the DataSet exposes the same programming model to any data file format supported in ADO.NET, such as XML files. The following section shows how XML data can easily be retrieved into a DataSet.