After the data in your dataset has been modified and validated, you can send the updated data back to a database by calling the Update method of a TableAdapter. The Update method updates a single data table and runs the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table. TableAdapter update method. TableAdapters use data commands to read to and write from the database. Use the TableAdapter's initial Fill (main) query as the basis for creating the schema of the associated data table, as well as the InsertCommand, UpdateCommand, and DeleteCommand commands that are associated with the TableAdapter.Update method.
-->Definition
Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named 'Table.'
Parameters
- dataSet
- DataSet
The DataSet used to update the data source.
Returns
The number of rows successfully updated from the DataSet.
Implements
Exceptions
The source table is invalid.
An attempt to execute an INSERT, UPDATE, or DELETE statement resulted in zero records affected.
Examples
The following example uses the derived class, OleDbDataAdapter, to Update the data source.
Remarks
The update is performed on a by-row basis. For every inserted, modified, and deleted row, the Update method determines the type of change that has been performed on it (Insert, Update or Delete). Depending on the type of change, the
Insert
, Update,
or Delete
command template executes to propagate the modified row to the data source. When an application calls the Update method, the DataAdapter examines the RowState property, and executes the required INSERT, UPDATE, or DELETE statements iteratively for each row, based on the order of the indexes configured in the DataSet. For example, Update might execute a DELETE statement, followed by an INSERT statement, and then another DELETE statement, due to the ordering of the rows in the DataTable.It should be noted that these statements are not performed as a batch process; each row is updated individually. An application can call the GetChanges method in situations where you must control the sequence of statement types (for example, INSERT before UPDATE). For more information, see Updating Data Sources with DataAdapters.
If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. However, you can create a SqlCommandBuilder or OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the
SelectCommand
property of a .NET Framework data provider. Then, any additional SQL statements that you do not set are generated by the CommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information see Generating Commands with CommandBuilders.The Update method retrieves rows from the table listed in the first mapping before performing an update. The Update then refreshes the row using the value of the UpdatedRowSource property. Any additional rows returned are ignored.
After any data is loaded back into the DataSet, the OnRowUpdated event is raised, allowing the user to inspect the reconciled DataSet row and any output parameters returned by the command. After a row updates successfully, the changes to that row are accepted.
When using Update, the order of execution is as follows:
- The values in the DataRow are moved to the parameter values.
- The OnRowUpdating event is raised.
- The command executes.
- If the command is set to
FirstReturnedRecord
, then the first returned result is placed in the DataRow. - If there are output parameters, they are placed in the DataRow.
- The OnRowUpdated event is raised.
- DataRow.AcceptChanges is called. This will raise both the DataTable.RowChanging and DataTable.RowChanged events for the updated DataRow.
Each command associated with the DataAdapter usually has a parameters collection associated with it. Parameters are mapped to the current row through the
SourceColumn
and SourceVersion
properties of a .NET data provider's Parameter
class. SourceColumn
refers to a DataTable column that the DataAdapter references to obtain parameter values for the current row.SourceColumn
refers to the unmapped column name before any table mappings have been applied. If SourceColumn
refers to a nonexistent column, the action taken depends on one of the following MissingMappingAction values.Enumeration value | Action taken |
---|---|
MissingMappingAction.Passthrough | Use the source column names and table names in the DataSet if no mapping is present. |
MissingMappingAction.Ignore | A SystemException is generated. When the mappings are explicitly set, a missing mapping for an input parameter is usually the result of an error. |
MissingMappingAction.Error | A SystemException is generated. |
The
SourceColumn
property is also used to map the value for output or input/output parameters back to the DataSet
. An exception is generated if it refers to a nonexistent column.The
SourceVersion
property of a .NET data provider's Parameter
class determines whether to use the Original, Current, or Proposed version of the column value. This capability is often used to include original values in the WHERE clause of an UPDATE statement to check for optimistic concurrency violations.Calling the AcceptChanges method or AcceptChanges method will commit all changes in the DataSet or DataTable. If either of these methods are called before the Update method is called, no changes will be committed when the Update method is called, unless further changes have been made since AcceptChanges or AcceptChanges was called.
Note
If an error occurs while updating a row, an exception is thrown and execution of the update is discontinued. To continue the update operation without generating exceptions when an error is encountered, set the ContinueUpdateOnError property to
true
before calling Update. You may also respond to errors on a per-row basis within the RowUpdated
event of a DataAdapter. To continue the update operation without generating an exception within the RowUpdated
event, set the Status property of the RowUpdatedEventArgs to Continue.For every column that you propagate to the data source on Update, a parameter should be added to the
InsertCommand
, UpdateCommand
, or DeleteCommand
. The SourceColumn
property of the parameter should be set to the name of the column. This setting indicates that the value of the parameter is not set manually, but is taken from the particular column in the currently processed row.Applies to
See also
-->This walkthrough provides detailed instructions for running SQL statements directly against a database by using the DBDirect methods of a TableAdapter. The DBDirect methods of a TableAdapter provide a fine level of control over your database updates. You can use them to run specific SQL statements and stored procedures by calling the individual
Insert
, Update
, and Delete
methods as needed by your application (as opposed to the overloaded Update
method that performs the UPDATE, INSERT, and DELETE statements all in one call).During this walkthrough, you will learn how to:
- Create a new Windows Forms Application.
- Create and configure a dataset with the Data Source Configuration Wizard.
- Select the control to be created on the form when dragging items from the Data Sources window. For more information, see Set the control to be created when dragging from the Data Sources window.
- Create a data-bound form by dragging items from the Data Sources window onto the form.
- Add methods to directly access the database and perform inserts, updates, and deletes.
Prerequisites
This walkthrough uses SQL Server Express LocalDB and the Northwind sample database.
- If you don't have SQL Server Express LocalDB, install it either from the SQL Server Express download page, or through the Visual Studio Installer. In the Visual Studio Installer, you can install SQL Server Express LocalDB as part of the Data storage and processing workload, or as an individual component.
- Install the Northwind sample database by following these steps:
- In Visual Studio, open the SQL Server Object Explorer window. (SQL Server Object Explorer is installed as part of the Data storage and processing workload in the Visual Studio Installer.) Expand the SQL Server node. Right-click on your LocalDB instance and select New Query.A query editor window opens.
- Copy the Northwind Transact-SQL script to your clipboard. This T-SQL script creates the Northwind database from scratch and populates it with data.
- Paste the T-SQL script into the query editor, and then choose the Execute button.After a short time, the query finishes running and the Northwind database is created.
Create a Windows Forms application
The first step is to create a Windows Forms Application.
- In Visual Studio, on the File menu, select New > Project.
- Expand either Visual C# or Visual Basic in the left-hand pane, then select Windows Desktop.
- In the middle pane, select the Windows Forms App project type.
- Name the project TableAdapterDbDirectMethodsWalkthrough, and then choose OK.The TableAdapterDbDirectMethodsWalkthrough project is created and added to Solution Explorer.
Create a data source from your database
This step uses the Data Source Configuration Wizard to create a data source based on the
Region
table in the Northwind sample database. You must have access to the Northwind sample database to create the connection. For information about setting up the Northwind sample database, see How to: Install sample databases.To create the data source
- On the Data menu, select Show Data Sources.The Data Sources window opens.
- In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.
- On the Choose a Data Source Type screen, select Database, and then select Next.
- On the Choose your Data Connection screen, do one of the following:
- If a data connection to the Northwind sample database is available in the drop-down list, select it.-or-
- Select New Connection to launch the Add/Modify Connection dialog box.
- If your database requires a password, select the option to include sensitive data, and then select Next.
- On the Save connection string to the Application Configuration file screen, select Next.
- On the Choose your Database Objects screen, expand the Tables node.
- Select the
Region
table, and then select Finish.The NorthwindDataSet is added to your project and theRegion
table appears in the Data Sources window.
Add controls to the form to display the data
Create the data-bound controls by dragging items from the Data Sources window onto your form.
To create data bound controls on the Windows form, drag the main Region node from the Data Sources window onto the form.
A DataGridView control and a tool strip (BindingNavigator) for navigating records appear on the form. A NorthwindDataSet,
RegionTableAdapter
, BindingSource, and BindingNavigator appear in the component tray.To add buttons that will call the individual TableAdapter DbDirect methods
- Drag three Button controls from the Toolbox onto Form1 (below the RegionDataGridView).
- Set the following Name and Text properties on each button.
Name Text InsertButton
Insert UpdateButton
Update DeleteButton
Delete
To add code to insert new records into the database
- Select InsertButton to create an event handler for the click event and open your form in the code editor.
- Replace the
InsertButton_Click
event handler with the following code:
To add code to update records in the database
- Double-click the UpdateButton to create an event handler for the click event and open your form in the code editor.
- Replace the
UpdateButton_Click
event handler with the following code:
To add code to delete records from the database
- Select DeleteButton to create an event handler for the click event and open your form in the code editor.
- Replace the
DeleteButton_Click
event handler with the following code:
Run the application
![Tableadapter Tableadapter](/uploads/1/2/5/8/125864201/986754609.png)
- Select F5 to run the application.
- Select the Insert button, and verify that the new record appears in the grid.
- Select the Update button, and verify that the record is updated in the grid.
- Select the Delete button, and verify that the record is removed from the grid.
Next steps
Depending on your application requirements, there are several steps you might want to perform after creating a data-bound form. Some enhancements you could make to this walkthrough include:
- Adding search functionality to the form.
- Adding additional tables to the dataset by selecting Configure DataSet with Wizard from within the Data Sources window. You can add controls that display related data by dragging the related nodes onto the form. For more information, see Relationships in Datasets.