Dela via


Walkthrough: Creating a TableAdapter with Multiple Queries

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:

Creating a New Windows Application

The first step is to create a Windows application.

To create a new Windows Application project

  1. In Visual Studio, from the File menu, create a new project.

  2. Choose a programming language in the Project Types pane.

  3. Click Windows Application in the Templates pane.

  4. 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

  1. On the Data menu, click Show Data Sources.

  2. In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.

  3. Select Database on the Choose a Data Source Type page, and then click Next.

  4. 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).

  5. If your database requires a password, select the option to include sensitive data, and then click Next.

  6. Click Next on the Save connection string to the Application Configuration file page.

  7. Expand the Tables node on the Choose your Database Objects page.

  8. 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

  1. Right-click NorthwindDataset in the Data Sources window.

  2. 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

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

    The TableAdapter Query Configuration Wizard opens.

  2. Select Use SQL statements, and then click Next.

  3. Select SELECT which returns rows, and then click Next.

  4. 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 = ?)

  5. 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.

  6. 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

  1. Select Form1 in Solution Explorer, and click View Designer.

  2. Drag the Customers node from the Data Sources window to Form1.

  3. Change to code view by selecting Code from the View menu.

  4. 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 Data Validation Overview.

See Also

Tasks

How to: Create TableAdapters

How to: Create TableAdapter Queries

Concepts

TableAdapter Overview

What's New in Data

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Displaying Data on Forms in Windows Applications

Editing Data in Your Application