Walkthrough: Adding a SQL Server Compact Database to an Application and Deploying it

  • Article

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

This walkthrough illustrates the following tasks:

  • Creating a new Windows-based application.

  • Adding a SQL Server Compact database to a project.

  • Creating data-bound Windows Forms controls.

  • Deploying the application (including the database) by using ClickOnce deployment.

Prerequisites

In order to complete this walkthrough, you need:

  • SQL Server Compact

Creating a Windows-Based Application

The first step is to create a Windows-based application project.

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.

To create a new Windows-based application

  1. On the File menu, choose New, Project.

  2. Name the project SQLCompactWalkthrough.

  3. Choose Windows Application and then choose the OK button. For more information, see Developing Client 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 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 ConfigurationWizard. The Data Source Configuration Wizard automatically starts when a SQL Server Compact database file is added to a project.

Note

The Northwind sample database for SQL Server Compact 4.0 is called Northwind.sdf and is located at C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Samples or at C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Samples.

To create a data source

  1. On the Project menu, choose Add Existing Item.

  2. In the Add Existing Item dialog box, browse to the location of the Northwind.sdf file, and then choose the Add button.

    Note

    In the Add Existing Item dialog box, you have to choose Data Files in the Objects of type list to make the .sdf file visible.

    The Data Source Configuration Wizard opens.

  3. On the Choose a Database Model page, select Dataset, and then choose the Next button.

  4. On the Choose Your Database Objects page, expand the Tables node and select the Customers table check box.

  5. Choose the Finish button and the database (Northwind.sdf) file is added to the project, together with a typed dataset (NorthwindDataset).

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

  1. From the Data Sources window, drag the Customers node onto Form1.

  2. Choose 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 database consists of configuring the appropriate publish information in the Project Designer.

Note

Adding a SQL Server Compact 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 database

  1. To open the Project Designer, in Solution Explorer, choose My Project if you are working on a Visual Basic project, or Properties if you are working on a C# project.

  2. On the Publish tab, choose the Application Files button, and then set the Publish Status of Northwind.sdf to Data File (Auto).

    This setting notifies the installer to put this file in the Data Directory.

  3. Choose the OK button.

  4. Choose Prerequisites and then select the SQL Server Compact check box.

    This setting notifies the installer to check whether the SQL Server Compact 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 database together with the application, it is time to create the installer.

To create the installer

  1. In the Publishing Location area, choose the ellipsis button (...) to open the Open Web Site dialog box.

  2. Select File System.

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

  4. Choose the Open button.

  5. Choose Publish Now to create the installer.

Next Steps

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

See Also

Tasks

How to: Add a SQL Server Compact Database to a Project

How to: Deploy a SQL Server Compact 4.0 Database with an Application

Concepts

SQL Server Compact 4.0 and Visual Studio

Creating Data Applications

Other Resources

Accessing Data in Visual Studio