Using the CLR to create a Random Number generator

Following on from my last post about a random number generator in SQL, another method of providing a random number generator would be to add the function using C# and the Common Language Runtime, and fortunately the tools to do this are now installed with SQL Server.

We could do this using Visual Studio but for a small function like this we can do it all with notepad and the command line compiler installed with .net.

The first thing to do is check that the compiler is accessible and that the path to the csc.exe file is included in your path statement. Open a CMD window and at the prompt type CSC. If you see a message like Microsoft visual C# compiler version x.x.xxxxx.xxxxx then all is well. If you get an error message then you need to check you path statement.

The compiler will be installed in the c:\windows\microsoft.net\framework\(version) folder where version is the version of dot net that you have installed. If you check in the framework folder you may have multiple versions, and if this is the case go for the latest version on the machine.

Next add the path to the folder into the PATH variable. This is usually located in the Environment Variables option under system properties and the exact method of accessing it will vary depending on the version of the operating system you are running.

Once you have updated your path statement go back to your CMD window and try entering the CSC command again, if all is well you will see the Microsoft visual C# compiler version x.x.xxxxx.xxxxx message, if not check the previous step and retry.

Now that the compiler is working, we can now write the code for the function we wish to create. Open a blank file in Notepad.exe and enter the following:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Security.Cryptography;
 
public class RandomNumberFn
{
    [SqlFunction(Name="CryptoRnd")]
    public static SqlByte CryptoRnd()
    {
        SqlByte value= new SqlByte(255);
        RNGCryptoServiceProvider rngCsp = new RNGCryptoServiceProvider();
        byte[] randomNumber = new byte[1] ; 
        while(value>250)
        {
            rngCsp.GetBytes(randomNumber);
            value = randomNumber[0];
        }
        return value;
    }
}

If you are familiar with C# this code should be self-explanatory, and uses the RNGCryptoServiceProvider() which is the C# implementation of the CRYPT_GEN_RANDOM() number generator we looked at in TSQL in the last entry.

Save the file somewhere simple as rnd.cs, in my case K:\ as because I was using the command line version of the compiler I wanted something easy to type in.

In the CMD window enter the command csc /target:library K:\rnd.cs

After a second or two it should return you to the command prompt with no errors.

The /target:library switch on the compiler generates a .dll file rather than a .exe. The .dll file will be created in the same directory as the source file and appropriate permissions will be required to ensure the file can be created.

Next we need to add the function into SQL server, and we can do this by opening up the SQL Management Studio and creating a new query window.

In the query window enter and execute

CREATE ASSEMBLY CryptoRnd from ‘k:\rnd.dll’ with PERMISSION_SET=SAFE

If you have an error about the CLR Integration not being enabled, run the following script:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

This script will only need to be done once, and you will need the ALTER SETTINGS permission for your SQL Server user.

Next enter this query into the query manager and execute it

Create function CLR_RandomNumber() returns tinyint

as external name CryptoRnd.RandomNumberFn.CryptoRnd

This will actually create the function from the CLR code. The External name is in the form <assembly>.<class>.<functionname>

You can now test your new function by executing this query

Select dbo.CLR_RandomNumber()

Or it can be used in place of the random functions previously as

Select *,dbo.CLR_RandomNumber() from mytable.