Creating SQL CLR Assembly in C#

byZafar Ali Khan Post a Comment
Create CLR Assembly in C#
SQL CLR
SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment. This technology, introduced in Microsoft SQL Server 2005, allow users for example to create the following types of managed code objects in SQL Server in .NET languages such as C# or VB.NET.
  • Stored procedures (SPs) which are analogous to procedures or void functions in procedural languages like VB or C,
  • Triggers which are stored procedures that fire in response to Data Manipulation Language (DML) or Data Definition Language (DDL) events,
  • User-defined functions (UDFs) which are analogous to functions in procedural languages,
  • User-defined aggregates (UDAs) which allow developers to create custom aggregates that act on sets of data instead of one row at a time,
  • User-defined types (UDTs) that allow users to create simple or complex data types which can be serialized and deserialized within the database.
The SQL CLR relies on the creation, deployment, and registration of CLI assemblies, which are physically stored in managed code dynamic load libraries (DLLs). These assemblies may contain CLI namespaces, classes, functions and properties.
Creating CLR Assembly in Visual Studio 2019
  • Create Sql Server Database Project
  • Add CLR User defined function to it


 

User Defined Functions 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,
         FillRowMethodName = "FillRow",
        TableDefinition = "PasswordSalt varbinary(max),PasswordHash varbinary(max)")]
    public static IEnumerable clr_fnCreatePasswordHash(string password)
    {
        List<HashSalt> lst = new List<HashSalt>();
        if (password == null) throw new ArgumentNullException("password");
        if (string.IsNullOrWhiteSpace(password)) throw new ArgumentException("Value cannot be empty or whitespace only string.", "password");
        HashSalt obj = new HashSalt();
        using (var hmac = new System.Security.Cryptography.HMACSHA512())
        {
            obj.PasswordSalt = hmac.Key;
            obj.PasswordHash = hmac.ComputeHash(System.Text.Encoding.UTF8.GetBytes(password));
        }
        lst.Add(obj);
        return lst;
    }
    public static void FillRow(Object obj, out byte[] PasswordSalt, out byte[] PasswordHash)
    {
        HashSalt hs = obj as HashSalt;
        // var rowItem = obj;       
        PasswordSalt = hs.PasswordSalt;
        PasswordHash = hs.PasswordHash;
    }

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static byte[] clr_fnCreateHashFromSalt(string password, byte[] salt)
    {
        byte[] passwordSalt = salt;
        using (var hmac = new System.Security.Cryptography.HMACSHA512(passwordSalt))
        {
            var computedHash = hmac.ComputeHash(System.Text.Encoding.UTF8.GetBytes(password));
            byte[] passwordHash = computedHash;
            return passwordHash;
        }
    }
    public static byte[] StringToByteArray(string hex)
    {
        List<byte> byteList = new List<byte>();

        string hexPart = hex.Substring(2);
        for (int i = 0; i < hexPart.Length / 2; i++)
        {
            string hexNumber = hexPart.Substring(i * 2, 2);
            byteList.Add((byte)Convert.ToInt32(hexNumber, 16));
        }

        byte[] original = byteList.ToArray();
        return original;
    }



    public class HashSalt
    {
        public byte[] PasswordSalt { get; set; }
        public byte[] PasswordHash { get; set; }
    }
}


Build the project and import the assembly into your database.
Importing into database 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
alter database MyDB
set trustworthy on
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  
CREATE ASSEMBLY MyAssembly   
FROM 'D:\MyAssembly\bin\Debug\MyAssembly.dll'  
WITH PERMISSION_SET = SAFE;  
GO
Create Function 
1
2
3
4
5
6
7
CREATE FUNCTION [dbo].[clr_fnCreatePasswordSaltAndHash](@password [nvarchar](300))
RETURNS  TABLE (
	[PasswordSalt] [varbinary](max) NULL,
	[PasswordHash] [varbinary](max) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyAssembly].[UserDefinedFunctions].[clr_fnCreatePasswordHash]
Test the function 
1
select * from [dbo].[clr_fnCreatePasswordSaltAndHash]('abc123')
 

Tagged In:
Previous Post Next Post

Post a Comment

Post a Comment