Creating A CLR Function (SQL SERVER 2005)

Title - Creating a CLR Function – (Converting Numbers to Words)

Description

CLR or Common Language Runtime functions allow you as a developer to harness the power of .net inside SQL Server, and in the case of functions, actually in-line with your SQL.
This means you can do things like expose .Nets regular expressions and string manipulation ability in your function.
In this example I will take a C# Class that I have found on the internet, which converts Numbers to their Word representation, and alter it so that it fits inside the CLR Function, I will then show how to register and run the function within SQL Server.

Requirements

SQL SERVER 2005 & VS Studio 2005 or above


Tutorial

First you need to create a new project inside visual studio. Go to New | Project >> Database | SQL Server Project.

Fig1.

Attachment 69068


Once you have named & created your project right click on the project and select Add | User-Defined Function.

Fig 2.

Attachment 69069

This will add a class with a basic function shell to the project for you, and it will look something like this

vb Code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Function1()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Above the function declaration you will notice this line -

vb Code:

[Microsoft.SqlServer.Server.SqlFunction]

If you wish to read from the SQL Server tables inside your CLR function you will need to alter it to read;

vb Code:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

Now remove the current function outline so you have a blank class awaiting the import of code.

vb Code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    
//Code Goes Here
 
};

At this point you could essentially add in any supported code into your project that takes in a value and returns something.
I was looking for some specific code to convert Numbers to Words, something that is very useful in Cheque processing for instance. I decided to use the code from the following site -

http://www.codeproject.com/KB/cs/codesamples.aspx

You will then need to import the code, and changed all the subs/functions to static, as they have to be static in a CLR Function and because logically they should be static anyway as a CLR Function is not object specific and returns a value based upon the Input, not upon an object.
e.g.

vb Code:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction] (DataAccess = DataAccessKind.Read)]
    
public static SqlString convertNumericToWords(SqlDouble numb)
        {
            String num = numb.ToString();
            return (changeToWords(num, false));
        }
};

For the Public functions that are going to be registered with SQL Server you will also need to change the data types of the function itself and its input variables. The variables need to match up by Type with what it is being passed by SQL Server.

There is a great link which tells you what datatypes map to which – Here

The Datatypes SqlString & SqlDouble along with others all become available because you are in a .Net SQL Server Project, and their SQL Server equivalents are nVarchar and Float.

Your final code should now look like this –

