CLR function and procedures in SQL Server(GZipStream)

CLR function and procedures in SQL Server(GZipStream)

Today I have work with some CLR function and procedures in SQL Server. And plan to publish some CLR code example in my weblog. 


following code will explain,

• How to Write CLR function using C# and T-SQL.
• How to add “GZipStream” in to CLR function.
• How to compress and decompress given string.
• How to Create “ASSEMBLY” using T-SQL.

First will create C# code for CLR Assembly mapping DLL,

using System;using System.Collections.Generic;

using System.Text;using System.IO;

using System.IO.Compression;using System.IO.IsolatedStorage;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

    public class GZipDatabaseUserGroup
    {
        public static byte[] GZip(string value)
        {
            byte[] byteArray = Encoding.UTF8.GetBytes(value);
            MemoryStream ms = new MemoryStream();
            GZipStream zip = new GZipStream(ms, CompressionMode.Compress);
            zip.Write(byteArray, 0, byteArray.Length);
            zip.Close();
            byteArray = ms.ToArray();
            ms.Close();
            zip.Dispose();
            ms.Dispose();
            return byteArray;
        }

        public static string unGZip(byte[] data)
        {
            MemoryStream output = new MemoryStream();
            MemoryStream ms = new MemoryStream(data);
            GZipStream zip = new GZipStream(ms, CompressionMode.Decompress);
            zip.CopyTo(output);          
            string strOutPut = Encoding.UTF8.GetString(output.ToArray());
            return strOutPut.ToString();
        }

        public static string fn_CLR_DecompressGZIPBytesAsString(byte[] ZipInput)
        {
            try
            {
                string returnVal = unGZip(ZipInput);
                return returnVal;
            }
            catch
            {
                return "Error";
            }
        }

        public static byte[] fn_CLR_CompressGZIPStringToBytes(string ZipInput)
        {
            try
            {
                byte[] returnVal = GZip(ZipInput);
                return returnVal;
            }
            catch
            {
                return null;
            }
        }
    }

using above C# code you can build DLL. And next with using above DLL you can create  assembly as follows,

CREATE ASSEMBLY <ASSEMBLY NAME> from '<DLL Path>\GZipDatabaseUserGroup.dll' WITH PERMISSION_SET = SAFE;

Using above Assembly name, you can create CLR function as follows,

CREATE FUNCTION fn_CompressGZIPStringToBytes(@InPut nvarchar(max)) RETURNS varbinary(max)ASEXTERNAL NAME <ASSEMBLY NAME>.<C# Name>.<C# function name>;GO

Hope now you have some idea about how to create CLR function.And this will need bit C# knowledge, if you are not friendly with C#, then you can get some help from Microsoft MVA.

Well! now i'll show you full T-SQL code example for above task,

use master
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

USE [DATABASEUSERGROUP]
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[GZipDatabaseUserGroup_Table]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[GZipDatabaseUserGroup_Table];
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[GZipDatabaseUserGroup_Table]') AND [type] in (N'U')) BEGIN
CREATE TABLE [dbo].[GZipDatabaseUserGroup_Table](
[id] [int] NULL,
[strData] [nvarchar](4000) NULL,
[unGzipBinary] [varbinary](max) NULL,
[GzipBinary] [varbinary](max) NULL
); 
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_DecompressGZIPBytesAsString]') AND [type]in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN
DROP FUNCTION [dbo].[fn_DecompressGZIPBytesAsString];
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_CompressGZIPStringToBytes]') AND [type]in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN
DROP FUNCTION [dbo].[fn_CompressGZIPStringToBytes];
END
GO

IF EXISTS (SELECT 1 FROM sys.assemblies WHERE [name]='GZipDatabaseUserGroup') BEGIN
DROP ASSEMBLY GZipDatabaseUserGroup;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.assemblies WHERE [name]='GZipDatabaseUserGroup') BEGIN
CREATE ASSEMBLY GZipDatabaseUserGroup from 'E:\databaseusergroup\bin\Debug\GZipDatabaseUserGroup.dll' WITH PERMISSION_SET = SAFE;
END
GO

CREATE FUNCTION fn_CompressGZIPStringToBytes
(@InPut nvarchar(max)) RETURNS varbinary(max)
AS
EXTERNAL NAME GZipDatabaseUserGroup.GZipDatabaseUserGroup.fn_CLR_CompressGZIPStringToBytes
GO

CREATE FUNCTION fn_DecompressGZIPBytesAsString
(@InPut varbinary(max)) RETURNS nvarchar(max)
AS
EXTERNAL NAME GZipDatabaseUserGroup.GZipDatabaseUserGroup.fn_CLR_DecompressGZIPBytesAsString;
GO

--insert data
INSERT INTO [dbo].[GZipDatabaseUserGroup_Table]([id],[strData],[unGzipBinary],[GzipBinary]) 
VALUES(1, 'Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server',
CAST(N'Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server' AS varbinary(max)),
dbo.fn_CompressGZIPStringToBytes('Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server')) ;
GO
SELECT DATALENGTH([unGzipBinary])unGzipBinary,DATALENGTH([GzipBinary])GzipBinary,* FROM [dbo].[GZipDatabaseUserGroup_Table];
GO

SELECT dbo.fn_DecompressGZIPBytesAsString([GzipBinary]) GzipBinary,* FROM [DATABASEUSERGROUP].[dbo].[GZipDatabaseUserGroup_Table];
GO


use master
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Cheers...

and Most welcome your comments and ideas...