Friday, July 27, 2007

ClickOnce Deployment in .NET Framework 2.0

ClickOnce Deployment in .NET Framework 2.0
By Thiru Thangarathinam
Rating: 4.2 out of 5
Rate this article

It is very common among the developers of previous generations to choose web applications over rich Windows UIs because of the deployment challenges associated with deploying a Smart Client Windows Forms application. However with the release of Visual Studio 2005, Microsoft has released a new technology named ClickOnce that is designed to solve the deployment issues for a windows forms application. This new technology not only provides an easy application installation mechanism but also enables easy deployment of upgrades to existing applications. This article will demonstrate how to take advantage of the excellent features of the ClickOnce deployment technology by discussing examples on how to utilize this new feature.

Introduction

Since the introduction of the powerful server side web technologies such as ASP, JSP, ASP.NET, developers have shown more interest in building web applications rather than in windows applications. The factors that attracted the developers toward web applications can be summarized as follows:

  • A web application is ubiquitous, making it accessible in all the places where an internet connection is available.
  • The second and most important factor is the deployment. With web applications, there is no need to deploy any software on the client side. All the client application needs is just the browser. This makes it possible for the developers to easily deploy updates to the existing web application without impacting the client machines.

If you talk to the developers, you will find that the main reason for preference for web applications over windows applications is the second point in the above list. Even though this is true with traditional applications, Microsoft is making every attempt to ensuring that windows applications can be deployed and updated with the same ease as the web applications. You can see proofs of this in the initial release of .NET Framework when Microsoft introduced the deployment of windows forms application through HTTP. Using this approach, you could simply use HREF HTML element to point to a managed executable (.exe). Then when you click on the HREF link, Internet Explorer can automatically download and install the executable on the client machine. Even though this approach sounds very promising, it also presents some interesting challenges. One of the most important challenges is the downloading of the updated code through the HTTP. Since this process was not transacted, it was possible for the application to be left in an inconsistent state. Moreover there was no way for you to specify if the application could work in offline mode apart from the traditional online mode. Combined with the operational mode issue, this approach also did not provide the ability to create shortcuts that can be used to launch the application. Even though this approach presented itself with a lot of issues, it could still be used in controlled environments. However for complex multi-assembly dependant windows forms applications, you needed a transacted and easily updateable way of deployment. This is exactly what the ClickOnce technology introduced with .NET Framework 2.0 provides. In this article, we will see how to effectively use this technology to deploy a windows form application.

How does ClickOnce Deployment Technology Work?

Before we look at an example, let us understand how this technology works.

  • You create a Windows forms application and use the Publish option to deploy the application onto any of the following locations: File System, Local Web Server, FTP Site, or a Remote Web Site.
  • Once the application is deployed onto the target location, the users of the application can browse to the publish.htm file and install the application onto their machine. Note that publish.htm file is the entry point for installing the application and this will be discussed in the later part of this article.
  • Once the user has installed the application, a shortcut icon will be added to the desktop and the application will also be listed in the Control Panel/Add Remove Programs.
  • When the user launches the application again, the manifest will contain all the information to decide if the application should go to the source location and check for updates to the original application. Let us say, for instance, a newer version of the application is available, it will be automatically downloaded and made available to the user. Note that when the new version is downloaded, it is performed in a transacted manner meaning that either the entire update is downloaded or nothing is downloaded. This will ensure that the application integrity is preserved.

Let us look at an example to understand this.

A Simple ClickOnce Deployment Example

Let us start by creating a simple Visual C# Windows forms application named ClickOnceDemo using Visual Studio 2005. Once the project is created, place a command button on the form and double click on the command button and modify its code to look like the following.

private void btnClick_Click(object sender, EventArgs e)
{
MessageBox.Show(System.Reflection.Assembly.
GetExecutingAssembly().Location);
}

If you run the ClickOnceDemo application and click on the button, you will see an output that is somewhat similar to the following depending on the location of your project folder.

Now that we have created the application, let us publish it. But before we do that, let us look at some of the properties associated with publishing of the forms application. Right click on the ClickOnceDemo project from the solution explorer and select Properties from the context menu. You should see the following dialog box being displayed.

