How do I call C# code from T-SQL, SQL Server

Question

A little context, I need a means to losslessly insert/select/compare single and double precision floating point into a SQL Server database without using parameters, thought I'd be clever and call a DLL as a super-fast function.

I wrote and tested this C# class (which I've well-tested):

using System;
using System.Linq;
using System.Reflection;

namespace SqlServerCast
{
    public class SqlServerCast
    {
        static public char ByteOrder;

        public enum ByteOrderEnum {
            BigEndian = 1,     /*  The most significant byte (highest address) is stored first. */
            LittleEndian = 0   /*  The least significant byte (lowest address) is stored first. */}

        public static void Main()
        {
            Assembly assem = typeof(SqlServerCast).Assembly;
            Object o = assem.CreateInstance("SqlServerCast", false,
                BindingFlags.ExactBinding,
                null, new Object[] { 2 }, null, null); 
        }

        public SqlServerCast(char val)    //  constructor
        {
            if (val != 0) ByteOrder = (char) ByteOrderEnum.BigEndian;
            else ByteOrder = (char) ByteOrderEnum.LittleEndian;
        }

        public static double CastToDBL(string str) {return BitConverter.ToDouble(StringToByteArray(str), 0);}

        public static float CastToSGL(string str)  {return BitConverter.ToSingle(StringToByteArray(str), 0);}

        private static string Dash = "-", NullStr = null;
        public static string CastFromDBL(double dbl) {
            switch (ByteOrder)
            {
                case (char)ByteOrderEnum.BigEndian:   //  actually, network byte order, big endian
                    byte[] bytes = BitConverter.GetBytes(dbl);
                    return BitConverter.ToString(bytes.Reverse().ToArray()).Replace(Dash, NullStr);
                case (char)ByteOrderEnum.LittleEndian:
                    return BitConverter.ToString(BitConverter.GetBytes(dbl)).Replace(Dash, NullStr);
                default:
                    return null;
            }
         }

        public static string CastFromSGL(float sgl)
        {
            switch (ByteOrder)
            {
                case (char)ByteOrderEnum.BigEndian:   //  actually, network byte order, big endian
                    byte[] bytes = BitConverter.GetBytes(sgl);
                    return BitConverter.ToString(bytes.Reverse().ToArray()).Replace(Dash, NullStr);
                case (char)ByteOrderEnum.LittleEndian:
                    return BitConverter.ToString(BitConverter.GetBytes(sgl)).Replace(Dash, NullStr);
                default:
                    return null;
            }
        }

        private static byte[] StringToByteArray(String hex)
        {
            int NumberChars = hex.Length;
            byte[] bytes = new byte[NumberChars / 2];
            for (int i = 0; i < NumberChars; i += 2)
                bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
            switch (ByteOrder) {
                case (char) ByteOrderEnum.BigEndian:   //  actually, network byte order, big endian
                    return bytes.Reverse().ToArray();
                case (char) ByteOrderEnum.LittleEndian:
                    return bytes;
                default:
                    return null;
            }
        }
    }
}

...trying to follow the prescriptions on SO and MSDN for T-SQL -callable DLLs and so far I get this error:

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'SqlServerCast' failed because assembly 'SqlServerCast' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

Can someone give me a step-by-step path to success here? I'm stuck...

BTW: I know about creating the hashes, I don't think that's the issue yet.

Answer 1

These instructions are for Microsoft SQL Server Management Studio 2014.

Import the Assembly

First of all you need to import that assembly into your database inside SQL Server Management Studio by navigating to the New Assembly dialog window:

DatabaseName -> Programmability -> Assemblies -> (Right Click) 'New Assembly...'

Inside the 'New Assembly' dialog window, chose Browse under the Path to assembly field and select the assembly you want to import. Adjust Permissions and click ok.

Wrap Assembly Methods in a SQL Function

Next you need to create sql function to wrap your assembly method like this:

CREATE FUNCTION [dbo].[fn_funcName](@str [varchar](max))
RETURNS 
   varchar(max) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [YourSqlAssemblyName].[YourAssemblyName.Class1].[GetName]

If you want to return table from your function read about SqlFunctionAttribute in .NET.

Step by Step Guide to Create Sql server database project and compile dll to be used in Sql Server:

https://dbtut.com/index.php/2019/05/05/what-is-clr-and-how-to-import-dll-in-sql-server/