Skip to main content
:ProfileText:
Home Library Learn Samples Downloads Support Community Forums
Application Deployment with Shared and Private Deployments (C#)
SQL Server 2005 Compact Edition Technical Articles
Application Deployment with Shared and Private Deployments (C#)
 

Microsoft Corporation

January 2007

Applies to:
   Microsoft Visual Studio 2005
   Microsoft SQL Server 2005 Compact Edition

Summary: In this tutorial, you will learn about two different deployment scenarios available for deploying SQL Server 2005 Compact Edition with your applications: centrally installing SQL Server 2005 Compact Edition and privately installing SQL Server 2005 Compact Edition as part of your application. You will be using Visual C#.NET in this tutorial. There is another tutorial that shows how to use Visual Basic.NET to perform the same tasks. (22 printed pages)

Contents

Introduction
Tutorial Objective
Conclusion

Tutorial requirements

The following applications are required to run this tutorial:

Credentials used

None

Level

200

Estimated time to complete this tutorial

20 minutes

Introduction

In this tutorial, you will learn about two different deployment scenarios available for deploying SQL Server 2005 Compact Edition with your applications: centrally installing SQL Server 2005 Compact Edition and privately installing SQL Server 2005 Compact Edition as part of your application.

When you centrally install SQL Server 2005 Compact Edition, all applications that use SQL Server 2005 Compact Edition use the same SQL Server 2005 Compact Edition files. The centrally installed SQL Server 2005 Compact Edition files are automatically updated and serviced by Microsoft Update. However, any user that performs the application installation must have administrative rights.

When you privately install SQL Server 2005 Compact Edition, as part of your application, the SQL Server 2005 Compact Edition files are installed directly into the application directory. The privately installed SQL Server 2005 Compact Edition files do not affect any other applications; therefore, the application installation can be performed by virtually any user because administrative rights are not required. However, updating and servicing the privately installed SQL Server 2005 Compact Edition files is the responsibility of the application author because the private SQL Server 2005 Compact Edition files are not updated or serviced by Microsoft Update.

You will be using Visual C#.NET in this tutorial. There is another tutorial that shows how to use Visual Basic.NET to perform the same tasks.

Tutorial Objective

In this tutorial, you will learn about two different options for deploying SQL Server 2005 Compact Edition with your applications: SQL Server 2005 Compact Edition central installation and SQL Server 2005 Compact Edition private application installation. At the completion of this tutorial, you will know how to perform both central installations and private installations of SQL Server 2005 Compact Edition and be able to identify the advantages and disadvantages of each SQL Server 2005 Compact Edition installation technique.

In this tutorial, you will perform the following exercises:

  • Application deployment using central (or shared) deployment of SQL Server 2005 Compact Edition
  • Application deployment using private (or local) deployment of SQL Server 2005 Compact Edition

Exercise 1: Performing Application Deployment Using Central (or Shared) Deployment of SQL Server 2005 Compact Edition

In this exercise, you will learn how to perform application deployment using central (or shared) deployment of SQL Server 2005 Compact Edition. When using a central install of SQL Server 2005 Compact Edition, all applications that use SQL Server 2005 Compact Edition will use the same SQL Server 2005 Compact Edition files.

The main benefits of centrally installing SQL Server 2005 Compact Edition are that all applications on the computer use a common SQL Server 2005 Compact Edition installation and Microsoft Update automatically updates and services the SQL Server 2005 Compact Edition installation. The main concern when centrally installing SQL Server 2005 Compact Edition is that the user performing the application installation must have administrative rights on the computer to successfully complete the install.

To open the project

Note   The project that you use in this tutorial is a slightly modified version of the Visual Studio project created in the tutorial entitled "Updatable Cursor Programming with ResultSet (C#)". The only modifications to the project are the addition of two text boxes located at the bottom of the application form: one displays the location of the application directory and the other displays the location of the data directory.
  1. Start Visual Studio 2005 by clicking Start | All Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005.
  2. Click File | Open | Project/Solution.
  3. Navigate within the Open Project dialog box to the folder in which you installed this tutorial's files, and then to the Exercises\SSCEDeploymentCSharp subdirectory, select the SSCEDeploymentCSharp.sln file, and click Open.

    Visual Studio opens the solution.

To configure the application for deployment with SQL Server Compact Edition centrally installed

Note   As listed in the Tutorial Requirements near the beginning of this manual, you must have Visual Studio 2005 Service Pack 1 installed to successfully complete this tutorial.
  1. If the Solution Explorer pane is not already visible, open it by clicking View | Solution Explorer in Visual Studio.
  2. In the Solution Explorer, expand the SSCEDeploymentCSharp project.
  3. Double-click Properties, which is displayed under SSCEDeploymentCSharp, as shown in Figure 1.

    Figure 1. Properties listed in the Solution Explorer

    Visual Studio displays the Project Designer, which you use to configure the available project settings. The configuration setting values are grouped into panes, with each pane accessible by way of the tabs listed on the left edge of the Project Designer.

  4. Click on the Publish tab to display the publication settings pane, as shown in Figure 2.

     

    Click here for larger image

    Figure 2. The publication settings pane (Click on the picture for a larger image)

  5. Click Prerequisites... listed under the Install Mode and Settings section on the Publish pane.

    The Prerequisites dialog box is displayed, as shown in Figure 3.

    Figure 3. The Prerequisites dialog box

  6. Both SQL Server 2005 Compact Edition and .NET Framework 2.0 should already be checked. If either of these two prerequisites is not already checked, check it now.
    Note   If SQL Server 2005 Compact Edition was not already checked when you opened the Prerequisites dialog box, your computer probably doesn't have Visual Studio 2005 Service Pack 1 installed. With Visual Studio 2005 Service Pack 1 installed, Visual Studio automatically makes SQL Server 2005 Compact Edition a prerequisite to the project when a SQL Server Compact Edition database file is added to the project. In the case of this project, Visual Studio made SQL Server 2005 Compact Edition a project prerequisite when the Northwind.sdf file was added to the project. If you do not have Visual Studio 2005 Service Pack 1 installed, you might encounter difficulty in completing this tutorial.
  7. Select the radio button for Download prerequisites from the same location as my application.
    Note   Choosing Download prerequisites from the same location as my application places the setup files for SQL Server Compact Edition on your Web site in addition to the setup files for your application. At the time that this tutorial is being written you must choose to download the SQL Server Compact Edition files from the same location as your application because SQL Server Compact Edition is not yet a released product; therefore, the SQL Server Compact Edition setup files referenced by ClickOnce are not yet available from the vendor's (Microsoft) Web site. If you are doing this tutorial after the public release of SQL Server Compact Edition, you might want to try selecting Download prerequisites from the component vendor's Web site. Configuring the ClickOnce package to download SQL Server Compact Edition from the vendor's Web site reduces the download burden on your Web site and ensures that the user gets the most up-to-date version of SQL Server Compact Edition.
  8. Click OK to close the Prerequisites dialog box.
  9. Click File | Close on the Visual Studio menu to close the Project Designer.

Understanding the DataDirectory substitution string

  1. In the Solution Explorer, right-click on the Form1.cs file and click View Code.

    Visual Studio displays a window containing the code associated with the form.

  2. Within the Form1 constructor, notice that the connection string that is passed to the SqlCeConnection constructor includes the |DataDirectory| substitution string in the data source path as shown in the following line of code. 
    _conn = new SqlCeConnection(@"Data Source = |DataDirectory|\Northwind.sdf");

    At run time, the DataDirectory substitution string is replaced with the appropriate directory path for the application's current execution environment. Without the DataDirectory substitution string, your application would have to programmatically determine the location of the database file based on the current execution environment and then dynamically build the path used in the connection string.

  3. Close the Form1.cs window.

To publish your application for ClickOnce deployment

  1. In the Solution Explorer pane, right-click the SSCEDeploymentCSharp project and select Publish, as shown in Figure 4.

    Figure 4. Selecting Publish on the SSCEDeploymentCSharp context menu

    The Publish Wizard appears.

  2. In response to the question on where to publish the application, leave the default location of http://localhost/SSCEDeploymentCSharp/, and click Finish, as shown in Figure 5.

    Figure 5. Specifying the location to publish the application

    After the wizard builds and publishes the application and verifies the publication, the wizard opens Internet Explorer with the Web page that it has published, as shown in Figure 6. It might take a few moments before the Web page is displayed.

    Figure 6. The generated publication to be used to install the SSCEDeploymentCSharp application

    Notice the two links on the generated page. The Launch link launches the application without performing any prerequisites checks. The Install link installs any of the prerequisites that are not already installed and then installs and launches the application. You are now ready to test installing your application.

To prepare your computer so that the ClickOnce install experience is consistent with the end-user's experience

For the purposes of this tutorial, if you already have SQL Server 2005 Compact Edition installed on your computer, you will uninstall it. With SQL Server Compact Edition removed from your computer, you can test the ClickOnce installation behavior as an end user who does not have SQL Server 2005 Compact Edition already installed experiences the ClickOnce installation.

  1. Close Visual Studio by choosing File | Exit. If you are prompted to save files, click Yes.

    This is necessary because Visual Studio is using a reference to SQL Server 2005 Compact Edition and will therefore interfere with the SQL Server Compact Edition uninstall.

  2. Open the Windows Control Panel by selecting Control Panel from the Windows Start menu
  3. Choose Add or Remove Programs from the Windows Control Panel.
  4. If Microsoft SQL Server 2005 Compact Edition is listed in the Currently installed programs list, select it and then click Remove. Otherwise, proceed to the next task.
  5. Click Yes on the Add or Remove Programs confirmation dialog box.

    You have successfully removed SQL Server Compact Edition from your computer.

To install and run your application with ClickOnce

  1. On the publication Web page, click Install. Your computer then downloads the ClickOnce setup program that Visual Studio generated when you published your application. The setup program checks that your computer has all of the necessary prerequisites installed and installs any prerequisites that are missing.
  2. In response to the File Download - Security Warning dialog box, click Run, as shown in Figure 7.

    Figure 7. The File Download security warning

  3. In response to the security warning that the publisher cannot be verified, click Run, as shown in Figure 8.

    Figure 8. The Setup security warning

    The setup program executes and as part of the prerequisites check determines that SQL Server 2005 Compact Edition is not installed. The setup program then displays an installation dialog box, as shown in Figure 9. The setup program automatically combines the End User License Agreement (EULA) for each of the required prerequisites and displays the combined EULA on the setup dialog box. In this exercise, the setup dialog box displays only the SQL Server Compact Edition EULA because SQL Server Compact Edition is the only prerequisite that the setup program is installing.

    Figure 9. SSCEDeploymentCSharp Setup, indicating that the SQL Server 2005 Compact Edition prerequisite is about to be installed

  4. Click Accept on the SSCEDeploymentCSharp Setup dialog box.

    The SSCEDeploymentCSharp Setup installs SQL Server 2005 Compact Edition.

  5. In response to the security warning that the publisher cannot be verified, click Install, as shown in Figure 10.

    Figure 10. The Application Install security warning

    After installation, the application appears, as shown in Figure 11.

    Figure 11. The deployed application

    The application is now fully installed on your local computer. In addition to installing your application, the ClickOnce installation process has centrally installed SQL Server Compact Edition; therefore, SQL Server Compact Edition is available for use by all applications on your computer.

    All aspects of your program are now locally installed on your computer including the SQL Server Compact Edition database file. All changes that you make to the data in the application are stored to the local database. Notice that your program is now available under Start | All Programs | Microsoft | SSCEDeploymentCSharp. You can exit the application then launch it from the Start menu and see that any changes that you make to the application data are maintained between program executions. You can also use Add or Remove Programs in the Windows Control Panel to uninstall the application.

    The Application Directory and Data Directory values at the bottom of the application form show the current locations of the application directory and data directory. You'll recall that when you ran the program directly from the executable file created by Visual Studio that the SQL Server Compact Edition database file was located in the same directory as the application. When you install the application by way of ClickOnce, the application directory and data directory are now in different locations. This change in the location of the SQL Server Compact Edition database file relative to the application directory is why it is crucial to use the DataDirectory substitution string when specifying the path to the data source instead of using a fixed path.

  6. Close the application.

Exercise 2: Performing Application Deployment Using Private (or Local) Deployment of SQL Server 2005 Compact Edition

In this exercise, you will learn how to perform application deployment using private (or local) deployment of SQL Server 2005 Compact Edition. When using a private install, the SQL Server 2005 Compact Edition files are deployed with the application and placed in the application directory. A private SQL Server 2005 Compact Edition installation makes SQL Server 2005 Compact Edition only available to the program with which it is installed; no other programs are affected by the installation.

The main benefits of privately installing SQL Server 2005 Compact Edition are that the user performing the application installation can do so without having administrative rights and when privately installing SQL Server 2005 Compact Edition, the application author has control over the updates applied to the SQL Server 2005 Compact Edition files used by the application. The main concern when privately installing SQL Server 2005 Compact Edition is that the application author assumes the responsibility of updating and servicing the SQL Server 2005 Compact Edition files; the privately installed SQL Server 2005 Compact Edition files are not updated or serviced by Microsoft Update.

To open the project

  1. Start Visual Studio 2005 by clicking Start | All Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005.
  2. Click File | Open | Project/Solution.
  3. Navigate within the Open Project dialog box to the folder in which you installed this tutorial's files, and then to the Exercises\SSCEDeploymentCSharp subdirectory, select the SSCEDeploymentCSharp.sln file, and click Open.

    Visual Studio opens the project you used in Exercise 1.

To configure the application for ClickOnce deployment with SQL Server Compact Edition privately installed

  1. If the Solution Explorer pane is not already visible, open it by clicking View | Solution Explorer in Visual Studio.
  2. In the Solution Explorer, expand the SSCEDeploymentCSharp project.
  3. Double-click Properties, which is displayed under SSCEDeploymentCSharp, as shown in Figure 12.

    Figure 12. Properties listed in the Solution Explorer

    Visual Studio displays the project designer tabs to allow you to configure project settings.

  4. Click on the Publish tab to display the publication settings pane, as shown in Figure 13.

     

    Click here for larger image

    Figure 13. The publication settings pane (Click on the picture for a larger image)

  5. Click Prerequisites..., listed under the Install Mode and Settings section on the Publish pane.

    The Prerequisites dialog box is displayed.

  6. Uncheck the box next to SQL Server 2005 Compact Edition, as shown in Figure 14.

    You remove SQL Server Compact Edition as a project prerequisite because you are copying the SQL Server Compact Edition files as part of your application. With SQL Server Compact Edition marked as a prerequisite, the ClickOnce setup utility will perform a central install of SQL Server Compact Edition on the target machine as it did in Exercise 1 of this tutorial.

    Figure 14. The Prerequisites dialog box

  7. Click OK to dismiss the Prerequisites dialog box.
  8. Close the project properties window.

To add the SQL Server 2005 Compact Edition files to the project

To privately install the SQL Server Compact Edition with your application, you must include the SQL Server Compact Edition DLL files with your application and copy the DLL files to the application directory.

  1. In the Solution Explorer, right-click on the SSCEDeploymentCSharp project.
  2. On the context menu that appears, click Add | Existing Item....
  3. In the Add Existing Item dialog box, change the Files of type drop-down box to the value All Files (*.*).
  4. Still in the Add Existing Item dialog box, navigate to the location where the SQL Server Compact Edition DLL files are installed, which by default is C:\Program Files\Microsoft SQL Server Compact Edition\v3.1.
  5. Select the files sqlceca30.dll, sqlcecompact30.dll, sqlceer30EN.dll, sqlceme30.dll, sqlceoledb30.dll, sqlceqp30.dll, and sqlcese30.dll, as shown in Figure 15.

    Figure 15. Selecting SQL Server 2005 Compact Edition DLLs

  6. Click Add.

    The selected DLL files are added to the project and are now visible in the Solution Explorer pane, as shown in Figure 16.

    Figure 16. SQL Server 2005 Compact Edition DLLs in the Solution Explorer

  7. Select all seven of the SQL Server Compact Edition DLL files in the Solution Explorer.
  8. With the seven SQL Server Compact Edition DLL files selected, right-click on the selected DLL files and click Properties.
  9. In the Properties pane, set the value of the Copy to Output Directory property to Copy if newer.

    With the Copy to Output Directory property set to Copy if newer, Visual Studio copies the SQL Server Compact Edition DLL files to the application directory when building or publishing the project. Visual Studio copies the SQL Server Compact Edition DLL files to the application directory if the DLL files do not exist in the application directory or if the SQL Server Compact Edition DLL files in the project directory are newer than the existing DLL files in the application directory.

  10. Expand References in the Solution Explorer pane, and select System.Data.SqlServerCe.
  11. In the Properties pane, change the value of the Copy Local property to True.

    With the Copy Local property set to True, Visual Studio copies the System.Data.SqlServerCe assembly to the application directory when building or publishing the project. When you centrally install SQL Server Compact Edition as you did in Exercise 1, the installation process installs the System.Data.SqlServerCe assembly in the GAC. In the case of a private SQL Server Compact Edition installation, the System.Data.SqlServerCe assembly might not be available from the GAC; therefore, your application needs the assembly to be in the application directory. When you privately install SQL Server Compact Edition with your application, the application always uses the copy of the System.Data.SqlServerCe assembly in the application directory even if SQL Server Compact Edition is centrally installed on the computer running your application because the .NET assembly loader always prefers a local copy of an assembly over a copy installed in the GAC.

To test that your application copies all required files to the output directory

  1. In Visual Studio, click Build | Rebuild Solution and wait for the build to complete.
  2. Display the entire contents of the project directory and subdirectories within the Solution Explorer by clicking the Show All Files button located at the top of the Solution Explorer pane.
  3. Still in the Solution Explorer, expand the bin folder.
  4. Expand the Debug folder.

    Notice that Visual Studio copies all of the specified SQL Server Compact Edition DLL files, including the System.Data.SqlServerCe assembly, to the output directory, as shown in Figure 17.

    Note   If you do not see the DLL files and the assembly listed under the bin\Debug directories, refresh the Solution Explorer view by either clicking the Refresh button on the Solution Explorer or selecting View | Refresh from the Visual Studio menu.

    Figure 17. Viewing output files in the Solution Explorer pane

To view the list of files that will be installed with your application

  1. In the Solution Explorer, double-click Properties to open the Visual Studio Project Designer.
  2. Select the Publish tab to display the publication settings pane.
  3. On the publication settings pane, click Application Files..., which is located under the Install Mode and Settings section.

    The Application Files dialog box is displayed, as shown in Figure 18. The ApplicationFiles dialog box lists all of the files that are installed with your application including whether each file is installed in the application directory or the data directory.

    Figure 18. The Application Files dialog box, displaying a list of files to be installed with the application

  4. Dismiss the Application Files dialog box by clicking Cancel.

To publish your application for ClickOnce deployment

  1. In the Solution Explorer pane, right-click the SSCEDeploymentCSharp project.
  2. On the context menu that pops up, click Publish, as shown in Figure 19.

    Figure 19. Clicking Publish on the SSCEDeploymentCSharp context menu

  3. On the Publish Wizard Where do you want to publish the application? dialog box, leave the default location of http://localhost/SSCEDeploymentCSharp/, as shown in Figure 20. Click Finish to complete the wizard.

    Figure 20. Specifying the location to publish the application

    After the wizard builds and publishes the application, the wizard opens Internet Explorer displaying the application installation Web page, as shown in Figure 21. It might take a few moments before the Web page is displayed.

    Figure 21. The generated publication to be used to install the SSCEDeploymentCSharp application

    Notice that the application installation page that Visual Studio generates in this case is simpler than the application installation page from Exercise 1 of this tutorial. By converting the application to install SQL Server Compact Edition locally rather then installing SQL Server Compact Edition centrally, you have eliminated the need for application prerequisite installs; therefore, the application installation page need only provide a way to install the actual application.

To install and run your application with ClickOnce

  • On the publication Web page, click Install to test your application's installation. Click Install in response to any security warnings.

    After installation, the application appears, as shown in Figure 22.

    Figure 22. The deployed application

    The application is now fully installed on your local computer and is completely self-contained with all of the SQL Server Compact Edition DLL files and assemblies located in the application directory. The ClickOnce private installation of SQL Server Compact Edition makes SQL Server Compact Edition fully available to your application but does not centrally install SQL Server Compact Edition on the local computer and does not impact any other applications on the local computer. By configuring the application installation to privately install SQL Server Compact Edition, any user can install the application because the user performing the install can do so without being required to have administrator rights.

    Other then installing the SQL Server Compact Edition DLL files and assembly in the application directory, all aspects of your program are identical to when you performed the ClickOnce install in Exercise 1 of this tutorial. The ClickOnce install installs the SQL Server Compact Edition database file on the local computer in the appropriate data directory and all changes that you make to the data in the application are stored to the local database. Just as in Exercise 1, your program is now available under Start | All Programs | Microsoft | SSCEDeploymentCSharp and you can exit the application then launch it from the Start menu and see that any changes that you make to the application data are maintained between program executions. You can also use Add or Remove Programs in the Windows Control Panel to uninstall the application.

To verify that the SQL Server 2005 Compact Edition files are installed with the application

  1. If the application is not already running, start it now.
  2. Highlight and copy the contents of the application's Application Directory text box.
  3. Select Start | Run on the Windows Start bar and paste the value that you copied from the Application Directory text box into the Run dialog box.
  4. Click OK.
  5. A Windows Explorer Window opens and displays the contents of the application directory, as shown in Figure 23.

    In this window, you can see that the application, the SQL Server Compact Edition DLL files, and the System.Data.SqlServerCe assembly are installed into the application directory.

    Figure 23. The contents of the application directory

  6. Now, highlight and copy the contents of the application's Data Directory text box.
  7. Select Start | Run on the Windows Start bar and paste the value that you copied from the Data Directory text box into the Run dialog box.
  8. Click OK.
  9. A Windows Explorer Window opens and displays the contents of the data directory, as shown in Figure 24. In this window, you can see that the Northwind.sdf data file is installed into the directory.

    Figure 24. The contents of the data directory

Conclusion

In this tutorial, you performed the following exercises:

  • Application deployment using central (or shared) deployment of SQL Server 2005 Compact Edition
  • Application deployment using private (or local) deployment of SQL Server 2005 Compact Edition

In this tutorial, you learned about two different deployment scenarios available for SQL Server 2005 Compact Edition applications. You learned how to perform both a central installation and a private installation of SQL Server 2005 Compact Edition with your application.


© Microsoft Corporation. All rights reserved.

© 2013 Microsoft. All rights reserved.