vb Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static String convertNumericToWords(double numb)
    {
 
        String num = numb.ToString();
 
        return changeToWords(num, false);
 
    }
 
    public static String convertCurrencyToWords(double numb)
    {
 
        return changeToWords(numb.ToString(), true);
 
    }
 
    private static String changeToWords(String numb, bool isCurrency)
    {
 
        String val = "", wholeNo = numb, points = "", andStr = "", pointStr = "";
 
        String endStr = (isCurrency) ? ("Only") : ("");
 
        try
        {
 
            int decimalPlace = numb.IndexOf(".");
 
            if (decimalPlace > 0)
            {
 
                wholeNo = numb.Substring(0, decimalPlace);
 
                points = numb.Substring(decimalPlace + 1);
 
                if (Convert.ToInt32(points) > 0)
                {
 
                    andStr = (isCurrency) ? ("Pounds and") : ("point");// just to separate whole numbers from points/pence
 
                    endStr = (isCurrency) ? ("Pence " + endStr) : ("");
 
                    if (isCurrency == true)
                    {
                        pointStr = translatePence(points);
                    }
                    else
                    {
                        pointStr = translatePoints(points);
                    }
 
                }
 
            }
 
            val = String.Format("{0} {1}{2} {3}", translateWholeNumber(wholeNo).Trim(), andStr, pointStr, endStr);
 
        }
 
        catch
        {
 
            ;
        }
 
        return val;
 
    }
 
    private static String translateWholeNumber(String number)
    {
 
        string word = "";
 
        try
        {
 
            bool beginsZero = false;//tests for 0XX
 
            bool isDone = false;//test if already translated
 
            double dblAmt = (Convert.ToDouble(number));
 
            //if ((dblAmt > 0) && number.StartsWith("0"))
 
            if (dblAmt > 0)
            {//test for zero or digit zero in a nuemric
 
                beginsZero = number.StartsWith("0");
 
                int numDigits = number.Length;
 
                int pos = 0;//store digit grouping
 
                String place = "";//digit grouping name:hundres,thousand,etc...
 
                switch (numDigits)
                {
 
                    case 1://ones' range
 
                        word = ones(number);
 
                        isDone = true;
 
                        break;
 
                    case 2://tens' range
 
                        word = tens(number);
 
                        isDone = true;
 
                        break;
 
                    case 3://hundreds' range
 
                        pos = (numDigits % 3) + 1;
 
                        place = " Hundred ";
 
                        break;
 
                    case 4://thousands' range
 
                    case 5:
 
                    case 6:
 
                        pos = (numDigits % 4) + 1;
 
                        place = " Thousand ";
 
                        break;
 
                    case 7://millions' range
 
                    case 8:
 
                    case 9:
 
                        pos = (numDigits % 7) + 1;
 
                        place = " Million ";
 
                        break;
 
                    case 10://Billions's range
 
                        pos = (numDigits % 10) + 1;
 
                        place = " Billion ";
 
                        break;
 
                    //add extra case options for anything above Billion...
 
                    default:
 
                        isDone = true;
 
                        break;
 
                }
 
                if (!isDone)
                {//if transalation is not done, continue...(Recursion comes in now!!)
 
                    word = translateWholeNumber(number.Substring(0, pos)) + place + translateWholeNumber(number.Substring(pos));
 
                    //check for trailing zeros
 
                    if (beginsZero) word = " and " + word.Trim();
 
                }
 
                //ignore digit grouping names
 
                if (word.Trim().Equals(place.Trim())) word = "";
 
            }
 
        }
 
        catch { ;}
 
        return word.Trim();
 
    }
 
    private static String tens(String digit)
    {
 
        int digt = Convert.ToInt32(digit);
 
        String name = null;
 
        switch (digt)
        {
 
            case 10:
 
                name = "Ten";
 
                break;
 
            case 11:
 
                name = "Eleven";
 
                break;
 
            case 12:
 
                name = "Twelve";
 
                break;
 
            case 13:
 
                name = "Thirteen";
 
                break;
 
            case 14:
 
                name = "Fourteen";
 
                break;
 
            case 15:
 
                name = "Fifteen";
 
                break;
 
            case 16:
 
                name = "Sixteen";
 
                break;
 
            case 17:
 
                name = "Seventeen";
 
                break;
 
            case 18:
 
                name = "Eighteen";
 
                break;
 
            case 19:
 
                name = "Nineteen";
 
                break;
 
            case 20:
 
                name = "Twenty";
 
                break;
 
            case 30:
 
                name = "Thirty";
 
                break;
 
            case 40:
 
                name = "Fourty";
 
                break;
 
            case 50:
 
                name = "Fifty";
 
                break;
 
            case 60:
 
                name = "Sixty";
 
                break;
 
            case 70:
 
                name = "Seventy";
 
                break;
 
            case 80:
 
                name = "Eighty";
 
                break;
 
            case 90:
 
                name = "Ninety";
 
                break;
 
            default:
 
                if (digt > 0)
                {
 
                    name = tens(digit.Substring(0, 1) + "0") + " " + ones(digit.Substring(1));
 
                }
 
                break;
 
        }
 
        return name;
 
    }
 
    private static String ones(String digit)
    {
 
        int digt = Convert.ToInt32(digit);
 
        String name = "";
 
        switch (digt)
        {
 
            case 1:
 
                name = "One";
 
                break;
 
            case 2:
 
                name = "Two";
 
                break;
 
            case 3:
 
                name = "Three";
 
                break;
 
            case 4:
 
                name = "Four";
 
                break;
 
            case 5:
 
                name = "Five";
 
                break;
 
            case 6:
 
                name = "Six";
 
                break;
 
            case 7:
 
                name = "Seven";
 
                break;
 
            case 8:
 
                name = "Eight";
 
                break;
 
            case 9:
 
                name = "Nine";
 
                break;
 
        }
 
        return name;
 
    }
 
    private static String translatePence(String Pence)
    {
        String cts = "", digit = "", engOne = "";
        int i = 0;
        char[] delimiterChars = { '.' };
 
            digit = Pence[i].ToString();
 
            if (digit.Equals("0"))
            {
                engOne = "Zero";
            }
            else
            {
                if (Pence.Length == 1)
                {
                    engOne = ones(digit);
                }
                else if (Pence.Length == 2)
                {
                    engOne = tens(Pence);
                }
                else
                {
                    Pence = "0." + Pence;
                    digit = Convert.ToString(Math.Round(Convert.ToDouble(Pence), 2));
                    String[] words = digit.Split(delimiterChars);
                    engOne = tens(words[1]);
                }
            }
            cts += " " + engOne;
            return cts;
        }
 
    private static String translatePoints(String Points)
    {
 
        String cts = "", digit = "", engOne = "";
 
        for (int i = 0; i < Points.Length; i++)
        {
 
            digit = Points[i].ToString();
 
            if (digit.Equals("0"))
            {
 
                engOne = "Zero";
 
            }
 
            else
            {
 
                engOne = ones(digit);
 
            }
 
            cts += " " + engOne;
 
        }
 
        return cts;
    }
}

