Create md5 Hash-code using Assemblies in SQL Server 2005






4.43/5 (7 votes)
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:
- Create a new project in Visual Studio. Choose class library template, enter project name and click Ok.
- 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 asvoid
) - 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 returnresult
-SqlString
pass-by-reference data type parameter which stored procedure returning through anOUTPUT
argument
Build our project.
- Your methods must be implemented as
- 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:
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