Data Adapter Configuration Wizard

The Data Adapter Configuration Wizard helps you set the properties of a new or existing data adapter. A data adapter contains SQL commands that your application can use to read data into a dataset from a database and write it back again. The wizard can optionally create a data connection that allows the adapter to communicate with a database.

Configuring a data adapter is one of several steps involved in using a dataset in your application. After running the wizard, you still need to separately add a dataset to your application and write code to provide a way for the data adapter to work with the dataset.

Running the Wizard

You can run the wizard to create new adapters or edit existing ones. You can also run the wizard against an existing data adapter to change the adapter's properties.

To run the wizard

1. Do one of the following:

   • If you are creating a new adapter, drag a data adapter object from the Toolbox onto a form or component.

   • If you are editing an existing adapter, select it in the form or component, and then select Configure Data Adapter on the context menu.

   The wizard starts.

2. Specify connection and SQL command information. To help you, the wizard displays several dialog boxes:

   • If you ask to create a connection, the wizard displays the Connection tab of the Add/Modify Connection Dialog Box (General), which allows you to specify a provider, server name, database name, user name, and password for the connection.

   • To help you create SQL statements, the wizard provides the Query Builder, a utility that allows you to create and test a Select statement using visual tools. To launch it, click the Query Builder button when asked for a SQL statement. For more information, see Query and View Designer Tools.

     Tip

     If you specify that you want to create stored procedures, click Preview SQL Script to see the script that the wizard would use to create the stored procedures. You can copy this script and modify it to create your own procedures.

   Note

   If you click Cancel in the wizard, the adapter is still created but its properties are not set. You can set its properties manually in the Properties window, or you can run the wizard again.

What the Wizard Does

The Data Adapter Configuration Wizard runs you through the entire process of creating a data adapter (and, if necessary, a data connection). The wizard accomplishes the following:

• Creates the adapter.

• If necessary, creates a data connection and sets the adapter to use the connection.

• Creates the SQL statement or stored procedures used to read and write data.

• Creates and configures parameters for the SQL statements or stored procedures so that the commands read and write the correct data.

• Maps table and column names between the database and the dataset.

Configuring the Command Objects

The main part of the wizard's work is in configuring the four data command objects that the adapter uses to pass data to and from the database. These include the SelectCommand object, and optionally, the UpdateCommand, InsertCommand, and DeleteCommand objects. Each of these objects contains either a SQL statement or the name of a stored procedure used to read and write data. The properties of each of these commands (especially the CommandText property) are set according to choices you make in the wizard.

The wizard allows you to use either SQL statements or stored procedures for the commands. It gives you these options:

• Use SQL statements. You can specify a Select statement, and if you choose to create them, the wizard builds corresponding Update, Insert, and Delete statements based on what you have included in the Select statement. The Update and Insert statements include parameters to pass new values. The Update, Insert, and Delete statements include parameters in a WHERE clause to locate the correct record in the database.

• Create new stored procedures. This option is similar to using a statement, except that the wizard generates stored procedures instead of statements. Because stored procedures can be faster than SQL statements, you might choose this option for performance. In this option, the wizard generates stored procedures for all four commands (again based on what you specify in the Select command). You can choose to have the new stored procedures written to your database. If you don't, you can still capture the SQL script that the wizard would use to create the stored procedures, and you can execute the script yourself later.

• Use existing stored procedures. In this option, you pick stored procedures for each of the four commands. The wizard displays the names and details of stored procedures that it can find in the database. This option is useful if you already have stored procedures that perform the four database operations.

Advanced Wizard Options

The wizard offers you advanced options that allow you to control how Update, Insert, and Delete commands are created for the data adapter. You can display the Advanced SQL Generation Options dialog box from the wizard's Generate SQL Statements pane by clicking Advanced Options.

This dialog box allows you to specify these options:

• Generate Insert, Update and Delete statements. Indicates that the data adapter will generate additional statements or stored procedures, based on the Select statement, to write data to the database. If the adapter will be used only to read data and not update it, clearing this check box can result in more efficient code.

• Use optimistic concurrency. Indicates that you want the wizard to include logic to test whether a record has changed since you read it from the database.

• Refresh the dataset. Indicates whether you want the wizard to generate code that re-reads a record from the database after updating it. This gives you a refreshed view of a record, including values inserted by the database such as default and auto-incremented values.

For more information, see Advanced SQL Generation Options Dialog Box.

Next Steps

After the wizard has finished, the data adapter (and optionally, connection) are added to your form. You might perform a number of steps after that. Some steps are almost always performed, while others are optional.

Steps That Might Be Required

The following are steps that you often perform after the Data Adapter Configuration Wizard has finished:

• Add more data adapters. The wizard creates one data adapter. Typically, each data adapter is used to read and write data to a single data table. If you intend to use a dataset that contains multiple tables, you will probably want to add more data adapter to your form or component. Each time you add a data adapter from the Toolbox, you rerun the wizard to configure that particular data adapter.

• Change an adapter's table mappings. By default, the adapter assumes that it will be reading and writing to tables and columns with the same names. However, you might be using the adapter to read from a database that uses one name into a dataset that uses different names. Or you might be reading more columns from the database than you need in the dataset.

• Add logic to fill the dataset. To use the data adapter to fill a dataset, you call the adapter's Fill method. This executes the command in the adapter's SelectCommand object and fills the results into the dataset. For more information, see Populating a DataSet from a DataAdapter (ADO.NET).

See Also

Reference

TableAdapter Configuration Wizard

Concepts

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Overview of Data Applications in Visual Studio

Connecting to Data in Visual Studio