en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
נוב25

Written by: ronen ariely
25/11/2013 18:56 RssIcon

SQL Random String using CLR

Using LINQ [available on dot.Net 4 by default]

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")]
[assembly: AssemblyCompany("Ariely Ronen")]
 
///
/// 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:\ArielyDesktop\SQL\CLR\RandomString\Fn_RandomStringCLR_1.1.0.0.dll" "S:\ArielyDesktop\SQL\CLR\RandomString\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.
///
///
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();
 
    ///
    ///
    ///
    ///
    ///
    ///
    [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();
 
    }
};


The above code is much better!

Old Code [work on DOT.NET 2.0 +]


using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
 
// AssemblyVersion attribute
using System.Reflection;
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]
[assembly: AssemblyDescription("Description: Ariely Ronen Random string using CLR")]
[assembly: AssemblyCompany("Ariely Ronen")]
 
///
/// C:\Windows\Microsoft.NET\Framework\v2.0.50727
/// C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe /target:library /out:"S:\ArielyDesktop\SQL\CLR\RandomString\Fn_RandomStringCLR.dll" "S:\ArielyDesktop\SQL\CLR\RandomString\Fn_RandomStringCLR.cs"
///
public partial class UserDefinedFunctions
{
 
    private static readonly Random _RandomSize = new Random();
    private static readonly Random _RandomGroup = new Random();
    private static readonly Random _random = new Random();
 
    ///
    /// יותר מהיר לעבוד עם מערך שמכיל את כל התוום שמרשים להכניס ואז אין צורך בהמרות ואחד הרנדום מיותר
    /// פשוט התעצלתי והתבססתי על פונקציה אחרת שכתבתי קודם שעשתה שימוש בכל תוו בלי יוצא מהכלל וזה מה שיצא בנסיון ראשון
    ///
    ///
    ///
    ///
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString Fn_RandomStringCLR(
        int sMaxSize,
        int IsFixed
    )
    {
        if (IsFixed == 0)
        {
            //Random _RandomSize = new Random();
            sMaxSize = _RandomSize.Next(1, sMaxSize);
        }
 
        StringBuilder builder = new StringBuilder();
 
        char ch;
        //Random _random = new Random();
        for (int i = 0; i < sMaxSize; i++)
        {
            switch (_RandomGroup.Next(1, 5))
            {
                case 1: // NUmbers
                    ch = Convert.ToChar(_random.Next(48, 58));
                    break;
                case 2: // English uppercase
                    ch = Convert.ToChar(_random.Next(65, 91));
                    break;
                case 3: // English lowercase
                    ch = Convert.ToChar(_random.Next(97, 123));
                    break;
                default: // Hebrew
                    ch = Convert.ToChar(_random.Next(1488, 1515));
                    break;
            }
            //ch = Convert.ToChar(_random.Next(97, 122));
            builder.Append(ch);
        }
 
        return builder.ToString();
 
    }
};

Tags: SQL , sql server , clr , Random
Categories: SQL , C#