CLR Function in SQL Server & Time Zone in SQL Server Using CLR Function

Businesses logic has been running behind applications, tools, and software that makes their lives easier by reducing runtime, accelerating productivity hours, and decreasing man-hours. It is imperative for businesses to maintain large databases yet make it simpler to fetch and utilize the database. In SQL Server, UDF (User-defined function) is a programming language that accepts multiple parameters and utilizes the same approved parameters to deliver a specific outcome. The .NET with SQL CLR Server allows any of the .NET languages to be used to create database objects such as CLR stored procedures, user-defined SQL functions, triggers, aggregate functions, and user-defined types.

SQL Server CLR also enables you to enhance the SQL Server database engine’s capabilities Microsoft .Net  framework’s programming language like C# or VB.Net.

1. User-Defined SQL CLR Function

Following are steps to create a new Common Language Runtime -CLR User Designed function.

1. To start with, Create a new SQL-Server Database Project in Visual Studio.
2. Right click on the Project button and select Add->New Item->SQL CLR C# to create a new Scalar value SQL CLR function.

We can write C# code here, and furthermore we can add required DLLs reference. and deploy. We can also add third-party DLL and deploy third party DLL in the C# code.

Now select the target database project, then click on Publish or Generate Script option.

2. Time Zone and Daylight-Saving Time

SYSDATETIMEOFFSET() Function returns current local date, time & time zone offset value. Time zone offset values is represented as [+|-] hh:mm (E.g. for India, Its +05:30). However, from this value, the time zone name is not identified. For example, the time zones of India and Sri Lanka have the same offset value. Similarly, using this function, one cannot identify that this is Daylight Saving Time (DST) or not.

To get these values, the following CLR functions can be helpful.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
///
/// Check current time is in Daylight Saving time
///
/// If current time zone is daylight saving, then return true else false
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean IsDayLightSavingTime()
{
return TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now);
}
///
/// Get Current Time Zone Name
///
/// Time Zone Name
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString TimeZoneName()
{
//If current time is daylight saving , then return day light name , else it will return Standard Name of time zone.
if (TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now))
return TimeZone.CurrentTimeZone.DaylightName;
else
return TimeZone.CurrentTimeZone.StandardName;
}
};

Notes: CLR function might not be available in SQL Server in default options.

Software development companies can enable change by executing the following scripts. However, this command is not recommended. To allow the use of CLR objects, the SQL Server CLR integration should be enabled (Note: By default, SQL Server CLR integration is disabled.) and developers should use the Asymmetric key or certificate.

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;

RECONFIGURE;

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE;

3. Conclusion

With this blog, Now it would be easy to fetch and analyze data using SQL in Visual Studio. A comprehensive understanding of how queries work, how to use user-defined SQL CLR functions, using time zone functions to automatically generate time. As explained in the above example of the CLR function, users can now create user-defined functions in C# or VB.Net and take advantage of .net development services and third-party DLLs.