Walkthrough: A Parameterized Query Application

Article
09/03/2015

This walkthrough shows you how to use the Visual Studio environment to develop a simple parameterized query application. Databinding and much of the user interface are generated for you automatically. Relying on the familiar Northwind database, this application provides for the scenario where smart device users must determine the Shipping Country when they know only the Order Number. The applications that you build here provide for user input of the Order Number and the consequent display of the corresponding Shipping Country.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

This walkthrough was written using Visual Basic Development Settings and Visual C# Development Settings.

Prerequisites

The Northwind database for SQL Server Compact 3.5, included in Visual Studio.

Note

If you are not an Administrator on your development computer, you cannot open the Northwind.sdf file in its default location, drive:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples. Copy the file to the desktop or to My Documents and open it from there when you are prompted.

Choosing a Target Device

To ensure that you are prompted to select a device when you deploy your solution, complete the following procedure.

To prompt for device choices at deployment time

On the Tools menu, click Options, expand Device Tools, and then click General.
Select the Show device choices before deploying a device project check box.

Creating the Application

This is a simple Windows Forms application to host the data functionality of this walkthrough.

To create a Windows Forms device project

(Visual Basic) On the File menu in Visual Studio, click New Project.

—or—

(Visual C#) On the File menu in Visual Studio, point to New, and then click Project.
In the Project Types pane, expand Visual Basic or Visual C#, click Smart Device, click Smart Device Project in the Templates pane, type ParamQuerySample in the Name box, and then click OK.
In the Add New Smart Device Project dialog box, select Pocket PC 2003 from the Target platform drop-down, click Device Application, and then click OK.

A representation of a Pocket PC device appears in the Windows Forms designer.

Adding Data Functionality

This section consists of the following tasks:

Selecting a type of data source.
Selecting and configuring a data connection.
Selecting database objects.
Adding data-bound controls to the form.

To select a type of data source

On the Data menu, click Add New Data Source to open the Data Source Configuration Wizard.
On the Choose a Data Source Type page, click Database, and then click Next.

To select and configure a data connection

On the Choose Your Data Connection page, click New Connection.
In the Choose Data Source dialog box, click Microsoft SQL Server Compact 3.5, and then click Continue.

Note

Depending on settings and previous projects, the Add Connection dialog box might appear instead of the Choose Data Source dialog box. If this occurs, click Change in the Add Connection dialog box to open the Change Data Source dialog box. Then select Microsoft SQL Server Compact 3.5, and click OK.
In the Add Connection dialog box, select My Computer.
On the Add Connection dialog box, click Browse.
In the Select SQL Server Compact 3.5 Database File dialog box, click Northwind.sdf, and then click Open.
In the Add Connection dialog box, leave the Password box empty.

This database has no password.

Security Note:

Use a strong password for databases that are used in real-world applications.
Click Test Connection to verify the connection.

Note

If access to the Northwind.sdf file is denied, copy the file to the desktop, and browse to that copy to open. This situation can occur if you do not have sufficient rights on the development computer to open the file in its default location, which is listed toward the beginning of this walkthrough.
Click OK on the message box that shows the connection succeeded, and then click OK to close the Add Connection dialog box.
On the Choose Your Data Connection page, click Next.
In the message box that asks whether you want to copy the file to your project, click Yes.

Security Note:
Use a strong password for databases that are used in real-world applications.

To select database objects

On the Choose Your Database Objects page, expand Tables, and then select the Orders table.
Click Finish.

The NorthwindDataset is created. You can view this data source by clicking Show Data Sources on the Data menu.

To create the query

In the Data Sources window, expand the Orders table.
Click the Ship Country column, click the drop-down arrow, and then select the Label option.
Drag the Ship Country column onto the form in the designer.
On the label control in the designer, click the smart tag, and then click Add Query.
In the Search Criteria Builder dialog box, click Query Builder.
In the Filter column of the Order ID row, type a question mark (?).

This symbol indicates that users of the application will have to enter a value for Order ID.
Click OK.

The WHERE clause in the Query Text box should now read ([Order ID]=@PARAM1).
Click OK to close the Search Criteria Builder dialog box.

A panel appears on the form in the designer.

To refine the user interface

Right-click the PARAM1 label control in the designer, and then click Properties.

—or—

Select the Param1Label control in the Properties window.
Change the Text property to Order ID.
Select the FillBy button, and then change its text property to Show country.
Expand the panel and controls to eliminate the scroll bars and show all the text. Be especially careful that the Ship_CountryLabel and its text box are not hidden behind the FillByPanel and its controls.

Testing the Application

In this section you build the application, download it to the Pocket PC 2003 SE emulator, and verify that the application works correctly.

To test the application

On the Debug menu, click Start or Start Debugging.
In the Deploy dialog box, select Pocket PC 2000 SE Emulator, and then click Deploy.

Deployment progress appears in the Status bar. Deployment to the emulator may take some time.
When the application is running on the emulator, type an order number, which runs from 10000 to 11077 in the Northwind database, and then click Show country.

The Ship Country for that order appears in the label control.

Preparing for Additional Walkthroughs

If you plan to do additional walkthroughs or open other projects, you want to shut down the emulator and exit this solution.

To close the emulator and the solution

On the File menu of the emulator, click Exit.
In the Device Emulator message box, click No to the message asking if you want to save the emulator state.
(Visual Basic) On the File menu, click Close Project.

—or—

(Visual C#) On the File menu, click Close Solution.

If you are prompted to save the project or solution, click Save if you want to use it again later; otherwise, click Discard and the files will not be saved.