FAQ: How do I write objects in Managed code, the beta 1 samples/docs on the web don’t work with SQL Express?


The syntax for much of SQLCLR was changed between B1 and the current builds to make it more consistent. Building SQLCLR objects in Visual Studio 2005 is really easy as we have new project types, but the Express SKUs of VS do not include this project type so the code has to be written by hand. The below is a sample from Ramachandran Venkatesh one of the PMs on the SQLCLR team in SQL Server.

-Euan Garden

Product Unit Manager

SQL Server Tools

***C# Sample***

Here is a soup-to-nuts sample to build a hello world function in C#, illustrating the basic steps of creating a source file, compiling it, registering the assembly, creating a function over it, and then invoking the function.

public class c
{
    public static string HelloWorld() { return “hello world”; }
}
save to hello.cs,

csc /t:library hello.dll hello.cs

***The following code needs to be executed in a query editor***

create assembly hello from ‘c:\hello.dll’
go

create function hello() returns nvarchar(129) as external name
[hello].[c].[HelloWorld]
go

select dbo.hello()
go

Ofcourse this is just the tip of the iceberg, in terms of the functionality, but you get an idea of the basic sequence.

Comments (10)

  1. Kevin Daly says:

    Thanks for that, that’s cool.

    Will there be any guidance soon on writing UDTs?

    It’s great having the dedicated project type in the full version of Visual Studio 2005 (blah Beta 1 blah blah), but even with that it’s not immediately obvious what we need to provide so that a type serialises appropriately to the database and so on (my one experiment so far got me a "memory violation". Ouch), and what rules govern what we can and can’t include.

    This may all be in the full MSDN library documentation, but if I download that on top of what I’ve already downloaded (Visual Studio 2005 Beta 1), that’s going to max out my international download cap for the month. Sigh.

    So would there be any hints?

  2. Sam Gentile says:

    There is a little error in you command line. It wont compile like that. It should be:

    >csc /t:library hello.cs

    to produce hello.dll or I guess you could have used the /out

  3. Euan Garden says:

    Kevin,

    Yes there will be guidance, some of it will come with SQL Server 2005 B2 and some will come slightly later.

    BTW If you have MSDN membership everthing will come on CD/DVD fairly soon, VS and SQL Server

  4. Euan Garden says:

    Sam,

    Thanks for fixing the typo

  5. TINY SQL CLR FUNCTION (SQL Server 2005 Express)

  6. TINY SQL CLR FUNCTION "types do not match"(SQL Server 2005 Express)

  7. Very nice example.

    I created a new version that emphasizes the different components (namespace, c# class, c#method, sql assembly, sql function) to help slow learners like me 😉

    Here is the source code in case any one else is interested on it:

    http://www.hectorcorrea.com/DotWiki.htm?topic=UsingSqlExpress