Saving data back to the database

Saving data back to the database

 

The dataset is an in-memory copy of data. If you modify that data, you might want to save those changes back to the database. You do that in one of three ways:

  • by calling one of the Update methods of a TableAdapter

  • by calling one of the TableAdapter's DBDirect methods.

  • by calling the UpdateAll method on the TableAdapterManager that Visual Studio generates for you when the dataset contains tables that are related to other tables in the dataset.

When you data-bind dataset tables to controls on a Windows Form, or XAML page, the databinding architecture does all this work for you.

If you are familiar with Table Adapters, you can jump directly to one of these topics:

Topic

Description

How to: Insert New Records into a Database

How to perform updates and inserts using TableAdapters or Command Objects

How to: Update Data by Using a TableAdapter

How to perform updates with TableAdapters.

Hierarchical Update

How to perform updates from a dataset with two or more related tables.

Walkthrough: Handling a Concurrency Exception

How to handle exceptions when two users attempt to change the same data in a database at the same time.

How to: Save Data by Using a Transaction

How to save data in a transaction using the System.Transactions namespace and the TransactionScope object

Walkthrough: Saving Data in a Transaction

How to save data in a transaction using the System.Transactions namespace.

Walkthrough: Saving Data to a Database (Multiple Tables)

How to edit records and save changes in multiple tables back to the database.

How to: Save Data from an Object to a Database

How to pass data from an object that is not in a dataset to a database by using a TableAdapter DbDirect method.

Walkthrough: Saving Data with the TableAdapter DBDirect Methods

How to use the TableAdapter to send SQL queries directly to the database.

How to: Save a Dataset as XML

How to save a dataset to an XML document.

Updating a data source is a two-step process. The first step is to update the dataset with new records, changed records, or deleted records. If your application never sends those changes back to the data source, then you are finished with the update.

If you do send the changes back to the database, then a second step is required. If you are not using data-bound controls, then you have to manually call the Update method of the same TableAdapter (or data adapter) that you used to populate the dataset, although you can also use different adapters, for example, to move data from one data source to another or to update multiple data sources. If you are not using databinding, and are saving changes for related tables, you have to manually instantiate a variable of the auto-generated TableAdapterManager class and call its UdpateAll method.

Visual Basic Dataset Updates

Two-stage update process and the role of the DataRowVersion in a successful update

A dataset contains collections of tables, which contain a collections of rows. When adding or removing rows, if you intend to later update an underlying data source you must use the methods on the DataTable.DataRowCollection property. Those methods perform the change tracking needed for updating the data source. If you call the RemoveAt collection on the Rows property, the deletion will not be communicated back to the database.

You can update the contents of a dataset by merging it with another dataset. This involves copying the contents a source dataset into the calling dataset (referred to as the target dataset). When you merge datasets, new records in the source dataset are added to the target dataset. Additionally, extra columns in the source dataset are added to the target dataset. Merging datasets is useful when you have a local dataset and get a second dataset from another application or from a component such as an XML Web service, or when you need to integrate data from multiple datasets.

When merging datasets, you can pass a Boolean argument (preserveChanges) that tells the Merge method whether to retain existing modifications in the target dataset. Because datasets maintain multiple versions of records, it is important to keep in mind that more than one version of the records is being merged. The following table illustrates a record in two datasets that will be merged:

DataRowVersion

Target dataset

Source dataset

Original

James Wilson

James C. Wilson

Current

Jim Wilson

James C. Wilson

Calling the Merge method on the above table with preserveChanges=false targetDataset.Merge(sourceDataset) results in the following:

DataRowVersion

Target dataset

Source dataset

Original

James C. Wilson

James C. Wilson

Current

James C. Wilson

James C. Wilson

Calling the Merge method with preserveChanges = true targetDataset.Merge(sourceDataset, true) results in the following:

DataRowVersion

Target dataset

Source dataset

Original

James C. Wilson

James C. Wilson

Current

Jim Wilson

James C. Wilson

System_CAPS_cautionCaution

In the preserveChanges = true scenario, if the RejectChanges method is called on a record in the target dataset, then it will revert to the original data from the source dataset. This means that if you try to update the original data source with the target dataset, it may not be able to find the original row to update. You can prevent a concurrency violation by filling another dataset with the updated records from the data source and then performing a merge prevents a concurrency violation. (A concurrency violation occurs when another user modifies a record in the data source after the dataset has been filled.)

