How to use CLR (dotnet classes and methods) within SQL SERVER: a simple example

step 1: create a simple class like this in csharp

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System;
using System.Data;
using System.Globalization;

public class T
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static DateTime Jalali2Gregorian(string jalaliDate)
DateTime sqlMinDate = Convert.ToDateTime(“1/1/1753”);
DateTime res = sqlMinDate;
jalaliDate = jalaliDate.Trim();
int year = Convert.ToInt32(jalaliDate.Substring(6, 4));
int month = Convert.ToInt32(jalaliDate.Substring(3, 2));
int day = Convert.ToInt32(jalaliDate.Substring(0, 2));
PersianCalendar pc = new PersianCalendar();
res = pc.ToDateTime(year, month, day, 0, 0, 0, 0);
//do nothing
return (DateTime)res;

Step 2: compile it using following command line:

csc.exe /t:library FirstUdf.cs

Step 3: introduce generated dll (FirstUdf.dll) to sql server and its functions using following sql script:

drop function Jalali2Gregorian
drop ASSEMBLY FirstUdf
CREATE ASSEMBLY FirstUdf FROM ‘d:FirstUdf.dll’
CREATE FUNCTION Jalali2Gregorian(@JalaliDate NVarchar(100)) RETURNS DateTime
AS EXTERNAL NAME FirstUdf.T.Jalali2Gregorian

step 4: prepare sql server 2005 to run it:


USE master
EXEC sp_configure ‘clr enabled’, ‘1’;
EXEC sp_configure ‘show advanced option’, ‘1’;

step 5: now, you have your function ready to run in SqlServer and can run it like this!:

select [dbo].[Jalali2Gregorian] (’31/03/1386′)

step 6: this simple function converts Persian date to its equal date in Gregorian