Create md5 hash-code using assemblies in SQL Server 2005

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

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

1. Create new project in visual studio. Choose class library template, enter project name and click Ok.

2. Write function that implement md5 hash-code. For this purpose you need add assembly "System.Security.Cryptography" in your project. Listing below show 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 folow by next rules:
   - your methods must be implemented as a 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 back to out listing. We have class called "MD5Hash". This class have only one method "HashString" marked with attribute [Microsoft.SqlServer.Server.SqlProcedure].  By this atribute we define that out 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 stored procedure on the SQL Serve side. For using assemblies in T-SQL we must create assembly object in SQL Server database. The folowing example shows how to do it:

create assembly a_md5Hash
from '\\server\SolutionFolder\bin\Debug\MD5Hash.dll'
go

This SQL-statement create sql assembly based on out .net class libraly. Next step is to create 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 Framefork assembly for a CLR stored procedure to reference. Parameters:

1. assembly_name is a name of our SQL Server assembly object. 
2. class_name is a namespace and class name of our .net library
3. method_name is a name of md5 class method in .net library

4. 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!

Using unmanaged Dll libraries and setting default dll directory for .NET application

Sometimes it's necessary to call Windows API functions or some custom functions from DLL libraries (not assemblies) in .Net applications (C# or VB.net). For example, we have DLL library created in Visual C++ or Delphi. So, how can we call functions in this library?First of all we need add references on this libraries to our project? Using menu "Add references" is not good idea because it works only with .net assemblies. If you try to add reference on this dll library you've got next error message:

To solve this problem you shoud use DllImport attribute in your project. Let's try to call MessageBox API function from user32.dll library by using DllImport attribute.

I will show this example by using C# console application.

Firstly import namespace System.Runtime.InteropServices. And then add attribute DllImport in class declaration with name of dll library. Parameter CallingConvention define default convention for calling unmanaged function with platform invoke.

using System;

using System.Runtime.InteropServices;

namespace test

{

class Program

{

[DllImport("user32.dll", CallingConvention = CallingConvention.StdCall)]

public static extern int MessageBox(System.IntPtr hWnd, String lpText, String lpCaption, System.UInt32 uType);

static void Main(string[] args)

{

MessageBox(System.IntPtr.Zero, "Test", "Text", 0);

}

}

}

After attribute DllImport I declare WinAPI function MessageBox. Prototype of this function you can find in MSDN. It's important to mark all unmanaged dll function with keywords static and extern.

To mapping C# types with Windows types read http://msdn.microsoft.com/en-us/library/ac7ay120.aspx#Mtps_DropDownFilterText

Run this project and you will see standard windows dialog message box.

Now let's consider next situation: for example you have your own dll library and you want to distribute this library with application.

So, there is two ways: you should add this library into root of project or add special folder to project and add file there. Don't forget to mark library as content:

Well, now if you bild your project dll library will copy to output directory. But there is some problem. If you library located in subfolder (for examle, in folder called "Dlls") of your project when you try to call some dll functions you have got error message "Cannot find dll library".

To solve this problem you need to set dll directory path. For this purpose let's try to use WinAPI function SetDllDirectory. This function add directory to the search path used to locate DLLs for your application.

After calling SetDllDirectory, the DLL search path is:

1. The directory from which the application loaded.
2. The directory specified by the lpPathName parameter.
3. The system directory. The name of this directory is System32.
4. The 16-bit system directory. There is no function that obtains the path of this directory, but it is searched. The name of this directory is System.
5. The Windows directory.
6. The directories that are listed in the PATH environment variable.

I use this function in Load event of my project.

public partial class MyForm : Form

{

[DllImport("Kernel32.dll", CallingConvention = CallingConvention.StdCall)]

public static extern bool SetDllDirectory(String lpPathName);

private void MyForm_Load(object sender, EventArgs e)

{

// Restores the default search order

SetDllDirectory(null);

// Add dll directory path

SetDllDirectory(AppDomain.CurrentDomain.BaseDirectory + "Dlls");

// Some code here

}

}

First line restore default search paths of application and second line add new path for dll directory.

It's very useful to store dll libraries in separate folder. Is help to structure files in project correctly and always find needed library very quickly.