Sql Server 2008 Performance Tuning A Query

Ok, I am going to try and give a quick tip on how to perform query tuning in SQL Server.

Before you start, you need to make sure that the buffers and procedure cache are cleared so that running queries will give consistent results, that is not impacted by previous execution of the query in question.

This is done by executing
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Next, there are two tools you can use to analyse your query. The first is SET STATISTICS and the second is the visual query plan in SSMS.

To turn on statistics, execute
SET STATISTICS IO ON
SET STATISTICS TIME ON

When you execute a query such as
SELECT * FROM Customers WHERE Name = ‘Peter’;
it will give you a result such as

Table ‘Customers’. Scan count 1, logical reads 957, physical reads 1, read-ahead reads 945, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The idea of query tuning is to write queries that reduce the impact on CPU and I/O. As your expertise grows, you can look at other statistics, but for now concentrate your efforts on the logical reads figure.

Now, the query above has no index on the Name column. You need to add an index to the Name column to have an impact on the number of logical reads.

To add a non-clustered index to the name column, you execute do the following:
CREATE NON-CLUSTERED INDEX IDX_Customer_Name ON dbo.Customer
(
Name ASC
)

Now execute the same query again:
SELECT * FROM Customers WHERE Name = ‘Peter’;

The statistics will return a much lower figure for logical reads:
Table ‘Customers’. Scan count 1, logical reads 112, …

The total number of logical reads has dropped considerably, meaning the query is now more efficient.

If you are doing a batch of queries, you are probably better off choosing Include Actual Execution Plan from the SSMS menu. You execute your batch and then you follow the bottleneck approach. That is, determine which queries are having the most detrimental impact on performance, and concentrating on improving those queries.

The beauty of the graphically generated plan is that you can often get some quick tips on where you should concentrate your efforts. For example, if the plan is showing lots of Scans instead of Seeks, it indicates that adding some indexes may cause an instant improvement. If there are a lot of nested joins, perhaps an investigation of these and restructuring the query may lead to better results.

Quick Guide to Installing TFS in Windows 2008 with SQL Server 2008 in a Hyper-V Virtual Machine

I have to say, installing TFS is one of the most cubersome processes that Microsoft has come up with.

Firstly, you need and edition of Windows Server. I decided that I would install it on Windows Server 2008 R2 64-bit Release Candidate. But that’s no good, because it is designed for 32-bit Windows Server and has very little support for 64-bit. It detects that it’s not 32-bit, and prevents you from continuing with the TFS install.

I had real trouble installing Windows Server 2008 first release on my machine; some problem with drivers that I couldn’t resolve, so for the moment I am pretty much stuck with the release candidate of Windows Server 2008 R2.

So I decided to install a 32-bit version of Windows Server 2008 in a Virtual Machine using Hyper-V. That was fun. I installed from an ISO image and I was up and running. To configure the Hyper-V site, I had to set up a virtual network, and I wanted to expose my TFS site to the outside world, so I had to tick the box to share the actual physical network adapter with the virtual one.

That got interesting, because the moment I did that, it cleared the settings on the actual physical network adapter. So I decided it needed to have the settings put back in. Wrong move! Leaving the network adapter how it is, without the settings, works.

Next, within the Hyper-V environment, you need to run the operating system, then you need to select Action from the Hyper-V main menu, and then click “Insert Integration Services Disk”. This puts a virtual ISO in the Virtual DVD drive, and a popup box helps you install this vital piece of software.

Once the Integration Services disk is installed, the VM should be able to have network access. To test, simply enter a web site, such as http://www.msdn.microsoft.com.

Next, the TFS installation. First, you need to set up all the accounts. The best place to look for which accounts need to be created is the chm file that you download from the Microsoft site, at http://www.microsoft.com/downloads/details.aspx?FamilyId=FF12844F-398C-4FE9-8B0D-9E84181D9923&displaylang=en

Next, you must use TFS with Service Pack 1 to be able to install on SQL Server 2008. This is not fun. To be able to perform this from scratch involves manually extracting files from the TFS installer and Service Pack 1 intallers and then creating a new merged installer from the two images. If you don’t do this, you will not be able to get TFS to install with SQL Server 2008. This step is quite complex; it’s not for the faint hearted.

It requires SQL Server Standard or Enterprise Edition, 2005 or 2008. On that machine, I had a SQL Server 2008 Development Edition. Nope, it can’t accept that. It must be a Standard or Enterprise Edition. So I installed a copy of SQL Server 2008 and installed SP1, and tried to install TFS. Wrong again. It didn’t recognise SQL Server 2008 with SP1.

So I had a think about it and decided that it must have worked without the service pack. So I removed my SP1 version of SQL Server 2008 and reinstalled without the service pack. It finally detected SQL Server 2008 and installed successfully.

A reboot after all this is a must.

Next, within Visual Studio 2008 Team Edition Development Edition, you need to install Team Explorer. Funnily enough, it’s called Team Edition but that means nothing without the Team Explorer. It’s yet another otherwise unneccessarily annoying steps to achieving the goal.

Of course, it doesn’t end here, because you also need to install Visual Studio 2008 SP1 again, as the Team Explorer downloaded will install in Visual Studio but is not the SP1 version. The implication here is that it won’t be able to connect to the project site correctly, meaning you won’t be able to add Projects.

Next, you need to add the users to the TFS Licenced Users group. This is how TFS tracks the total number of users that can access TFS. I am only using a Workgroup edition, so that limit is 5.  

Well, that was a tough installation process, but it was worth it. TFS is now up and running. It gives you a project structure, it gives you process guidance, a Sharepoint site to manage each project, reporting with SQL Server Reporting Services (SSRS) amongst many other features. Hope this helps!

