CS 112D Lecture Notes - Using DataAdapter to Update Database from DataSet (Chapter 6 part 2)

Updating a Database from a DataSet

• The DataAdapter's Update method is used to save changes in a DataSet to the data source (database) For its first argument, you can give it a DataSet or an array of DataRows
• If you pass a DataSet into Update, it updates the rows in the order in which they're contained in the DataTable
• If you want to control the order in which changes to the database are made, use the DataTable's Select or GetChanges method to get an array of DataRows and then pass them to Update, which will update just those rows.
• With Data-aware controls, changes to the current record aren't saved unless the BindingContext's current edit is finished. This happens automatically if you navigate to a new record, or you can call EndCurrentEdit() on the BindingContext to make it end the current edit. Then calling Update on the DataAdapter will save the latest changes.
• There are three different ways to set the Update Commannds (InsertCommand, UpdateCommand, and DeleteCommand):
  • Use a CommandBuilder object to generate Commands at run-time
  • Write the code (and stored procedures if desired) for each Command explicitly
  • Use the DataAdapter design-time Component and its Configuration Wizard.

Using a CommandBuilder Object to Generate Update Commands

• A CommandBuilder object uses a DataAdapter's SelectCommand to figure out and generate the corresponding InsertCommand, UpdateCommand, and DeleteCommand. If one of those Commands already exists, the CommandBuilder will leave it intact.
• CommandBuilders are easy but limited. They work only with one table in isolation. They don't consider foreign key issues and they can't handle joins. They also can't handle spaces or other non- alphanumeric characters in table names.
• To use a CommandBuilder, simply construct it using the DataAdapter as an argument.
• After it's instantiated and tied to a DataAdapter, the CommandBuilder will be used when the DataAdapter's Update method is called. That's all there is to it!
• The great advantage of a CommandBuilder is that it generates Commands at run-time, so if you don't know the exact queries at design-time, it's the way to go.

Writing Explicit Update Commands

• This is the most flexible and fastest (in execution) way to code your DataAdapter, but it also requires the most code-writing.
• For each Command, write a query or stored procedure with parameters for each column.
• Instantiate Parameter objects for each parameter, using the SourceColumn and SourceVersion properties to set the parameter value based on the DataSet.

Using the DataAdapter Design-Time Component's Configuration Wizard to Generate Update Commands

• By default in Visual Studio 2005, design-time support for data adapters has been turned off. To turn on this support for data adapters, you must add them to the Toolbox, as described here:
  To add data adapters, connections, and commands to the Toolbox Right-click the Toolbox and choose Choose Items. Select the items you want to add from the .NET Framework Components tab of the Choose Toolbox Items dialog box. (E.g. SqlDataAdapter, SqlConnection, and SqlCommand). Click OK to add the selected items to the Toolbox.
• If you add a DataAdapter component to your form from the Toolbox's Data tab, it opens a configuration wizard.
• This wizard is more powerful and flexible than a CommandBuilder, with several configuration options.
• To run it again on an already-existing DataAdapter component, right-click on the component and select "Configure Data Adapter"

Return to the main CS 112D page