You might have noticed that RadScheduler uses very cryptic strings to describe repeating appointments. We have decided not to reinvent the wheel and in fact this format is closely based on the iCalendar (RFC2445) standard. Here is an example:
DTSTART:20080218T000000Z
DTEND:20080219T000000Z
RRULE:FREQ=MONTHLY;INTERVAL=12;BYDAY=-2MO;COUNT=3;
This rule says: "The event starts at midnight on 18th of February 2008, lasts one day and repeats each year, for 3 years, on the second to last Monday of February". Now say this three times fast.
As much as standards-based those strings are, they are useless if you want to process your appointments on the database server itself. There are many reasons for you to want to do this. You might want to check for conflicting appointments. Or you might need to prepare some statistics for a report.
From version 2005 and up, SQL Server supports Managed Stored Procedures and User-Defined Functions (UDF). This exciting feature lets you plug in your own managed code into SQL Server and use it inside your queries. This is exactly what we need.
The solution we are presenting comes in the form of a managed UDF. This function uses the same recurrence engine as RadScheduler to do the heavy lifting required to evaluate the rules.
The attached file contains a setup script that enables CLR integration, registers the two assemblies (recurrence engine, UDF) and creates the function itself. Full source code of the UDF is included. Be sure to read the section on CLR integration on MSDN. There are good reasons that it is not enabled by default.
Let's see how all this comes together. A common scenario is to request all appointments in a given time range. Here is how we can do this with our function:
DECLARE
@@RangeStart as DATETIME