Database Updates from Datasets

After changes have been made in a dataset, you can transmit the changes to a data source. Most commonly, you do this by calling the Update method of a data adapter. The method loops through each record in a data table, determines what type of update is required (update, insert, or delete), if any, and then executes the appropriate command.

Security Note   When using data commands with a CommandType property set to Text, carefully check information that is sent from a client before passing it to your database. Malicious users might try to send (inject) modified or additional SQL statements in an effort to gain unauthorized access or damage the database. Before you transfer user input to a database, you should always verify that the information is valid; it is a best practice to always use parameterized queries or stored procedures when possible. For more information, see Scripting Exploits.

How an Update Is Transmitted to the Data Source

As an illustration of how updates are made, suppose your application uses a dataset containing a single data table. The application fetches two rows from the database. After the retrieval, the in-memory data table looks like this:

(RowState)     CustomerID   Name             Status
(Unchanged)    c200         Robert Lyon      Good
(Unchanged)    c400         Nancy Buchanan    Pending

Your application changes Nancy Buchanan's status to "Preferred." As a result of this change, the value of the DataRow.RowState property for that row changes from Unchanged to Modified. The value of the RowState property for the first row remains Unchanged. The data table now looks like this:

(RowState)     CustomerID   Name             Status
(Unchanged)    c200         Robert Lyon      Good
(Modified)     c400         Nancy Buchanan    Preferred

You application now calls the Update method to transmit the dataset to the database. The method inspects each row in turn. For the first row, the method transmits no SQL statement to the database, because that row has not changed since it was originally fetched from the database.

For the second row, however, the Update method automatically invokes the proper data command and transmits it to the database. The specific syntax of the SQL statement depends on the dialect of SQL supported by the underlying data store. But the following general traits of the transmitted SQL statement are noteworthy:

  • The transmitted SQL statement is an UPDATE statement. The data adapter knows to use an UPDATE statement because the value of the RowState property is Modified.
  • The transmitted SQL statement includes a WHERE clause indicating that the target of the UPDATE statement is the row whose CustomerID = 'c400'. This part of the SELECT statement distinguishes the target row from all others because the CustomerID is the primary key of the target table. The information for the WHERE clause is derived from the original version of the record (DataRowVersion.Original), in case values required to identify the row have been changed.
  • The transmitted SQL statement includes the SET clause, to set the new values of the modified columns.
    Note   If the data adapter's UpdateCommand property has been set to the name of a stored procedure, the adapter does not construct an SQL statement. Instead, it invokes the stored procedure with the appropriate parameters passed in.

Passing Parameters

Values for records to be updated in the database are usually passed using parameters. When the data adapter's Update method executes an UPDATE statement, it needs to fill in the parameter values. It gets these values from the Parameters collection for the appropriate data command — in this case, the UpdateCommand object in the data adapter.

If you have used the Visual Studio tools to generate a data adapter, the UpdateCommand object will contain a collection of parameters that correspond to each parameter placeholder in the statement.

The SqlParameter.SourceColumn property of each parameter points to a column in the data table. For example, the SourceColumn property for the au_id and Original_au_id parameters is set to whatever column in the data table contains the author id. When the adapter's Update method runs, it reads the author id column from the record being updated and fills the values into the statement.

In an UPDATE statement, you need to specify both the new values (those that will be written to the record) as well as the old values (so that the record to be updated can be located in the database). There are therefore two parameters for each value: one for the SET clause and a different one for the WHERE clause. Both parameters read data from the record being updated, but they get different versions of the column value based on the parameter's SqlParameter.SourceVersion Property. The parameter for the SET clause gets the current version, and the parameter for the WHERE clause gets the original version.

Note   You can also set values in the Parameters collection yourself in code, which you would typically do in an event handler for the data adapter's RowChanging event. For more information, see Parameters in Data-Adapter Commands.

Updating Related Tables

If your dataset contains multiple tables, you have to update them individually by calling the Update method of each data adapter separately. If the tables have a parent-child relationship, it is likely that you will have to send updates to the database in a particular order. A common scenario is that you have added both parent and related child records to a dataset — for example, a new customer record and one or more related order records. If the database itself is enforcing relational integrity rules, it will raise errors if you send the new child records to the database before the parent record has been created.

Conversely, if you delete related records in the dataset, you generally have to send updates in the reverse order: child table first, then parent table. Otherwise, the database is likely to raise an error because referential integrity rules will prevent you from deleting a parent record while related child records still exist.

A general rule for sending updates for related tables is to follow this order:

  1. Child table: delete records.
  2. Parent table: insert, update, and delete records.
  3. Child table: insert and update records.

Refreshing the Dataset

In many circumstances, after updating a data source, you will want to refresh the dataset by repopulating it. Doing so has several benefits:

  • The dataset reflects any changes that have been made to the database by other users.
  • It retrieves that values of columns that are calculated by the database, such as identity columns or columns with default values.
  • It refreshes the timestamp on records in the dataset, in case you want to use the timestamp for concurrency control.

You can refresh a dataset manually by calling an adapter's Fill method after calling its Update method.

Alternatively, you can configure the data adapter to automatically execute an SQL SELECT statement or stored procedure after performing the update. In that case, the data adapter creates two SQL statements for the UpdateCommand and InsertCommand objects. The first statement executes the update, and the second statement is a SELECT statement designed to refresh the dataset.

Note   In order for the second SELECT statement to execute, the data source must support batch queries as in SQL Server.

For more information on configuring the data adapter, see Data Adapter Configuration Wizard.

Concurrency Control

Because datasets are disconnected from the data source, you do not hold locks on records in the data source. Therefore, if you want to update the database, and if it is important for your application to maintain concurrency control, you must reconcile records in the dataset with those in the database. For example, you might find that records in the database have changed since last filling the dataset. In that case, you must execute application-appropriate logic to specify what should happen with the database record or the changed record you have in your dataset. For more information, see Concurrency Control in ADO.NET.

See Also

Dataset Updates in Visual Studio .NET | SqlParameter.SourceColumn Property | OleDbParameter.SourceColumn Property | SqlParameter.SourceVersion Property | OleDbParameter.SourceVersion Property | DataAdapter.Update Method | Parameters in Data-Adapter Commands