TableAdapter Query Configuration Wizard

Article
02/04/2013

The TableAdapter Query Configuration Wizard helps create and edit the additional queries you can add to TableAdapters. A TableAdapter query is any valid SQL query or stored procedure that returns data conforming to the same schema as the TableAdapter's associated data table (or that returns a scalar value). After you complete the wizard, a method is added to the TableAdapter that, when called, executes the query. (For example, CustomersTableAdapter.FillByCity(NorthwindDataSet.Customers, "Seattle").)

Running the Wizard

Drag queries onto the Dataset Designer, or configure existing queries (any query listed below the first query).

The first query in a TableAdapter is the TableAdapter's main query. Editing this main query opens the TableAdapter Configuration Wizard and edits the schema of the TableAdapter's data table. All queries listed below the main query are additional queries and are configured using the TableAdapter Query Configuration Wizard. For more information on running the wizard, see How to: Start the TableAdapter Query Configuration Wizard.

Choose Your Data Connection

Choose an existing connection from the list of connections or click New Connection to open the Add/Modify Connection Dialog Box (General) and create a connection to your database.

Upon completion of the Connection Properties dialog box, the Connection Details area displays read-only information about the selected provider as well as the connection string.

Save the Connection String to the Application Configuration File

Choose Yes, save the connection as to store the connection string in the application configuration file. Type a name for the connection or use the provided default name.

Saving connection strings in the application configuration file simplifies the process of maintaining your application if the database connection changes. In the event of a change in the database connection, you can edit the connection string in the application configuration file. That way, you do not have to edit the source code and recompile your application. For information on editing a connection string in the application configuration file, see How to: Save and Edit Connection Strings.

Security Note
Information is stored in the application configuration file as plain text. To reduce the possibility of unauthorized access to sensitive information, you may want to encrypt your data. For more information, see Encrypting and Decrypting Data.

Use SQL Statements

This section explains how to complete the TableAdapter Query Configuration Wizard when selecting the Use SQL statements option.

Choose a Query Type

The wizard creates several types of queries depending on the requirements of your application. You can choose SELECT queries that return rows of data (a data table) or SELECT queries that return a scalar value (a single value such as Count or Sum).

On the Choose a Query Type page, select the type of query to create from the list of available queries.

Note

Creating an INSERT, UPDATE, or DELETE statement does not replace the TableAdapter's commands that are used when calling the TableAdapter's Update method. For example, selecting UPDATE as a query type will create a new query with a name you specify later in the wizard. You execute this query by calling this named method of the TableAdapter. Calling the TableAdapter's Update method will execute statements created when the original TableAdapter was configured.

Specify a SQL <Query Type> Statement

On the Specify a SQL Statement page, type the SQL statement to execute when calling the query.

Tip

The wizard provides access to the Query Builder, a visual tool for creating SQL queries. To open it, click the Query Builder button. For more information on using the Query Builder, see Query and View Designer Tools or press F1 in that dialog box.

Choose Methods to Generate

This page provides options for selecting which methods the wizard generates for the query.

Fill a DataTable
Creates a method for filling the data table. You pass the name of the data table as a parameter when calling this method to fill the data table with the returned data.

Optionally, you can change the default name in the Method Name box. Providing a meaningful name can be helpful when working with this query in code.
Return a DataTable
Creates a method for returning a filled data table. In certain applications, it can be more desirable to return a filled data table as opposed to filling the existing data table with data.

Optionally, you can change the default name in the Method Name box.

Choose Function Name

Type a name for the function. Creating a TableAdapter query adds a method to the TableAdapter with the name provided here. Call this method to execute the query. Providing a meaningful name is helpful when working with this query in code.

Note

When creating new stored procedures, you are asked for two names. The first name is the name of the stored procedure created in the database; the second name is the name of the method on the TableAdapter that executes the stored procedure when called.

Create New Stored Procedures

This section explains how to complete the TableAdapter Query Configuration Wizard when selecting the Create new stored procedures option.

In the Generate the Stored Procedures page, type the SQL statement to execute when calling the stored procedure.

Note

The wizard provides access to the Query Builder, a visual tool for creating SQL queries. To open it, click the Query Builder button. For more information on using the Query Builder, see Query and View Designer Tools or press F1 in that dialog box.
In the Create the stored procedures page, do the following:
1. Type a name for the new stored procedure.
2. Specify whether to create the stored procedure in the underlying database.
  
  Note
  
  The ability to create a stored procedure in the database is determined by the security settings for the specific database.
The View Wizard Results page shows the results of creating the TableAdapter query. If the wizard encounters problems, this page provides the error information.

Use Existing Stored Procedures

This section explains how to complete the TableAdapter Query Configuration Wizard when selecting the Use existing stored procedures option.

Select an existing stored procedure from the drop-down list on the Choose an existing stored procedure page of the wizard.

The Parameters and Results for the selected stored procedure are displayed for reference.
Click Next.

Choose the Shape of Data Returned by the Stored Procedure

The type of data returned by the selected stored procedure determines how the wizard creates the TableAdapter methods.

Select the type of data returned by this query.

Selecting Tabular data opens the Choose Methods to Generate page (described earlier on this Help page), which allows you to specify the types of methods, method names, and paging support to be created.
Selecting A single value creates a typed method that returns a single value. This option opens the Choose Function Name page (described earlier on this Help page).
Selecting No value creates a typed method that executes the stored procedure and expects no data to be returned. This option opens the Choose Function Name page (described earlier on this Help page).

View Wizards Results

The View Wizard Results page shows the results of creating the TableAdapter query. If the wizard encounters problems, the details are displayed on this page.

Share via