SQL Server 2005 Express Edition - Part 12 - ClickOnce Deployment and Updates
December 10, 2007
In the previous installment of our series dedicated to SQL Server 2005 Express Edition, we presented the publishing options of ClickOnce-capable applications (leveraging functionality built into .NET 2.0 Framework) that involve User Instance-based databases, by reviewing the interface exposed in the Publish section of the project Properties window in Visual Basic 2005 Express Edition. In this article, we will take a closer look at the deployment process, demonstrating the impact of your selections on its characteristics.
As we mentioned earlier, there are two basic methods (referred to as "install
modes") of making your database application available to clients. The
first one, known as "online only", requires that an original
publishing location remains reachable from a computer where the application is
launched. Once a user clicks on a link to that location (formatted in the URL
or UNC notation), published files are downloaded temporarily to the ClickOnce
cache on a local disk and executed immediately afterwards. (This temporary area
is located within the
Operating in "online or offline" mode typically implies that, at some point, you will need to deal with an update to the existing version of your application. As you might recall, this is configurable via the Application Updates dialog box, where you can dictate whether the check for a newer release should occur prior or after the code executes (and enforce the minimum allowed revision). The former setting is a bit more disruptive, since it not only introduces a small delay after the application shortcut is selected from the Start->All Programs menu, but downloads and installs the software as soon as it becomes available (assuming you accept the default OK option within the Update Available dialog box). As the result, application assemblies, settings, and the manifest file get stored in their own directory structure, separately from its data (although all of them reside within the ClickOnce Cache).
Application updates introduce an additional challenge, which deals with
preserving existing data. While it is possible to customize this process
through coding, it is significantly easier to take advantage of built-in
ClickOnce behavior. According to its rules, when a deployment of a next version
takes place, all existing data files (included in the previous release or created
subsequently by a user) are automatically copied over to folders hosting the
new installation. If deployed data files have not changed between versions
(this is determined by comparing their hash values), data migration is
completed. Otherwise, the new ones are used instead of their older equivalents,
which, in turn, are moved to a subfolder named
If this mechanism does not meet your needs (as is frequently the case), you might want to exclude database files from being copied to the local computer (by assigning to them Exclude publish status in the Application Files dialog box of the Publish section in the project Properties dialog box) and employ an alternative means of making them available to your users. One way to accomplish this is by scripting the creation of a database (using the "Script Database as" option in its context sensitive menu within Object Explorer of SQL Server Management Studio Express Edition) and populating it with the desired values. The resulting code can then be incorporated into your application. An upgrade would involve developing a script that modifies the existing database (rather than replacing it with a newer copy); including a version check, that triggers its execution.
In general, your choice of deployment strategy will likely depend on the availability of a network connection (and its bandwidth) between the application source and a target computer. As we explained earlier, you can choose between several different methods incorporated into the Publish Wizard (accessible from the Publish section of the project Properties window):
With the default options, users will be prompted to install missing prerequisites, such as .NET Framework 2.0, Windows Installer 3.1, or SQL Server 2005 Express Edition (as defined in the Prerequisites dialog box accessible via the Publish section of the project's Properties dialog box) if they are not already present on the target computer. Note, however, that such actions (unlike application execution or access to a user instance and its auto attached databases) require administrative privileges, so plan their deployment accordingly. In addition, when distributing our sample application, it is important to realize that names of SQL Server 2005 Express Edition parent instances on the source and target computers have to match (this is controlled by the connection string defined in the application's config file and can be modified to match your needs).
Once the code is executing, it will initiate a user instance on the local computer, load the database included with the application, and display already familiar form that contains data grid with records exposed by the vProductAndDescription view. If you decided to configure automatic updates (as described earlier), subsequent application launches will be affected by the configuration you chose. For example, if you picked "Before the application starts" option, you might be presented with the Update Available dialog box we mentioned earlier, which gives you an option to install a new version of the application (assuming there is one available at that point). In addition, especially when using ClickOnce software deployment via Web-based methods, you are likely to run into security related issues, caused by insufficient privileges granted to the executing application. We will take a closer look into Code Access Security topics that deal with this behavior in our next article.