Creating the Timezone function as a C# CLR function with XML

In my quest to find the most efficient process for a timezone function for MS SQL I next wanted to try creating a C# CLR function there the configuration for the function is in an XML file.  This is a test to see if the over-head for a SQL query is higher or lower than the over-head of IO access to a file.

I have an administrative class to manage the parms for the timezone conversion process.  The class definition is below.

public class TimeZoneParm

{

    private string timeZoneCd;

    private string timeZoneName;

    private int offSetHr;

    private int offSetMi;

    private int dstOffSetHr;

    private int dstOffSetMi;

    private string dstEffDt;

    private string dstEndDt;

    public TimeZoneParm()

    {

    }

    public string TimeZoneCd

    {

        get { return timeZoneCd; }

        set { timeZoneCd = value; }

    }

    public string TimeZoneName

    {

        get { return timeZoneName; }

        set { timeZoneName = value; }

    }

    public int OffSetHr

    {

        get { return offSetHr; }

        set { offSetHr = value; }

    }

    public int OffSetMi

    {

        get { return offSetMi; }

        set { offSetMi = value; }

    }

    public int DstOffSetHr

    {

        get { return dstOffSetHr; }

        set { dstOffSetHr = value; }

    }

    public int DstOffSetMi

    {

        get { return dstOffSetMi; }

        set { dstOffSetMi = value; }

    }

    public string DstEffDt

    {

        get { return dstEffDt; }

        set { dstEffDt = value; }

    }

    public string DstEndDt

    {

        get { return dstEndDt; }

        set { dstEndDt = value; }

    }

}

This function is similar to the C# CLR function that does the SQL look-up.  the biggest difference is in that it does its lookup from a XML file using LINQ oppose to a SQL statement from a table.  If you read through my previous post the logic of this function is the same with the exception of where the function gets its initial information.  One thing to note is that the process needs the full path to the file.  Since the function is stored in a MS SQL table when it is registered you cannot use relative pathing.  Also the ID that the SQL Server service is using to run must have read access to the folder where the file is stored.  For ease of use I put the file in the path, c:\program files (x86)\Microsoft SQL Server\.

The first function that I started, similar to the other processes, was the function that converts a date/time value to UTC time based upon the provided timezone code.

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "XML_UTC_TIME")]

public static SqlDateTime fnXmlUtcTime(SqlDateTime OrigDate, SqlString CurrentTimeZone)

