Adding the iTextSharp dll for use with CLR Stored Procedures

Question

Has anyone had any luck creating an assembly for iTextSharp for use with CLR Stored procedures? I've been trying all afternoon and have not been able to get any results. SQL management studio is saying I need to register system.drawing first (can't be the new version, has to be the old version no less) but it gives me issues when I try to run create it for safe and unsafe.

So I'm just wondering if anyone has had any luck with setting up iTextSharp to work via CLR SPROCS? The reason I want to do this is because then I'd be able to pass parameters and generate reports much easier (CLR SPROC holds the standard static stuff while parameters could control dynamic stuff)

Thanks for any help

Answer 1

You do not need System.Windows.Forms.dll as the other answer suggests.
Just Add these 2 References to your Visual Studio Project:

1. System.Drawing.dll
2. itextsharp.dll

Make sure both of these DLL's are Referenced by your Project.
Then in the Properties of these 2 Referenced DLL's, set the "Model Aware" Property to "True"
Something about this barely-documented Property will auto-deploy your Referenced Assembly.

Rebuild your Project (make sure to do this or you may see an error) and try Deploying.
It should automatically add these 2 References as Assemblies to your SQL Database.
Once added, it is at least smart enough not to drop and add them again on subsequent re-deploys.

NOTE: After doing this, it fails every other time I try to Deploy my Project.
I don't know why that is, but I'm willing to put up with it (I'm using VS2013).

---

WARNING: I ran into this horrible error:

ALTER ASSEMBLY failed because the referenced assemblies would change. The referenced assembly list must remain the same.

If this happens, you need to drop the entire assembly, because something about the deployment causes part of the assembly to remain intact and does not update the External-References and instead gives this cryptic/unuseful error instead.

What I ended up doing was dropping all dependencies and the Assembly in my PreDeployScript.sql . This way I avoid the issue on any Server I deploy my SQL Assembly to (or when I add new References).

Answer 2

Register these assemblies on the server:

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

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

CREATE ASSEMBLY [itextsharp] FROM
  'C:\MY_CLR\itextsharp.dll'
  WITH PERMISSION_SET = UNSAFE

Then you can find refence for itextsharp in Add reference dialog (SQL Server tab)

It works (VS2010, C#, SQLServer2005, itextsharp5.0)