Sometimes, you need to do some hashing in T-SQL on the server side and use it in C# or vice-versa.
The algorithm used by the native 'checksum' is not divulged.
Here is a simple string to hash 32 bit function that can be used to get a number from a string.
An optional parameter is available if you want to reduce the result to a shorter number.
Both functions return the same.
T-SQL Function Source
CREATE FUNCTION FnMD5Hash(@SourceString varchar(8000), @Modulo INT = 0)
RETURNS INTEGER
AS
BEGIN
IF @Modulo = 0
RETURN CAST(HashBytes( 'MD5', @SourceString) AS INTEGER)
ELSE
RETURN ABS(CAST(HashBytes( 'MD5', @SourceString) AS INTEGER)) % @Modulo
RETURN 0
END
C# Function Source
static System.Security.Cryptography.MD5CryptoServiceProvider md5Provider =
new System.Security.Cryptography.MD5CryptoServiceProvider();
static System.Text.Encoding encoding =
System.Text.Encoding.GetEncoding(1252);
static Int32 SQLStringToHash(string sourceString, int modulo = 0)
{
var md5Bytes = md5Provider.ComputeHash(encoding.GetBytes(sourceString));
var result = BitConverter.ToInt32(new byte[] {
md5Bytes[15], md5Bytes[14], md5Bytes[13], md5Bytes[12] }, 0);
if (modulo == 0) return result;
else return Math.Abs(result) % modulo;
}
T-SQL Test Source
DECLARE @SourceString AS VARCHAR(255) = 'Joyeux Noël'
PRINT 'T-SQL Test'
PRINT 'Source: ' + @SourceString
PRINT 'Hash:'
PRINT dbo.FnMD5Hash(@SourceString, default)
PRINT 'Hash (0..999):'
PRINT dbo.FnMD5Hash(@SourceString, 1000)
C# Test Source
string sourceString = "Joyeux Noël";
Console.WriteLine("C# Test");
Console.WriteLine("Source: "+sourceString);
Console.WriteLine("Hash: " + SQLStringToHash(sourceString));
Console.WriteLine("Hash (0..999): " + SQLStringToHash(sourceString, 1000));
T-SQL Test Output
T-SQL Test
Source: Joyeux Noël
Hash: -92694766
Hash (0..999): 766
C# Test Output
C# Test
Source: Joyeux Noël
Hash: -92694766
Hash (0..999): 766