Windows 7 and Windows 2008 Server R2 have RTM’d

Of course, I’m trying to get a server going with Windows Server 2008, and it’s giving me all sorts of problems!

Link to official release of Windows Server 2008 R2:

http://blogs.technet.com/windowsserver/archive/2009/07/22/windows-server-2008-r2-rtm.aspx

Link to official release of Windows 7:

http://windowsteamblog.com/blogs/windows7/archive/2009/07/22/windows-7-has-been-released-to-manufacturing.aspx

SQL Server 2005 – When it is acceptable to use cursors

I had someone recently ask me when it is acceptable to use cursors, because, believe it or not, there are acceptable circumstances where they should be considered.

Firstly, if you’re performing a one off batch process where the execution time doesn’t really matter, I mean, why not?  Whatever gets the job done, done quickly, and done in a form that is clear enough for you to understand – although that said, some would call it bad form because they consider that it doesn’t give you practice with “correct” set-based SQL.

Secondly, take a look at MVP Greg Low’s post on Avoiding Blocking Issues in ASP.Net Session State Databases. Here, Greg describes a scenario where the use of set-based SQL actually causes slower querying due to blocking that occurs while rows that are no longer being used are cleaned up.

More importantly, this has an implication in any database where you are using temporary session-style records that need to periodically be cleaned up. One scenario that I came across recently was with the building of advanced queries – the queries have multiple query parts and it was decided to store the state in the database against the session and user, but to not specifically use session objects to store this information. After a period of time, however, unused queries are cleaned up. This could potentially create the same sort of blocking problem that Greg is talking about in his article above.

How to create an HTML table with frozen headers and columns

This discussion is just intended to get you started and is in no way a complete work. Hopefully others will be able to use what I’ve learnt to make the process of setting up frozen headers and columns even more simple.

What we are trying to achieve:

Standard table, ready for scrolling

Now, I tried a couple of different ways of achieving this as it is quite a complex little problem. You see, scrolling in one direction is relatively easy. You can either fix the headers or fix the columns, but generally not both because as soon as you want to scroll in two directions, you need to break the table not into two parts, but 4 parts.

When scrolling vertically, you want the two bottom parts to be able to scroll up and down, as follows. Note that I have shown how the table is divided up.

Table divided up into four parts, vertical scrolling

And when you want to scroll horizontally, you want the two right parts to be able to scroll left and right, as follows:

Table divided up, horizontal scrolling

Firstly, you can’t do this using stylesheets alone. There is no way that you can freeze the headers or column using relative positioning or the like. And anyway, stylesheet solutions are pretty much IE only, they don’t work in other browsers. So then you’re stuck with a javascript solution. Luckily, it’s a relatively easy one at that.

 

Within my solution, the 4 parts of the table are actually internally 4 separate html tables. Each of the 4 parts are placed in their own div tag. I have set each of  the tables to table-layout:fixed in the stylesheet. This makes it so that you can set the columns widths to a fixed pixel value. This will maintain the widths across the different div tags. Also, I have set the line heights. That is important so that the content is consistent between browsers. I have hidden the overflowing content within the cells so that it doesn’t warp the heights and widths in the different parts. IE has a style attribute text-overflow:ellipses if you want a dot-dot-dot at the end of a cell if there is overflow. Note that if there is overflow, you can do one of two things. Firstly, you could find some code that allows you do adjust the widths of columns, or secondly, you can put a tooltip containing the full content on the cell. Sorry, no help here (yet).

 

The first part (containing Head1) never moves, no matter whether you are scrolling left to right or top to bottom. The second part (the other Head fields) scrolls left to right, but it doesn’t scroll up and down as you still want to know what column you’re looking at. The third part (Col1) scrolls up and down but not left and right, and the fourth part scrolls in both directions.

 

At first I had a go at putting the scrollbars inside the main content part of the table. The problem with this solution is that you have to take that into consideration when setting the width and height of the frozen column and frozen header. That’s a complete pain. It wasn’t very friendly from a developer point of view.

 

The trick is to separate out the scroll bars, so that they can operate independently of the table parts. Each of the divs is of fixed size, with all overflow hidden, again via stylesheets. The scrollbars sit next to the tables and match the heights and widths of 2 parts on each side.

 

The way to separate out the scrollbars is to put a div inside another div tag. I originally tried a few other methods, including putting a transparent image inside the tag, but in the end the two divs approach won out. So for a stand alone horizontal scrollbar, you want something like:

 

<div style="overflow-x: scroll; width: 200px; height: 23px;">
<div style="width: 1000px; height: 1px;"></div>
</div>

and for a vertical scrollbar you just switch widths with heights and change the overflow-x to an overflow-y.

 

Next, you need to be able to tell what position the scrollbar is in. Luckily, you can put the javascript “onscroll” event call on the outer scroll div tag. When scrolling left to right, you can find the scroll position using scrollLeft on the scroll div. When scrolling top to bottom, you can find the scroll position using scrollTop on the scroll div.

 

On each of the 4 parts above, the scrollbars are hidden. But this doesn’t mean that you can’t actually scroll the content. If you set the scrollLeft and scrollTop properties on those elements, the content still scrolls. This is great, because it means that you can have smooth scrolling!

 

I have two methods, one for repositioning the divs based on the horizontal scrollbar moving left/right.

 

function reposHorizontal(e) {
    var h = document.getElementById('topright'); /* header row, top right */
    var c = document.getElementById('bottomright'); /* main content, bottom right */
    h.scrollLeft = e.scrollLeft;
    c.scrollLeft = e.scrollLeft;
}

