Automatically Generated Commands

For cases where the SelectCommand is dynamically specified at runtime, such as through a query tool that takes a textual command from the user, you may not be able to specify the appropriate InsertCommand, UpdateCommand, or DeleteCommand at design time. If your DataTable maps to or is generated from a single database table, you can take advantage of the CommandBuilder object to automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter.

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 determines the syntax of the automatically generated INSERT, UPDATE, and DELETE statements.

The CommandBuilder must execute the SelectCommand in order to return the metadata necessary to construct the insert, update, and delete commands. As a result, an extra trip to the data source is necessary which can hinder performance. To achieve optimal performance, specify your commands explicitly rather than using the CommandBuilder.

The 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 CommandBuilder automatically generates the InsertCommand, UpdateCommand, and 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 will not be able to use the CommandBuilder to automatically generate commands and will need to specify your commands explicitly. For information about explicitly setting commands to resolve updates to a DataSet back to the data source, see Updating the Database with a DataAdapter and the DataSet.

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 CommandBuilder will not map output parameters to columns in an updated row by default. In this instance you will need to 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.

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 such 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 new data being overwritten. In cases 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 will not affect any records and a DBConcurrencyException will be thrown.

If you want the UPDATE or DELETE to complete regardless of original values, you will need to 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 standalone 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 CommandBuilder for updating columns involved in a foreign key constraint and instead, explicitly specify the statements used to perform the operation.

Table and Column Names

Automatic command generation logic fails 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. 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.

Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)
custCB.QuotePrefix = "["
custCB.QuoteSuffix = "]"

Dim custDS As DataSet = New DataSet

nwindConn.Open()
custDA.Fill(custDS, "Customers")

' Code to modify data in the DataSet here.

' Without the SqlCommandBuilder, this line would fail.
custDA.Update(custDS, "Customers")
nwindConn.Close()
[C#]
SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", nwindConn);
SqlCommandBuilder custCB = new SqlCommandBuilder(custDA);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";

DataSet custDS = new DataSet();

nwindConn.Open();
custDA.Fill(custDS, "Customers");

// Code to modify data in the DataSet here.

// Without the SqlCommandBuilder, this line would fail.
custDA.Update(custDS, "Customers");
nwindConn.Close();

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 CommandBuilder.

If you want to know what command was automatically generated, you can obtain a reference to the automatically generated commands using the GetInsertCommand, GetUpdateCommand, and GetDeleteCommand methods of the CommandBuilder object, and check the CommandText property of the associated Command.

The following code example writes to the console the update command that was automatically generated.

Console.WriteLine(custCB.GetUpdateCommand().CommandText)

The following example continues the code from the previous example (in the section "Using the CommandBuilder to Automatically Generate an SQL Statement") and recreates the Customers table, replacing the CompanyName column with the ContactName column. The RefreshSchema method is called to refresh the automatically generated commands with this new column information.

nwindConn.Open()

custDA.SelectCommand.CommandText = "SELECT CustomerID, ContactName FROM Customers"
custCB.RefreshSchema()

custDS.Tables.Remove(custDS.Tables("Customers"))
custDA.Fill(custDS, "Customers")

' Code to modify the new table in the DataSet here.

' Without the call to RefreshSchema, this line would fail.
custDA.Update(custDS, "Customers")

nwindConn.Close()
[C#]
nwindConn.Open();

custDA.SelectCommand.CommandText = "SELECT CustomerID, ContactName FROM Customers";
custCB.RefreshSchema();

custDS.Tables.Remove(custDS.Tables["Customers"]);
custDA.Fill(custDS, "Customers");

// Code to modify the new table in the DataSet here.

// Without the call to RefreshSchema, this line would fail.
custDA.Update(custDS, "Customers");

nwindConn.Close();

See Also

Using .NET Framework Data Providers to Access Data | OleDbDataAdapter Class | OdbcDataAdapter Class | SqlDataAdapter Class