To make changes to an existing data row, you add or update data in the individual columns. If the dataset contains constraints (such as foreign keys or non-nullable constraints), it is possible that as you update a record — after you have finished updating one column but before you get to the next one — the record can temporarily be in an error state.

To prevent premature constraint violations you can temporarily suspend update constraints. This serves two purposes:

  • It prevents an error from being thrown when you update one column before you get to another column.

  • It suspends certain update events from being raised (events that are often used for validation).

After you have completed an update, you can re-enable constraint checking, which also re-enables update events and raises them.

System_CAPS_noteNote

In Windows Forms, the data-binding architecture built into the datagrid suspends constraint checking until focus moves out of a row, and you do not have to explicitly call the BeginEdit, EndEdit or CancelEdit methods.

Constraints are automatically disabled when the Merge method is invoked on a dataset. When the merge is complete, if there are any constraints on the dataset that cannot be enabled, then a ConstraintException is thrown. In this situation, the EnforceConstraints property is set to false and all constraint violations must be resolved before resetting the EnforceConstraints property to true.

After you have completed an update, you can re-enable constraint checking, which also re-enables update events and raises them.

For more information about suspending events, see How to: Turn off constraints while filling a dataset.

When you update a record in a dataset, there is the possibility of an error. For example, you might inadvertently write data to a column that is of the wrong data type, or too long, or that has some other integrity problem. Or you may have application-specific validation checks that can raise custom errors during any stage of an update event. For more information, see Validating data in datasets.

Information about the changes in a dataset is maintained in two ways: by flagging the row that indicates whether it has been changed (RowState), and by keeping multiple copies of a record (DataRowVersion). By using this information, processes can determine what has changed in the dataset and can send appropriate updates to the data source.

The RowState property of a DataRow object is a value that provides information about the status of a particular row of data.

The following table details the possible values of the DataRowState enumeration:

DataRowState Value

Description

Added

The row has been added as an item to a DataRowCollection. (A row in this state does not have a corresponding original version since it did not exist at the time the last AcceptChanges method was called).

Deleted

The row was deleted using the Delete of a DataRow object.

Detached

The row has been created but is not part of any DataRowCollection. A DataRow object is in this state immediately after it has been created and before it is added to a collection, or if it has been removed from a collection.

Modified

A column value in the row has been changed in some way.

Unchanged

The row has not changed since AcceptChanges was last called.

Datasets maintain multiple versions of records. The DataRowVersion enumeration of a DataRow object is a value that can be used to return a specific version of a DataRow object.

The following table details the possible values of the DataRowVersion enumeration:

DataRowVersion Value

Description

Current

The current version of a record contains all modifications performed on the record since the last time AcceptChanges has been called. If the row has been deleted there is no current version.

Default

The default value of a record, as defined by the dataset schema or data source.

Original

The original version of a record is a copy of the record as it was the last time changes were committed in the dataset. In practical terms, this is typically the version of a record as read from a data source.

Proposed

The proposed version of a record that is available temporarily, while you are in the middle of an update — that is, between the time you called the BeginEdit method and the EndEdit method. You typically access the proposed version of a record in a handler for an event such as RowChanging. Invoking the CancelEdit method reverses the changes and deletes the proposed version of the data row.

The original and current versions are useful when update information is transmitted to a data source. Typically, when an update is sent to the data source, the new information for the database is in the current version of a record. Information from the original version is used to locate the record to update. For example, in a case where the primary key of a record is changed, you must have a way to locate the proper record in the data source, in order to update the changes. If no original version existed, then the record would most likely be appended to the data source, resulting not only in an extra unwanted record, but in one record that is inaccurate and out of date. The two versions are also used in concurrency control; you can compare the original version against a record in the data source to determine if the record has changed since it was loaded into the dataset.

The proposed version is useful when you need to perform validation before actually committing the changes to the dataset.

Even if records have changed, there are not always original or current versions of that row. When you insert a new row into the table, there is no original version, only a current version. Similarly, if you delete a row by calling the table's Delete method, there is an original version, but no current version.

You can test to see if a specific version of a record exists by querying a data row's HasVersion method. You can access either version of a record by passing a DataRowVersion enumeration value as an optional argument when you request the value of a column.

