Share via

Advanced SQL Generation Options Dialog Box

  • Article

Allows you to control how SQL statements or stored procedures are created for an adapter. The dialog box appears when you click the Advanced Options button in the SQL statements page of one of the data wizards. The options in this dialog box all pertain to the statements or procedures that are called as part of an update procedure (for example, CustomersTableAdapter.Update(DataTable)).

User Interface Element List

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

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

See Also

Concepts

What's New in Data Application Development

Binding Windows Forms Controls to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio