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,
Using above Assembly name, you can create CLR function as follows,
Well! now i'll show you full T-SQL code example for above task,
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>;GOHope 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...
