Walkthrough: Using SQL Server Compact Edition in an Application

Data Access in Client and Middle-Tier Programming

This topic is new for Visual Studio 2005 SP1.

This walkthrough provides step-by-step instructions for incorporating a SQL Server Compact Edition database into a Windows-based application.

During this walkthrough, you will learn how to:

Create a new Windows-based application.
Add a SQL Server Compact Edition database to a project.
Create data-bound Windows Forms controls.
Deploy the application (including the database) by using ClickOnce deployment.

Prerequisites

In order to complete this walkthrough, you need:

Visual Studio 2005 SP1
SQL Server Compact Edition
SQL Server Compact Edition Tools for Visual Studio

Creating the Windows-based Application

The first step is to create a Windows Application project.

To create a new Windows Application project

From the File menu, create a new project.
Name the project SQLCompactWalkthrough.
Click Windows Application and then click OK. For more information, see Creating Windows-based Applications.

The SQLCompactWalkthrough project is created and added to Solution Explorer.

Adding the Database and Creating the Data Source

The next step after creating the project is to add the SQL Server Compact Edition database to the project and create a new data source based on tables in the database. The new data source will be used for drag-and-drop data binding from the Data Sources window in the next section.

In this procedure you will create a data source based on the Customers table in the Northwind sample database by using the Data Source Configuration Wizard. The Data Source Configuration Wizard automatically starts when a SQL Server Compact Edition database file is added to a project.

Note
The Northwind sample database for SQL Server Compact Edition is called Northwind.sdf and is located at C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0.

To create a data source

On the Project menu, click Add Existing Item.
In the Add Existing Item dialog box, browse to the location of the Northwind.sdf file, and then click Add.

Note

In the Add Existing Item dialog box, you have to click All Files (*.*) in the Files of type list to make the .sdf file visible.

After you add the database file, the Data Source Configuration Wizard opens to the Choose your Database Objects page.
Expand the Tables node and select the Customers table.
Click Finish and the database (Northwind.sdf) file is added to the project, together with a typed dataset (NorthwindDataset).

Note
In the Add Existing Item dialog box, you have to click *All Files (.)* in the Files of type list to make the .sdf file visible.

Creating Controls to Display the Data

Now that the data source is created and available in the Data Sources window, you can drag the table onto a Windows Form to create a data-bound grid.

To add a data-bound grid to the Windows Form

From the Data Sources window, drag the Customers node onto Form1.
Press F5 to run the application.

The form appears with the customer data loaded into the grid.

Deploying the Application

Assuming the application is complete, you are now ready to deploy it. For this walkthrough, you will use ClickOnce deployment.

Creating a ClickOnce deployment for an application that has a SQL Server Compact Edition database consists of configuring the appropriate publish information in the Project Designer.

Note
Adding a SQL Server Compact Edition database file to a project automatically configures the appropriate publish information for ClickOnce deployment. However, in this walkthrough you will inspect and verify each setting to understand how deployment works.

To configure ClickOnce deployment for an application that has a SQL Server Compact Edition database

To open the Project Designer, in Solution Explorer, double-click My Project if you are working on a Visual Basic project (or Properties if you are working on a C# project).
Click the Publish tab.
Click Application Files and then set the Publish Status of Northwind.sdf file to Data File (Auto).

This setting notifies the installer to put this file in the Data Directory.
Click Prerequisites and then select the check box for SQL Server Compact Edition.

This setting notifies the installer to check whether the SQL Server Compact Edition runtime exists, and to install it from the Internet if it is not found.

Creating the Installer

Now that the publish information is configured to deploy the SQL Server Compact Edition database together with the application, it is time to create the installer.

To create the installer

In the Publishing Location area, click the ellipsis button (...) to open the Open Web Site dialog box.
Select File System.
Create a new folder to deploy the installer to, and then select it.

Note

You can create the folder anywhere. Just remember where it is so that you can locate it later.
Click Open.
Click Publish Now to create the installer.

Note
You can create the folder anywhere. Just remember where it is so that you can locate it later.

Next Steps

Copy the folder you just created to a computer that does not contain SQL Server Compact Edition, and then install the application by running Setup. The installer will detect the SQL Server Compact Edition prerequisite and install SQL Server Compact Edition as part of the application's installation.

To create a new Windows Application project

To create a data source

To add a data-bound grid to the Windows Form

To configure ClickOnce deployment for an application that has a SQL Server Compact Edition database

Creating the Installer

To create the installer

Tasks

Concepts

Other Resources