TableAdapter Configuration Wizard

The TableAdapter Configuration Wizard creates and edits TableAdapters. The wizard creates TableAdapters based on SQL statements you enter or on existing stored procedures in the database. The wizard can also create new stored procedures in the database based on SQL statements you enter into the wizard.

A TableAdapter connects to a database, executes queries or stored procedures against a database, and fills a DataTable with the data returned by the query or stored procedure. In addition to filling existing data tables with data, TableAdapters can return new data tables filled with data. For more information, see TableAdapter Overview.

Running the Wizard

Run the wizard to create or edit TableAdapters and their associated DataTables. For more information, see How to: Start the TableAdapter 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.

Choose a Command Type

Choose which type of command to execute against the database.

On the Choose a Command Type page, select from the following methods of fetching data from the database:

• Use SQL statements allows you to type a SQL statement to select the data from your database.

• Create new stored procedures: Select this option to have the wizard create new stored procedures (in the database) based on the specified SELECT statement.

• Use existing stored procedures: Select this option to map stored procedures that already exist in your database to the SELECT, INSERT, UPDATE, and DELETE commands of the TableAdapter.

Use SQL Statements

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

On the Generate the SQL statements page, type the SQL statement that, when executed, will fill your data table with data.

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.

The View Wizard Results page shows the results of creating the TableAdapter. If the wizard encounters any problems, this page provides the error information.

Choose Methods to Generate

This page provides options for selecting which TableAdapter methods the wizard generates for fetching and updating data.

• Fill a DataTable
  Creates the TableAdapter's default method for filling the data table. Pass the name of the data table as a parameter when calling this method.

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

• Return a DataTable
  Creates the TableAdapter's 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.

• Create methods to send updates directly to the database
  Creates the INSERT, UPDATE, and DELETE commands necessary for writing changes to the underlying database. Selecting this box is the equivalent to setting the GenerateDBDirectMethods property to true.

  Note

  This option is unavailable when the original SQL statement does not provide enough information or the query is not an updateable query. This situation can occur, for example, in JOIN queries and queries that return a single value (scalar).

Create New Stored Procedures

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

1. In the Generate the stored procedures page, enter the SQL statement that fills the data table. This will be the TableAdapter's SELECT statement and the basis for creating the stored procedures for selecting, updating, inserting, and deleting data.

   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.

2. 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 stored procedures in the database is determined by the security settings and permissions for the specific database. For example, if you do not have permission to create new stored procedures in the database, this option cannot be completed.

   The View Wizard Results page shows the results of creating the TableAdapter. If the wizard encounters any problems, this page provides the error information.

Use Existing Stored Procedures

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

Bind Commands to Existing Stored Procedures

Select the stored procedure to execute for the SELECT, INSERT, UPDATE, and DELETE commands of the TableAdapter. These stored procedures are executed when the associated methods are called on the TableAdapter. For example, the stored procedure assigned to the Update command is executed when the TableAdapter.Update() method is called.

Map parameters from the selected stored procedure to the corresponding columns in the data table. For example, if your stored procedure accepts a parameter named @CompanyName that it passes to the CompanyName column in the table, set the Source Column of the @CompanyName parameter to CompanyName.

Note

The stored procedure assigned to the SELECT command is executed by calling the method of the TableAdapter that you name in the next step of the wizard. The default is Fill, so the typical code to execute the SELECT procedure is TableAdapter.Fill(tableName). Substitute Fill with the name you assign if you change it from the default of Fill, and replace "TableAdapter" with the actual name of the TableAdapter (for example, CustomersTableAdapter).

Advanced Options

Clicking Advanced Options accesses the following advanced options of the TableAdapter. For more information, see Advanced SQL Generation Options Dialog Box.

• Generate Insert, Update, and Delete statements

  When you select this option, the wizard will attempt to generate INSERT, UPDATE, and DELETE statements based on the SELECT statement defined on the Generate SQL statements page.

• Use optimistic concurrency

  Selecting this option modifies the UPDATE and DELETE statements to detect whether individual records have been modified since they were originally read into the data table. An exception is thrown when concurrency violations are detected.

• Refresh the DataTable

  Selecting this option refreshes the data in the table after INSERT and UPDATE statements are executed.

Next Steps

After the wizard has finished, the TableAdapter is added to the dataset and is available for viewing and editing in the Dataset Designer. You might perform a number of steps after that.

• Add more TableAdapters. The wizard creates one TableAdapter. If you intend to use a dataset that contains multiple tables, you will probably want to add more TableAdapters to your dataset.

• Add additional queries to the TableAdapter. For more information, see How to: Create TableAdapter Queries.

• View this dataset in the Data Sources Window and drag items onto your form(s) to create data-bound controls. For more information, see Binding Windows Forms Controls to Data in Visual Studio.

See Also

Tasks

How to: Create TableAdapter Queries

How to: Edit TableAdapter Queries

How to: Connect to Data in a Database

Walkthrough: Displaying Data on a Windows Form

Reference

TableAdapter Query Configuration Wizard

Concepts

What's New in Data Application Development

Binding Windows Forms Controls to Data in Visual Studio

Dataset Designer

Data Sources Overview

Binding Controls to Data in Visual Studio

Validating Data

Other Resources

Data Walkthroughs