Walkthrough: Creating a TableAdapter with Multiple Queries

Article
02/04/2013

In this walkthrough, you will create a TableAdapter in a dataset using the Data Source Configuration Wizard. The walkthrough takes you through the process of creating a second query in the TableAdapter using the TableAdapter Query Configuration Wizard within the Dataset Designer.

Tasks illustrated in this walkthrough include:

Creating a new Windows Application project.
Creating and configure a data source in your application by building a dataset with the Data Source Configuration Wizard.
Opening the new dataset in the Dataset Designer.
Adding queries to the TableAdapter with the TableAdapter Query Configuration Wizard.

Prerequisites

In order to complete this walkthrough, you need:

Access to the Northwind sample database (SQL Server or Access version). For more information, see How to: Install Sample Databases.

Creating a New Windows Application

The first step is to create a Windows application.

To create a new Windows Application project

In Visual Studio, from the File menu, create a new project.
Choose a programming language in the Project Types pane.
Click Windows Application in the Templates pane.
Name the project TableAdapterQueriesWalkthrough, and then click OK.

Visual Studio adds the project to Solution Explorer and displays a new form in the designer.

Creating a Database Data Source with a TableAdapter

This step creates a data source using the Data Source Configuration Wizard based on the Customers table in the Northwind sample database. You must have access to the Northwind sample database to create the connection. For information on setting up the Northwind sample database, see How to: Install Sample Databases.

To create the data source

On the Data menu, click Show Data Sources.
In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.
Select Database on the Choose a Data Source Type page, and then click Next.
On the Choose your Data Connection page do one of the following:
- If a data connection to the Northwind sample database is available in the drop-down list, select it.
  
  -or-
- Select New Connection to launch the Add/Modify Connection dialog box. For more information, see Add/Modify Connection Dialog Box (General).
If your database requires a password, select the option to include sensitive data, and then click Next.
Click Next on the Save connection string to the Application Configuration file page.
Expand the Tables node on the Choose your Database Objects page.
Select the Customers table, and then click Finish.

The NorthwindDataSet is added to your project and the Customers table appears in the Data Sources window.

Opening the Dataset in the Dataset Designer

To open the dataset in the Dataset Designer

Right-click NorthwindDataset in the Data Sources window.
On the shortcut menu, choose Edit DataSet with Designer.

The NorthwindDataset opens in the Dataset Designer.

Adding a Second Query to the CustomersTableAdapter

The wizard created the dataset with a Customers data table and CustomersTableAdapter. This section of the walkthrough adds a second query to the CustomersTableAdapter.

To add a query to the CustomersTableAdapter

Drag a Query from the DataSet tab of the Toolbox onto the Customers table.

The TableAdapter Query Configuration Wizard opens.
Select Use SQL statements, and then click Next.
Select SELECT which returns rows, and then click Next.
Add a WHERE clause to the query so that it reads:
```
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax 
FROM Customers 
WHERE City = @City
```
Note

If you are using the Access version of Northwind, replace the @City parameter with a question mark. (SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE City = ?)
On the Choose Methods to Generate page, name the Fill a DataTable method FillByCity.

Note

The method to Return a DataTable is not used in this walkthrough, so you can clear the check box or leave the default name.
Click Next and finish the wizard.

The FillByCity query is added to the CustomersTableAdapter.

Adding Code To Execute the Additional Query on the Form

To execute the query

Select Form1 in Solution Explorer, and click View Designer.
Drag the Customers node from the Data Sources window to Form1.
Change to code view by selecting Code from the View menu.

Replace the code in the Form1_Load event handler with the following to run the FillByCity query.

Dim cityValue As String = "Seattle"
CustomersTableAdapter.FillByCity(NorthwindDataSet.Customers, cityValue)

string cityValue = "Seattle";
customersTableAdapter.FillByCity(northwindDataSet.Customers, cityValue);

Running the Application

To run the application

Press F5.
The grid is filled with customers with a City value of Seattle.

Next Steps

To add functionality to your application

Add a TextBox control and Button control and pass the value in the text box to the query. (CustomersTableAdapter.FillByCity(NorthwindDataSet.Customers, TextBox1.Text)).
Add validation logic to the ColumnChanging or RowChanging event of the data tables in the dataset. For more information, see Validating Data in Datasets.