Exception Handling in SQL Server 2000 and 2005

Till the era of SQL Server 2000, there was no standard and structured way of exception handling in procedures return in SQL. Developers have to use the standard error variable “@@ERROR” to check if any error has occurred and take the necessary action.

A sample code in SQL Server 2000 will look like this:-

BEGIN TRANSACTION

--Write your statements here like “insert into some table”

set @Error = @@ERROR

--if there is some error

if @Error <> 0
Begin
ROLLBACK TRANSACTION
Else
COMMIT TRANSACTION
End
SQL Server 2005 have come with Try-Catch blocks, very similar to those used in frontline programming languages like C# and Java. Hence the same code above can be written in a much structured and efficient way in SQL Server 2005 as follows :-

BeginTry
BEGIN TRANSACTION
-- Do some operations here like “insert into some table”
COMMIT TRANSACTION
EndTry
BeginCatch
ROLLBACK TRANSACTION
EndCatch
Notice the important keywords here – “BeginTry”, “EndTry”, “BeginCatch” and “EndCatch”. These are equivalent of “Try-Catch” blocks used in C# or Java.

Passing two arguments in DataNavigateUrlFormatString in hyperlink field of .NET 2.0 Grid-View

In a hyperlink column of gridview, suppose the value of the property DataNavigateUrlFormatString is “ProductsForSupplierDetails.aspx?SupplierID={0}”. Many people think that the argument {0} is replaced by the value of the property DataKeyNames of the Gridview. But in actual case this is replaced by the value of the property DataNavigateUrlFields of the hyperlink column.

Now suppose you want to pass two arguments instead of one, something like this “ProductsForSupplierDetails.aspx?SupplierID={0}&CompanyName={1}”.

Now as these two arguments come from property DataNavigateUrlFields of the hyperlink column. So value of this property has to be “DataNavigateUrlFields="SupplierID,CompanyName”.

At run time the .NET replaces {0} with “SupplierID” and {1} with “CompanyName” of that row.

Pretty simple and straightforward it seems but I have seen many people struggling with this simple concept. Also I found wrong answers for this problem in some .NET forums. Hence thought to post it here for benefit of masses.

Renaming table column name in SQL Server

Well, that’s a pretty simple requirement if you are a database developer. Surprisingly I was only aware of how to do this using SQL Server Enterprise Manager. But one of my friends asked me how to do it using SQL Script. The requirement was that we cannot drop the existing table and recreate it again as the table has a lot of data. A little googling helped to figure out that it’s pretty simple.

There is an inbuilt system stored procedure “sp_rename” which can be used to accomplish this task. Infact this system stored procedure can be used to rename a user created object like table, column or user-defined data-type.

The syntax is pretty simple:-

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name', [ @objtype = ] 'object_type' ]

Eg. - To rename a column of table – “MyTable” from “OldColumnName” to “NewColumnName”, use following syntax:-

EXEC sp_rename 'MyTable.[OldColumnName]', 'NewColumnName', 'COLUMN'

And yes, that’s it. Pretty simple, but then you need to know it that something like this exists. :)

ClearType Text

Two days back, I installed IE 7 on my PC. After installing it, I found a visible change in the fonts in both IE and Outlook. And as usual, change is always uncomfortable. The fonts were coming more of blurred type and not very clear. After struggling with these new font styles, I finally googled for the problem and came out across a blog by Microsoft’s IE 7 project manager, which mentions what the new font style is all about.
http://blogs.msdn.com/ie/archive/2006/02/03/524367.aspx

Basically IE7 has come up with concept of “ClearType” text which makes text more readable and comprehensible to human eyes. It is especially useful for LCD monitors rather than old CRT monitors. But as Microsoft tries to dictate terms always, so they enable this in IE7 by default without even asking the user about it or telling how to turn if ON-OFF. Thankfully, the option is there to turn it off by using Tools > Internet Options > Multimedia > Uncheck “Always use clear-type for HTML”. Once this is done and IE is restarted (very imp.), we return back to our good old fonts.

Coming back to ClearType text technology, this has been introduced in Office 2007 and Windows Vista by default. So whenever you install these softwares on your PC, these are enabled by default. I am yet to figure out how to turn them off. Presently I am using windows XP where ClearType text is turned off by default. We can turn it ON by changing some windows registry settings but that is always dangerous. A little more googling lead me to following link:-
http://www.microsoft.com/typography/ClearTypePowerToy.mspx

This is a downloadable utility which can be installed on your PC and then it appears in Control Panel. Using this you can turn on “ClearType” on your XP PC and also choose the setting and font contrast which you find most comfortable.

