The CommandBuilder has poorer performance compared to custom update logic because of the time it takes to request metadata and construct the updating logic. The update logic the CommandBuilder generates is limited to simple single-table scenarios and provides no support for stored procedures.

To generate update commands for the DataAdapter using a CommandBuilder object, you must set the SelectCommand property of the DataAdapter to the SQL statement that retrieves data into the DataSet. The CommandBuilder uses the SELECT statement to retrieve the metadata needed to generate the DELETE, INSERT, and UPDATE statements. The update logic is generated for the DeleteCommand, InsertCommand, and UpdateCommand objects of the DataAdapter when the Update( ) method of the DataAdapter is called or when the commands are explicitly retrieved.

If the SelectCommand is changed after the metadata is retrieved, the RefreshSchema() method of the CommandBuilder should be called to update the metadata that generates the update logic; otherwise, the DeleteCommand, InsertCommand, and UpdateCommand retain the values based on the original metadata.

The SelectCommand must return a primary key or at least one unique column. Otherwise, an InvalidOperationException is raised.

Because the CommandBuilder is a connected class, each .NET data provider implements its own CommandBuilder, with a similar interface and function as other CommandBuilder classes but in its own unique namespace. The

Get ADO.NET in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.