Edit

Add existing constraints to a DataSet

  • Article

Applies to: .NET Framework .NET .NET Standard

Download ADO.NET

The Fill method of the SqlDataAdapter 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.

Note

Foreign key constraint information is not included and must be created explicitly.

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.

Note

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.

The following code example shows how to add schema information to a DataSet using FillSchema:

// Assumes that connection is a valid SqlConnection object.
string queryString =
"SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

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

The following code example shows how to add schema information to a DataSet using the MissingSchemaAction property and the Fill method:

// Assumes that customerConnection and orderConnection are valid SqlConnection objects.
SqlDataAdapter custAdapter = new SqlDataAdapter(
"SELECT * FROM dbo.Customers", customerConnection);
SqlDataAdapter ordAdapter = new SqlDataAdapter(
"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"]);
}

Handling multiple result sets

If the DataAdapter meets 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". The tables will be given a zero-based incremental name of TableName N, starting with TableName instead of "TableName0" if a table name is passed as an argument to the FillSchema method.

See also