15.1 SqlCommandBuilder Class Overview

The command builder can automatically generate commands used by the data adapter to update changes made to a DataSet back to the data source. The class is limited to single-table updates using SQL statements.

The command builder uses the SelectCommand object of the DataAdapter to retrieve the metadata required to build the update command objects DeleteCommand , InsertCommand, and UpdateCommand. If the SelectCommand is changed, the RefreshSchema( ) method should be called to generate new update commands. This forces the command builder to regenerate its updating logic when the Update( ) method of the DataAdapter is called or when one of the update commands is retrieved from the command builder using GetDeleteCommand, GetInsertCommand, or GetUpdateCommand.

The SelectCommand must contain a primary key, or at least one unique column, so that records can be located by the generated DeleteCommand and UpdateCommand. The SelectCommand must also contain all the required columns without default values in the DataRow for the generated InsertCommand to work. Also, as previously mentioned, the SelectCommand must return data from only one table. An InvalidOperation exception is raised otherwise, and the commands aren't generated.

The command builder is useful because it lets you update the data source with changes made to the DataSet using very little code. It also lets you create update logic without understanding how to code the actual delete, insert, and update SQL statements. There are drawbacks, however, including slower performance because of the time that it takes to request metadata and construct the updating logic, updates that are limited to simple single-table scenarios, and a lack of support for stored procedures.