Populating a DataSet from a DataAdapter

The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.

Each .NET Framework data provider included with the .NET Framework has a DataAdapter object: the .NET Framework Data Provider for OLE DB includes an OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server includes a SqlDataAdapter object, and the .NET Framework Data Provider for ODBC includes an OdbcDataAdapter object. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source and Command objects to retrieve data from and resolve changes to the data source.

The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet. These properties are covered in more detail in Updating the Database with a DataAdapter and the DataSet.

The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.

The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema. Column types are created as .NET Framework types according to the tables in Mapping .NET Data Provider Data Types to .NET Framework Data Types. Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. If Fill finds that a primary key exists for a table, it will overwrite data in the DataSet with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the DataSet. Fill uses any TableMappings that may exist when populating the DataSet (see Setting Up DataTable and DataColumn Mappings).

Note   If the SelectCommand returns the results of an OUTER JOIN, the DataAdapter will not set a PrimaryKey value for the resulting DataTable. You will need to define the PrimaryKey yourself to ensure that duplicate rows are resolved correctly. For more information, see Defining a Primary Key for a Table.

The following code example creates an instance of a DataAdapter that uses a Connection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and Connection arguments passed to the DataAdapter constructor are used to create the SelectCommand property of the DataAdapter.

SqlClient

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim selectCMD As SqlCommand = New SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30

Dim custDA As SqlDataAdapter = New SqlDataAdapter
custDA.SelectCommand = selectCMD

nwindConn.Open()

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")

nwindConn.Close()
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;

SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = selectCMD;

nwindConn.Open();

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

nwindConn.Close();

OleDb

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
                                                       "Integrated Security=SSPI;Initial Catalog=northwind")

Dim selectCMD As OleDbCommand = New OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30

Dim custDA As OleDbDataAdapter = New OleDbDataAdapter
custDA.SelectCommand = selectCMD

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
                                                "Integrated Security=SSPI;Initial Catalog=northwind");

OleDbCommand selectCMD = new OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;

OleDbDataAdapter custDA = new OleDbDataAdapter();
custDA.SelectCommand = selectCMD;

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

Odbc

Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
                                                     "Trusted_Connection=yes;Database=northwind")

Dim selectCMD As OdbcCommand = New OdbcCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30

Dim custDA As OdbcDataAdapter = New OdbcDataAdapter
custDA.SelectCommand = selectCMD

nwindConn.Open()

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")

nwindConn.Close()
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
                                              "Trusted_Connection=yes;Database=northwind");

OdbcCommand selectCMD = new OdbcCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;

OdbcDataAdapter custDA = new OdbcDataAdapter();
custDA.SelectCommand = selectCMD;

nwindConn.Open();

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

nwindConn.Close();

Note that the code does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished. This can simplify your code when dealing with a single operation such as a Fill or an Update. However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Open method of the Connection, performing the operations against the data source, then calling the Close method of the Connection. You should strive to keep connections to the data source open for a minimal amount of time to free up the resource to be used by other client applications.

Multiple Result Sets

If the DataAdapter encounters multiple result sets, it will create multiple tables in the DataSet. The tables will be 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 will be given an incremental default name of TableNameN, starting with "TableName" for TableName0.

Populating a DataSet from Multiple DataAdapters

Any number of DataAdapters can be used in conjunction 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, enabling you to relate data from multiple dissimilar data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed via OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication to each data source.

The following code example populates a list of customers from the Northwind database on Microsoft SQL Server 2000, and a list of orders from the Northwind database stored in Microsoft® Access 2000. The filled tables are related with a DataRelation, and the list of customers is then displayed with the orders for that customer. For more information about DataRelation objects, see Adding a Relationship between Tables and Navigating a Relationship between Tables.

Dim custConn As SqlConnection= New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
                                                 "Initial Catalog=northwind;")
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", custConn)

Dim orderConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                       "Data Source=c:\Program Files\Microsoft Office\" & _
                                                       "Office\Samples\northwind.mdb;")
Dim orderDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Orders", orderConn)

custConn.Open()
orderConn.Open()

Dim custDS As DataSet = New DataSet()

custDA.Fill(custDS, "Customers")
orderDA.Fill(custDS, "Orders")

custConn.Close()
orderConn.Close()

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

Dim pRow, cRow As DataRow

For Each pRow In custDS.Tables("Customers").Rows
  Console.WriteLine(pRow("CustomerID").ToString())

  For Each cRow In pRow.GetChildRows(custOrderRel)
    Console.WriteLine(vbTab & cRow("OrderID").ToString())
  Next
Next 
[C#]
SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);

OleDbConnection orderConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
                                                "Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\northwind.mdb;");
OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);

custConn.Open();
orderConn.Open();

DataSet custDS = new DataSet();

custDA.Fill(custDS, "Customers");
orderDA.Fill(custDS, "Orders");

custConn.Close();
orderConn.Close();

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

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

SQL Server Decimal Type

The DataSet stores data 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. The .NET Framework decimal data type allows a maximum of 28 significant digits, while 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 greater than 28 characters, the current row will not be added to the DataTable. Instead the FillError event will occur, which enables you to determine if a loss of precision will occur, and respond appropriately. For more information about the FillError event, see Working with DataAdapter Events. To get the SQL Server decimal value, you can also use a SqlDataReader object and call the GetSqlDecimal method.

OLE DB Chapters

Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter) can be used to fill the contents of a DataSet. When the DataAdapter 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 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 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 will result in new rows being added to the tables rather than 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.

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
                                         "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

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

Dim custDS As DataSet = New DataSet()

custDA.Fill(custDS, "Customers")
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" +
                                  "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

OleDbDataAdapter custDA = new OleDbDataAdapter("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +
                                      "  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders " +
                                      "  RELATE CustomerID TO CustomerID)", nwindConn);

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

When the Fill operation is complete, the DataSet will contain 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 auto-increment. A DataRelation, CustomersOrders, is created using the columns that were added to the tables with Customers as the parent table. The following tables show some sample results.

TableName: Customers

TableName: CustomersOrders

See Also

Using .NET Framework Data Providers to Access Data | Mapping .NET Data Provider Data Types to .NET Framework Data Types | DataSet Class | DataTable Class | OleDbCommand Class | OleDbConnection Class | OleDbDataAdapter Class | OdbcCommand Class | OdbcConnection Class | OdbcDataAdapter Class | SqlCommand Class | SqlConnection Class | SqlDataAdapter Class