database / Project 702

Compiling .Net Languages in Windows

We have spoiled with NMIT’s laptops, which include a whole lot of goodies like Microsoft’s Visual Studio. Well, I have just discovered that the .Net Framework comes with it’s own compiler for various .Net languages including C#. I have been using this for creating C# libraries that can then be called from SQL Server.

An Example:

using System;

public class Hello {

    public static void Main() {

        Console.WriteLine("Hello");
        return;
    }
}

And to compile and run:

user@user $ C:\Windows\Microsoft.Net\Framework64\v4.0.30319\css.exe ~\helloworld.css
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.

user@user $ helloworld.exe
Hello

user@user

To compile a .dll for SQL Server:

user@user $ C:\Windows\Microsoft.Net\Framework64\v4.0.30319\css.exe /target:library ~\helloworld.css
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.

user@user $ dir
Volume in drive C is OS
 Volume Serial Number is 7630-508B

 Directory of ...

20/10/2013  06:07 p.m.    <DIR>          .
20/10/2013  06:07 p.m.    <DIR>          ..
20/10/2013  06:04 p.m.               138 helloworld.css
20/10/2013  06:07 p.m.             3,072 helloworld.dll
20/10/2013  06:05 p.m.             3,584 helloworld.exe

user@user $

Finally, to make this available as a SQL Server database object, you need to enable the Common Language Runtime Environment (CLR), create an assembly that includes the .dll, and finally create a SQL procedure that calls your function (with the appropriate namespaces):

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CREATE ASSEMBLY HelloWorld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

CREATE PROCEDURE hello
AS
EXTERNAL NAME HelloWorld.Hello.Main

EXEC hello

Anyone used to the .Net Framework will recognise the EXTERNAL NAME, … The .NET function can now be called like any other function.

In a future post I will compare the performance of CLR vs. user-defined functions vs. a set-based approach for the calculation of WBGT.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s