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 will 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
- 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 at the bottom of the Properties window, click the Configure DataAdapter link.
The wizard starts.
- 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 Data Link Properties dialog box, which allows you to specify a provider, server name, database name, user name, and password for the connection. For assistance when using this dialog box, click Help in the dialog box.
- 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 help with the Query Builder, press F1 in the builder.
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.
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. For more information on how to change these settings, see Mapping Data-Source Columns to Dataset Data-Table Columns.
- Create a dataset. When you create a data adapter, you usually intend for it to fill a dataset. The wizard does not create the dataset for you. You can generate a new dataset based on the adapters you have, or you can use a dataset that is already in your project. For more information about creating a new dataset, see Creating Typed Datasets with the Component Designer. To use an existing dataset, see Adding Existing Typed Datasets to a Form or Component.
- Preview the data that the adapter will fill into the dataset. For more information, see Previewing Data-Adapter Results.
- 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.
- Add logic to update the database. If the dataset will be changed (records updated, inserted, or deleted), you need to transfer these changes back to the database. You usually do this by calling the adapter's Update method. For more information, see Updating the Database with a DataAdapter and the DataSet.
- If you are working with a form, you can bind controls to the dataset that you generate from the data adapter.
Additional Steps for Further Development
After the wizard has generated the data adapter, you might find it useful to take additional steps related to reading and writing data. Some changes you might make include:
- Changing the SQL statement or stored procedure for one of the adapter's command objects. For more information, see Introduction to DataCommand Objects in Visual Studio.
- Add logic to set the parameters for a command object at run time. For more information, see Using Parameters with a DataAdapter.
- Add validation logic in the dataset to make sure data being put into the dataset is correct. For more information, see Data Validation in Datasets.
Introduction to Data Adapters | Creating Data Adapters | Data Walkthroughs