Walkthrough: Creating a Windows Form to Search Data

Article
04/28/2015

A common application scenario is to display selected data on a form. For example, you might want to display the orders for a specific customer or the details of a specific order. In this scenario, a user enters information into a form, and then a query is executed with the user's input as a parameter; that is, the data is selected based on a parameterized query. The query returns only the data that satisfies the criteria entered by the user. This walkthrough shows how to create a query that returns customers in a specific city, and modify the user interface so that users can enter a city's name and press a button to execute the query.

Using parameterized queries helps make your application efficient by letting the database do the work it is best at — quickly filtering records. In contrast, if you request an entire database table, transfer it over the network, and then use application logic to find the records you want, your application can become slow and non-efficient.

You can add parameterized queries to any TableAdapter (and controls to accept parameter values and execute the query) using the Search Criteria Builder Dialog Box. Open the dialog box by selecting the Add Query command on the Data menu (or on any TableAdapter smart tag).

Tasks illustrated in this walkthrough include:

Creating a new Windows Application project.
Creating and configuring the data source in your application with the Data Source Configuration Wizard.
Setting the drop type of the items in the Data Sources Window. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.
Creating controls that display data by dragging items from the Data Sources window onto a form.
Adding controls to display the data on the form.
Completing the Search Criteria Builder Dialog Box.
Entering parameters into the form and execute the parameterized query.

Prerequisites

In order to complete this walkthrough, you need:

Access to the Northwind sample database. For more information, see How to: Install Sample Databases.

Creating the Windows Application

The first step is to create a Windows Application. Assigning a name to the project is optional at this step but we will give it a name because we are planning on saving it later.

To create the new Windows Application project

From the File menu, create a new project.
Name the project WindowsSearchForm.
Select Windows Application and click OK. For more information, see Developing Client Applications with the .NET Framework.

The WindowsSearchForm project is created and added to Solution Explorer.

Creating the Data Source

This step creates a data source from a database using the Data Source Configuration Wizard. 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.
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.

Creating the Form

You can create the data-bound controls by dragging items from the Data Sources window onto your form.

To create data-bound controls on the form

Expand the Customers node in the Data Sources window.
Drag the Customers node from the Data Sources window to your form.

A DataGridView and a tool strip (BindingNavigator) for navigating records appear on the form. A NorthwindDataSet, CustomersTableAdapter, BindingSource, and BindingNavigator appear in the component tray.

Adding Parameterization (Search Functionality) to the Query

You can add a WHERE clause to the original query using the Search Criteria Builder Dialog Box.

To create a parameterized query and controls to enter the parameters

Select the DataGridView control, and then choose Add Query on the Data menu.
Type FillByCity in the New query name area on the Search Criteria Builder Dialog Box.
Add WHERE City = @City to the query in the Query Text area.

The query should be similar to the following:

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax

FROM Customers

WHERE City = @City

Note

Access and OleDb data sources use the question mark '?' to denote parameters, so the WHERE clause would look like this: WHERE City = ?.
Click OK to close the Search Criteria Builder dialog box.

A FillByCityToolStrip is added to the form.

Testing the Application

Running the application opens your form ready to take the parameter as input.

To test the application

Press F5 to run the application.
Type London into the City text box and then click FillByCity.

The data grid is populated with customers that meet the parameterization criteria. In this example, the data grid only displays customers that have a value of London in their City column.

Next Steps

Depending on your application requirements, there are several steps you may want to perform after creating a parameterized form. Some enhancements you could make to this walkthrough include:

Adding controls that display related data. For more information, see How to: Display Related Data in a Windows Forms Application.
Editing the dataset to add or remove database objects. For more information, see How to: Edit a Dataset.

Partager via