In the end, all these things are dependent on individual perception. Some people may like it, some may not. I am trying to adjust my eyes for this new font style from last two days and I have pretty much succeeded in that. Though fonts look slightly blurry in this style but their readability has definitely improved. I am able to concentrate better while reading documents and web-pages.

To Summarize, its individual perception and comfort level to change that matters. But I recommend giving it a try atleast once. If you are not satisfied, you always have the option to turn it off. :)

Creating Hourglass cursor in Web-Applications

Very often, in most of the windows application, there is a functionality in which the mouse cursor changes to hour-glass whenever the application is doing some background processing or is busy. This is done to give user an intimation to wait for application to finish processing.

We had a similar requirement in one of our web-applications. In the application, the mouser cursor has to be changed to hour-glass when user clicks on a particular button or whenever post-back happens. The cursor has to return back to normal as soon as the processing of the page finishes.

We achieved this functionality using JavaScript and HTML.

For this first we write a JavaScript function which changes the mouse cursor to hour-glass.

function ChangeToHourGlass()
{
document.body.style.cursor = 'wait';
}

Now we need to call this function appropriately so that the required effect is achieved. So we call this function in two page events – onbeforeunload and onunload. This is done by adding these events handlers in the “body” tag of your ASPX/HTML page.

<body onbeforeunload="ChangeToHourGlass();" onunload="ChangeToHourGlass ();">

Now let’s explain in short about both these event handlers:-

Onbeforeunload – This event is fired before a page is getting unloaded.
OnUnload – This event is fired just before the page is getting unloaded.

How the actual functionality works:-

When we click on a submit button in a page, the current page is unloaded. So one of these events is fired and the mouse cursor changes to hour-glass. While the request goes to back-end and returns back with the result, the current page continues to display. So mouse cursor appears as hour-glass. As soon as the back-end processing is finished, the page is re-created in which the mouse cursor returns back to normal. Hence the desired functionality is achieved.

The reason for using both these event handlers is that Internet Explorer browser prefers “Onbeforeunload” event while other browsers prefer “OnUnload” event. So to make this functionality work in all types of browsers, the JavaScript function is called in both these events.

Adding Custom Functions to XSL using XSLT Extensions

Many times it is our requirement to perform various types of processing in XSL file using functions. There are many in-built functions in XSL for doing various types of tasks like string manipulation, number calculations etc but sometimes we need to do some custom processing which can only be achieved by writing our own custom functions. These custom functions can be written in either JavaScript or in some other language. This BOK focuses on how to write these custom functions in a .NET compliant language (like VB.NET, C#) and then use it in our XSL file.

Here are the steps to achieve the same:-

1. Create the custom function in a class. If you need to write many custom functions, it is better to place these functions in a separate class; otherwise the function can be written in same class file as your calling code.

Here a separate class “XSLCustomFunctions” is created which has a function for date formatting.

Public Class XSLCustomFunctions
Public Function Format_Date(ByVal sDateTime As String) As DateTime
Return CDate(sDateTime)
End Function
End Class

Point to Note - Here class name is “XSLCustomFunctions” and custom function name is “Format_Date”

2. Create an object of the custom function class. This has to be done in the function where the XSL transform is being applied.

Dim oCustFunc As New XSLCustomFunctions
3. Now we need to create XML namespace declaration in the XSL file for this custom class

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:myCustFunc="urn:custFunc">

Point to Note – Here “myCustFunc” is the XML namespace which refers to “urn:custFunc”. This will be passed as XSL extension while adding XSL extension object in step 5

4. Use the custom function in the XSL at the required place. Please note that function is used in the format – “XML_NameSpace:CustomFunction”.

<xsl:value-of select="myCustFunc:Format_Date(Value)" />

5. Create an object of XSLT argument list in the VB.NET code and add extension object to it and then only apply the XSL transform.

Private Function ApplyXSLTransform(ByVal sXml As String) As String

Dim oArgsList As New XsltArgumentList
Dim oXmlDocument As New XmlDocument
Dim oXslTransform As New XslTransform
Dim oStringWriter As New System.IO.StringWriter
Dim oCustFunc As New XSLCustomFunctions

oXmlDocument.LoadXml(sXml)
oXslTransform.Load(Server.MapPath("xsl_doc.xsl"))

oArgsList.AddExtensionObject("urn:custFunc", oCustFunc)

oXslTransform.Transform(oXmlDocument, oArgsList, oStringWriter, Nothing)

Return oStringWriter.ToString
End Function
In this way, we can use our own custom functions created in VB.NET/C# in the XSL file.