Giving SQL some Class

One of the fancy new features in SQL 2005 is the ability to run Managed Code (C# or what-have-you) on SQL Server. That's where this blog gets its name. We will be exploring many facets of this integration but first, let's just jump in with a simple "Hello World" exercise. SQL treats your class code like any other object in the database, first you have to code the class and build into an assembly. Then you deploy your assembly to SQL using the CREATE ASSEMBLY statement. To build an assembly and a class we need some flavor of Visual Studio. Just for fun, I'm going to use Visual C# 2005 Express because it's free (and only a 30MB download) and I've never checked it out. The Installer gives you the option of installing SQL Express, but that's just going to confuse me so I skip that. I'll also skip the 200+MB MSDN library install because I'll be getting my help online.

Firing up the C# IDE I'm creating a new Class Library Project called HelloCLR. I'll Code a simple class called Say and a public property called Hello. It looks something like this:

using System;
using System.Collections.Generic;
using System.Text;

//please notice there is no Namespace defined
//more on this later

public class Say {
public static string Hello() {
return "Hello From Managed Code!";
}
}

I'll need to create a place to put this assembly, so I create a folder on my SQL Server box and grant myself some rights to it. In a production environment, we would not be creating shares on the SQL Server, but for now it's OK. Later on we'll talk about deployment issues. Now I'll build my solution and move it out to my deployment drop point.

C:\Projects\HelloCLR\HelloCLR\bin\Release\HelloCLR.dll
…Move this to…
\\NoSQL2005\DeployDropPoint

We are done coding, so fire up SQL Server Management Studio on the client machine and attach to our SQL Server instance. I notice I don't even have a testing database set up, so I'll create a new one that will hold all our exercises and call it SqlClrDB just for fun. Once selected in the Object explorer, I'll hit the New Query button. And attempt to add my assembly to the database with the following script:

create assembly HelloCLR
from 'c:\DeployDropPoint\HelloCLR.dll'

My command completed successfully. Now to access my Hello() method I'm going to have to wrap it in a SQL Function. So let's do that:

create function HelloClr() returns nvarchar(max) as external name HelloCLR.Say.Hello

and finally to see the fruits of out labor:

select dbo.HelloCLR()

but I got an error instead!

Msg 6263, Level 16, State 1, Line 1

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Such a bummer. Now what? Seems the default install is to disable the .Net Framework. One way to turn this on is to remote into your SQL Server and run the Surface Area Configuration tool. There is a CLR Integration option that you can enable. If that sounds like too much fooling around with the mouse, you can also issue this:

sp_configure 'clr enabled', 1
go
reconfigure
go

and finally, when we re-issue the call to our function:

select dbo.HelloCLR()

------------------------

Hello From Managed Code!

(1 row(s) affected)

 

What did we learm from this exersize?

1. You can load your Managed Code Assemblies into SQL Server 2005.
   
2. You can call static methods from you classes if you wrap them in a SQL Function.
   
3. We hope to learm more about namespace and how they affect assemble loads.
   
4. The CLR is disabled by default when you install SQL Server 2005.
   
5. There are deployment issues moving your assembly to a location where SQL Server can see them.
   
6. Hello world in the SQLCLR world is just as boring as it is in any new platform.