SQL SERVER İÇİN MD5 KÜTÜPHANESİ YAZIP C# DA EKLEME YAPMAK

 

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.
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:
Hide   Copy Code
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:
Hide   Copy Code
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:
Hide   Copy Code
declare @res nvarchar(max) exec dbo.sp_md5Hash 'Hello World!', @res output select @res

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