The Publish tab in the Project Properties dialog box shown in the above screenshot provides a number of options for configuring publish related settings. Let us look at each one of these options in detail.

At the top of the properties page is the Publishing Location dropdown box that allows you to specify the location in which the application will be published. When you click on the ... button right next to the dropdown, it brings up the following window wherein you can specify the target virtual directory for publishing.

Using the Publish properties dialog box, you can also specify if the application is available only in online mode or in both online and offline modes. This option is available in the Install Mode and Settings panel. This panel also contains options that let you specify the prerequisites for your windows forms application. To specify this, click on the Prerequisites... command button and you will see the following dialog box.

By default, the .NET Framework 2.0 option will be checked and you can choose any of the remaining options depending on your application requirements.

Right below the Prerequisities option, there is a command button named Updates... that lets you configure the manner and timing in which the updates to the application will be delivered to the client machine. Clicking on the Updates... button will result in the following dialog box.

As you can see from the above dialog box, you have a number of options that allow you to exercise finer level of control over the application updates.

Right below the Updates button is the Options... button that brings up the following dialog box when clicked.

Using the Publish options, you can not only indicate the language in which the application will be published but also specify the Start menu shortcut name. Once you publish this application to the target location, the root directory of the application install will contain a file called publish.htm, which you can use to launch the application on the client machine. By default, the name of the file is set to publish.htm and can be modified using the above dialog box.

Now that we have a understanding of the properties in a general sense, let us see how to publish the application.

Publishing the Application

To publish the application, start by selecting Publish ClickOnceDemo from the Publish menu. This will bring up the first step in the publish wizard, which is shown below.

In the above dialog box, you can specify the location where you want to publish the application. The locations to which you can deploy the application are: File System, Local IIS, FTP Sites, and Remote Sites. For the purposes of this example, let us leave the default value of http://localhost/ClickOnceDemo. Clicking Next in the above dialog box will result in the following dialog box, which allows you to specify the modes in which the application will be available.

The default value in the above screen is dependant on the value set in the Install Mode Settings panel of the Publish properties dialog box. Then click Next and you will be asked to specify the key file used for strongly signing the application. Since we haven't created a key file yet, select the option that says "" as shown in the following screenshot.

Clicking Next in the above dialog box will bring up the Confirm dialog box, wherein you can hit Finish to complete the publishing.

If you hit Finish in the above dialog box, Visual Studio will build the project, publish the application to the ClickOnceDemo folder under the default web site, and then automatically bring up the publish.htm file in the browser. Before we look at the publish.htm file, let us examine the files generated by the wizard. Navigate to the ClickOnceDemo folder in the default web site through the Windows explorer. You will see the following screenshot.

The ClickOnceDemo_1.0.0.0 folder is the one that contains the .exe file and the corresponding manifest files. Since we mentioned that the prerequisites (specified through the Publish properties dialog box in the earlier section) for this application is .NET Framework 2.0, Visual Studio automatically created a folder named dotnetfx that contained the executable for installing the .NET Framework 2.0. Now that we have had a look at the files created by the wizard, let us turn our focus to the publish.htm that provides a means for launching the application.

As you can see from the above screenshot, this html page enables you to install the ClickOnceDemo application through a hyperlink. Click the Install ClickOnceDemo hyperlink to install the application. You will be presented with the following dialog box, wherein you can just hit Install to continue the installation.

Once the application is installed, it will automatically execute the application and bring up the form. Click on the command and you will see an output that is somewhat similar to the following.

As you can see, now the path displayed the message box is different than the previously generated message box. This is because of the fact that the application is now downloaded and locally cached in the folder shown in the above message box. If you navigate to that folder from windows explorer, you will see folders as listed in the following screenshot. These folders contain files such as manifests, and the actual executable itself and so on.

You will also notice the following changes as a result of the installation.

  • An icon is added to the Start menu
  • An entry is added in Control Panel -> Add/Remove Programs.

From this point onwards, you can launch the application through the shortcut in the Start -> Programs menu. From Add/Remove Programs, you can either completely uninstall the application or revert to the previous version, if there was one.

Conclusion

The features of ClickOnce we have seen in this article are only the tip of the iceberg and it offers many more benefits. However, the key motivation for using ClickOnce is the ease of deployment, installation, and versioning for your Windows Forms applications.

