.NET in SQL Server 

Last month we looked at the upcoming MS SQL Server 2005 Beta 2. In this version, the programmability of SQL Server is not anymore restricted only to SQL statements. You can now create solutions in SQL Server using any .NET language. There are many advantages of doing so. Firstly, you get a back end framework that is tightly integrated with your front end framework, giving you better control over the database. Secondly, in many cases, a .NET version of a particular functionality can scale and perform better than a local T-SQL version of the same due to the pre-compiled .NET code that resides within the SQL Server memory space. However, a set of SQL statements might require loading, compiling and executing during a call.

We look at some simple examples of tasks that you can do with SQL Server 2005 using the .NET 2.0 framework (both these technologies are currently in various stages of beta releases). You will need SQL Server 2005 Beta 2 (not SQL Server Express) and VS.NET 2005 Beta 1 or above. 

First, fire up VS.NET 2005 and create a new Database>SQL Server Project in a location and language (C#/VB.NET) of your choice. Now, you will get a new solution where you can add

different types of SQL Server 'types'. Let's add a couple of simple UDFs (User Defined Functions) in .NET, deploy them to the SQL Server and then test them. We use VB.NET here.

Right click on the project you just created and select Add>User Defined Function, and name it, MyFunctions.vb. The new file that gets created shows that there are a few new namespaces already imported for you as in:

Imports System



Imports System.Data.Sql


Imports System.Data.SqlTypes

There's a new class that you can rename to MyFunctions and a tag:

_



Public Shared Function Function1() As SqlString


' Add your code here


Return New SqlString("Hello")


End Function

The .NET assembly loaded within the SQL Server Management Studio

The tag defines the function following it to be a UDF in SQL Server. We remove this one and add two other UDFs as:

_



Public Shared Function GetTodaysDate() As Date


Return System.DateTime.Today


End Function

_



Public Shared Function CheckZIPCode(ByVal ZIP As String) As Boolean


Return System.Text.RegularExpressions.Regex.IsMatch _


(ZIP, "^\s*(\d{5}|(\d{5}-\d{4}))\s*$")


End Function

This defines two new UDFs- first, returning current date and the second that checks whether the string matches a ZIP code pattern and returns true or false. 



Let's now deploy these functions to SQL Server. There are two ways of doing this. One, where you right click on the solution in VS.NET and select Deploy. This compiles the code and deploys the assembly to SQL Server. The other is by using extended T-SQL statements to register the components yourself. And this is how you do it.

Once you build the code in VS.NET you get a new assembly (.DLL) within your project folder. To register this within SQL Server, open up SQL Management Studio, connect to the server, and open a new query window into the database into which you wish to add this assembly. Now, issue the statement (change the path as required to point to your compiled assembly) as:

CREATE ASSEMBLY SQLCLR From 'C:\SQL\SQLCLR\SQLCLR\bin\SQLCLR.dll'

This will register the assembly in your database and you can even see it from | Databases | | Programmability | Assemblies | . Next register the actual functionality you want-in this case the two UDFs you just created. For this, use the following statements.

CREATE FUNCTION GetTodaysDate()



RETURNS DateTime


AS EXTERNAL NAME SQLCLR..GetTodaysDate





CREATE FUNCTION CheckZIPCode(


@ZIP nvarchar(15))


RETURNS BIT


AS EXTERNAL NAME SQLCLR..CheckZIPCode

Running the test script deploys the assembly and runs the script, which allows you to view the results in the Output window in VS.NET 2005

These statements are extended versions of the normal Create Function SQL commands. In this, you need to supply the name of the new function, the return type, any parameters and their types, as well as the reference to the external assembly and exact function within that. The

SQLCLR..GetTodaysDate refers to

AssemblyName..FunctionName. 

So how do you test whether the functions actually work or not? Again, this is very simple. If you have deployed the assembly using VS.NET, you can simply use it again to test it.

Right click on the project and select Add test script. This creates a new folder called TestScripts and a file within that, where you can put in test scenarios. For this case, you can put in:

SELECT dbo.GetTodaysDate()



SELECT dbo.CheckZIPCode('AAAAA')


SELECT dbo.CheckZIPCode('12200')

Save the file, right click on it and select Set as default test script. Now press the green arrow to run the solution. This deploys the entire solution as well as the test script and displays the results of the script. You can also, of course, simply put the same set of SQL statements in a query window in SQL Management Studio and view the same results.

Vinod Unny Enterprise InfoTech