It is common that you do not update every record in a dataset. For example, a user might be working with a Windows Forms DataGridView control that displays many records. However, the user might update only a few records, delete one, and insert a new one. Datasets and data tables provide a method (GetChanges) for returning only the rows that have been modified.

You can create subsets of changed records using the GetChanges method of either the data table (GetChanges) or of the dataset (GetChanges) itself. If you call the method for the data table, it returns a copy of the table with only the changed records. Similarly, if you call the method on the dataset, you get a new dataset with only changed records in it. GetChanges by itself will return all changed records. In contrast, by passing the desired DataRowState as a parameter to the GetChanges method, you can specify what subset of changed records you want: newly added records, records marked for deletion, detached records, or modified records.

Getting a subset of changed records is useful when you want to send records to another component for processing. Instead of sending the entire dataset, you can reduce the overhead of communicating with the other component by getting only the records that the component needs. For more information, see How to: Retrieve Changed Rows.

As changes are made in the dataset, the RowState property of changed rows is set. The original and current versions of records are established and maintained and made available to you by the RowVersion property. The metadata stored in these properties representing the changes is needed to send the proper updates to the data source.

If the changes reflect the current state of the data source, you no longer need to maintain this information. Typically, there are two times when the dataset and its source are in synch:

  • Immediately after you have loaded information into the dataset, such as when you read data from the source.

  • After sending changes from the dataset to the data source, (but not before, because you would lose the change information required to send changes to the database).

You can commit the pending changes to the dataset by calling the AcceptChanges method. Typically, AcceptChanges would be called at the following times in your application.

  • After you have loaded the dataset. If you load a dataset by calling a TableAdapter's Fill method, then the adapter automatically commits changes for you. However, if you load a dataset by merging another dataset into it, then you have to commit the changes manually.

    System_CAPS_noteNote

    You can prevent the adapter from automatically committing the changes when calling the Fill method by setting the AcceptChangesDuringFill property of the adapter to false. If it is set to false, then the RowState of each row inserted during the fill is set to Added.

  • After you have sent dataset changes to another process, such as an XML Web service.

    System_CAPS_cautionCaution

    Committing the change this way erases any change information. Do not commit changes until after you have performed any operations in which your application depends on knowing what changes have been made in the dataset.

This method accomplishes the following:

The AcceptChanges method is available at three levels. You can call it on a DataRow object, which commits changes for just that row. You can also call it on a DataTable object to commit all rows in a table, or on the DataSet object to commit all pending changes in all records of all tables of the dataset.

The following table describes which changes are committed based on what object the method is called on.

Method

Result

DataRow.AcceptChanges

Changes are committed only on the specific row

DataTable.AcceptChanges

Changes are committed on all rows in the specific table

DataSet.AcceptChanges

Changes are committed on all rows in all tables of the dataset

System_CAPS_noteNote

If you load a dataset by calling a TableAdapter's Fill method, you do not have to explicitly accept changes; by default the Fill method calls the AcceptChanges method when it has finished populating the data table.

A related method, RejectChanges, undoes the effect of changes by copying the Original version back into the Current version of records and setting the RowState of each record back to Unchanged.

In order to verify that the data in your application meets the requirements of the processes that it is passed to, you often have to add validation. This might involve checking that a user's entry in a form is correct, validating data sent to your application by another application, or even checking that information calculated within your component falls within the constraints of your data source and application requirements.

You can validate data in several ways:

  • In the business layer, by adding code to your application to validate data. The dataset is one place you can do this. The dataset provides some of the advantages of back-end validation — such as the ability to validate changes as column and row values are changing. For more information, see Validating data in datasets.

  • In the presentation layer, by adding validation to forms. For more information, see User Input Validation in Windows Forms.

  • In the data back end, by sending data to the data source — for example, the database — and allowing it to accept or reject the data. If you are working with a database that has sophisticated facilities for validating data and providing error information, this can be a practical approach because you can validate the data no matter where it comes from. However, it might not accommodate application-specific validation requirements. Additionally, having the data source validate data can result in numerous round trips to the data source, depending on how your application facilitates resolving validation errors raised by the back end.

    System_CAPS_security 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 Script Exploits Overview.

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 TableAdapter (or 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.

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

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

    System_CAPS_noteNote

    If the TableAdapter'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.

Values for records to be updated in the database are usually passed using parameters. When the TableAdapter'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 TableAdapter.

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.

System_CAPS_noteNote

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.

Show:
© 2016 Microsoft