If the DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. The tables are given an incremental default name of TableN, starting with "Table" for Table0. If a table name is passed as an argument to the Fill method, the tables are given an incremental default name of TableNameN, starting with "TableName" for TableName0.

Any number of DataAdapterobjects can be used with a DataSet. Each DataAdapter can be used to fill one or more DataTable objects and resolve updates back to the relevant data source. DataRelation and Constraint objects can be added to the DataSet locally, which enables you to relate data from dissimilar data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed through OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication to each data source.

Example

' Assumes that customerConnection is a valid SqlConnection object.
' Assumes that orderConnection is a valid OleDbConnection object.
Dim custAdapter As SqlDataAdapter = New SqlDataAdapter( _
  "SELECT * FROM dbo.Customers", customerConnection)

Dim ordAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
  "SELECT * FROM Orders", orderConnection)

Dim customerOrders As DataSet = New DataSet()
custAdapter.Fill(customerOrders, "Customers")
ordAdapter.Fill(customerOrders, "Orders")

Dim relation As DataRelation = _
  customerOrders.Relations.Add("CustOrders", _
  customerOrders.Tables("Customers").Columns("CustomerID"), _ 
  customerOrders.Tables("Orders").Columns("CustomerID"))

Dim pRow, cRow As DataRow
For Each pRow In customerOrders.Tables("Customers").Rows
  Console.WriteLine(pRow("CustomerID").ToString())

  For Each cRow In pRow.GetChildRows(relation)
    Console.WriteLine(vbTab & cRow("OrderID").ToString())
  Next
Next 

// Assumes that customerConnection is a valid SqlConnection object.
// Assumes that orderConnection is a valid OleDbConnection object.
SqlDataAdapter custAdapter = new SqlDataAdapter(
  "SELECT * FROM dbo.Customers", customerConnection);
OleDbDataAdapter ordAdapter = new OleDbDataAdapter(
  "SELECT * FROM Orders", orderConnection);

DataSet customerOrders = new DataSet();

custAdapter.Fill(customerOrders, "Customers");
ordAdapter.Fill(customerOrders, "Orders");

DataRelation relation = customerOrders.Relations.Add("CustOrders",
  customerOrders.Tables["Customers"].Columns["CustomerID"],
  customerOrders.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in customerOrders.Tables["Customers"].Rows)
{
  Console.WriteLine(pRow["CustomerID"]);
   foreach (DataRow cRow in pRow.GetChildRows(relation))
    Console.WriteLine("\t" + cRow["OrderID"]);
}

By default, the DataSet stores data by using .NET Framework data types. For most applications, these provide a convenient representation of data source information. However, this representation may cause a problem when the data type in the data source is a SQL Server decimal or numeric data type. The .NET Framework decimal data type allows a maximum of 28 significant digits, whereas the SQL Server decimal data type allows 38 significant digits. If the SqlDataAdapter determines during a Fill operation that the precision of a SQL Server decimal field is larger than 28 characters, the current row is not added to the DataTable. Instead the FillError event occurs, which enables you to determine whether a loss of precision will occur, and respond appropriately. For more information about the FillError event, see Handling DataAdapter Events (ADO.NET). To get the SQL Server decimal value, you can also use a SqlDataReader object and call the GetSqlDecimal method.

ADO.NET 2.0 introduced enhanced support for System.Data.SqlTypes in the DataSet. For more information, see SqlTypes and the DataSet (ADO.NET).

Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter) can be used to fill the contents of a DataSet. When the OleDbDataAdapter encounters a chaptered column during a Fill operation, a DataTable is created for the chaptered column, and that table is filled with the columns and rows from the chapter. The table created for the chaptered column is named by using both the parent table name and the chaptered column name in the form "ParentTableNameChapteredColumnName". If a table already exists in the DataSet that matches the name of the chaptered column, the current table is filled with the chapter data. If there is no column in an existing table that matches a column found in the chapter, a new column is added.

Before the tables in the DataSet are filled with the data in the chaptered columns, a relation is created between the parent and child tables of the hierarchical rowset by adding an integer column to both the parent and child table, setting the parent column to auto-increment, and creating a DataRelation using the added columns from both tables. The added relation is named by using the parent table and chapter column names in the form "ParentTableNameChapterColumnName".

Note that the related column only exists in the DataSet. Subsequent fills from the data source can cause new rows to be added to the tables instead of changes being merged into existing rows.

Note also that, if you use the DataAdapter.Fill overload that takes a DataTable, only that table will be filled. An auto-incrementing integer column will still be added to the table, but no child table will be created or filled, and no relation will be created.

The following example uses the MSDataShape Provider to generate a chapter column of orders for each customer in a list of customers. A DataSet is then filled with the data.

Using connection As OleDbConnection = New OleDbConnection( _
  "Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
  "Data Source=(local);Integrated " & _
  "Security=SSPI;Initial Catalog=northwind")

Dim adapter As OleDbDataAdapter = New OleDbDataAdapter( _
  "SHAPE {SELECT CustomerID, CompanyName FROM Customers} " & _
  "APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders " & _
  "RELATE CustomerID TO CustomerID)", connection)

Dim customers As DataSet = New DataSet()

adapter.Fill(customers, "Customers")
End Using

using (OleDbConnection connection = new OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" +
  "Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind"))
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +
  "APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders " +
  "RELATE CustomerID TO CustomerID)", connection);

DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");
}

When the Fill operation is complete, the DataSet contains two tables: Customers and CustomersOrders, where CustomersOrders represents the chaptered column. An additional column named Orders is added to the Customers table, and an additional column named CustomersOrders is added to the CustomersOrders table. The Orders column in the Customers table is set to auto-increment. A DataRelation, CustomersOrders, is created by using the columns that were added to the tables with Customers as the parent table. The following tables show some sample results.

Concepts

Other Resources