Share via

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

  • Article

You have two deployment options for applications that contain SQL Server Compact databases. The method of deployment you choose depends on the servicing requirements of your application and whether your users will need administrative credentials on the computer on which the application will be installed.

Following are the deployment options for SQL Server Compact databases:

  • Traditional Microsoft Windows Installer (Microsoft setup technology)

    • Users need administrative credentials to install the application.

    • SQL Server Compact will be serviced by Microsoft Update.

    • Can use ClickOnce deployment.

    -or-

  • Private file–based deployment (deploying the SQL Server Compact DLLs as part of the project)

    • Users do not need administrative credentials to install the application.

    • SQL Server Compact will not be serviced by Microsoft Update.

    • Can also use ClickOnce deployment.

Traditional Windows Installer

Traditional Windows Installer technology is used in both standard Setup and Deployment projects and in ClickOnce deployment. When you deploy a SQL Server Compact database, ClickOnce deployment provides an option that automatically installs SQL Server Compact if it is not detected on the target computer. For this reason, ClickOnce is the preferred method of deployment for applications that include SQL Server Compact databases (as opposed to creating a custom action in a Setup and Deployment project).

ClickOnce deployment has been updated so that it automatically includes the SQL Server Compact runtime as a prerequisite for applications that include SQL Server Compact databases. It also recognizes .sdf files as data files and sets these to the correct publish status.

Creating a ClickOnce deployment for an application that contains a SQL Server Compact database consists of configuring the proper 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.

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 use Windows Installer technology for ClickOnce deployment of an application that contains a SQL Server Compact 4.0 database

  1. To open the Project Designer, in Solution Explorer/Database Explorer, double-click My Project if you are working on a Visual Basic project (or Properties if you are working on a C# project).

  2. Click the Publish tab.

  3. Click Application Files and set the .sdf file to Data File (Auto).

    This setting notifies the installer to treat this as a local data file and to put it in the Data Directory.

  4. Click Prerequisites and select SQL Server Compact.

    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

After the publish information is configured, create the installer.

To create the installer

  1. In the Publishing Location box, type the Web site, FTP server, or file path to publish the installer to.

  2. Click Publish Now to create the installer.

The application is ready to be installed. Go to the location you published to, and install the application to verify.

Private File–Based Deployment

Private file–based deployment refers to the process of including the required SQL Server Compact DLLs as files in the project (as opposed to a reference to DLLs already on the target computer). If you include the necessary DLLs with the application, the requirement to install SQL Server Compact is removed. Therefore, the administrative credentials are no longer needed.

You can use ClickOnce deployment technology for private file–based deployment. If you do, you must remember to clear the SQL Server Compact prerequisite so that the Setup program does not install it.

To deploy a SQL Server Compact 4.0 database by using private file–based deployment

  1. To open the Project Designer, in Solution Explorer/Database Explorer, double-click My Project if you are working on a Visual Basic project (or Properties if you are working on a C# project).

  2. Click the Publish tab.

  3. Click Prerequisites and then clear the check box for SQL Server Compact.

  4. Close the Project Designer.

  5. Go to the directory that contains the SQL Server Compact DLLs. These are located in C:\Program Files\Microsoft SQL Server Compact Edition\v4.0.

  6. Select the seven SQL Server Compact DLLs and copy them:

    • Sqlceca40.dll

    • Sqlcecompact40.dll

    • Sqlceer40EN.dll

    • Sqlceme40.dll

    • Sqlceoledb40.dll

    • Sqlceqp40.dll

    • Sqlcese40.dll

  7. Paste the DLLs into the project in Solution Explorer/Database Explorer.

  8. Select all seven DLLs in Solution Explorer/Database Explorer and open the Properties window.

  9. Set the Copy to Output Directory property to Copy if newer.

    This will replace any earlier DLLs in an existing application with the newer ones if the application is updated.

  10. Click the Show All Files button in Solution Explorer/Database Explorer.

  11. Expand the References node.

  12. Select System.Data.SqlServerCe.

  13. Set the Copy Local property to True.

    Because your development computer has the SqlServerCe DLLs in the global assembly cache, you must configure the application to use the DLLs in the output directory.

  14. Right-click the project in Solution Explorer/Database Explorer and select Publish to open the Publish Wizard.

  15. Complete the wizard to publish the application.

The application is ready to be installed. Go to the location you published to, and install the application to verify.

See Also

Tasks

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

Walkthrough: Creating a SQL Server Compact Database

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

Concepts

SQL Server Compact 4.0 and Visual Studio

Creating Data Applications

Other Resources

Accessing Data in Visual Studio