This method is set on the scrollbar itself. Note that I have cleaned up the style a bit.

<div class='horizontal-scroll' onscroll='reposHorizontal(this);'>
    <div>
    </div>
</div>

Next it’s just a matter of getting the heights and widths of all the elements right. Firefox and IE7 were fine, but IE6 needed some different values for heights and widths. I haven’t noticed a pattern in the styles yet, I am just at the beginning.

Here is the result with vertical scrolling (top to bottom, header row stays frozen):

 

Scroll top to bottom

and here’s the result with horizontal scrolling (left to right, left column stays frozen):

Scroll left to right

And here’s what happens when I scroll to the bottom right corner:

Scroll to bottom right corner

 I have added an html page that you can take a look at here: ScrollTable.htm

How to Style the Ajax Control Toolkit Calendar Extender control

The calendar extender control straight out of the box is a bit boring. More of you should be styling the control. The following may help you style this control a little.

Use a web palette to select your colors. I used this one: http://oreilly.com/catalog/wdnut/excerpt/web_palette.html for no particular reason. There are literally thousands of palettes out there. The preference has generally been to use 256 colour palette. Stylesheets have been optimised so that you can use 3 hex digits to represent colours. For example, color: #f69 is equivalent to color: #ff6699.

Calendar Styled

 I have deliberately made this ugly with each section coloured differently so it’s easier for you to restyle.

I added a CssClass to the Calendar Extender control, called “custom-calendar”.

The following are the styles I applied to this Calendar.

.custom-calendar .ajax__calendar_container
{
 background-color:#ffc; /* pale yellow */
 border:solid 1px #666;
}
.custom-calendar .ajax__calendar_title
{
 background-color:#cf9; /* pale green */
 height:20px;
 color:#333;
}
.custom-calendar .ajax__calendar_prev,
.custom-calendar .ajax__calendar_next
{
 background-color:#aaa; /* darker gray */
 height:20px;
 width:20px;
}
.custom-calendar .ajax__calendar_today
{
 background-color:#cff;  /* pale blue */
 height:20px;
}
.custom-calendar .ajax__calendar_days table thead tr td
{
 background-color:#ff9; /* dark yellow */
 color:#333;
}
.custom-calendar .ajax__calendar_day
{
 color:#333; /* normal day - darker gray color */
}
.custom-calendar .ajax__calendar_other .ajax__calendar_day
{
 color:#666; /* day not actually in this month - lighter gray color */
}

How to add a close button to the Ajax Control Toolkit Calendar Extender control

In this series of educational articles, I have been explaining how to enhancing the Ajax Control Toolkit Calendar Extender control to support a number of different modes, such as Day, Month, Quarter, Year, Decade. I have also introduced implemented the InitialView property, which allows you to select an initial mode that you want the calendar to display in. These features may be used to reduce the number of clicks required to select a birthdate from 9 to 4, which is a productivity improvement for anyone seriously using the calendar for data entry purposes

These are the articles so far:

How to patch the Ajax Control Toolkit CalendarExtender to add Decade support and InitialView – Part 1 

How to patch the Ajax Control Toolkit Calendar Extender control to show a Quarter view

How to patch the Ajax Control Toolkit Calendar Extender control to show a Day, Month, MonthYearDecade, Quarter, YearDecade and Decade view

The code for the starting point for these modifications is found here:

http://cid-5e237543fffb2891.skydrive.live.com/self.aspx/Public/AjaxControlToolkit-Framework3.5SP1withDecadeYearQuarterMonthDayandInitialView.zip

Unzip this code and place it in your projects folder. All code here is supplied under the Microsoft Public Licence, found here: http://ajaxcontroltoolkit.codeplex.com/license

The code:

To start with, you’ll need a Close Button. The close button is 15 pixels by 15 pixels. I have created one based on the Internet Explorer X button used to stop web pages from executing. You can download my image by right clicking this image and saving it onto your local drive:

This close button image is called close-button.gif (note that it’s a dash, not an underscore).

Copy this image into the Calendar folder in the Ajax Control Toolkit, and set it’s Build Action to Embedded Resource. This is important, as without it, the image is not contained within the Ajax Control Toolkit dll.

Adding the close button image

Next, you need to tell the compiler about the image by making it a Web Resource. This is done by adding an entry at the top of the CalendarExtender.cs file, as follows. Note that I am using S5/E5 to tag changes.

Original code:

#region [ Resources ]

