INFO: When to Use FillSchema and MissingSchemaAction with the DataAdapter in ADO.NET

The DataAdapter object is optimized for read-only scenarios by default. The Fill method only retrieves the amount of schema that is necessary to populate a DataSet object. To obtain the additional schema that are necessary to update or validate DataSet objects, use one of the following methods for DataSet objects that are populated by the DataAdapater:

• Use the FillSchema method of the DataAdapter.
• Use the AddWithKey enumeration for the MissingSchemaAction property of the DataAdapter.

This article describes how to choose between these two methods when you want to populate updateable DataSet objects with the DataAdapter.

Setting the MissingSchemaAction property of the DataAdapter to AddWithKey is analogous to using the DataAdapter.FillSchema method. Both methods add schema information about primary keys, AutoIncrement fields, nullable fields, and unique indexes.

When to Use DataAdapter's FillSchema Method

Use the FillSchema method of the DataAdapter if you will be reading the same data multiple times. If you use MissingSchemaAction.AddWithKey, the schema is fetched with every call to the DataAdapter.Fill method.

When to Use DataAdapter's MissingSchemaAction.AddWithKey Method

• Use DataAdapter.MissingSchemaAction.AddWithKey when you are retrieving multiple result sets in a single batch. If you are using the OLE DB .NET Data Provider, the DataAdapter.FillSchema method retrieves schema information for only the first result set when there are multiple SQL SELECT commands in a batch.
• Use DataAdapter.MissingSchemaAction.AddWithKey if you are calling DataAdapter.Fill just one time for a DataSet.

When Not to Use Either FillSchema or MissingSchemaAction.AddWithKey

• Do not use either method when you are obtaining a read-only DataSet to avoid adding unnecessary overhead.
• Do not use either method when you use the Visual Design Tools to generate a DataSet because the code for updateability is already incorporated into the tool-generated DataSet class.
• Do not use either method when you load a DataSet from XML if you want the DataSet to use the XML schema instead of a generated schema.

For more information, see the following topics in the Microsoft Visual Studio .NET Help documentation: For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: