« Introduction to SQL CLR Extensibility | Main | The Dark Art of Trace Flags »

Adding External References to SQL CLR Projects

A common need when working with SQL CLR assemblies (or projects) is to add a reference to a third-party (or external) assembly. Interestingly enough though (at least as far as I can tell), the steps for doing this are hardly ever documented.

So this blog post will fix that – by documenting just how you go about adding External References in Visual Studio to SQL Server CLR projects or assemblies.

Danger

In my recent webcast: Introduction to SQL CLR Extensibility, I was hoping to cover exactly how to do this. I was also going to (and partially did) throw out a few caveats when doing this.

Specifically: don’t go crazy adding external references. Good code IS reusable code – so good developers may occasionally run into the need to drop of ‘core’ logic into their CLR assemblies. On the other hand, lots of complex business logic just isn’t going to find a good home in your SQL Server’s memory-space; bundling up tons of business rules and the likes into SQL CLR assemblies (or as referenced assemblies) is a great way to bloat your CLR memory usage and, more importantly, will make versioning your code PAINFUL if you start to abuse this power.

Setting the Stage

For this tutorial, I’m going to assume that you’ve already GOT a CLR project/assembly that you’ve already deployed successfully to SQL Server – and that you’ve got it running as needed (minus the external references). If you don’t have that, then watch the webcast from the link provided up above, and it will show you how to get to this ‘assumed’ point.

To that end, if you’re an experience .NET Developer, your first instinct is to right-click on the References folder in Solution Explorer within your solution, and click on the Add Reference option. That in turn brings up the following:

AddCLRReference

The only problem, of course, is that it doesn’t have any ‘browse’ options or tabs. It does let you reference other projects – but even that has some serious limitations too – to the point where even if you were to add your existing ‘solutions’ as projects and try to build them and reference them, you won’t be able to do so.

What’s Going On?

The key to adding external references to a SQL CLR project is understanding that the ‘Add References’ dialog isn’t showing for what’s on YOUR development machine. Instead, it’s polling the SQL Server you’ve CONNECTED TO as part of your project. So, if you’re a developer you’re used to Visual Studio letting you add references to projects and DLLs that exist on YOUR workstation. Then, when you deploy or publish your code, those references are bundled along with your code and copied out to the destination.

SQL CLR projects DO NOT work that way. Instead, they ONLY let you reference assemblies that are already located on the server and which have already been REFERENCED with the server.

Once you know that, the rest is easy.

Adding Reference Assemblies to Your SQL Server

So, since you can only add ‘registered’ assemblies to your projects, the next question is how do you do that?

In this tutorial, I’m going to start with a brand NEW assembly. To do that I’m going to do the following:

Open Up a Brand New Instance of Visual Studio (I’ve already got my existing Solution open).

Create a New Project.

I’m going to Specify the Visual C# Type, and specify that I want to create a Class Library:

NewClassLibrary

I’m going to name this Class Library ‘CoreLogic’:

CoreLogic

Then, in that Class Library, I’m just going to add in some ‘core’ logic that I want to be able to ‘consume’ from my referencing assembly.

In this case, I’m just going to use some patently lame logic – which flips a string around (i.e. it turns ‘string’ into ‘gnirts’):

Then, at that point, I’m going to BUILD my ‘CoreLogic’ project, and then copy the CoreLogic.dll that came from the \bin\Debug\CoreLogic.dll folder within my project out to a location on my server.

From here I can then register the ‘CoreLogic.dll’ on my server – and it will then be available as a potential reference from within my other solution.

To register my assembly, I just need to use CREATE ASSEMBLY as follows:

 CreateAssembly

And note that I’m creating this assembly in the DATABASE that I’ve connected to in my SQL CLR project (i.e., the project where I want to be able to add the external reference).

Full Circle – Adding the Reference to your CLR Project

Now that I’ve created the assembly up on my SQL Server, if I go back to the original SQL CLR project where I want to be able to add a reference to my ‘Core Logic’:

AddReference

I’ll see the reference to CoreLogic show up if I again right click on the References node and select Add Reference:

added

From here I’m able to then add a using statement and so on – just as you would expect with a ‘normal’ .NET project.

Troubleshooting

Depending upon how you’re deploying your SQL Server CLR Assembly or Project, you may run into some ugly issues when you now try to push up your new assembly – and it has references to external resources.

The best way to deal with this in my experience is just to clean house (i.e., destroy your CLR assembly) and then re-register it.

To do this:

1) DROP any sprocs, udfs, triggers, types, etc that DEPEND upon YOUR CLR Assembly.

2) Issue a DROP ASSEMBLY MyAssemblyNameHere command – and you’ll now free up all the ‘references’ that this assembly thinks it has.

3) (With your external assembly already registered/loaded) Issue a CREATE ASSEMBLY MyAssemblyNameHere command – or push ‘Deploy’ from Visual Studio again – and it will push your CLR Assembly up to the server.

4) Recreate any of your sprocs, udfs, triggers, etc that you nuked in step 1.



Comments

This article is fantastic, great info and very clear, just what I needed!

Hi,

I want to add the HtmlAgilityPack.dll to SQL Server CLR Assembly to fix HTML/XML data. I am getting the following error message:
CREATE ASSEMBLY failed because method 'get_MimeTypes' on type 'HtmlAgilityPack.HtmlWeb' in safe assembly 'HtmlAgilityPack' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Is there any way to fix that.
Thanks

you'll need to register it as unsafe

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help... and Also i would like to thank for all the information you are providing on sql.

This is an awesome article.

i am gettin g error while adding mail.dll


Msg 10301, Level 16, State 1, Line 1
Assembly 'Mail' references assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.


 

Post a comment

(Not displayed - [privacy policy])

Remember personal info?