[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.CalendarBehavior.js", "text/javascript")]
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.Calendar.css", "text/css", PerformSubstitution = true)]
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.arrow-left.gif", "image/gif")]
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.arrow-right.gif", "image/gif")]
#endregion

Modified code:

#region [ Resources ]

[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.CalendarBehavior.js", "text/javascript")]
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.Calendar.css", "text/css", PerformSubstitution = true)]
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.arrow-left.gif", "image/gif")]
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.arrow-right.gif", "image/gif")]
//S5
[assembly: System.Web.UI.WebResource("AjaxControlToolkit.Calendar.close-button.gif", "image/gif")]
//E5
endregion

Next, we want to add a property to the Calendar Extender to allow us to turn the close button display on or off – not everyone wants a close button on their calendar. Most people are probably content with clicking off the calendar. I will add a property called ShowCloseButton to the extender control. In the CalendarExtender.cs file, I copy the EnabledOnClient property and paste it to the location next to it. Then I rename the variables to be consistent with the property I want.

Code added under EnabledOnClient property:

//S5
[DefaultValue(false)]
[ExtenderControlProperty]
[ClientPropertyName("showCloseButton")]
public virtual bool ShowCloseButton
{
    get { return GetPropertyValue("ShowCloseButton", false); }
    set { SetPropertyValue("ShowCloseButton", value); }
}
//E5

The ClientPropertyName attribute contains the client-side javascript variable name. So next we go into the CalendarBehavior.js file and add in a couple of javascript variables. The first is the showCloseButton variable, shown above, and the second is the placeholder variable for the close button html content that will be created when the close button is required to be displayed. This code goes in the declarations section of the CalendarBehavior.js file. I put mine just below the declaration for this._nextArrow = null;

/*S5*/
this._showCloseButton = false;
this._closeButton = null;
/*E5*/

Add the getter and setter next. I put mine just below the set_enabled function block.

//S5
get_showCloseButton: function() {
    /// <value type="Boolean">
    /// Whether this behavior is available for the current element
    /// </value>

    return this._showCloseButton;
},
set_showCloseButton: function(value) {
    if (this._showCloseButton != value) {
        this._showCloseButton = value;
        this.invalidate();
        this.raisePropertyChanged("showCloseButton");
    }
},
//E5

The function that builds the calendar in the first place is called _buildHeader. This is where we need to add code to test if the close button is supposed to display, create the div that contains the image, and attach the event handlers. This is also where the stylesheet class is attached to the div. The image itself is referenced from that stylesheet class, called ajax__calendar_close.

/*S5 - because the close button is floated right, it needs to go before the nextArrow div as that is
also floated right, and right floating reverses the order of display*/
if (this._showCloseButton) {
    var closeButtonWrapper = $common.createElementFromTemplate({ nodeName: "div" }, this._header);
    this._closeButton = $common.createElementFromTemplate({
        nodeName: "div",
        properties: {
            id: id + "_closeButton",
            mode: "close"
        },
        events: this._cell$delegates,
        cssClasses: ["ajax__calendar_close"]
    }, closeButtonWrapper);
}
/*E5*/

 

 Note in that code the reference to the mode property. That is the string that gets passed into the _cell_onclick event handler. So on clicking the close button image, we want the calendar to execute the same code that is executed when the Escape button is clicked. So I had a look in the _button_onkeypress function and saw the code that is being executed when the Escape key is pressed. I will copy that code into the _cell_onclick function, and add a case for the ‘close’ mode, as follows:

Original code:

case "prev":
case "next":
    this._switchMonth(target.date);
    break;

Modified code:

case "prev":
case "next":
    this._switchMonth(target.date);
    break;
/*S5*/
case "close":
    e.stopPropagation();
    e.preventDefault();
    this.hide();
    break;
/*E5*/

Finally, you need to dispose of the close button content correctly. In the dispose function, add the following modifications:

Original code:

if (this._nextArrow) {
    $common.removeHandlers(this._nextArrow, this._cell$delegates);
    this._nextArrow = null;
}

Modified code:

if (this._nextArrow) {
    $common.removeHandlers(this._nextArrow, this._cell$delegates);
    this._nextArrow = null;
}
/*S5*/
if (this._showCloseButton && this._closeButton) {
    $common.removeHandlers(this._closeButton, this._cell$delegates);
    this._closeButton = null;
}
/*E5*/

Finally, you need to add the calendar extender css class for the close button. Open the Calendar.css file, and just below the entry for .ajax__calendar_next, add the following line:

/*S5*/
.ajax__calendar_close {cursor:pointer;width:15px;height:15px;float:right;background-repeat:no-repeat;background-position:50% 50%;background-image:url(<%=WebResource("AjaxControlToolkit.Calendar.close-button.gif")%>);}
/*E5*/

 

Run it up, add a calendar extender, set it’s ShowCloseButton property to true, and this is what you see:

Calendar Close Button Image

Note that I have deliberatelychosen a non-intrusive close button, the same size as the prev and next buttons. The close button just needs to be there – it doesn’t need to out-weigh the other elements on the calendar. I used to have a bright red button, but that is so 20th century.

If you would like to see if you’ve got the code right, I have a completed version to compare with here:

AjaxControlToolkit3.5SP1withModesAndCloseButton.zip

Sharepoint, AjaxControlToolkit, ModalPopup and DocType problems

I am currently integrating a Sharepoint web site with an ASP.Net web site. A Sharepoint web site is essentially an ASP.Net web site too, and Microsoft have provided a way to integrate exiting web sites into that, essentially by inheriting a Sharepoint master page.

My ASP.Net web site uses the AjaxControlToolkit. The problem is, the AjaxControlToolkit by default uses the XHTML DocType. Sharepoint doesn’t actually have a DocType specified, which means it defaults to HTML 4.0 transitional. That mode is ok. There are a number of reasons why you might want to use HTML 4.0 transitional. Most of the component libraries out there still use the old DocTypes because they offered a richer set of functionality. Recently I used the Intersoft control suite. These operates very strangely under  XHTML, and in fact they don’t support that mode.

But back to Sharepoint and the AjaxControlToolkit. I was trying to use the modal popup extender, so that I can have one of my forms displayed on clicking a button on the page. Without the DocType of XHTML, every time I clicked on the button, it would display the bottom-right hand corner of the dialog in the upper left-hand corner of my form, effectively clipping the control and making the control otherwise useless.

There are workarounds. You can go into the Modal Popup Extender and set the X and Y co-ordinates. The problem with that is that often you don’t know the exact co-ordinates where you want the popup to be displayed. Part of the desire to use the modal popup is because it centres on the page and will move with the page when you scroll. It also repositions itself when you adjust the form.

The answer to this is to obtain a re-write of the getClientBounds javascript function that goes into the AjaxControlToolkit source code, common.js file. This re-write may be obtained from here: http://forums.asp.net/t/1002123.aspx?PageIndex=1 and was put together by Umer Farooq of Islamabad.

Here it is repeated below:

getClientBounds : function() {
        /// <summary>
        /// Gets the width and height of the browser client window (excluding scrollbars)
        /// </summary>
        /// <returns type="Sys.UI.Bounds">
        /// Browser's client width and height
        /// </returns>

        var clientWidth;
        var clientHeight;
        switch(Sys.Browser.agent) {
            case Sys.Browser.InternetExplorer:
                if (document.documentElement && document.documentElement.clientWidth)
                    clientWidth = document.documentElement.clientWidth;
                else if (document.body)
                    clientWidth = document.body.clientWidth;
                //clientWidth = document.documentElement.clientWidth;
                if (document.documentElement && document.documentElement.clientHeight)
                    clientHeight = document.documentElement.clientHeight;
                else if (document.body)
                    clientHeight = document.body.clientHeight;
                //clientHeight = document.documentElement.clientHeight;               
                break;
            case Sys.Browser.Safari:
                clientWidth = window.innerWidth;
                clientHeight = window.innerHeight;
                break;
            case Sys.Browser.Opera:
                clientWidth = Math.min(window.innerWidth, document.body.clientWidth);
                clientHeight = Math.min(window.innerHeight, document.body.clientHeight);
                break;
            default:  // Sys.Browser.Firefox, etc.
                clientWidth = Math.min(window.innerWidth, document.documentElement.clientWidth);
                clientHeight = Math.min(window.innerHeight, document.documentElement.clientHeight);
                break;
        }
        return new Sys.UI.Bounds(0, 0, clientWidth, clientHeight);
    },

Basically, it works by detecting various instances of objects on the client in the Internet Explorer part of th code. If XHTML is present, document.documentElement.clientWidth exists, otherwise it will use the default document.body.clientWidth.

There is a second issue, that is to do with the positioning of the modal background. Basically, because the modal popup defaults to position:fixed, the background positions itself within the content part of the page. This is not desirable, as we want it to make the entire page inoperable while our dialog is showing. I actually don’t like the use of position:fixed, because it is unavailable in IE6. To solve this problem, simply find references to ‘fixed’ in the ModalPopupBehavior.js file in the AjaxControlToolkit and change them to ‘absolute’. You might want to alter the left and top values for the background element in this file too. I changed mine from zero (0) to minus 1 (-1).

How to patch the Ajax Control Toolkit Calendar Extender control to show a Day, Month, MonthYearDecade, Quarter, YearDecade and Decade view

In this series of educational articles, I am demonstrating how you might enhance the Calendar Extender control found in the publicly available Ajax Control Toolkit. All code here is an extension of my previous work, first of which was to enhance the Ajax Control Toolkit Calendar Extender so that it could be better utilised to select a birthdate. It introduced the decade view, and also allowed the setting of a initial view of the control, which can be Day, Month, Year and Decade. That article is available here: http://tonesdotnetblog.wordpress.com/2009/04/15/how-to-patch-the-ajax-control-toolkit-calendarextender-to-add-decade-support-and-initialview-part-1/

Secondly, I enhanced the calendar extender control so that it showed a quarter, and quarter/year/decade view. That article is available here: http://tonesdotnetblog.wordpress.com/2009/04/17/how-to-patch-the-ajax-control-toolkit-calendar-extender-control-to-show-a-quarter-view/

You can download the code for the latest version (20820) of the Ajax Control Toolkit from here: http://www.codeplex.com/AjaxControlToolkit.

My enhanced decade, initial view and quarter view source code can be found here: http://cid-5e237543fffb2891.skydrive.live.com/self.aspx/Public/AjaxControlToolkit-DecadeQuarterInitialView.zip This enhancement is an extension of that control, so you’ll need to download that code to start this exercise.

All code here is published under the Microsoft Public Licence, found here: http://ajaxcontroltoolkit.codeplex.com/license

What we are trying to achieve:

As long as computers have been around, there have been scenarios where selecting a date, a month, a year or some other timeframe has been necessary.  Zhi-Qiang Ni, from Microsoft, wrote a modification to display a month view of the control, so that a month and year can be selected using the Ajax Control Toolkit. I have implemented Zhi-Qiang’s version, which may be found here: http://tonesdotnetblog.wordpress.com/2009/04/21/how-to-create-a-month-view-with-the-ajax-control-toolkit-calendar-extender-control/

In this situtation, I extend my existing enhancement to select a month, or a month and a year, or a year or even a decade. I have also added a few extra modes, where I hide the title and today bar under certain conditions, for example when you want the user to select a month only.

Selection of a Decade:

Decade View in the Ajax Control Toolkit Calendar Extender control

Selection of a Month:

Month Selection

Selection of a Month and Year:

Month Year Selection

The code:

Ok, the first thing to do is to modify the already existing CalendarView class, found in the Calendar folder in the Ajax Control Toolkit source code. I added this class in the previous article, which enhanced the Calendar Extender to show a quarter view. I will be using tags S4/E4 for this enhancement. Any other tags are from previous enhancements and should only be changed if it is required by S4.

Original code:

//S3 - Add Calendar View so that you can choose the calendar view for your date selection.
//Choosing a calendar view will close the calendar at the bottom view and return the selected date/value

namespace AjaxControlToolkit
{
    /// <summary>
    /// The calendar view of the calendar
    /// </summary>
    public enum CalendarView
    {
        DayMonthYearDecade = 0,   //the default view.
        Quarter = 1,              //shows Quarter selection but not Year or Decade.
        QuarterYearDecade = 2   //shows Quarter Year Decade.
    }
}
//E3

Modified code:

//S3 - Add Calendar View so that you can choose the calendar view for your date selection.
//Choosing a calendar view will close the calendar at the bottom view and return the selected date/value

namespace AjaxControlToolkit
{
    /// <summary>
    /// The calendar view of the calendar
    /// </summary>
    public enum CalendarView
    {
        DayMonthYearDecade = 0,   //the default view.
        Quarter = 1,              //shows Quarter selection but not Year or Decade.
//S4 - add in Month, MonthYearDecade, YearDecade and Decade views
//        QuarterYearDecade = 2   //shows Quarter Year Decade.
        QuarterYearDecade = 2,    //shows Quarter Year Decade.
        Month = 3,                //shows Month only.
        MonthYearDecade = 4,      //shows Month Year Decade.
        YearDecade = 5,           //shows Year Decade
        Decade = 6                //shows Decade only
//E4
    }
}
//E3

 Next, we need to make the changes to the enumeration within the javascript to support the new values. In the CalendarBehavior.js file, make the following changes to bring the two into line. The code is found at the bottom of the CalendarBehavior.js file.

Original code:

AjaxControlToolkit.CalendarView.prototype = {
    DayMonthYearDecade: 0,
    Quarter: 1,
    QuarterYearDecade: 2
}

Modified code:

AjaxControlToolkit.CalendarView.prototype = {
    DayMonthYearDecade: 0,
    Quarter: 1,
    //S4 add variables to be consistent with server side code
    //    QuarterYearDecade: 2
    QuarterYearDecade: 2,
    Month: 3,
    MonthYearDecade: 4,
    YearDecade: 5,
    Decade: 6
    //E4
}

Within the “show” function, we need to introduce new entry points for these views. Below the case statement within show for switching modes for QuarterYearDecade, add the following lines of code, which handle initial view for each of the types, and also switch to the correct modes based on the selected calendar view.

New code:

//S4 introduce new entry points                      
case AjaxControlToolkit.CalendarView.Month:
    this._switchMode("months", true);
    break;
case AjaxControlToolkit.CalendarView.MonthYearDecade:
    if (this._selectedDate == null) {
        switch (_initialView) {
            //case AjaxControlToolkit.InitialView.Quarter:     
            //    this._switchMode("quarters", true);     
            //    break;     
            case AjaxControlToolkit.InitialView.Year:
                this._switchMode("years", true);
                break;
            case AjaxControlToolkit.InitialView.Decade:
                this._switchMode("decades", true);
                break;
            default:
                this._switchMode("months", true);
                break;
        }
    }
    else {
        this._switchMode("months", true);
    }
    break;
case AjaxControlToolkit.CalendarView.YearDecade:
    if (this._selectedDate == null && _initialView == AjaxControlToolkit.InitialView.Decade) {
        this._switchMode("decades", true);
    }
    else {
        this._switchMode("years", true);
    }
    break;
case AjaxControlToolkit.CalendarView.Decade:
    this._switchMode("decades", true);
    break;
//E4                         

Now we want to hide the title display if the mode is Month. The title allows us to show the year and we don’t want that for month only view.

 Original code:

//S3 Where we don't want the title to display, we simply hide it
//this is only the case if you only want to select a value at the current mode
        if (this._calendarView == AjaxControlToolkit.CalendarView.Quarter)
            this._header.style.display = 'none';
//E3

 Modified code:

//S3 Where we don't want the title to display, we simply hide it
//this is only the case if you only want to select a value at the current mode
//S4 hide for Month and Quarter only selection. Year allows clicking decade view, I haven't created
//a year only view and you'll need to switch between decades
//        if (this._calendarView == AjaxControlToolkit.CalendarView.Quarter)
//            this._header.style.display = 'none';
if ((this._calendarView == AjaxControlToolkit.CalendarView.Quarter)
    || (this._calendarView == AjaxControlToolkit.CalendarView.Month))
    this._header.style.display = 'none';
//E4
//E3

Finally, in the _cell_onclick, modify the code to return the date on cell selection. This needs to happen if the mode is “month” and the CalendarView is set to a month type, the mode is “year” and the CalendarView is a year type, or the mode is “decade” and the CalendarView is a decade type. I refactored this code in Zhi-Qiang’s example, and now I’ve had to refactor it again because of all the extra modes added.

Original code:

_cell_onclick: function(e) {
    /// <summary>
    /// Handles the click event of a cell
    /// </summary>
    ///
<param name="e" type="Sys.UI.DomEvent">The arguments for the event</param>

    e.stopPropagation();
    e.preventDefault();

    if (!this._enabled) return;

    var target = e.target;
    var visibleDate = this._getEffectiveVisibleDate();
    Sys.UI.DomElement.removeCssClass(target.parentNode, "ajax__calendar_hover");

    //S3 - now to return the date. If the CalendarView is Quarter or QuarterYear and the mode returned is
    //quarter then select the date, close the calendar and return
    var calendarView = this.get_calendarView();
    if (target.mode == "quarter" && (calendarView == AjaxControlToolkit.CalendarView.Quarter
      || calendarView == AjaxControlToolkit.CalendarView.QuarterYearDecade)) {
    {
        this.set_selectedDate(target.date);
        this._blur.post(true);
        this.raiseDateSelectionChanged();
        return;
    }
    //E3

Modified code:

_cell_onclick: function(e) {
    /// <summary>
    /// Handles the click event of a cell
    /// </summary>
    ///
<param name="e" type="Sys.UI.DomEvent">The arguments for the event</param>
    e.stopPropagation();
    e.preventDefault();
    if (!this._enabled) return;
    var target = e.target;
    var visibleDate = this._getEffectiveVisibleDate();
    Sys.UI.DomElement.removeCssClass(target.parentNode, "ajax__calendar_hover");
    var calendarView = this.get_calendarView();
    //S4 introduce new modes selected. Returns when the mode and view matches
    if ((target.mode == "quarter" && (calendarView == AjaxControlToolkit.CalendarView.Quarter
                                   || calendarView == AjaxControlToolkit.CalendarView.QuarterYearDecade))
      || (target.mode == "month" && (calendarView == AjaxControlToolkit.CalendarView.Month
                                  || calendarView == AjaxControlToolkit.CalendarView.MonthYearDecade))
      || (target.mode == "year" && (calendarView == AjaxControlToolkit.CalendarView.YearDecade))
      || (target.mode == "decade" && (calendarView == AjaxControlToolkit.CalendarView.Decade))
      || (target.mode == "today")
      || (target.mode == "day")
       ) {
        this._closeCalendar(target);
        return;
    }
    switch (target.mode) {
        case "month":
            if (target.month != visibleDate.getMonth()) {
                this._visibleDate = target.date;
            }
            this._switchMode("days");
            break;
        case "year":
            if (calendarView != AjaxControlToolkit.CalendarView.QuarterYearDecade
            && calendarView != AjaxControlToolkit.CalendarView.Quarter) {
                if (target.date.getFullYear() != visibleDate.getFullYear()) {
                    this._visibleDate = target.date;
                }
                this._switchMode("months");
            }
            else {
                if (target.date.getFullYear() != visibleDate.getFullYear()) {
                    this._visibleDate = target.date;
                }
                this._switchMode("quarters");
            }
            break;
        case "decade":
            if (target.date.getFullYear() != visibleDate.getFullYear()) {
                this._visibleDate = target.date;
            }
            this._switchMode("years");
            break;
        case "prev":
        case "next":
            this._switchMonth(target.date);
            break;
        case "title":
            switch (this._mode) {
                case "days": this._switchMode("months"); break;
                case "months": this._switchMode("years"); break;
                case "years": this._switchMode("decades"); break;
                case "quarters": this._switchMode("years"); break;
            }
            break;
    }
    //E4
},
//S4 - I introduced the _closeCalendar function to separate out the functionality for when the calendar closes.
//This was beneficial when determining how to refactor the code as I could then see what was common and what was not.
_closeCalendar: function(target) {
    this.set_selectedDate(target.date);
    this._blur.post(true);
    this.raiseDateSelectionChanged();
    return;
},
//E4

And that’s it! With only a handful of additional lines of code, there are now many more views that you can choose from. Note that I have also refactored the dispose code. There was a lot of repetition in the code, so I decided to make a method to do all the repeated work and call that to dispose of each of the views. That now looks like the following:

//E4 - refactor dispose code to remove repetition 
dispose: function() {
    /// <summary>
    /// Disposes this behavior's resources
    /// </summary>

    if (this._popupBehavior) {
        this._popupBehavior.dispose();
        this._popupBehavior = null;
    }
    this._modes = null;
    this._modeOrder = null;
    if (this._modeChangeMoveTopOrLeftAnimation) {
        this._modeChangeMoveTopOrLeftAnimation.dispose();
        this._modeChangeMoveTopOrLeftAnimation = null;
    }
    if (this._modeChangeMoveBottomOrRightAnimation) {
        this._modeChangeMoveBottomOrRightAnimation.dispose();
        this._modeChangeMoveBottomOrRightAnimation = null;
    }
    if (this._modeChangeAnimation) {
        this._modeChangeAnimation.dispose();
        this._modeChangeAnimation = null;
    }
    if (this._container) {
        if (this._container.parentNode) { // added this check before calling removeChild WI: 8486
            this._container.parentNode.removeChild(this._container);
        }
        this._container = null;
    }
    if (this._popupDiv) {
        $common.removeHandlers(this._popupDiv, this._popup$delegates);
        this._popupDiv = null;
    }
    if (this._prevArrow) {
        $common.removeHandlers(this._prevArrow, this._cell$delegates);
        this._prevArrow = null;
    }
    if (this._nextArrow) {
        $common.removeHandlers(this._nextArrow, this._cell$delegates);
        this._nextArrow = null;
    }
    if (this._title) {
        $common.removeHandlers(this._title, this._cell$delegates);
        this._title = null;
    }
    if (this._today) {
        $common.removeHandlers(this._today, this._cell$delegates);
        this._today = null;
    }
    if (this._button) {
        $common.removeHandlers(this._button, this._button$delegates);
        this._button = null;
    }

   // disposeView is a new method I created to do all the work of cleaning up each view. Further refactoring of this method
   // is possible.
    this.disposeView(this._daysBody);
    this.disposeView(this._monthsBody);
    this.disposeView(this._yearsBody);
    this.disposeView(this._decadesBody);
    this.disposeView(this._quartersBody);
    var elt = this.get_element();
    $common.removeHandlers(elt, this._element$delegates);
    AjaxControlToolkit.CalendarBehavior.callBaseMethod(this, "dispose");
},
disposeView: function(viewBody) {
    if (viewBody) {
        for (var i = 0; i < viewBody.rows.length; i++) {
            var row = viewBody.rows[i];
            for (var j = 0; j < row.cells.length; j++) {
                $common.removeHandlers(row.cells[j].firstChild, this._cell$delegates);
            }
        }
        viewBody = null;
    }
},
//E4

If you ran into trouble and need to have a look at the source code, it can be found here:

AjaxControlToolkit-Framework3.5SP1withDecadeYearQuarterMonthDayandInitialView.zip

Still to go, I believe I have figured out a way to add date restriction to the component. It might take me a few days to figure this out, but I should be able to do it.

Edit: I just put in a fix to the _isSelected function in the CalendarBehavior.js file. I had misread the original code which allowed the date to return a true in the wrong case. This caused the selected date square to potentially be around more than one date on the calendar view. The zip file above now has the corrected code.

How to add a Year View to the Ajax Control Toolkit Calendar Extender control

Further to Zhi-Qiang Ni’s article on showing a Month View in the Calendar Extender control, the next obvious step is to produce a Year View. I have separated out the javascript that was in the CreateDelegate function. I added that as a separate function, called _cell_onclick.

I added a separate calendar extender control to the page, so that the Year view can be demonstrated, and I changed the output date format so that it displays a full year.

I created a javascript method for each of the different start modes. By modifying the start mode of the calendar it displays the different calendar view. I do the same thing in the InitialView property I added in a previous article.

Because only one calendar shows at a time, I will leave the cal variable and create two new variables, monthCalendar and yearCalendar. I’ve also renamed the BehaviorID properties to monthCalendar and yearCalendar. The BehaviorID is what is used to find the calendar control from within client script. $find is then used to find the client side javascript Ajax control object.

I couldn’t help myself, so I’ve refactored the code in the _cell_onclick function.

The result is this:

Year View in the Calendar Extender control

<!--Page Language="C#" AutoEventWireup="true" CodeBehind="Sample5.aspx.cs" Inherits="CalendarSample.Sample5"-->

 

<script type="text/javascript"><!--
        var cal;
        var monthCalendar;
        var yearCalendar;

        function _cell_onclick(e) {
            /// <summary>
            /// Handles the click event of a cell
            /// </summary>
            ///
<span  name="e" type="Sys.UI.DomEvent" class="mceItemParam"></span>The arguments for the event</param>
            e.stopPropagation();
            e.preventDefault();
            if (!cal._enabled) return;
            var target = e.target;
            var visibleDate = cal._getEffectiveVisibleDate();
            Sys.UI.DomElement.removeCssClass(target.parentNode, "ajax__calendar_hover");
            //S1
            if ((target.mode == "year" && cal == yearCalendar)
              || (target.mode == "month" && cal == monthCalendar)
              || target.mode == "today")
                closeCalendar(cal, target.date);
            else if ((target.mode == "month" && target.month != visibleDate.getMonth())
                 || (target.mode == "year" && target.year != visibleDate.getYear())) {
                cal._visibleDate = target.date;
            }
            else if (target.mode == "prev" || target.mode == "next") {
                cal._switchMonth(target.date);
            }
            else if (target.mode == "title") {
                switch (cal._mode) {
                    case "days": cal._switchMode("months"); break;
                    case "months": cal._switchMode("years"); break;
                }
            }
            //            switch (target.mode) {
            //                case "prev":
            //                case "next":
            //                    cal._switchMonth(target.date);
            //                    break;
            //                case "title":
            //                    switch (cal._mode) {
            //                        case "days": cal._switchMode("months"); break;
            //                        case "months": cal._switchMode("years"); break;
            //                    }
            //                    break;
            //                case "month":
            //                    //if the mode is month, then stop switching to day mode.
            //                    if (target.month != visibleDate.getMonth()) {
            //                        cal._visibleDate = target.date;
            //                    }
            //                    //this._switchMode("days");
            //                    break;
            //                case "year":
            //                    if (target.date.getFullYear() != visibleDate.getFullYear()) {
            //                        cal._visibleDate = target.date;
            //                    }
            //                    break;
            //                //                case "day":
            //                //                    this.set_selectedDate(target.date);
            //                //                    this._switchMonth(target.date);
            //                //                    this._blur.post(true);
            //                //                    this.raiseDateSelectionChanged();
            //                //                    break;
            //                case "today":
            //                    closeCalendar(cal, target.date);
            //                    break;
            //            }
            //E1
        }

        function pageLoad() {
            monthCalendar = $find("monthCalendar");
            yearCalendar = $find("yearCalendar");
            //we need to modify the original delegate of the month cell.
            monthCalendar._cell$delegates = {
                mouseover: Function.createDelegate(monthCalendar, monthCalendar._cell_onmouseover),
                mouseout: Function.createDelegate(monthCalendar, monthCalendar._cell_onmouseout),
                click: Function.createDelegate(monthCalendar, _cell_onclick)
            }
            yearCalendar._cell$delegates = {
                mouseover: Function.createDelegate(yearCalendar, yearCalendar._cell_onmouseover),
                mouseout: Function.createDelegate(yearCalendar, yearCalendar._cell_onmouseout),
                click: Function.createDelegate(yearCalendar, _cell_onclick)
            }
        }

        function closeCalendar(cal, targetDate) {
            cal.set_selectedDate(targetDate);
            cal._switchMonth(targetDate);
            cal._blur.post(true);
            cal.raiseDateSelectionChanged();
        }

        function onCalendarMonth(sender, args) {
            //set the default mode to month
            cal = monthCalendar;
            sender._switchMode("months", true);
        }

        function onCalendarYear(sender, args) {
            cal = yearCalendar;
            sender._switchMode("years", true);
        }

// --></script>

 

<form id="form1" enctype="application/x-www-form-urlencoded">
<div>The Month:</div>
<div>The Year:</div>
</form>

Now I will move this code back into my already enhanced Ajax Control Toolkit calendar control. The code will take me about 20 minutes to do, but the time to write the article takes a lot longer (seems to be a ratio of about 1 to 4 at the moment). I’ll do that next week.

Code for this is at http://cid-5e237543fffb2891.skydrive.live.com/self.aspx/Public/CalendarSampleWithYearView.zip