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 CommandBuilder class namespaces for several ADO.NET data providers are listed in Table 30-1.
Table 30-1. Provider-specific CommandBuilder classes
Table 30-2 describes the commonly used public properties of the CommandBuilder class.
Table 30-2. CommandBuilder properties
The commonly used public methods of the CommandBuilder class are listed in Table 30-3.
Table 30-3. CommandBuilder methods
Public static members of the CommandBuilder class are safe for multithreaded operations; instance members aren't guaranteed to be thread-safe.