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
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
CREATE FUNCTION GetTodaysDate()
RETURNS DateTime
AS EXTERNAL NAME SQLCLR.
CREATE FUNCTION CheckZIPCode(
@ZIP nvarchar(15))
RETURNS BIT
AS EXTERNAL NAME SQLCLR.
|
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.
AssemblyName.
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