Generating Random Strings of Characters in SQL

The following Transact SQL procedure can be used to generate a random string of characters. As such it can be used to for example generate a default password for a user. The specific characters that are used to generate the string can be specified, so it can be customised (e.g. to only create passwords of digits or lower cased letters). The length of the generated random string can also be specified.

It is recommended that this SQL procedure be used as a stored procedure.

Using as a Stored Procedure

The following stored procedure creates a random string of characters of a length specified by the parameter @Length:

CREATE PROCEDURE sp_GeneratePassword
(
@Length int
)
AS
DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)

SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'

DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''
SET NOCOUNT ON
SET @counter = 1

WHILE @counter < (@Length + 1)
BEGIN
SET @RandomNumber = Rand()
SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
SET @counter = @counter + 1

SET @RandomID = @RandomID + @CurrentCharacter
END

SELECT @RandomID AS 'Password'
GO

Extension Methods

The .NET Framework employs the concept of sealed classes. A sealed class is a class that cannot be inherited from. But, what if we want to extend these classes? Based on the meaning of sealed it’s not possible. Compound the technical inability to extend sealed classes with classes that are defined as the result of a LINQ query, called projections, and there is no opportunity to extend.

Another desire that class designers have is to avoid deep inheritance trees. Generally using inheritance to add a capability or two is undesirable because it leads to deep inheritance trees that are difficult to comprehend and maintain.

To overcome some of these challenges Microsoft introduced the extension method. Extension methods are defined in separate static classes as static methods and the first argument of the method is the extended type. The extended type—the first argument—is modified with the keyword this. Although an extension method is a static method in a static class, extension methods have member method semantics. That is, extension methods are called is if they were a member of the extended class, a regular member method.

Extension methods enable you to “add” methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type. Extension methods are a special kind of static method, but they are called as if they were instance methods on the extended type. For client code written in C# and Visual Basic, there is no apparent difference between calling an extension method and the methods that are actually defined in a type.

Extension method are only in scope when you explicitly import the namespace into your source code with “using” directive.

namespace ExtensionMethods
{
 public static class ExtensionsClass
 {
  public static string Reverse(this String strReverse)
  {
   char[] charArray = new char[strReverse.Length];
   int len = strReverse.Length - 1;
   for (int i = 0; i <= len; i++)
   {
     charArray[i] = strReverse[len - i];
   }
  return new string(charArray);
  }
 }
}

using ExtensionMethods;
protected void Page_Load(object sender, EventArgs e)
{
 string str = "Hello Extension Methods";
 string strReverse = str.Reverse();
}