Results 1 to 6 of 6

Thread: Creating A CLR Function (SQL SERVER 2005)

  1. #1

    Thread Starter
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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.

    Name:  CreateCLRProject.png
Views: 9041
Size:  30.5 KB


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

    Fig 2.

    Name:  AddCLRFunction.png
Views: 8706
Size:  30.0 KB

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

    vb Code:
    1. using System;
    2. using System.Data;
    3. using System.Data.SqlClient;
    4. using System.Data.SqlTypes;
    5. using Microsoft.SqlServer.Server;
    6.  
    7. public partial class UserDefinedFunctions
    8. {
    9.     [Microsoft.SqlServer.Server.SqlFunction]
    10.     public static SqlString Function1()
    11.     {
    12.         // Put your code here
    13.         return new SqlString("Hello");
    14.     }
    15. };

    Above the function declaration you will notice this line -
    vb Code:
    1. [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:
    1. [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:
    1. using System;
    2. using System.Data;
    3. using System.Data.SqlClient;
    4. using System.Data.SqlTypes;
    5. using Microsoft.SqlServer.Server;
    6.  
    7. public partial class UserDefinedFunctions
    8. {
    9. [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    10.    
    11. //Code Goes Here
    12.  
    13. };

    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:
    1. public partial class UserDefinedFunctions
    2. {
    3. [Microsoft.SqlServer.Server.SqlFunction] (DataAccess = DataAccessKind.Read)]
    4.    
    5. public static SqlString convertNumericToWords(SqlDouble numb)
    6.         {
    7.             String num = numb.ToString();
    8.             return (changeToWords(num, false));
    9.         }
    10. };

    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.
    Last edited by NeedSomeAnswers; Mar 2nd, 2010 at 04:18 AM.

  2. #2

    Thread Starter
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Creating A CLR Function (SQL SERVER 2005)

    Your final code should now look like this –

    vb Code:
    1. using System;
    2. using System.Collections.Generic;
    3. using System.Data;
    4. using System.Data.SqlClient;
    5. using System.Data.SqlTypes;
    6. using Microsoft.SqlServer.Server;
    7. using System.Text;
    8.  
    9. public partial class UserDefinedFunctions
    10. {
    11.     [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    12.     public static String convertNumericToWords(double numb)
    13.     {
    14.  
    15.         String num = numb.ToString();
    16.  
    17.         return changeToWords(num, false);
    18.  
    19.     }
    20.  
    21.     public static String convertCurrencyToWords(double numb)
    22.     {
    23.  
    24.         return changeToWords(numb.ToString(), true);
    25.  
    26.     }
    27.  
    28.     private static String changeToWords(String numb, bool isCurrency)
    29.     {
    30.  
    31.         String val = "", wholeNo = numb, points = "", andStr = "", pointStr = "";
    32.  
    33.         String endStr = (isCurrency) ? ("Only") : ("");
    34.  
    35.         try
    36.         {
    37.  
    38.             int decimalPlace = numb.IndexOf(".");
    39.  
    40.             if (decimalPlace > 0)
    41.             {
    42.  
    43.                 wholeNo = numb.Substring(0, decimalPlace);
    44.  
    45.                 points = numb.Substring(decimalPlace + 1);
    46.  
    47.                 if (Convert.ToInt32(points) > 0)
    48.                 {
    49.  
    50.                     andStr = (isCurrency) ? ("Pounds and") : ("point");// just to separate whole numbers from points/pence
    51.  
    52.                     endStr = (isCurrency) ? ("Pence " + endStr) : ("");
    53.  
    54.                     if (isCurrency == true)
    55.                     {
    56.                         pointStr = translatePence(points);
    57.                     }
    58.                     else
    59.                     {
    60.                         pointStr = translatePoints(points);
    61.                     }
    62.  
    63.                 }
    64.  
    65.             }
    66.  
    67.             val = String.Format("{0} {1}{2} {3}", translateWholeNumber(wholeNo).Trim(), andStr, pointStr, endStr);
    68.  
    69.         }
    70.  
    71.         catch
    72.         {
    73.  
    74.             ;
    75.         }
    76.  
    77.         return val;
    78.  
    79.     }
    80.  
    81.     private static String translateWholeNumber(String number)
    82.     {
    83.  
    84.         string word = "";
    85.  
    86.         try
    87.         {
    88.  
    89.             bool beginsZero = false;//tests for 0XX
    90.  
    91.             bool isDone = false;//test if already translated
    92.  
    93.             double dblAmt = (Convert.ToDouble(number));
    94.  
    95.             //if ((dblAmt > 0) && number.StartsWith("0"))
    96.  
    97.             if (dblAmt > 0)
    98.             {//test for zero or digit zero in a nuemric
    99.  
    100.                 beginsZero = number.StartsWith("0");
    101.  
    102.                 int numDigits = number.Length;
    103.  
    104.                 int pos = 0;//store digit grouping
    105.  
    106.                 String place = "";//digit grouping name:hundres,thousand,etc...
    107.  
    108.                 switch (numDigits)
    109.                 {
    110.  
    111.                     case 1://ones' range
    112.  
    113.                         word = ones(number);
    114.  
    115.                         isDone = true;
    116.  
    117.                         break;
    118.  
    119.                     case 2://tens' range
    120.  
    121.                         word = tens(number);
    122.  
    123.                         isDone = true;
    124.  
    125.                         break;
    126.  
    127.                     case 3://hundreds' range
    128.  
    129.                         pos = (numDigits % 3) + 1;
    130.  
    131.                         place = " Hundred ";
    132.  
    133.                         break;
    134.  
    135.                     case 4://thousands' range
    136.  
    137.                     case 5:
    138.  
    139.                     case 6:
    140.  
    141.                         pos = (numDigits % 4) + 1;
    142.  
    143.                         place = " Thousand ";
    144.  
    145.                         break;
    146.  
    147.                     case 7://millions' range
    148.  
    149.                     case 8:
    150.  
    151.                     case 9:
    152.  
    153.                         pos = (numDigits % 7) + 1;
    154.  
    155.                         place = " Million ";
    156.  
    157.                         break;
    158.  
    159.                     case 10://Billions's range
    160.  
    161.                         pos = (numDigits % 10) + 1;
    162.  
    163.                         place = " Billion ";
    164.  
    165.                         break;
    166.  
    167.                     //add extra case options for anything above Billion...
    168.  
    169.                     default:
    170.  
    171.                         isDone = true;
    172.  
    173.                         break;
    174.  
    175.                 }
    176.  
    177.                 if (!isDone)
    178.                 {//if transalation is not done, continue...(Recursion comes in now!!)
    179.  
    180.                     word = translateWholeNumber(number.Substring(0, pos)) + place + translateWholeNumber(number.Substring(pos));
    181.  
    182.                     //check for trailing zeros
    183.  
    184.                     if (beginsZero) word = " and " + word.Trim();
    185.  
    186.                 }
    187.  
    188.                 //ignore digit grouping names
    189.  
    190.                 if (word.Trim().Equals(place.Trim())) word = "";
    191.  
    192.             }
    193.  
    194.         }
    195.  
    196.         catch { ;}
    197.  
    198.         return word.Trim();
    199.  
    200.     }
    201.  
    202.     private static String tens(String digit)
    203.     {
    204.  
    205.         int digt = Convert.ToInt32(digit);
    206.  
    207.         String name = null;
    208.  
    209.         switch (digt)
    210.         {
    211.  
    212.             case 10:
    213.  
    214.                 name = "Ten";
    215.  
    216.                 break;
    217.  
    218.             case 11:
    219.  
    220.                 name = "Eleven";
    221.  
    222.                 break;
    223.  
    224.             case 12:
    225.  
    226.                 name = "Twelve";
    227.  
    228.                 break;
    229.  
    230.             case 13:
    231.  
    232.                 name = "Thirteen";
    233.  
    234.                 break;
    235.  
    236.             case 14:
    237.  
    238.                 name = "Fourteen";
    239.  
    240.                 break;
    241.  
    242.             case 15:
    243.  
    244.                 name = "Fifteen";
    245.  
    246.                 break;
    247.  
    248.             case 16:
    249.  
    250.                 name = "Sixteen";
    251.  
    252.                 break;
    253.  
    254.             case 17:
    255.  
    256.                 name = "Seventeen";
    257.  
    258.                 break;
    259.  
    260.             case 18:
    261.  
    262.                 name = "Eighteen";
    263.  
    264.                 break;
    265.  
    266.             case 19:
    267.  
    268.                 name = "Nineteen";
    269.  
    270.                 break;
    271.  
    272.             case 20:
    273.  
    274.                 name = "Twenty";
    275.  
    276.                 break;
    277.  
    278.             case 30:
    279.  
    280.                 name = "Thirty";
    281.  
    282.                 break;
    283.  
    284.             case 40:
    285.  
    286.                 name = "Fourty";
    287.  
    288.                 break;
    289.  
    290.             case 50:
    291.  
    292.                 name = "Fifty";
    293.  
    294.                 break;
    295.  
    296.             case 60:
    297.  
    298.                 name = "Sixty";
    299.  
    300.                 break;
    301.  
    302.             case 70:
    303.  
    304.                 name = "Seventy";
    305.  
    306.                 break;
    307.  
    308.             case 80:
    309.  
    310.                 name = "Eighty";
    311.  
    312.                 break;
    313.  
    314.             case 90:
    315.  
    316.                 name = "Ninety";
    317.  
    318.                 break;
    319.  
    320.             default:
    321.  
    322.                 if (digt > 0)
    323.                 {
    324.  
    325.                     name = tens(digit.Substring(0, 1) + "0") + " " + ones(digit.Substring(1));
    326.  
    327.                 }
    328.  
    329.                 break;
    330.  
    331.         }
    332.  
    333.         return name;
    334.  
    335.     }
    336.  
    337.     private static String ones(String digit)
    338.     {
    339.  
    340.         int digt = Convert.ToInt32(digit);
    341.  
    342.         String name = "";
    343.  
    344.         switch (digt)
    345.         {
    346.  
    347.             case 1:
    348.  
    349.                 name = "One";
    350.  
    351.                 break;
    352.  
    353.             case 2:
    354.  
    355.                 name = "Two";
    356.  
    357.                 break;
    358.  
    359.             case 3:
    360.  
    361.                 name = "Three";
    362.  
    363.                 break;
    364.  
    365.             case 4:
    366.  
    367.                 name = "Four";
    368.  
    369.                 break;
    370.  
    371.             case 5:
    372.  
    373.                 name = "Five";
    374.  
    375.                 break;
    376.  
    377.             case 6:
    378.  
    379.                 name = "Six";
    380.  
    381.                 break;
    382.  
    383.             case 7:
    384.  
    385.                 name = "Seven";
    386.  
    387.                 break;
    388.  
    389.             case 8:
    390.  
    391.                 name = "Eight";
    392.  
    393.                 break;
    394.  
    395.             case 9:
    396.  
    397.                 name = "Nine";
    398.  
    399.                 break;
    400.  
    401.         }
    402.  
    403.         return name;
    404.  
    405.     }
    406.  
    407.     private static String translatePence(String Pence)
    408.     {
    409.         String cts = "", digit = "", engOne = "";
    410.         int i = 0;
    411.         char[] delimiterChars = { '.' };
    412.  
    413.             digit = Pence[i].ToString();
    414.  
    415.             if (digit.Equals("0"))
    416.             {
    417.                 engOne = "Zero";
    418.             }
    419.             else
    420.             {
    421.                 if (Pence.Length == 1)
    422.                 {
    423.                     engOne = ones(digit);
    424.                 }
    425.                 else if (Pence.Length == 2)
    426.                 {
    427.                     engOne = tens(Pence);
    428.                 }
    429.                 else
    430.                 {
    431.                     Pence = "0." + Pence;
    432.                     digit = Convert.ToString(Math.Round(Convert.ToDouble(Pence), 2));
    433.                     String[] words = digit.Split(delimiterChars);
    434.                     engOne = tens(words[1]);
    435.                 }
    436.             }
    437.             cts += " " + engOne;
    438.             return cts;
    439.         }
    440.  
    441.     private static String translatePoints(String Points)
    442.     {
    443.  
    444.         String cts = "", digit = "", engOne = "";
    445.  
    446.         for (int i = 0; i < Points.Length; i++)
    447.         {
    448.  
    449.             digit = Points[i].ToString();
    450.  
    451.             if (digit.Equals("0"))
    452.             {
    453.  
    454.                 engOne = "Zero";
    455.  
    456.             }
    457.  
    458.             else
    459.             {
    460.  
    461.                 engOne = ones(digit);
    462.  
    463.             }
    464.  
    465.             cts += " " + engOne;
    466.  
    467.         }
    468.  
    469.         return cts;
    470.     }
    471. }

    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
    Last edited by NeedSomeAnswers; Feb 19th, 2009 at 08:41 AM.

  3. #3

    Thread Starter
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Creating A CLR Function (SQL SERVER 2005)

    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:
    1. EXEC sp_configure 'show advanced options' , '1';    -- Enable Advanced Options,                                 this allows you to get to CLR option
    2. go
    3. reconfigure;
    4. EXEC sp_configure 'clr enabled' , '1'           -- Enable CLR in SQL SERVER
    5. go
    6. reconfigure;
    7. go
    8. EXEC sp_configure 'show advanced options' , '0';    -- Disable Advanced Options
    9. go

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

    SQL Code:
    1. sp_dbcmptlevel 'DatabaseName', 90       --Change to your database name
    2. 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:
    1. IF EXISTS(SELECT * FROM sys.objects WHERE type = 'FS' AND schema_id = SCHEMA_ID('dbo')
    2.    AND name = 'convertNumericToWords')
    3. DROP FUNCTION dbo.convertNumericToWords
    4. GO
    5. IF EXISTS(SELECT * FROM sys.objects WHERE type = 'FS' AND schema_id = SCHEMA_ID('dbo')
    6.    AND name = 'convertCurrencyToWords')
    7. DROP FUNCTION dbo.convertCurrencyToWords
    8. GO
    9. IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = 'NumberFunctions')
    10. DROP ASSEMBLY NumberFunctions
    11. GO
    12.  
    13. CREATE ASSEMBLY NumberFunctions
    14. FROM 'C:\Documents and Settings\#Username#\My Documents\Visual Studio 2008\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\CLRFunction1.dll'
    15. GO
    16. CREATE FUNCTION convertNumericToWords(@Num float) RETURNS nVarchar(512)
    17. AS EXTERNAL NAME NumberFunctions.UserDefinedFunctions.convertNumericToWords;
    18. GO
    19. CREATE FUNCTION convertCurrencyToWords(@Num float) RETURNS nVarchar(512)
    20. AS EXTERNAL NAME NumberFunctions.UserDefinedFunctions.convertCurrencyToWords;
    21. 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:
    1. SELECT dbo.convertNumericToWords(Field) FROM Table
    2.  
    3. SELECT dbo.convertCurrencyToWords(Field) FROM Table

    Example


    Name:  CLR_Results2.png
Views: 7626
Size:  12.4 KB
    Last edited by NeedSomeAnswers; Feb 19th, 2009 at 08:40 AM.

  4. #4

    Thread Starter
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Creating A CLR Function (SQL SERVER 2005)

    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.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Creating A CLR Function (SQL SERVER 2005)

    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??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Creating A CLR Function (SQL SERVER 2005)

    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.

    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>
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width