Update Usage Patterns
The process of using ADO.NET to update data from a DataSet can be outlined as follows:
- Create a DataAdapter object and fill a DataSet object with the results of a database query. The data will be cached locally.
- Make changes to the local DataSet object. These changes can include updates, deletions, and insertions to one or more tables in the locally cached DataSet.
- Initialize the DataAdapter update-related properties. This step configures exactly how updates, deletions, or insertions will be processed. Since there are several ways to handle this, recommended approaches are discussed below in "Initializing DataAdapters for Update."
- Invoke the DataAdapter Update method to submit the pending changes. Each of the changed records of the locally cached DataSet will be processed. (Records with no changes will be automatically ignored by the Update method.)
- Handle exceptions thrown by the DataAdapter Update method. Exceptions arise when the requested changes cannot be made in the database.
(There is one other way to perform updates. You can directly execute a SQL update query using the ExecuteNonQuery method. This technique is appropriate when you want to update specific rows programmatically, without using a DataSet object.)
Initializing DataAdapters for Update
In ADO.NET, you must add your own code for submitting database updates to the DataAdapter object. There are three ways of doing this:
- You can supply your own updating logic.
- You can use the Data Adapter Configuration Wizard to generate the updating logic.
- You can use the CommandBuilder object to generate the updating logic.
It is recommended that you supply your own updating logic. To save time, you can use the Data Adapter Configuration Wizard, but if you do, try not to generate the logic at run time. Do not rely on CommandBuilder objects unless you have to because your performance will suffer and you cannot control the updating logic the objects generate. In addition, a CommandBuilder will not help you submit updates using stored procedures.
You can use CommandBuilder with applications that dynamically generate data access logic, such as reporting or data extraction tools. Using the CommandBuilder eliminates the need for these tools to write their own code-generating modules.
Using Stored Procedures
Using stored procedures for updates allows the administrator of your database to implement more granular security than is available with dynamic SQL, as well as more sophisticated data integrity checks. For example, the stored procedure might insert an entry into an audit log as well as perform the requested update. Stored procedures can provide the best performance as well because of offline query optimization performed within the database on stored procedures. Finally, the insulation between the database structure and the application that stored procedures provide allows for easy maintenance.
Because ADO.NET applications that use stored procedures provide many benefits and are no more difficult to implement than those that make changes directly to the database, this approach is recommended in nearly every case. The exception to this is if you must work with multiple back ends or a database, such as Microsoft Access, that doesn't support them. In those cases, use query-based updates.