Paging in SQL Server 2005

15 Seconds : Paging in SQL Server 2005
David Beahm
06/28 / 2007

Introduction

Developers and database administrators have long debated methods for paging recordset results from Microsoft SQL Server, trying to balance ease of use with performance. The simplest methods were less efficient because they retrieved entire datasets from SQL Server before eliminating records which were not to be included, while the best-performing methods handled all paging on the server with more complex scripting. The ROW_NUMBER() function introduced in SQL Server 2005 provides an efficient way to limit results relatively easily.

Paging Efficiency

In order to scale well, most applications only work with a portion of the available data at a given time. Web-based data maintenance applications are the most common example of this, and several data-bindable ASP.NET classes (such as GridView and Datagrid) have built-in support for paging results. While it is possible to handle paging within the web page code, this may require transferring all of the data from the database server to the web server every time the control is updated. To improve performance and efficiency, data which will not be used should be eliminated from processing as early as possible.

Paging Methods

Many popular databases offer functions allowing you to limit which rows are returned for a given query based upon their position within the record set. For example, MySQL provides the LIMIT qualifier, which takes two parameters. The first LIMIT parameter specifies which (zero-based) row number will be the first record returned, and the second parameter specifies the maximum number of records returned. The query:

SELECT * FROM table LIMIT 20,13

...will return the 20th through the 32nd records -- assuming at least 33 records are available to return. If fewer than 33 records are available, the query will return all records from record 20 on. If fewer than 20 records are available, none will be returned.

SQL Server does not have this functionality, however the 2005 release does have a number of other new tricks. For instance, support for CLR procedures means it is possible to use existing paging methods to write VB.NET or C# code that would execute within the SQL Server environment. Unfortunately, CLR procedures are not as efficient as native Transact SQL. To ensure best performance, queries should still be written in TSQL whenever practical.

Using ROW_NUMBER()

TSQL in the 2005 release includes the ROW_NUMBER() function, which adds an integer field to each record with the record's ordinal result set number. Stated more simply, it adds the record's position within the result set as an additional field so that the first record has a 1, the second a 2, etc. This may appear to be of little value, however by using nested queries we can use this to our advantage.

To demonstrate ROW_NUMBER() and to explore how the paging solution works, create a simple salary table and populate it with random data using the following commands:

CREATE TABLE [dbo].[Salaries](
[person] [nvarchar](50) NOT NULL,
[income] [money] NOT NULL,
CONSTRAINT [PK_salaries] PRIMARY KEY CLUSTERED(
[person] ASC
)) ON [PRIMARY]
GO

INSERT INTO Salaries VALUES ('Joe', '28000')
INSERT INTO Salaries VALUES ('Sue', '96000')
INSERT INTO Salaries VALUES ('Michael', '45000')
INSERT INTO Salaries VALUES ('John', '67000')
INSERT INTO Salaries VALUES ('Ralph', '18000')
INSERT INTO Salaries VALUES ('Karen', '73000')
INSERT INTO Salaries VALUES ('Waldo', '47000')
INSERT INTO Salaries VALUES ('Eva', '51000')
INSERT INTO Salaries VALUES ('Emerson', '84000')
INSERT INTO Salaries VALUES ('Stanley', '59000')
INSERT INTO Salaries VALUES ('Jorge', '48000')
INSERT INTO Salaries VALUES ('Constance', '51000')
INSERT INTO Salaries VALUES ('Amelia', '36000')
INSERT INTO Salaries VALUES ('Anna', '49000')
INSERT INTO Salaries VALUES ('Danielle', '68000')
INSERT INTO Salaries VALUES ('Stephanie', '47000')
INSERT INTO Salaries VALUES ('Elizabeth', '23000')

The ROW_NUMBER() function has no parameters - it simply adds the row number to each record in the result set. To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax for querying the Salaries table is:

SELECT ROW_NUMBER() OVER(ORDER BY person), person, income
FROM Salaries

This returns the following result:

(No column name)personincome
1Amelia36000.00
2Anna49000.00
3Constance51000.00
4Danielle68000.00
5Elizabeth23000.00
6Emerson84000.00
7Eva51000.00
8Joe28000.00
9John67000.00
10Jorge48000.00
11Karen73000.00
12Michael45000.00
13Ralph18000.00
14Stanley59000.00
15Stephanie47000.00
16Sue96000.00
17Waldo47000.00

The Salaries data now appears sorted by person, and it has an extra column indicating each record's position within the results.

If for any reason you wanted the results to display in a different order than they were numbered in, you can include a different ORDER BY clause as part of the normal SELECT syntax:

SELECT ROW_NUMBER() OVER(ORDER BY person), person, income
FROM Salaries
ORDER BY income

This returns the following result:

(No column name)personincome
13Ralph18000.00
5Elizabeth23000.00
8Joe28000.00
1Amelia36000.00
12Michael45000.00
15Stephanie47000.00
17Waldo47000.00
10Jorge48000.00
2Anna49000.00
3Constance51000.00
7Eva51000.00
14Stanley59000.00
9John67000.00
4Danielle68000.00
11Karen73000.00
6Emerson84000.00
16Sue96000.00

If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one and provide a name for the ROW_NUMBER() column. To limit our results to records 5 through 9, we can use the following query:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
rownum, person, income FROM Salaries) AS Salaries1
WHERE rownum >= 5 AND rownum <= 9

This returns the following result:

rownumpersonincome
5Elizabeth23000.00
6Emerson84000.00
7Eva51000.00
8Joe28000.00
9John67000.00

Again, we can change the sort order by adding an ORDER BY clause. This is most easily accomplished by using the outer SELECT statement:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
rownum, person, income FROM Salaries) AS Salaries1
WHERE rownum >= 5 AND rownum <= 9
ORDER BY income

This returns the following result:

rownumpersonincome
5Elizabeth23000.00
8Joe28000.00
7Eva51000.00
9John67000.00
6Emerson84000.00

If we want to support the same type of arguments that MySQL's LIMIT() supports, we can create a stored procedure that accepts a beginning point and a maximum number of records to return. ROW_NUMBER requires that the data be sorted, so we will also have a required parameter for the ORDER BY clause. Execute the following statement to create a new stored procedure:

CREATE PROCEDURE [dbo].[pageSalaries]
@start int = 1
,@maxct int = 5
,@sort nvarchar(200)
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL statement to execute
@ubound int

IF @start < 1 SET @start = 1
IF @maxct < 1 SET @maxct = 1
SET @ubound = @start + @maxct
SET @STMT = ' SELECT person, income
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @sort + ') AS row, *
FROM Salaries
) AS tbl
WHERE row >= ' + CONVERT(varchar(9), @start) + ' AND
row < ' + CONVERT(varchar(9), @ubound)
EXEC (@STMT) -- return requested records

The pageSalaries procedure begins with SET NOCOUNT ON to disable the record count message (a common step for optimizing query performance). We then declare two necessary variables, @STMT and @ubound. Because we want to be able to change what ORDER BY argument is used, we need to dynamically generate our query statement by storing it in @STMT. The next lines ensure that only positive numbers are used for the starting position and maximum size, then calculate the range of ROW_NUMBER() values being requested. (If we wanted to be zero-based like MySQL's LIMIT, we could do so with a few minor tweaks.) Once the dynamic SQL command has been strung together, it is executed so that the results are returned.

Execute the following statement to test the stored procedure:

pageSalaries 4, 7, 'income'

This returns the following result:

personincome
Amelia36000.00
Michael45000.00
Stephanie47000.00
Waldo47000.00
Jorge48000.00
Anna49000.00
Constance51000.00

If we execute:

pageSalaries 13, 7, 'income'

we receive back:

personincome
John67000.00
Danielle68000.00
Karen73000.00
Emerson84000.00
Sue96000.00

... because the query goes beyond the number of records available.

Taking this one step further, we can make a stored procedure that does a more general form of paging. In fact, it can be generalized to the point that it can be used to return any collection of fields, in any order, with any filtering clause. To create this wunderkind marvel, execute the following command:

CREATE PROCEDURE [dbo].[utilPAGE]
@datasrc nvarchar(200)
,@orderBy nvarchar(200)
,@fieldlist nvarchar(200) = '*'
,@filter nvarchar(200) = ''
,@pageNum int = 1
,@pageSize int = NULL
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
,@recct int -- total # of records (for GridView paging interface)

IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @fieldlist +
'FROM ' + @datasrc +
'WHERE ' + @filter +
'ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = 'SELECT @recct = COUNT(*)
FROM ' + @datasrc + '
WHERE ' + @filter
EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
SELECT @recct AS recct -- return the total # of records

DECLARE
@lbound int,
@ubound int

SET @pageNum = ABS(@pageNum)
SET @pageSize = ABS(@pageSize)
IF @pageNum < 1 SET @pageNum = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@pageNum - 1) * @pageSize)
SET @ubound = @lbound + @pageSize + 1
IF @lbound >= @recct BEGIN
SET @ubound = @recct + 1
SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the
-- specified page
END
SET @STMT = 'SELECT ' + @fieldlist + '
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
FROM ' + @datasrc + '
WHERE ' + @filter + '
) AS tbl
WHERE
row > ' + CONVERT(varchar(9), @lbound) + ' AND
row < ' + CONVERT(varchar(9), @ubound)
EXEC (@STMT) -- return requested records
END