Build your project, navigate to the created .dll and grab the path.

Edited - the convert to Currency did not deal with decimal places very well, this has now been fixed to work properly for 2 decimal places. It will round any decimal number over 2 digits.
e.g 123.456 = 123.46

Now we have finished with the .Net side of things and we will progress to SQL Server, open a new query window and enter the following commands;

SQL Code:

EXEC sp_configure 'show advanced options' , '1';    -- Enable Advanced Options,                                 this allows you to get to CLR option
go
reconfigure;
EXEC sp_configure 'clr enabled' , '1'           -- Enable CLR in SQL SERVER
go
reconfigure;
go
EXEC sp_configure 'show advanced options' , '0';    -- Disable Advanced Options
go

The Above commands enable CLR functions and Stored Procedures in SQL Server

SQL Code:

sp_dbcmptlevel 'DatabaseName', 90       --Change to your database name
go

This line makes sure that the database you are going to register the Function with has its compatibility level set to SQL Server 2005. This will in particular be an issue with a migrated database where a 2000 database has been restored onto a 2005 server.

Finally you need to create your Assembly, which makes your .dll functions available to SQL Server and create the SQL Side of the function.

SQL Code:

IF EXISTS(SELECT * FROM sys.objects WHERE type = 'FS' AND schema_id = SCHEMA_ID('dbo')
   AND name = 'convertNumericToWords')
DROP FUNCTION dbo.convertNumericToWords
GO
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'FS' AND schema_id = SCHEMA_ID('dbo')
   AND name = 'convertCurrencyToWords')
DROP FUNCTION dbo.convertCurrencyToWords
GO
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = 'NumberFunctions')
DROP ASSEMBLY NumberFunctions
GO
 
CREATE ASSEMBLY NumberFunctions 
FROM 'C:\Documents and Settings\#Username#\My Documents\Visual Studio 2008\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\CLRFunction1.dll'
GO
CREATE FUNCTION convertNumericToWords(@Num float) RETURNS nVarchar(512)
AS EXTERNAL NAME NumberFunctions.UserDefinedFunctions.convertNumericToWords;
GO
CREATE FUNCTION convertCurrencyToWords(@Num float) RETURNS nVarchar(512)
AS EXTERNAL NAME NumberFunctions.UserDefinedFunctions.convertCurrencyToWords;
GO

Make sure you change the name and path in the Create Assembly code above to the name & path of your dll, and run the script.

You should now be able to use your new CLR Functions like this;

VB Code:

SELECT dbo.convertNumericToWords(Field) FROM Table
 
SELECT dbo.convertCurrencyToWords(Field) FROM Table

Example


Attachment 69206

Please feel free to leave any comments you have on the Tutorial, hopefully i will be able to use them to improve any future tutorials.

This is a really nice example - thanks for posting it.

Why the use of double? Can you work directly with the MS SQL "money" data type??

Thanks szlamany,

I remember really enjoying doing this at the time, but as i have moved up in to team leading/management i have had less and less time to do this sort of thing.

Quote:

---

Why the use of double? Can you work directly with the MS SQL "money" data type??

---

To be honest i cant remember i did this example around 5 years ago, i don't see any reason why you cant use the Money datatype. In fact i just took a look in the data Types link at the bottom of my first post above and it says -

SQL Server Data Type - money maps to SqlMoney Decimal, Nullable<Decimal>