Adding Existing Constraints to a DataSet
The Fill method of the DataAdapter fills a DataSet only with table columns and rows from a data source; though constraints are commonly set by the data source, the Fill method does not add this schema information to the DataSet by default. To populate a DataSet with existing primary key constraint information from a data source, you can either call the FillSchema method of the DataAdapter, or set the MissingSchemaAction property of the DataAdapter to AddWithKey before calling Fill. This will ensure that primary key constraints in the DataSet reflect those at the data source. Foreign key constraint information is not included and must be created explicitly, as shown in DataTable Constraints.
Adding schema information to a DataSet before filling it with data ensures that primary key constraints are included with the DataTable objects in the DataSet. As a result, when additional calls to fill the DataSet are made, the primary key column information is used to match new rows from the data source with current rows in each DataTable, and current data in the tables is overwritten with data from the data source. Without the schema information, the new rows from the data source are appended to the DataSet, resulting in duplicate rows.
Note
If a column in a data source is identified as auto-incrementing, the FillSchema method, or the Fill method with a MissingSchemaAction of AddWithKey, creates a DataColumn with an AutoIncrement property set to true. However, you will need to set the AutoIncrementStep and AutoIncrementSeed values yourself. For more information about auto-incrementing columns, see Creating AutoIncrement Columns.
Using FillSchema or setting the MissingSchemaAction to AddWithKey requires extra processing at the data source to determine primary key column information. This additional processing can hinder performance. If you know the primary key information at design time, we recommend that you explicitly specify the primary key column or columns in order to achieve optimal performance. For information about explicitly setting primary key information for a table, see Defining Primary Keys.
The following code example shows how to add schema information to a DataSet using FillSchema:
Dim custDataSet As New DataSet()
custAdapter.FillSchema(custDataSet, SchemaType.Source, "Customers")
custAdapter.Fill(custDataSet, "Customers")
var custDataSet = new DataSet();
custAdapter.FillSchema(custDataSet, SchemaType.Source, "Customers");
custAdapter.Fill(custDataSet, "Customers");
The following code example shows how to add schema information to a DataSet using the MissingSchemaAction.AddWithKey property of the Fill method:
Dim custDataSet As New DataSet()
custAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
custAdapter.Fill(custDataSet, "Customers")
var custDataSet = new DataSet();
custAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
custAdapter.Fill(custDataSet, "Customers");
Handling multiple result sets
If the DataAdapter encounters multiple result sets returned from the SelectCommand, it will create multiple tables in the DataSet. The tables will be given a zero-based incremental default name of Table N, starting with Table instead of "Table0". If a table name is passed as an argument to the FillSchema method, the tables will be given a zero-based incremental name of TableName N, starting with TableName instead of "TableName0".
Note
If the FillSchema method of the OleDbDataAdapter object is called for a command that returns multiple result sets, only the schema information from the first result set is returned. When returning schema information for multiple result sets using the OleDbDataAdapter, it is recommended that you specify a MissingSchemaAction of AddWithKey and obtain the schema information when calling the Fill method.