SQL Server Function using CLR

UPDATED: I’ve added one function to write from BLOB in SQL Server table to the disk straight away

I thought this article might be useful for anyone that wants to implement .NET code to SQL server level. In this case I really need CLR because I want to do compression of images and I believe it’s not possible to do that using pure SQL server stored procedure and I’m trying to avoid creating a .NET application just for compression of images through row by row processing.

Here we start:

This is your C#/.NET code, you need to declare it as SQL function or SQL stored procedure

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Zip = ICSharpCode.SharpZipLib.Zip.Compression;

namespace CLRCompressionFunctions
{
    public partial class CompressionCore
    {
        [SqlFunction()]
        public static SqlBytes fn_Compress(SqlBytes uncompressedBytes)
        {
            if (uncompressedBytes.IsNull)
                return uncompressedBytes;

            MemoryStream memory = new MemoryStream();

            ICSharpCode.SharpZipLib.Zip.Compression.Streams.DeflaterOutputStream stream =
                new ICSharpCode.SharpZipLib.Zip.Compression.Streams.DeflaterOutputStream(memory, new Zip.Deflater(Zip.Deflater.BEST_COMPRESSION), 131072);

            stream.Write(uncompressedBytes.Buffer, 0, Convert.ToInt32(uncompressedBytes.Length));
            stream.Flush();
            stream.Close();
            stream = null;

            return new SqlBytes(memory.ToArray());
        }

        [SqlFunction()]
        public static SqlBytes fn_Decompress(SqlBytes compressedBytes)
        {
            if (compressedBytes.IsNull)
                return compressedBytes;

            ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputStream stream =
                new ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputStream(new MemoryStream(compressedBytes.Buffer));
            MemoryStream memory = new MemoryStream();
            byte[] writeData = new byte[4096];
            int size;

            while (true)
            {
                size = stream.Read(writeData, 0, writeData.Length);
                if (size > 0)
                {
                    memory.Write(writeData, 0, size);
                }
                else break;
            }
            stream.Flush();
            stream.Close();
            stream = null;

            return new SqlBytes(memory.ToArray());
        }

[SqlFunction()]
        public static SqlString fn_WriteFile(SqlString path, SqlBytes bytesFile, SqlBoolean isCompressed)
        {
            string returnString = string.Empty;

            try
            {
                //check if the file exists or not
                FileStream myFStream = new FileStream(path.ToString(), FileMode.OpenOrCreate, FileAccess.ReadWrite);

                SqlBytes fileBytes = bytesFile;

                if (isCompressed)
                {
                    fileBytes = fn_Decompress(bytesFile);
                }

                int Length = 256;
                Byte[] buffer = new Byte[Length];

                Stream readStream = fileBytes.Stream;

                int bytesRead = readStream.Read(buffer, 0, Length);

                // write the required bytes
                while (bytesRead > 0)
                {
                    myFStream.Write(buffer, 0, bytesRead);
                    bytesRead = readStream.Read(buffer, 0, Length);
                }

                readStream.Close();
                myFStream.Close();
                returnString = "File is written successfully";
            }
            catch (Exception ex)
            {
                returnString = ex.ToString();
            }

            return new SqlString(returnString);
        }
    }
}

Installation time to your SQL Server, You need to register your assembly(.dll) as well as referenced Assembly to SQL Server

ALTER DATABASE TestAssembly SET TRUSTWORTHY ON
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
CREATE ASSEMBLY [ICSharpCode.SharpZipLib.dll]
                  FROM 'D:\Applications\FileCompressorApp\CLRCompressionFunctions\Deployment\ICSharpCode.SharpZipLib.dll'
                  WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [CLRCompressionFunctions]
                  FROM 'D:\Applications\FileCompressorApp\CLRCompressionFunctions\Deployment\CLRCompressionFunctions.dll'
                  WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

PERMISSION_SET = SAFE only if you don’t want the assembly accessing external resources such as writing to disk, but in this case the function is used to write into the disk
e.g How about if you want to use/register System.Drawing to your assembly? Yes you can do it by using

CREATE ASSEMBLY [System.Drawing.dll]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'
WITH PERMISSION_SET = UNSAFE

You need to enable the CLR on SQL server in order to use your function

Now you need to create your function based on your assembly

CREATE FUNCTION [fn_Compress]           (
                 @uncompressedBytes varbinary(MAX))
            RETURNS varbinary(MAX)
            AS    EXTERNAL NAME CLRCompressionFunctions.[CLRCompressionFunctions.CompressionCore].fn_Compress
GO
CREATE FUNCTION [fn_Decompress]           (
                 @uncompressedBytes varbinary(MAX))
            RETURNS varbinary(MAX)
            AS    EXTERNAL NAME CLRCompressionFunctions.[CLRCompressionFunctions.CompressionCore].fn_Decompress
GO
CREATE FUNCTION [fn_WriteFile]           (
                 @path nvarchar(4000),
				 @bytesFile varbinary(MAX),
				 @bitCompressed bit)
			RETURNS nvarchar(4000)
            AS    EXTERNAL NAME CLRCompressionFunctions.[CLRCompressionFunctions.CompressionCore].fn_WriteFile
GO

Usage, It’s the same as you call a function in SQL Server

SELECT dbo.[fn_Compress](testimage) FROM tblImages
SELECT dbo.[fn_Decompress](imgFileContent) FROM TABLE_NAME
GO
SELECT dbo.[fn_WriteFile]('c:\test.pdf',imgFileContent, 0) FROM TABLE_NAME
GO