You may receive the following error message from SQL Server, which you can confidently ignore:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'sp_executeSQL'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

The utilPage procedure accepts 6 parameters:

@datasrc - the table (or stored procedure, etc.) name
@orderBy- the ORDER BY clause
@fieldlis- the fields to return (including calculated expressions)
@filter- the WHERE clause
@pageNum- the page to return (must be greater than or equal to one)
@pageSize- the number of records per page

The stored procedure needs the name of a data source to query against (such as a table) and one or more fields to sort by (since OVER() requires an ORDER BY clause). If @filter is blank (the default), it will be set to "1 = 1" as a simple way to select all records. If @pageSize is not supplied, the query will run without paging and will not return a record count.

If, however, @pageSize is supplied, a version of the query is executed to get the total number of records. In order to have this record count available within the procedure and as a returned value, we use sp_executeSQL to support executing the statement while returning an output parameter. The record count is used to prevent returning empty results when possible, and to support paging interfaces that calculate the number of pages available (such as GridView). If we were calling this stored procedure to populate a GridView, we would return @recct as a ReturnValue parameter instead of using a result set, but we will use a result set for demonstration purposes.

The procedure calculates what the actual record positions will be for the requested page. Rather than allow the query to fail, there are safety checks ensuring that @pageSize and @pageNum are greater than zero, and that the result set will not be empty. If the specified page is out of range, this procedure will return the last possible page of records. This is helpful if a user changes more than one setting before refreshing their data, or if a significant amount of data is deleted between requests.

The remainder of the procedure is virtually identical to the pageSalaries procedure. To test the utilPAGE stored procedure, execute the following statement:

utilPAGE 'Salaries', 'person', '*', 'income > 1000', 2, 4

This returns the following two result sets:

recct
17

rowpersonincome
5Elizabeth23000
6Emerson84000
7Eva51000
8Joe28000

If we execute:

utilPAGE 'Salaries', 'person', 'person, income', '', 13, 3

...we receive back:

recct
17

personincome
Stephanie47000
Sue96000
Waldo47000

Even though the request should be for records 36 through 38 - far outside of what is available - the procedure returns the last available page of records. In contrast, requesting the third page with seven records per page using:

utilPAGE 'Salaries', 'person', 'person, income', '', 3, 7

...returns the last three records, as the page is not completely out of bounds:

personincome
Stephanie47000
Sue96000
Waldo47000

All of these examples are based on simple single-table queries, which may not reflect what you need in the real world. While the utilPAGE procedure does not support ad-hoc JOINs, it does work with SQL Views. If you want paging support for multi-table queries, you should create a View (with all of the necessary JOINs) to use as the data source. Using a View follows good design practices as it ensures that your Joins are performed consistently, allows easier ad-hoc querying from the command line, and is much easier to troubleshoot than a stored procedure's dynamic SELECT statement logic.

Conclusion

While SQL Server does not have as simple a method for paging results as some other databases, features introduced in the 2005 release have made it possible to page results efficiently more easily than ever before. In the next article in this series, we will go a step further and integrate this paging logic with a GridView through a Data Access Layer.