Generating Commands with CommandBuilders
SelectCommand property is dynamically specified at run time, such as through a query tool that takes a textual command from the user, you may not be able to specify the appropriate
DeleteCommand at design time. If your DataTable maps to or is generated from a single database table, you can take advantage of the DbCommandBuilder object to automatically generate the
UpdateCommand of the DbDataAdapter.
As a minimum requirement, you must set the
SelectCommand property in order for automatic command generation to work. The table schema retrieved by the
SelectCommand property determines the syntax of the automatically generated INSERT, UPDATE, and DELETE statements.
The DbCommandBuilder must execute the
SelectCommand in order to return the metadata necessary to construct the INSERT, UPDATE, and DELETE SQL commands. As a result, an extra trip to the data source is necessary, and this can hinder performance. To achieve optimal performance, specify your commands explicitly rather than using the DbCommandBuilder.
SelectCommand must also return at least one primary key or unique column. If none are present, an
InvalidOperation exception is generated, and the commands are not generated.
When associated with a
DataAdapter, the DbCommandBuilder automatically generates the
DeleteCommand properties of the
DataAdapter if they are null references. If a
Command already exists for a property, the existing
Command is used.
Database views that are created by joining two or more tables together are not considered a single database table. In this instance you cannot use the DbCommandBuilder to automatically generate commands; you must specify your commands explicitly. For information about explicitly setting commands to resolve updates to a
DataSet back to the data source, see Updating Data Sources with DataAdapters.
You might want to map output parameters back to the updated row of a
DataSet. One common task would be retrieving the value of an automatically generated identity field or time stamp from the data source. The DbCommandBuilder will not map output parameters to columns in an updated row by default. In this instance you must specify your command explicitly. For an example of mapping an automatically generated identity field back to a column of an inserted row, see Retrieving Identity or Autonumber Values.
Rules for Automatically Generated Commands
The following table shows the rules for how automatically generated commands are generated.
||Inserts a row at the data source for all rows in the table with a RowState of Added. Inserts values for all columns that are updateable (but not columns such as identities, expressions, or timestamps).|
||Updates rows at the data source for all rows in the table with a
||Deletes rows at the data source for all rows in the table with a
Optimistic Concurrency Model for Updates and Deletes
The logic for generating commands automatically for UPDATE and DELETE statements is based on optimistic concurrency--that is, records are not locked for editing and can be modified by other users or processes at any time. Because a record could have been modified after it was returned from the SELECT statement, but before the UPDATE or DELETE statement is issued, the automatically generated UPDATE or DELETE statement contains a WHERE clause, specifying that a row is only updated if it contains all original values and has not been deleted from the data source. This is done to avoid overwriting new data. Where an automatically generated update attempts to update a row that has been deleted or that does not contain the original values found in the DataSet, the command does not affect any records, and a DBConcurrencyException is thrown.
If you want the UPDATE or DELETE to complete regardless of original values, you must explicitly set the
UpdateCommand for the
DataAdapter and not rely on automatic command generation.
Limitations of Automatic Command Generation Logic
The following limitations apply to automatic command generation.
Unrelated Tables Only
The automatic command generation logic generates INSERT, UPDATE, or DELETE statements for stand-alone tables without taking into account any relationships to other tables at the data source. As a result, you may encounter a failure when calling
Update to submit changes for a column that participates in a foreign key constraint in the database. To avoid this exception, do not use the DbCommandBuilder for updating columns involved in a foreign key constraint; instead, explicitly specify the statements used to perform the operation.
Table and Column Names
Automatic command generation logic may fail if column names or table names contain any special characters, such as spaces, periods, quotation marks, or other nonalphanumeric characters, even if delimited by brackets. Depending on the provider, setting the QuotePrefix and QuoteSuffix parameters may allow the generation logic to process spaces, but it cannot escape special characters. Fully qualified table names in the form of catalog.schema.table are supported.
Using the CommandBuilder to Automatically Generate an SQL Statement
To automatically generate SQL statements for a
DataAdapter, first set the
SelectCommand property of the
DataAdapter, then create a
CommandBuilder object, and specify as an argument the
DataAdapter for which the
CommandBuilder will automatically generate SQL statements.
' Assumes that connection is a valid SqlConnection object ' inside of a Using block. Dim adapter As SqlDataAdapter = New SqlDataAdapter( _ "SELECT * FROM dbo.Customers", connection) Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter) builder.QuotePrefix = "[" builder.QuoteSuffix = "]"
// Assumes that connection is a valid SqlConnection object // inside of a using block. SqlDataAdapter adapter = new SqlDataAdapter( "SELECT * FROM dbo.Customers", connection); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); builder.QuotePrefix = "["; builder.QuoteSuffix = "]";
Modifying the SelectCommand
If you modify the
CommandText of the
SelectCommand after the INSERT, UPDATE, or DELETE commands have been automatically generated, an exception may occur. If the modified
SelectCommand.CommandText contains schema information that is inconsistent with the
SelectCommand.CommandText used when the insert, update, or delete commands were automatically generated, future calls to the
DataAdapter.Update method may attempt to access columns that no longer exist in the current table referenced by the
SelectCommand, and an exception will be thrown.
You can refresh the schema information used by the
CommandBuilder to automatically generate commands by calling the
RefreshSchema method of the
If you want to know what command was automatically generated, you can obtain a reference to the automatically generated commands by using the
GetDeleteCommand methods of the
CommandBuilder object and checking the
CommandText property of the associated command.
The following code example writes to the console the update command that was automatically generated.
The following example recreates the
Customers table in the
custDS dataset. The RefreshSchema method is called to refresh the automatically generated commands with this new column information.
' Assumes an open SqlConnection and SqlDataAdapter inside of a Using block. adapter.SelectCommand.CommandText = _ "SELECT CustomerID, ContactName FROM dbo.Customers" builder.RefreshSchema() custDS.Tables.Remove(custDS.Tables("Customers")) adapter.Fill(custDS, "Customers")
// Assumes an open SqlConnection and SqlDataAdapter inside of a using block. adapter.SelectCommand.CommandText = "SELECT CustomerID, ContactName FROM dbo.Customers"; builder.RefreshSchema(); custDS.Tables.Remove(custDS.Tables["Customers"]); adapter.Fill(custDS, "Customers");