Telerik RadAjaxManager & RadAjaxManagerProxy

This is a basic approach to utilising the Telerik Ajax Manager on your web pages.

If you are using a MasterPage it is particularly useful to know that you can specify your default AjaxManagement in the masterpage as below as the first this after the body tag:

< telerik : RadScriptManager ID="RadScriptManager1" runat="server" />
< telerik : RadAjaxManager runat="server" ID="RadAjaxManager1" DefaultLoadingPanelID="RadAjaxLoadingPanel1" />
< telerik : RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" Width="100%" >
< br />
< br />
< br />
< / telerik : RadAjaxLoadingPanel >

NB: It should be worth noting that on every occassion, to avoid isssues with trying to get Telerik Ajax working, the RadScriptManager should always be declared above RadAjaxManager in the page code.

Then in each page/usercontrol which uses the masterpage you need to declare a RadAjaxManagerProxy to Ajaxify a particular control on the page/usercontrol, in a similar fashion as below:

< telerik : RadAjaxManagerProxy runat="server" ID="rampTaskListWizardStep5" >
< AjaxSettings >
< telerik : AjaxSetting AjaxControlID="divFurtherAssessment" >
< UpdatedControls >
< telerik : AjaxUpdatedControl ControlID="divFurtherAssessment" LoadingPanelID="RadAjaxLoadingPanel1" / >
< telerik : AjaxUpdatedControl ControlID="divDSEEmployees" / >
< telerik : AjaxUpdatedControl ControlID="divManualHandlingEmployees" / >
< telerik : AjaxUpdatedControl ControlID="divExpectantMothersEmployees" / >
< telerik : AjaxUpdatedControl ControlID="divYoungPersonsEmployees" / >
< telerik : AjaxUpdatedControl ControlID="vsTaskListWizardStep5" / >
< / UpdatedControls >
< / telerik:AjaxSetting >
< / AjaxSettings >
< / telerik:RadAjaxManagerProxy >

If you have Ajaxified a whole page then you can use the following code in the Page_PreRender call in the page cycle:

ScriptManager _scriptManager = ScriptManager.GetCurrent(this.Page);
_scriptManager.RegisterPostBackControl(rpbDisplayScreenEquipment);

When the page loads it will exclude the control from the Asynchronous post back making it do a full page postback:

SQL Server 2005 : Mutliple Common Table Expressions (CTE)

Couple of hints using one of my favorite features of SQL server 2005 - Common table expressions. If you find this of use, leave a comment :)

USING MULTIPLE CTES IN A SINGLE SELECT STATEMENT
If you want to use more than one CTE, and let's face it - who wouldnt? Simply drop the WITH when adding the definition of the second (or more) CTE as shown below:

;with cte1 as
(select getdate() as field)
,cte2 as
(select getdate() as field)

select *
from cte1
inner join cte2 on cte1.field = cte2.field

CREATING A CTE BASED UPON ANOTHER CTE
If you dont like to have a derived table joined to the CTE, you might want to create a second CTE based on the first, here is how it can be done. Note that cte3 is just a select of CTE1 - simple but effective:

;with cte1 as
(select getdate() as field)
,cte2 as
(select getdate() as field)
,cte3 as
(select field as field
from cte1)

select *
from cte1
inner join cte2 on cte1.field = cte2.field
inner join cte3 on cte1.field = cte3.field

Enjoy!

Application Icons and PNG images

As you do, I find I spend too much time trawling the internet for that right image to spice up the look and feel of my application.

If only there was a site I could go directly to and search for the image I was after...

Well, I have found one! http://www.iconfinder.net

It's a fairly extensive archive and should be able to provide you with what you are after and more importantly there is a filter which you can select to show only those icons available for commercial use.

There maybe other sites out there, which I invite you to inform me of if you have knowledge of such sites, but if not... fill your boots!

SQL Server 2005 : Using XML Columns

Had cause to play around with XML Columns in SQL Server 2005 over the weekend on a personal project im working on (shameless plug : www.milanxbl.com).

To cut a long story short, I ended up having a (well formed) XML document saved into an XML Column in the database that I needed to break down into its component parts in order to process the info into tables which already existed. This XML contained 2 parts in a master/detail style - the detail section contained a collection of items and the master section was just a header with several values.

I broke apart the master chunk of the xml using the following TSQL

SELECT UniqueIDColumn
,xmlcolumn.value('(/Root/XMLValue1)[1]', 'varchar(max)') AS XMLValue1
,xmlcolumn.value('(/Root/XMLValue2)[1]', 'varchar(max)') AS XMLValue2
,xmlcolumn.value('(/Root/XMLValue3)[1]', 'varchar(max)') AS XMLValue3
,xmlcolumn.value('(/Root/XMLValue4)[1]', 'varchar(max)') AS XMLValue4

FROM dbo.TableName WITH (Nolock)

In the example above, we query the content of the xml column (xmlcolumn) for the each value and bring back a normal looking table for further manipulation.

Obviously, traversing the nodes within the detail elements of the xml was slightly more complex and was done using cross apply to return to the node which contained the collection, like this:

