INFO: Roadmap for ADO.NET DataAdapter Objects

This article provides a roadmap to learn and master ADO.NET DataAdapter objects. Roadmap articles provide links to useful information, including online documentation, Microsoft Knowledge Base articles, and Microsoft white papers, to help you learn about a Microsoft product or technology.

Overview

The .NET data provider DataAdapter object wraps reading records into a DataSet object and updates the database from the DataSet. The DataAdapter manages Command and DataReader objects and simplifies the synchronization process. You can use the OleDbDataAdapter object to fill a DataSet from a Microsoft ActiveX Data Objects (ADO) Recordset or Record object.

The DataAdapter object is just one way to transfer data between a database and a DataSet. If you need more control over the fill or update functionality, you can write a custom function to manage this process and manipulate the Command and the DataReader objects directly.

For information on the Connection, the Command, and the DataReader classes, how to achieve .NET data provider independence, or how to write your own .NET data provider, click the article number below to view the article in the Microsoft Knowledge Base: For information on the DataSet class, click the article number below to view the article in the Microsoft Knowledge Base: For additional ADO.NET technology roadmap articles, click the article number below to view the article in the Microsoft Knowledge Base:

Architecture

The DataAdapter manages several Command objects:

• SelectCommand
• DeleteCommand
• InsertCommand
• UpdateCommand

The DataAdapter includes three main methods:

• Fill. This method uses the DataReader to read records and then copies the records into a DataSet (or a DataTable object). Many applications (especially Web-based applications) only require read-only access to data. By default, the DataAdapter retrieves only the minimum amount of schema that is necessary (the column name and the data type) to support read-only operations. DataAdapter uses the SelectCommand property to return a DataReader from which the records are read.
• FillSchema. This method queries the database for schema information that is necessary to update. This includes the key columns, the nullability of columns, and so on. To configure the Fill method to retrieve this additional schema information, set the DataAdapter.MissingSchemaAction property value to MissingSchemaAction.AddWithKey.
  
  You can use SelectCommand to return a DataReader from which the schema is read. Because it is expensive to retrieve the DataSet schema, either store the schema as an Extensible Markup Language (XML) file, or use a typed DataSet.
• Update. When you update the database, the DataAdapter checks the RowState property of each DataRow object in the DataTable. The DataAdapter then calls the DeleteCommand, the InsertCommand, or the UpdateCommand property to change to the database as appropriate. You can hook the RowUpdating and the RowUpdated events to customize how DataAdapter processes the updates.

The DataAdapter does not have any information about the database that it is reading from and writing to; the DataAdapter only runs the commands that it manages. Therefore, the DataAdapter does not create DataRelation objects in the DataSet. To create DataRelation objects in the DataSet, you must use one of the following methods:

• Create the objects programmatically at run time.
• Load the schema from an XML Schema Definition (XSD) file.
• Build the objects into the design-time DataSet schema.

For more information about ADO.NET DataAdapter architecture, refer to the following topics in the Microsoft Visual Studio .NET Online Help documentation (on the Visual Studio .NET Help menu, click Contents):

• Visual Studio .NET
  • .NET Framework
    • Programming the .NET Framework
      • Accessing Data with ADO.NET
        • Overview of ADO.NET
          • .NET Data Providers (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetproviders.asp)

How To Articles, Walkthroughs, and QuickStart Samples

Microsoft Knowledge Base "How To" articles provide step-by-step instructions about how to accomplish specific tasks. Walkthroughs provide mini-tutorials that walk you through typical application development scenarios. QuickStart sample files provide code samples for your reference.

The Visual Studio .NET Online Help topics, QuickStart sample files, walkthroughs, and Microsoft Knowledge Base articles in the sections to follow describe how to use the ADO.NET DataAdapter.

QuickStart Samples

QuickStart sample files are installed on your computer in one of two locations. If you install the QuickStart sample files as part of Visual Studio .NET, the sample files are located in the following folder: If you install the QuickStart sample files as part of the .NET development platform, the sample files are located in the following folder: Microsoft Knowledge Base Articles

Fill a DataSet

You can use the Fill method of the DataAdapter object to populate a DataSet with data. By default, the Fill method only supplies sufficient schema for read-only access to the data. To make updates, use one of the following methods:

• Use the FillSchema method.
• Load the schema from an XSD file.
• Use a typed DataSet.

NOTE: If a Web Form application uses read-only data, it is more efficient for the application to bind to a DataReader instead of using the DataAdapter to fill a DataSet.

Visual Studio .NET Online Help DocumentationQuickStart Samples If you installed the QuickStart samples on your computer, the following subfolders contain additional samples:

• Adorstodataset subfolder. The sample in this subfolder uses the OleDbDataAdapter class to read an ADODB.Recordset into a DataSet. This sample uses reflection to open the Recordset late-bound (early binding is generally preferred).
• Gettingdata subfolder. The sample in this subfolder uses the SqlDataAdapter class to fill a DataSet.
• Relationaldata subfolder. The sample in this subfolder uses SqlDataAdapter to fill a DataSet with two tables. This sample creates a DataRelation class and uses the GetChildRows method to navigate to child records.

Knowledge Base Articles

Update a Single Table

In a single table update, you call the Update method of the DataAdapter. By default, the Update method ends and raises a DBConcurrencyException exception when the method encounters a row that fails to update. A row may fail to update because of a concurrency violation or a constraint violation. To determine this, the DataAdapter examines the RowsAffected property that is returned by the command that you use to update the database.

If the database does not return a RowsAffected property (for example, SQL Server SET NOCOUNT ON), DataAdapter assumes that all updates succeed. If you set the DataAdapter.ContinueUpdateOnError property to True, the DataAdapter tries to update all rows in the DataSet. Any errors that DataAdapter encounters are logged in the HasErrors and RowError properties of the DataRow objects that failed to update.

Additional Notes

• If the primary key is an Autonumber or Identity value, if the database automatically generates the primary key, or if the database fills in default field values, you must select the record again in the InsertCommand property to obtain the new field values.
  
  If the database can modify the field values during the course of a UPDATE statement (for example, through the update trigger), you must select the record again in the UpdateCommand property.
• If the primary key is an Autonumber or Identity column, the DataSet can automatically generate temporary values that are updated when you write back data to the database. To avoid conflicts with rows that already exist in the database, you can set the DataColumn.AutoIncrementSeed and the DataColumn.AutoIncrementStep property to -1. This causes new rows to have negative key values until you write them to the database.

Visual Studio .NET Online Help DocumentationQuickStart Samples If you installed the QuickStart samples on your computer, the following subfolders contain additional samples:

• Adooverview4 subfolder. The sample in this subfolder builds commands for SqlDataAdapter. The sample uses SqlDataAdapter to fill a DataSet and write back updates to the database.
• Updatingdata subfolder. The sample in this subfolder demonstrates how to use SqlDataAdapter and SqlCommandBuilder to update from or fill a DataSet.

Knowledge Base Articles

Update Parent and Child Rows

You may find it more difficult to update both parent rows and child rows in a batch process than if you send updates back to the database as the updates occur. To avoid referential integrity problems on the database (you need one DataAdapter for each DataTable), you must update rows in the following order:

• Process grandchild rows that are deleted.
• Process child rows that are deleted.
• Process parent rows that are deleted.
• Process parent rows that are updated.
• Process parent rows that are inserted.
• Process child rows that are updated.
• Process child rows that are inserted.
• Process grandchild rows that are updated.
• Process grandchild rows that are inserted.

To obtain a subset of deleted rows, pass DataViewRowState.Deleted to the DataTable.Select method. To obtain a subset of updated rows, pass DataViewRowState.ModifiedCurrent to the DataTable.Select method. To obtain a subset of inserted rows, pass DataViewRowState.Added to the DataTable.Select method.

Additional Notes

• If you cannot modify the primary key after a row is added to the database, you can use one step to process the rows that are updated and inserted.
• If you can modify the primary key after a row is added to the database, you need a database mechanism to cascade the updated key value to child records. Without this, the update causes a referential integrity violation. You must also change the UpdateCommand property of child tables to accept either the original or the current value of the foreign key when you perform the concurrency check.
• If the primary key is an Autonumber or Identity value, if the database automatically generates the primary key, or if the database fills in default field values, you must select the record again in the InsertCommand property to obtain the new field values. The DataSet automatically propagates the new key value to the child records so that these records have the correct foreign key value when they are inserted.
• If you update from a DiffGram that must be merged back into the main DataSet, for the DataSet.Merge method to work correctly, the key values must not change. If the key value can possibly change (for example, if you insert the value into a table with an Identity column), you must hook the RowUpdated event of the DataAdapter. This prevents the DataAdapter from automatically calling the DataRow.AcceptChanges method. You must call this method on the main DataSet after the Merge method.

Visual Studio .NET Online Help Documentation

Map Table and Field

When you use the DataAdapter.Fill and the Update methods, you specify a DataTable name. The DataTable name can differ from the database table name. In addition, the DataColumn name can differ from the column name on the database. Table mapping and field mapping are useful if you need to combine the data from two tables into one DataTable, especially if the field names do not match exactly.

You can also create TableMapping and FieldMapping objects and add these objects to the DataAdapter.TableMappings collection. If you create these objects and add them to this collection, you do not have to explicitly specify the DataTable name during the Fill and the Update methods. If you add a DataAdapter component to your project in Visual Studio .NET, you can edit the TableMapping and FieldMapping objects by using property pages at design time.

Visual Studio .NET Online Help DocumentationMSDN Articles:

Build Commands and Parameters

When you use the SqlClient, the OleDb, and the Odbc .NET data providers, the commands can be SQL statements, SQL batches, or stored procedures. However, third-party .NET data providers may not support a command language.

You can use one of the following methods to create the commands:

• Visual Design Tools. When you drag tables from the Server Explorer onto your form or component design surface, the visual design tools create Connection and DataAdapter objects that you can manipulate in the property pane. You can use graphical command builders and wizards to configure the DataAdapter. These graphical command builders and wizards include various configuration options, such as optimistic concurrency checking, creating stored procedures, rereading updated records, and so on. To see the code that is generated, click to expand the designer-created region in the Code window.
• CommandBuilder object. Each .NET data provider includes a CommandBuilder object. This object hooks the RowUpdating event of the DataAdapter. The CommandBuilder object uses the SelectCommand as a basis for building (at run time) any commands that are needed to process the updates. However, CommandBuilder requires that you load the appropriate schema into the DataSet and makes additional schema calls to the database. As a result, it can be fairly expensive to use CommandBuilder.
  
  The CommandBuilder object does not include any options to customize how you build the command. CommandBuilder always uses optimistic concurrency checking and never rereads records. Therefore, this object is useful in only a few situations. Do not deploy this method on systems where database performance is critical. You cannot modify the commands that the CommandBuilder generates. If you do, CommandBuilder regenerates the command during the next attempt to update.
• Custom Commands. For the most flexibility and control, you can write your own commands and parameter bindings. For example, you must write custom commands if you use the TimeStamp property for concurrency checking instead of checking every field value.
  
  When you create your own commands, if you select the record again after an insert or an update, you must set the Command.UpdatedRowSource property appropriately. When you create parameters, you must bind them to the specific RowVersion of the record. For example, when you run an UPDATE statement, bind the parameters for the updated values to DataRowVersion.Current, and then bind the parameters for the concurrency check to DataRowVersion.Original. Examine the code that the designer generates to see how to form the command text and parameters.

Visual Studio .NET Online Help Documentation

Hook Events

The DataAdapter raises the following three events:

• FillError. DataAdapter raises the FillError event if a problem occurs during the Fill method (such as a constraint violation in the DataSet).
• RowUpdating. DataAdapter raises the RowUpdating event immediately before it runs the DeleteCommand, InsertCommand, or UpdateCommand for each DataRow. You can use this event to selectively bypass rows for update.
• RowUpdated. DataAdapter raises the RowUpdated event immediately after it runs the command. You can use this event to monitor the status of the update, as well as to prevent the DataAdapter from automatically calling AcceptChanges on the DataRow.

Visual Studio .NET Online Help Documentation

Troubleshooting

If you encounter problems and need answers to your questions, consult the MSDN newsgroups. The MSDN newsgroups are the best place to obtain answers to your questions. In the MSDN newsgroups, you can share your experiences with your peers or search the Microsoft Knowledge Base for articles about specific issues.