SQL Server CLR Usage

[pholcroft]

Thanks for writing this great tool. I needed a function in SQL Server to convert data in RTF to HTML. I tried using this converted to a scalar function, but it didn't work very well.

I was able to use your library to achieve this very simply:

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
using RtfPipe;

namespace SqlRTFtoHTML
{
    public class RtfToHtmlConverter
    {
        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlString ConvertRtfToHtml(SqlString rtfContent)
        {
            if (rtfContent.IsNull)
                return SqlString.Null;

            try
            {
                string rtf = rtfContent.Value;
                string htmlContent = Rtf.ToHtml(rtf);

                return new SqlString(htmlContent);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
    }
}

Then in SSMS I ran the following commands using the database where the function was required:

DECLARE @AssemblyHash VARBINARY(64) = 0x6CD69916995AE21C324CC6FA28F5B0220B655AADA3DDAB4311CCBA47360F38B5AEA0B4E29534983B852618CDBA9AB5C4F324A723D97626C1A3D0E26F6D11BC73
EXEC sp_add_trusted_assembly @hash = @AssemblyHash, @description = N'Trusted assembly for Framework64\v4.0.30319\System.drawing.dll'
CREATE ASSEMBLY [system.drawing] AUTHORIZATION dbo FROM 'c:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.drawing.dll' WITH PERMISSION_SET = UNSAFE 
GO

/****** Object:  SqlAssembly [RtfPipe] ******/
DECLARE @AssemblyHash VARBINARY(64) = 0xC56F057B55F39C33B1B7F2F28C706CB546282A75A46584D41DCBC85937C1C7C547BC4AF36A338DDD9971DAAB22433BD28616E0B7007132F05CF05D7DD390A351
EXEC sp_add_trusted_assembly @hash = @AssemblyHash, @description = N'Trusted assembly for RtfPipe.dll'
CREATE ASSEMBLY [RtfPipe] AUTHORIZATION dbo FROM 'c:\temp\RtfPipe.dll' WITH PERMISSION_SET = UNSAFE
GO

/****** Object:  SqlAssembly [RtfToHtmlAssembly] ******/
DECLARE @AssemblyHash VARBINARY(64) = 0xE3CD5157C36DA4C0F78728D62C3624E92306BF737F6B8AA6FFA49F85F4F58EE8990851FEAFAE8597256EF146BF77FA4DC2B947DCF997ADCA096EAA0DA0238CD6
EXEC sp_add_trusted_assembly @hash = @AssemblyHash, @description = N'Trusted assembly for SqlRTFtoHTML.dll'
CREATE ASSEMBLY [RtfToHtmlAssembly] AUTHORIZATION dbo FROM 'c:\temp\SqlRTFtoHTML.dll' WITH PERMISSION_SET = UNSAFE 
GO

CREATE OR ALTER FUNCTION ConvertRtfToHtml(@rtfContent NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME RtfToHtmlAssembly.[SqlRTFtoHTML.RtfToHtmlConverter].ConvertRtfToHtml;

-- Example Usage
-- SELECT NoteID, dbo.ConvertRtfToHTML(NoteRtf) AS NoteHTML FROM dbo.Notes