30.1 Comments/Troubleshooting

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

Class	Data source
System.Data.SqlClient.SqlCommandBuilder	SQL Server
System.Data.OleDb.OleDbCommandBuilder	OLE DB provider
Microsoft.Data.Odbc.OdbcCommandBuilder	ODBC driver
System.Data.OracleClient.OracleCommandBuilder	Oracle

Table 30-2 describes the commonly used public properties of the CommandBuilder class.

Table 30-2. CommandBuilder properties

Property	Description
DataAdapter	Gets or sets the DataAdapater for which the updating logic is automatically generated.
QuotePrefix	Sets the character or characters that are prefixed to column, table, and other object names within automatically generated SQL statements.
QuoteSuffix	Sets the character or characters that are appended to column, table, and other object names within automatically generated SQL statements.

The commonly used public methods of the CommandBuilder class are listed in Table 30-3.

Table 30-3. CommandBuilder methods

Method	Description
DeriveParameters( )	Populates the collection of parameters for a stored procedure Command object from the data source.
GetDeleteCommand( )	Returns a reference to the automatically generated Command object that performs deletions on the data source when the Update method of the data adapter is called.
GetInsertCommand( )	Returns a reference to the automatically generated Command object that performs inserts into the data source when the Update method of the data adapter is called.
GetUpdateCommand( )	Returns a reference to the automatically generated Command object that performs updates on the data source when the Update method of the data adapter is called.
RefrreshSchema( )	Refreshes the metadata that automatically generates the updating logic.

Public static members of the CommandBuilder class are safe for multithreaded operations; instance members aren't guaranteed to be thread-safe.