Click here to Skip to main content
Click here to Skip to main content
Go to top

Create md5 Hash-code using Assemblies in SQL Server 2005

, 7 Jan 2011
Rate this:
Please Sign up or sign in to vote.
Create md5 hash-code using assemblies in SQL Server 2005

Introduction

It’s very simple to create an md5 hash function in such languages like C# or VB.NET. But in some cases, it’s necessary to implement md5-hash algorithm on SQL Server. Unfortunately, SQL Server does not have hash function like md5 and writing T-SQL script is not a good idea too. Performance of such script will be very low.

Using the Code

One way to solve this problem is to create a .NET assembly and attach it to SQL Server instance. Ok, let’s do it step by step:

  1. Create a new project in Visual Studio. Choose class library template, enter project name and click Ok.
  2. Write function that implements md5 hash-code. For this purpose, you need to add assembly “System.Security.Cryptography” in your project. Listing below shows md5 function.
    using System.Security.Cryptography;
    using System.Text;
    using System.Data;
    using System.Data.SqlTypes;
    namespace Md5Hash
    {
      public class Md5Class
      {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void HashString(SqlString value, out SqlString result)
        {
          string str = value.ToString().Trim();
          HashAlgorithm mhash = mhash = new MD5CryptoServiceProvider();
          byte[] bytValue = System.Text.Encoding.UTF8.GetBytes(str);
          byte[] bytHash = mhash.ComputeHash(bytValue);
          mhash.Clear();
    
          StringBuilder sBuilder = new StringBuilder();
          // Loop through each byte of the hashed data
          // and format each one as a hexadecimal string.
          for (int i = 0; i < bytHash.Length; i++)
          {
             sBuilder.Append(bytHash[i].ToString("x2"));
          }
          // Return the hexadecimal string.
          result = sBuilder.ToString();
        }
      }
    } 

    If you want to use your assembly methods over SQL Server stored procedures, you should follow the next rules:

    • Your methods must be implemented as public static methods on a class in your assembly
    • Your methods must be declared as void or return integer value (in my example, I declare method as void)
    • Number of parameters must be the same as in stored procedures
    • All parameters must be declared according to SQL Server data types (see MSDN article http://msdn.microsoft.com/en-us/library/ms131092(v=SQL.90).aspx)

    Let’s get back to our listing. We have a class called “MD5Hash”. This class has only one method “HashString” marked with attribute [Microsoft.SqlServer.Server.SqlProcedure]. By this attribute, we define that our method will be used like a stored procedure. Method “HashString” takes two arguments:

    • value SqlString data type parameter which hash-code we need to return
    • result - SqlString pass-by-reference data type parameter which stored procedure returning through an OUTPUT argument

    Build our project.

  3. Now we should create a stored procedure on the SQL Server side. For using assemblies in T-SQL, we must create assembly object in SQL Server database. The following example shows how to do it:
  4. create assembly a_md5Hash
    from '\\server\SolutionFolder\bin\Debug\MD5Hash.dll'
    go 

    This SQL-statement creates a SQL assembly based on our .NET class liberally. The next step is to create a stored procedure which will use our assembly:

    create procedure dbo.sp_md5Hash (@value nvarchar(20), _
    @return nvarchar(max) output) as _
    external name a_md5hash.[Md5Hash.Md5Class].HashString  

Argument as external name assembly_name.class_name.method_name specifies the method of a .NET Framework assembly for a CLR stored procedure to reference. Parameters:

  • class_name is a namespace and class name of our .NET library.
  • method_name is a name of md5 class method in .NET library.
  • assembly_name is a name of our SQL Server assembly object.
  • Ok. We have done it! Let’s try to get hash-code over our stored procedure:
declare @res nvarchar(max) exec dbo.sp_md5Hash 'Hello World!', @res output select @res 

Well, I’ve got 86fb269d190d2c85f6e0468ceca42a20. Good luck!

History

  • 7th January, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Denis Strukachev
Software Developer
Russian Federation Russian Federation
I have 5 years of experience developing enterprise software, starting from Delphi and moving towards C# on Windows platforms.
 
Visit my blog http://strukachev.wordpress.com
 

My Facebook Profile here

Comments and Discussions

 
GeneralMy vote of 1 PinmemberNathan J Bolstad10-Jan-11 10:51 
GeneralRe: My vote of 1 Pinmemberregeter13-Jun-11 6:06 
GeneralHashBytes PinmemberRichard Deeming10-Jan-11 7:01 
GeneralThanks for the article PinmemberKeith Vinson10-Jan-11 3:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 7 Jan 2011
Article Copyright 2011 by Denis Strukachev
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid