SQL Server: Create Random String Using CLR
Introduction
This article comes as a continuation of a similar article which shows several solutions using T-SQL queries to create a random string. This paper presents a simple code using C SHARP language, which allows to obtain the same results in a much more efficient manner and fast. This is very useful for maintenance tasks like testing (Populate large tables with random values), generate random password and so on...
Code
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Linq;
/******************************
* Version("1.1.0.0")
* FileVersion("1.1.0.0")
* WrittenBy("Ronen Ariely")
******************************/
// AssemblyVersion attribute
using System.Reflection;
[assembly: AssemblyVersion("1.1.0.0")]
[assembly: AssemblyFileVersion("1.1.0.0")]
[assembly: AssemblyDescription("Creating Random string using CLR. Written by Ronen Ariely")]
/// <summary>
/// How To compile:
/// 1. Open CMD SHELL
/// 2. move to the Dot.Net Folder
/// CD "C:\Windows\Microsoft.NET\Framework\v4.0.30319\"
/// 3. compile using csc.exe
/// csc.exe /target:library /out:"S:\Fn_RandomStringCLR_1.1.0.0.dll" "S:\Fn_RandomStringCLR_1.1.0.0.cs"
///
/// * LINQ is not supported using DOT.NET 2.0 by default,
/// There for this code fit to Dot.Net 4.
///
/// </summary>
public partial class UserDefinedFunctions
{
private static readonly Random _RandomSize = new Random();
private static readonly Random _random = new Random();
private static readonly int[] _UnicodeCharactersList =
Enumerable.Range(48, 10) // Numbers 48 - 57
.Concat(Enumerable.Range(65, 26)) // English uppercase 65 - 90
.Concat(Enumerable.Range(97, 26)) // English lowercase 97 - 122
.Concat(Enumerable.Range(1488, 27)) // Hebrew 1488 - 1514
.ToArray();
/// <summary>
///
/// </summary>
/// <param name="sMaxSize"></param>
/// <param name="IsFixed"></param>
/// <returns></returns>
[return: SqlFacet(MaxSize = -1)]
public static SqlString Fn_RandomStringCLR(
int sMaxSize,
int IsFixed
)
{
if (IsFixed == 0){
sMaxSize = _RandomSize.Next(1, sMaxSize);
}
StringBuilder builder = new StringBuilder();
char ch;
for (int i = 0; i < sMaxSize; i++)
{
ch = Convert.ToChar(
_UnicodeCharactersList[_random.Next(1, _UnicodeCharactersList.Length)]
);
builder.Append(ch);
}
return builder.ToString();
}
};
Next version is Thread safe, but since it use LOCK, we have to use UNSAFE mode:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
/******************************
* Version("2.1.0.0")
* FileVersion("2.1.0.0")
* WrittenBy("Ronen Ariely")
******************************/
// AssemblyVersion attribute
using System.Reflection;
[assembly: AssemblyVersion("2.1.0.0")]
[assembly: AssemblyFileVersion("2.1.0.0")]
[assembly: AssemblyDescription("Creating Random string using CLR. Written by Ronen Ariely")]
/// <summary>
///
/// >> This version use char array which is much faster then to use StringBuilder as in ver 1.x
/// >> Since we used "static readonly" Random parameters, which mean that they shared by all instances
/// ver 1.x did was not thread safe
/// This version use lock inorder to work with multi thread
/// But if we use LOCK then we need to use "/unsafe" or ASYMMETRIC KEY
///
/// >> How To compile:
/// -------------------
/// 1. Open CMD SHELL
///
/// 2. move to the Dot.Net Folder, depending on the version that you want to compile with:
/// cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319
/// cd C:\Windows\Microsoft.NET\Framework64\v2.0.50727
///
/// 3. compile using csc.exe
/// csc.exe /unsafe /target:library /out:"E:\CLR Random String\Fn_RandomStringCLR_2.1.0.0.dll" "E:\Fn_RandomStringCLR_2.1.0.0.cs"
///
/// CREATE ASSEMBLY Ariely_RandStrCLR_ASSEMBLY
/// FROM 'E:\Fn_RandomStringCLR_2.1.0.0.dll'
/// WITH PERMISSION_SET = UNSAFE;
/// GO
///
/// CREATE FUNCTION dbo.Ariely_RandStrCLR_Fn(
/// @MaxSize INT,
/// @IsFixed INT
/// )
/// RETURNS NVARCHAR(MAX)
/// EXTERNAL NAME Ariely_RandStrCLR_ASSEMBLY.ArielyCLR.ArielyRandStrFn;
/// GO
///
/// **********************************************************************
///
/// UNSAFe, Option 1: Using TRUSTWORTHY ON
/// ---------------------------------------
/// ALTER DATABASE [_ArielyAccessoriesDB] SET TRUSTWORTHY ON
/// GO
///
/// UNSAFe, Option 2: using ASYMMETRIC KEY
/// ---------------------------------------
/// CMD
/// cd C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\
/// C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64
/// sn -k "D:\Sample CLR\SampleCLRKey.snk"
/// * This tool is automatically installed with Visual Studio. To run the tool, use the Developer Command Prompt
/// * Make sure the SQL Server service account has access rights to the folder!
///
/// </summary>
public partial class ArielyCLR
{
private static readonly Random _RandomSize = new Random();
private static readonly Random _random = new Random();
private static readonly String _UnicodeCharactersList = @"!@#$%^&*()_-+=|\ ~`0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzאבגדקוזחטיכלמנסעפצקרשתןךףץ";
[return: SqlFacet(MaxSize = -1)]
public static SqlString ArielyRandStrFn(int sMaxSize, int IsFixed)
//public static String ArielyRandStrFn(int sMaxSize, int IsFixed)
{
if (IsFixed == 0)
{
lock (_RandomSize)
{
sMaxSize = _RandomSize.Next(1, sMaxSize);
}
}
char[] Ret = new char[sMaxSize];
for (int i = 0; i < sMaxSize; i++)
{
lock (_random)
{
Ret[i] = _UnicodeCharactersList[_random.Next(_UnicodeCharactersList.Length)];
}
}
// string(Ret): Initializes a new instance of the String class to the value indicated by an array of Unicode characters.
return (SqlString)(new string(Ret));
}
};
* If we want to use SAFE mode then we can insert the declaration of the random parameters into the method (remove the declaration "private static readonly"), and use as a seed this: (int)(DateTime.Now.Ticks % ((long)Int32.MaxValue + 1))
but in this case we do not get a full random function! since for 2 instances that the execute on the same time or in different less than (int)(DateTime.Now.Ticks % ((long)Int32.MaxValue + 1)) we will get the same value as before (since we use the same seed)
Next version is Thread safe and can be use in SAFE mode, but it is much slower (about 3 time), since we use System.Security.Cryptography.RNGCryptoServiceProvider instead of System.Random.. Yet, it is much faster then T-SQL.
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
/******************************
* Version("3.0.0.0")
* FileVersion("3.0.0.0")
* WrittenBy("Ronen Ariely")
******************************/
// AssemblyVersion attribute
using System.Reflection;
[assembly: AssemblyVersion("3.0.0.0")]
[assembly: AssemblyFileVersion("3.0.0.0")]
[assembly: AssemblyDescription("Creating Random string using CLR. Written by Ronen Ariely")]
/// <summary>
/// How To compile:
/// 1. Open CMD SHELL
///
/// 2. move to the Dot.Net Folder
/// cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319
/// cd C:\Windows\Microsoft.NET\Framework64\v2.0.50727
///
/// 3. compile using csc.exe
/// csc.exe /target:library /out:"E:\Users\RonenAri\Desktop\SQLSaturday #360\_04_Random String\CLR Random String\Fn_RandomStringCLR_3.0.0.0.dll" "E:\Users\RonenAri\Desktop\SQLSaturday #360\_04_Random String\CLR Random String\Fn_RandomStringCLR_3.0.0.0.cs"
///
/// CREATE ASSEMBLY Ariely_RandStrCLR_ASSEMBLY
/// FROM 'E:\Users\RonenAri\Desktop\SQLSaturday #360\_04_Random String\CLR Random String\Fn_RandomStringCLR_3.0.0.0.dll'
/// WITH PERMISSION_SET = SAFE;
/// GO
/// CREATE FUNCTION dbo.Ariely_RandStrCLR_Fn(
/// @MaxSize INT,
/// @IsFixed INT
/// )
/// RETURNS NVARCHAR(MAX)
/// EXTERNAL NAME Ariely_RandStrCLR_ASSEMBLY.ArielyCLR.ArielyRandStrFn;
/// GO
///
/// </summary>
public partial class ArielyCLR
{
private static readonly String _UnicodeCharactersList = @"!@#$%^&*()_-+=|\ ~`0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzאבגדקוזחטיכלמנסעפצקרשתןךףץ";
[return: SqlFacet(MaxSize = -1)]
public static SqlString ArielyRandStrFn(int sMaxSize, int IsFixed)
{
if (IsFixed == 0)
{
sMaxSize = RndInt32(_UnicodeCharactersList.Length);
}
char[] Ret = new char[sMaxSize];
for (int i = 0; i < sMaxSize; i++)
{
Ret[i] = _UnicodeCharactersList[RndInt32(_UnicodeCharactersList.Length) - 1];
}
return (SqlString)(new string(Ret));
}
public static int RndInt32(int NumSides)
{
// Create a byte array to hold the random value.
// max Byte value 255, I use 4 to get Int16
byte[] randomNumber = new byte[4];
// Create a new instance of the RNGCryptoServiceProvider.
System.Security.Cryptography.RNGCryptoServiceProvider Gen = new System.Security.Cryptography.RNGCryptoServiceProvider();
// Fill the array with a random value.
Gen.GetBytes(randomNumber);
// Convert the byte to an integer value to make the modulus operation easier.
//int rand = Convert.ToInt32(randomNumber[0]);
int rand = Math.Abs(BitConverter.ToInt32(randomNumber, 0));
// Return the random number mod the number
// of sides. The possible values are zero-
// based, so we add one.
return rand % NumSides + 1;
}
};
Conclusions
If you can use only single CPU or for any reason you sure to work only with one thread, then you can choose version 1.0.0.0, but if you want to be thread-safe and you can use UNSAFE mode, then you probably want to use version 2.1.0.0. If you need safe thread and only SAFE mode, then you can use version 3.0.0.0. This is probably the order of choosing the best solution that fit your needs (there are more versions with different advantages and disadvantages, which posted on Ronen Ariely's blogs).
Resources
This article is based on Ronen Ariely's Blog at:
http://ariely.info/Blog/tabid/83/EntryId/134/SQL-Random-String-using-CLR.aspx
* In addition to the code here, the blog includes older version fit DOT.NET 2.0 (with out using LINQ)
Random String - several T-SQL solutions
http://social.technet.microsoft.com/wiki/contents/articles/21196.t-sql-random-string.aspx
Deploying CLR Database Objects
http://technet.microsoft.com/en-us/library/ms345099.aspx