SELECT UniqueIDColumn
,cast(R.i.query('./XMLValue1[1]/text()') as varchar(max)) as XMLValue1
,cast(R.i.query('./XMLValue2[1]/text()') as varchar(max)) as XMLValue2
,cast(R.i.query('./XMLValue3[1]/text()') as varchar(max)) as XMLValue3
,cast(R.i.query('./XMLValue4[1]/text()') as varchar(max)) as XMLValue4
,cast(R.i.query('./XMLValue5[1]/text()') as varchar(max)) as XMLValue5

from dbo.TableName WITH (NOLOCK)
cross apply xmlcolumn.nodes('/Root/Node2/Node3/Node4') R(i)

In this example we don't see any reference to the xmlcolumn until we see the 'cross apply' - there we see that the xml column has a different syntax, we are joining the column to itself using the table valued function (R) and navigating down the (i) axis for the nodes. This all begins to look a little 'CUBE-esce' when you think about it.

Microsofts official word on the use of the APPLY keyword, says
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

For more info on the APPLY operator, refer to MSDN SQL Server Developer center

I suspect this will be very useful, its just a matter of time before an application outside of my pet project warrants its usage.

Not yet had a chance to look at performance of this process - its not really an issue for my project - this is all happending 'under the hood' in a pre-processed manner, rather than being done at runtime in any live queries.

Comments are welcome!

Object Search in SQL Server Management Studio

When the query writing tool of choice switched from Query Analyzer (SQL Server 2000 and below (8.0) ) to SQL Server Management Studio (SQL Server 2005+ (9.0)), along with it went the ability to search for columns in tables in a quick way.

Thankfully, there is a way to search the database you are currently in for those hard to find columns. This is particularly handy when getting to grips with new databases where you only know a bit of the column name. It's just a bit of a shame the GUI has gone.

Below is a script which will create you an SP that you can pass a partial column name. Obviously, this is geared towards a SQL Server 2005 box, change the nvarchar(max) to nvarchar(255) or something similar in SQL Server 2000.

It will return you the column which matches your search and the table its in.

Syntax for this is : EXEC TSQLColumn_Search 'search_term_here'

use
go

if exists (select 1 from sysobjects where name='TSQLColumn_Search')
begin
drop procedure TSQLColumn_Search
end
go

create procedure TSQLColumn_Search (@strColumnName nvarchar(max))
as
set transaction isolation level read uncommitted
select c.name as Column_Name
,o.name as Table_Name

from sys.all_columns c with (nolock)
inner join sysobjects o with (nolock) on o.id = c.object_id
inner join syscomments co with (nolock) on co.id = o.id
where c.name like '%'+@strColumnName+'%'
go


Enjoy!

Design Time Stylesheets for User Controls

Some people can be slightly pedantic when it comes to writing code and would like to have the utopian idea of a build with zero errors and zero warnings. I'm definately on this spectrum.

Visual Studio doesn't make this easy for you in some circumstances. Styling and Theming has had some excellent advancements in the last couple of iterations of the Visual Studio environment but how do you get round the issue of design time css warnings? You don't want to add the stylesheet to every control as it will output to the page in runtime but if you don't add it the VS doesn't know about it and can't validate your class names.

Well the first option is to turn off the warnings. This I wouldn't call elegant as you are then missing out on part of the validation process and the warnings can be used to your benefit. To do this click Tools -> Options. Expand the Text Editor item, then HTML, then Validation. Untick the 'as warnings (HTML, CSS)'. This plasters over the cracks which might be enough for you. But eventually the cracks will widen and it would have been really useful to see into the crack and find out what was pushing it wider.

The most elegant way is to take advange of Visual Studios rendering architecture. This code shows you how, those eagle eye'd among you will see the solution straight away.

<link href="mainStyle.css" rel="stylesheet" type="text/css" runat="server" id="lnkcss" visible="false" />

Link in the stylesheet to the user control and add the runat, id and then the attribute which makes all this worth while the visible="false". Using the visible=false the rendering engine will not output any part of the tag and then you've made a design time stylesheet. As the style sheet has no state management and no events there would only be a marginal increase to the memory usage for the site. And if you're that bothered about memory management then shut up and refactor your code again.

I'm not saying this is a solution for every situation but if the shoe fits....

Script to kill all processes for a database except your own

Declare @sql varchar(100)
Declare @dbname varchar(100)
Declare @spid int
set @dbname = 'PeninsulaukSP1'
DECLARE CursorQuery CURSOR FOR
SELECT spid FROM master..sysprocesses WHERE dbid = DB_ID(@dbname) AND spid != @@SPID
OPEN CursorQuery

FETCH NEXT FROM CursorQuery
INTO @spid
PRINT 'Starting Kill Process'
WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = RTRIM(convert(varchar(3),@spid))
PRINT 'Killing Process ' + @sql
EXEC('KILL '+ @sql)
FETCH NEXT FROM CursorQuery
INTO @SPID
END
CLOSE CursorQuery
DEALLOCATE CursorQuery