.NET CLR Introduction with SQL Server 2005

SQL Server 2005 or later version allows hosting DOT NET CLR (Common Language Runtime) Object in Database. That is, you can write Stored Procedure, Triggers or Function with C# or VB and can host those in your SQL Server. Thus CLR extends SQL Server functionality allowing you to write Database Objects in any programming language of your choice.

First of all, CLR is not enabled in SQL Server by default. You have to enable it. This is straight forward using following SQL Commands.

EXEC sp_configure ‘clr enabled’ , ‘1’
GO

RECONFIGURE
GO

Let’s create a SQL Server Project with Visual Studio 2008. Add Database Reference to it.

Add a User Defined Function, give it a name like MyFirstCLRFunction.cs.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MyFirstCLRFunction()
{
// Put your code here
return new SqlString(“Hello”);
}
};

This above code snippet will be generated. For the time being, we are not going to modify this MyFirstCLRFunction() for simplicity. So, this MyFirstCLRFunction() will just return a string “Hello”. Let’s build it and ensure that MyFirstCLRProject.dll file is in Debug folder.

Click on Deploy to deploy MyFirstCLRProject() CLR Function in SQL Server.

Check out if MyFirstCLRProject() Function has been deployed successfully in Database. However, you can do this deployment using SQL Command as well.

CREATE ASSEMBLY MyFirstCLRProject
FROM ‘C:\Documents and Settings\A Rahim Khan.KHANCOMPUTER\My Documents
\MyFirstCLRProject\MyFirstCLRProject\bin\Debug\MyFirstCLRProject.dll’

GO

CREATE FUNCTION MyFirstCLRFunction()
RETURNS NVARCHAR(100)
AS EXTERNAL NAME MyFirstCLRProject.UserDefinedFunctions.MyFirstCLRFunction

GO

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

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

%d bloggers like this: