Reconcile DataSet changes with the database

If you retrieve data from the database using an IfxDataAdapter object and make changes to the data in the data set, the IfxCommandBuilder class allows you to generate automatic INSERT, DELETE, and UPDATE commands to reconcile those changes with the database.

Retrieve data into a DataSet includes an example that demonstrates how to use IfxDataAdapter objects. IfxCommandBuilder object to reconcile changes with the database includes an example that demonstrates how to use IfxCommandBuilder objects.

Automatic generation of SQL statements for data reconciliation is initiated when you set the SelectCommand property of an IfxDataAdapter object with the SELECT statement you want to execute. Then, when you create an IfxCommandBuilder object, it automatically generates SQL statements for single-table updates to reconcile changes in the data set with the database. An IfxCommandBuilder object is always associated with an IfxDataAdapter object (in a one-to-one relationship).

The SELECT statement that you execute using the SelectCommand property must return at least one primary key or unique column. If none are present, an InvalidOperation exception is returned, and the reconciliation commands are not generated.

The IfxCommandBuilder object also uses the IfxCommand Connection, CommandTimeout, and Transaction properties for the SELECT statement you are executing (set by the SelectCommand property). If any of these properties are modified, or if the SELECT statement itself is replaced, you should call the IfxCommandBuilder.RefreshSchema method. Otherwise, the InsertCommand, UpdateCommand, and DeleteCommand properties retain their original values.

The IfxCommandBuilder.Dispose method disassociates the IfxCommandBuilder object from the IfxDataAdapter object, and the generated commands are no longer used.

An IfxCommandBuilder object may not generate efficient SQL statements. You can view the commands it generates by using the GetDeleteCommand, GetInsertCommand, and GetUpdateCommand methods.

The following limitations apply to the use of IfxCommandBuilder objects:
  • The SELECT statement must retrieve at least one primary key or unique column as part of the query.
  • The SELECT statement must refer to a single table; it cannot contain stored procedures or views that contain JOIN operators.
  • The SELECT statement must refer to columns that permit read-write operations.
  • The IfxCommandBuilder object makes no attempt, nor does it provide any mechanism, to fetch output arguments from the SELECT statement.
  • If the CommandText, Connection, CommandTimeout or Transaction properties for the query change, you must execute the IfxCommandBuilder.RefreshSchema method.
  • The UPDATE and DELETE commands generated by an IfxCommandBuilder object will not change any row that was modified in the database after the data was read by the SELECT.
  • The IfxCommandBuilder object is designed to work with single, unrelated tables. You cannot use IfxCommandBuilder objects to update tables with primary key/foreign key relationships.
  • If columns in your SELECT command contain special characters, such spaces, periods, quotation marks or non-alphanumeric characters, you cannot use IfxCommandBuilder objects unless you use the QuotePrefix and QuoteSuffix properties to specify the delimiter for table and column names in the queries it generates.

The IfxDataAdapter, IfxCommandBuilder, and other classes are described in detail and illustrated with examples in Type reference.