Related Service

.Net Development

Know more about our .Net Development Services

Learn More

CLR Function in SQL Server & Time Zone in SQL Server using CLR function

Published: Oct 29, 2020

Businesses are 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.

SQL Server also supports CLR functions, which can be written in any Microsoft .Net programming language like C# or VB.Net.

CLR User-Defined function

Following are steps to create a new 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#.

    Add SQL CLR C User Defined Function
    Image 1 Add SQL CLR C# User Defined Function
  3. It will then open a New File for writing function in C#.
    Write SQL Function in C#
    Write SQL Function in C#

    We can write C# code here. We can also add third-party dll and use it in C# code.

  4. After writing the function, from the solution explorer, right-click on the project. Select the Publish option.
    Publish Database
    Publish Database

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

  5. This Function will create the database and is easily seen in the Management Studio.

    Function in Management Studio
    Function in Management Studio
  6. The SQL Function has no difference and is similar to SQL statement that users execute normally

    Function Execution
    Function Execution

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
 
{
 
/// <summary>
 
/// Check current time is in Daylight Saving time
 
/// <summary>
 
/// <returns>If current time zone is daylight saving, then return true else false<returns>
 
[Microsoft.SqlServer.Server.SqlFunction]
 
public static SqlBoolean IsDayLightSavingTime()
 
{
 
return TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now);
 
}
 
/// <summary>
 
/// Get Current Time Zone Name
 
/// <summary>
 
/// <returns>Time Zone Name<returns>
 
[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;
 
}
 
};

TimeZone

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 CLR, 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;

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 functions, using time zone functions to automatically generate time. As explained in the above example of 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.

Comments

  • Leave a message...

Related Articles

Things you must know before choosing Power BI

Nov 26, 2020

What are ORMs and How does it work?

Nov 24, 2020

Introduction of Azure DevOps Pipelines

Nov 19, 2020