{

    try

    {

        System.Xml.Linq.XDocument xmlDoc = System.Xml.Linq.XDocument.Load("C:\\Program Files (x86)\\Microsoft SQL Server\\time_zones.xml");

        List<TimeZoneParm> tzp =

          (from time_zone in xmlDoc.Descendants("timezone")

           where time_zone.Attribute("timezone_cd").Value == CurrentTimeZone.ToString()

           select new TimeZoneParm

           {

               TimeZoneCd = time_zone.Attribute("timezone_cd").Value,

               TimeZoneName = time_zone.Attribute("timezone_name").Value,

               OffSetHr = int.Parse(time_zone.Attribute("offset_hr").Value),

               OffSetMi = int.Parse(time_zone.Attribute("offset_mi").Value),

               DstOffSetHr = int.Parse(time_zone.Attribute("dst_offset_hr").Value),

               DstOffSetMi = int.Parse(time_zone.Attribute("dst_offset_mi").Value),

               DstEffDt = time_zone.Attribute("dst_eff_dt").Value,

               DstEndDt = time_zone.Attribute("dst_end_dt").Value,

           }).ToList<TimeZoneParm>();

        int offsethr = 0;

        int offsetmi = 0;

        int dst_offsethr = 0;

        int dst_offsetmi = 0;

        string dst_eff_key = "01110000";

        string dst_end_key = "01110000";

        if (tzp.Count > 0)

        {

            offsethr = tzp[0].OffSetHr;

            offsetmi = tzp[0].OffSetMi;

            dst_offsethr = tzp[0].DstOffSetHr;

            dst_offsetmi = tzp[0].DstOffSetMi;

            dst_eff_key = tzp[0].DstEffDt;

            dst_end_key = tzp[0].DstEndDt;

        }

        DateTime origDate = DateTime.Parse(OrigDate.ToString());

        int dst_eff_mon = int.Parse(dst_eff_key.Substring(0, 2));

        int dst_eff_wk = int.Parse(dst_eff_key.Substring(2, 1));

        int dst_eff_dw = int.Parse(dst_eff_key.Substring(3, 1));

        int dst_eff_hr = int.Parse(dst_eff_key.Substring(4, 2));

        int dst_eff_mi = int.Parse(dst_eff_key.Substring(6, 2));

        int dst_end_mon = int.Parse(dst_end_key.Substring(0, 2));

        int dst_end_wk = int.Parse(dst_end_key.Substring(2, 1));

        int dst_end_dw = int.Parse(dst_end_key.Substring(3, 1));

        int dst_end_hr = int.Parse(dst_end_key.Substring(4, 2));

        int dst_end_mi = int.Parse(dst_end_key.Substring(6, 2));

        DateTime dst_eff_dt_test = new DateTime(origDate.Year, dst_eff_mon, 1);

        dst_eff_dt_test = dst_eff_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_eff_dt_test.DayOfWeek)));

        DateTime dst_eff_dt = dst_eff_dt_test.AddDays(dst_eff_wk * 7);

        dst_eff_dt = dst_eff_dt.AddHours(dst_eff_hr);

        dst_eff_dt = dst_eff_dt.AddMinutes(dst_eff_mi);

        dst_eff_dt = dst_eff_dt.AddDays(dst_eff_dw - 1);

        if (dst_eff_dt.Month != dst_eff_mon)

        {

            dst_eff_dt = dst_eff_dt.AddDays(-7);

        }

        DateTime dst_end_dt_test = new DateTime(origDate.Year, dst_end_mon, 1);

        dst_end_dt_test = dst_end_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_end_dt_test.DayOfWeek)));

        DateTime dst_end_dt = dst_end_dt_test.AddDays(dst_end_wk * 7);

        dst_end_dt = dst_end_dt.AddHours(dst_end_hr);

        dst_end_dt = dst_end_dt.AddMinutes(dst_end_mi);

        dst_end_dt = dst_end_dt.AddDays(dst_end_dw - 1);

        if (dst_end_dt.Month != dst_end_mon)

        {

            dst_end_dt = dst_end_dt.AddDays(-7);

        }

        DateTime newDate = origDate.AddHours(offsethr * -1).AddMinutes(offsetmi * -1);

        DateTime newDateDst = origDate.AddHours(dst_offsethr * -1).AddMinutes(dst_offsetmi * -1);

        bool isDST = false;

        if (origDate >= dst_eff_dt)

        {

            if (origDate <= dst_end_dt)

            {

                isDST = true;

            }

        }

        if (isDST)

        {

            return (SqlDateTime)newDateDst;

        }

        else

        {

            return (SqlDateTime)newDate;

        }

    }

    catch (Exception ex)

    {

        return OrigDate;

    }

}

The second function that I worked on was the one that converts time from UTC time to a date/time based upon a provided timezone code.  The logic is the same as the previous function with the exception of converting from UTC as oppose to converting to UTC.

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "XML_TZ_TIME")]

public static SqlDateTime fnXmlTzTime(SqlDateTime OrigDate, SqlString NewTimeZone)

{

    try

    {

        System.Xml.Linq.XDocument xmlDoc = System.Xml.Linq.XDocument.Load("C:\\Program Files (x86)\\Microsoft SQL Server\\time_zones.xml");

        List<TimeZoneParm> tzp =

          (from time_zone in xmlDoc.Descendants("timezone")

           where time_zone.Attribute("timezone_cd").Value == NewTimeZone.ToString()

           select new TimeZoneParm

           {

               TimeZoneCd = time_zone.Attribute("timezone_cd").Value,

               TimeZoneName = time_zone.Attribute("timezone_name").Value,

               OffSetHr = int.Parse(time_zone.Attribute("offset_hr").Value),

               OffSetMi = int.Parse(time_zone.Attribute("offset_mi").Value),

               DstOffSetHr = int.Parse(time_zone.Attribute("dst_offset_hr").Value),

               DstOffSetMi = int.Parse(time_zone.Attribute("dst_offset_mi").Value),

               DstEffDt = time_zone.Attribute("dst_eff_dt").Value,

               DstEndDt = time_zone.Attribute("dst_end_dt").Value,

           }).ToList<TimeZoneParm>();

        int offsethr = 0;

        int offsetmi = 0;

        int dst_offsethr = 0;

        int dst_offsetmi = 0;

        string dst_eff_key = "01110000";

        string dst_end_key = "01110000";

        if (tzp.Count > 0)

        {

            offsethr = tzp[0].OffSetHr;

            offsetmi = tzp[0].OffSetMi;

            dst_offsethr = tzp[0].DstOffSetHr;

            dst_offsetmi = tzp[0].DstOffSetMi;

            dst_eff_key = tzp[0].DstEffDt;

            dst_end_key = tzp[0].DstEndDt;

        }

        DateTime origDate = DateTime.Parse(OrigDate.ToString());

        int dst_eff_mon = int.Parse(dst_eff_key.Substring(0, 2));

        int dst_eff_wk = int.Parse(dst_eff_key.Substring(2, 1));

        int dst_eff_dw = int.Parse(dst_eff_key.Substring(3, 1));

        int dst_eff_hr = int.Parse(dst_eff_key.Substring(4, 2));

        int dst_eff_mi = int.Parse(dst_eff_key.Substring(6, 2));

        int dst_end_mon = int.Parse(dst_end_key.Substring(0, 2));

        int dst_end_wk = int.Parse(dst_end_key.Substring(2, 1));

        int dst_end_dw = int.Parse(dst_end_key.Substring(3, 1));

        int dst_end_hr = int.Parse(dst_end_key.Substring(4, 2));

        int dst_end_mi = int.Parse(dst_end_key.Substring(6, 2));

        DateTime dst_eff_dt_test = new DateTime(origDate.Year, dst_eff_mon, 1);

        dst_eff_dt_test = dst_eff_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_eff_dt_test.DayOfWeek)));

        DateTime dst_eff_dt = dst_eff_dt_test.AddDays(dst_eff_wk * 7);

        dst_eff_dt = dst_eff_dt.AddHours(dst_eff_hr);

        dst_eff_dt = dst_eff_dt.AddMinutes(dst_eff_mi);

        dst_eff_dt = dst_eff_dt.AddDays(dst_eff_dw - 1);

        if (dst_eff_dt.Month != dst_eff_mon)

        {

            dst_eff_dt = dst_eff_dt.AddDays(-7);

        }

        DateTime dst_end_dt_test = new DateTime(origDate.Year, dst_end_mon, 1);

        dst_end_dt_test = dst_end_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_end_dt_test.DayOfWeek)));

        DateTime dst_end_dt = dst_end_dt_test.AddDays(dst_end_wk * 7);

        dst_end_dt = dst_end_dt.AddHours(dst_end_hr);

        dst_end_dt = dst_end_dt.AddMinutes(dst_end_mi);

        dst_end_dt = dst_end_dt.AddDays(dst_end_dw - 1);

        if (dst_end_dt.Month != dst_end_mon)

        {

            dst_end_dt = dst_end_dt.AddDays(-7);

        }

        DateTime newDate = origDate.AddHours(offsethr).AddMinutes(offsetmi);

        DateTime newDateDst = origDate.AddHours(dst_offsethr).AddMinutes(dst_offsetmi);

        bool isDST = false;

        if (newDate >= dst_eff_dt)

        {

            if (newDate <= dst_end_dt)

            {

                isDST = true;

            }

        }

        if (isDST)

        {

            return (SqlDateTime)newDateDst;

        }

        else

        {

            return (SqlDateTime)newDate;

        }

    }

    catch (Exception ex)

    {

        return OrigDate;

    }

}

