Introduction to SQL CLR and Spire.PDF

SQL CLR is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.

This technology, introduced in Microsoft SQL Server 2005, allow users for example to create the following types of managed code objects in SQL Server in .NET languages such as C# or VB.NET.

• Stored Procedures
• User defined aggregates
• Triggers
• User defined types
• User defined functions

Spire.PDF is a PDF library which contains an incredible wealth of features to create, read, edit and manipulate PDF documents on .NET, Silverlight and WPF Platform. As an independent PDF library, it does not need users to install Adobe Acrobat or any other third party libraries. Spire.PDF for .NET is completely written in C#, but also supports VB.NET, Windows Forms and ASP.NET Applications.

How to Utilize Spire.PDF in SQL CLR?

In this article we will see, how to create a simple PDF document in SQL CLR using Spire.PDF and how to deploy it in SQL Server. Just follow following steps and it will get done.

Enabling SQL CLR Integration

For SQL Server is, SQL CLR assembly is an external code, so in SQL Server default installation configuration, SQL CLR is blocked not used, to use it, we must first open it.

• Open SQL Server Management Studio, connect and create a new database named SpirePDFCLR.
• Enable the SQL CLR with the following code.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Now the SQL Server is ready to execute the database objects that are built using the C# programming language.

Adding Dependency Assemblies

For the sake of using non-SAFE CLR Assemblies, firstly we need to turn Trustworthy on.

ALTER DATABASE SpirePDFCLR SET TRUSTWORTHY ON

Now, we can add the dependency assemblies by following code.

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

Once these assemblies are added, we can add the Spire.Pdf.Dll.

CREATE ASSEMBLY [Spire.Pdf] 
FROM 'D:\NET2.0\Spire.Pdf.dll'
WITH PERMISSION_SET=UNSAFE

Then go to the Object Browser, select the SpirePDFCLR database, right-click and choose "Refresh". They will be shown under Assemblies as below.

Creating and Manipulate PDF document in SQL CLR

Once the Spire.Pdf.dll is added into the database, we can create and manipulate the PDF document in SQL CLR. Here we will take a Stored Procedure as an example.

Put the following code in the Stored Procedure. It will create a PDF document when we execute the Stored Procedure.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Spire.Pdf;
using Spire.Pdf.Graphics;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoreProcedure()
    {
        PdfDocument doc = new PdfDocument();
        PdfPageBase page = doc.Pages.Add();
        page.Canvas.DrawString("Hello world", new PdfFont(PdfFontFamily.Courier, 100), PdfBrushes.Black, 0, 0);
        doc.SaveToFile("d:\\result.pdf");
    }
};

Next build and deploy the solution. If everything is fine then it will say that the deployment was successful. Then we will see the Store Procedure in the SpirePDFCLR database.

Now we will run the Stored Procedure from SQL Server, but here we will get an error as below.

We need to change the permission level of the CLR project as unsafe and deploy it again.

Now run it again, it will work fine and we will get the PDF document.