As a user of Visual Studio Standard 2005, I miss some of the nice stuff like integration with SQL Server when it comes to deploying CLR objects.
I recently, had a fairly sophisticated query to write, and I was trying to avoid multiple trips to and from the web server and the database server, for performance reasons. The original code was calling multiple procedures from a fairly involved loop in code. Despite every attempt to rationalise and tune it, it was "slow man". After some investigation I decided that the multiple trips between servers must be removed.
With out going into the ins and outs of how to write CLR procedures, this quick article shows you how to deploy them locally and to remote servers without the need for a Visual Studio 2005 that supports CLRSQL deployment.
Firstly write and build your DLL (i.e. a class library). Mine is called ClrProc.dll for this example. It contains a class (type) called Procs and a single method called DoIt().
For ease of this demo I have copied my dll to the root of C. That is C:\ClrProc.dll.
Open SQL Server Management Studio and open a new query window targeting the appropriate database.
Enter the following to register the assembly with SQL Server 2005.
CREATE ASSEMBLY MyClrProcAssembly from 'c:\ClrProc.dll' WITH PERMISSION_SET = SAFE
This has registered the assembly and if you drill down into the Assemblies branch of that database you should see it (you may need to refresh).
Now we need to register the method within the assembly as a procedure. Type the following:
CREATE PROCEDURE procDoIt AS EXTERNAL NAME MyClrProcAssembly.[MyClrProcAssembly.Procs].DoIt
Note that in the above the format of the 3rd line is assembly.type.method. However, sometimes it is required to include the fully referenced Assembly.Type for the "Type" part of this line. As I haven’t been able to work out why it’s sometimes needed I have got into the habit of doing the fully qualified version. So that actual format of that 3rd line is Assembly.[Assembly.Type].Method
Ok, that’s done. Set your permissions accordingly and you’re good to go.
Now to deploy this to a production server.
The easiest way is to right click the assembly and create a "create script" which can then be executed on the production machine. Note you don’t need access to the remote SQL Server’s file system so this is a neat way of doing it on a shared hosting scenario.
Then go ahead and script your procedures to create them on the target machine as well.
Finally note, that CLR functionality is turned off by default so this must be enabled. Ask your host of they support them first. Secondly, you must have CREATE ASSEMBLY permissions on the servers involved.