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