Generate Insert, Update, and Delete statements

When selected, the wizard will create SQL statements or stored procedures for the adapter's UpdateCommand, InsertCommand, and DeleteCommand objects. (The specifics of the statement or procedure to be generated are controlled by the Choose a Query Type pane of the wizard.) If this check box is cleared, the remaining options in the dialog box are disabled, as they do not apply.

You typically clear this check box under the following circumstances:

• The adapter will be used only to read data from a data source, not write it back. By clearing this box, you reduce the amount of code in your form or component.

• You will fill in the commands yourself after the wizard has finished.

If the box is cleared and there are no statements or stored procedures for the UpdateCommand, InsertCommand, or DeleteCommand objects, calling the data adapter's Update method does nothing.

Use optimistic concurrency

When selected, the wizard generates a SQL command with a WHERE clause that matches the value of every column in the record being updated against the corresponding record in the database.

Note:
This option is available only if the Generate Insert, Update, and Delete Commands check box is selected.

For example, the syntax for an update statement generated using this option might be:

UPDATE Customers
SET CustomerID = ?, CompanyName = ?, Phone = ?
WHERE (CustomerID = ?) AND (CompanyName = ?) AND 
      (Phone = ? OR ? IS NULL AND Phone IS NULL);

If this check box is cleared, the syntax would be:

UPDATE Customers
SET CustomerID = ?, CompanyName = ?, Phone = ?
WHERE (CustomerID = ?)

The effect of checking each column value is that the command fails if any column has changed, which happens if another user has changed the record since it was read into your dataset. If the option is not set and the WHERE clause simply locates the record, the changes made by other users can potentially be overridden without warning by your update.

For more information about concurrency checking when updating from a dataset, see Introduction to Data Concurrency in ADO.NET.

Refresh the data table

When selected, the wizard generates a Select statement for each Update and Insert command.

Note:
This option is available only if the Generate Insert, Update, and Delete Commands check box is selected.

The Select statement is added to the corresponding command object in the adapter and is executed immediately after the Update or Insert command has finished. The Select statement is configured to return just one record, namely the updated version of the record that has just been updated.

Note:
You can add multiple statements to the CommandText property of an adapter's command object by delimiting the statements with a semicolon (;).

Getting a new version of the record writes updated column values for that record into the dataset, including:

• Columns where the database fills in a default value.

• Auto-increment columns.

• Timestamp columns.

• Columns affected by the result of a database trigger.

Because this option causes two statements to be executed for each update (the update statement and a following Select statement), it can be inefficient when a majority of records in a dataset are being updated. In that case, it is more efficient to simply refill the entire data table at once after all updates have been performed.

Note:
Refreshing the data table is only supported on databases that support batching of SQL statements. For example, Microsoft Access cannot refresh the data table in the same command that performs other operations.

Concepts

Other Resources