The last function is the main function that we will use to convert from one time zone to another.  The logic in this one is really simple.  It utilizes the previous two functions to convert a provided date/time value to UTC based upon a start timezone code and then converts from UTC to the desired timezone code.

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "XML_NEW_TIME")]

public static SqlDateTime fnXmlNewTime(SqlDateTime OrigDate, SqlString CurrentTimeZone, SqlString NewTimeZone)

{

    try

    {

        SqlDateTime utcDate = fnXmlUtcTime(OrigDate, CurrentTimeZone);

        SqlDateTime newDate = fnXmlTzTime(utcDate, NewTimeZone);

        return newDate;

    }

    catch (Exception ex)

    {

        return OrigDate;

    }

}

Now that the code is all done we need to compile the dll, and then put the dll in a location where the SQL Server ID has access to it.  I put the dll in the folder c:\program files (x86)\microsoft sql server\.  The process to register the assembly and register the functions is the same though there is a couple of extra commands that are needed for this.  Since the CLR functions access the file system you have to set two options; grant external access to the user or user group that will need to access this function, and then you need to set the trustworthy attribute to “ON”.  This will not harm your database or computer though they are options that are turned off by default (the recent versions of SQL they tend to have everything turned off unless they are absolutely needed whether they are safe or not).

Similar to the other articles, this process starts off with setting options needed for this code, then dropping the functions if they already exist, dropping the assembly reference if it already exists and then reregistering it, and lastly registering all the necessary functions.

USE master

GRANT EXTERNAL ACCESS ASSEMBLY TO [BUILTIN\Administrators] 

GO 

ALTER DATABASE timezone SET TRUSTWORTHY ON;

GO

USE timezone

GO

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

-- =============================================

-- Drop existing instanced of objects

-- =============================================

if exists (select * from sys.objects o where o.name ='XML_NEW_TIME')

BEGIN

    DROP FUNCTION XML_NEW_TIME

END

GO

if exists (select * from sys.objects o where o.name ='XML_TZ_TIME')

BEGIN

    DROP FUNCTION XML_TZ_TIME

END

GO

if exists (select * from sys.objects o where o.name ='XML_UTC_TIME')

BEGIN

    DROP FUNCTION XML_UTC_TIME

END

GO

-- =============================================

-- Create ASSEMBLY reference to C# dll

-- =============================================

if exists (SELECT name FROM sys.assemblies o where o.name ='ExcendSolutions.SqlUtils')

BEGIN

    DROP ASSEMBLY [ExcendSolutions.SqlUtils]

END

GO

CREATE ASSEMBLY [ExcendSolutions.SqlUtils]

FROM 'C:\Program Files (x86)\Microsoft SQL Server\ExcendSolutions.SqlUtils.dll'

WITH PERMISSION_SET=EXTERNAL_ACCESS

GO

-- =============================================

-- Create scalar function references to C# CLR functions

-- =============================================

CREATE FUNCTION XML_NEW_TIME(@ORIGDATE DATETIME,@CURRENTTIMEZONE NVARCHAR(6),@NEWTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnXmlNewTime

GO

CREATE FUNCTION XML_TZ_TIME(@ORIGDATE DATETIME,@NEWTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnXmlTzTime

GO

CREATE FUNCTION XML_UTC_TIME(@ORIGDATE DATETIME,@CURRENTTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnXmlUtcTime

GO

CREATE FUNCTION API_NEW_TIME(@ORIGDATE DATETIME,@CURRENTTIMEZONE NVARCHAR(6),@NEWTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnApiNewTime

GO