|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
Programming a UDF with the .Net CLR
Volume 3 #13 September 14, 2005 Full TOC
by Andrew Novick
Sign up for this newsletter at: http://www.novicksoftware.com/coding-in-sql/coding-in-sql-signup.htm
One of the most anticipated features of soon to be released SQL Server 2005 is the integration of the .Net CLR into the SQL Server engine. This article is going to discuss just what that means and give you a first peak into how write .Net code and use it in SQL Server 2005. If you haven’t heard, SQL Server 2005 will ship on November 7th of this year along with the .Net Framework 2.0, and the new Visual Studio 2005.
The Common Language Runtime (CLR) is the execution environment of .Net. In .Net code is compiled from a high level language such as C# or VB.Net into an abstract Intermediate Language (IL). The framework SDK includes compilers for the C# and VB.Net language, which can be used from the command line. However, most developers use an Integrated Development Environment (IDE), such as Visual Studio to work with high level language code and to invoke the compiler.
Once IL code is compiled it isn’t executed directly. Instead, when the time comes to run compiled code, the CLR is invoked and the IL code is translated into machine language by the Just-In-Time (JIT) compiler. The translated machine code is what executes. However, it just doesn’t run on its own. It runs in within the CLR of the.Net Framework.
It’s the CLR that “hosts” the code that programmers create. And hosting involves much more than just loading the program. The CLR is an environment that executes the code in a very controlled way that includes memory management, security, and many other aspects of running a program. The CLR includes a large class library that .Net programs use to work with the operating system and with other programs, such as databases and web servers.
There are several CLR hosts in use today. Among them are ASP.Net and Windows. SQL Server 2005 is another CLR host. Before we get to how the CLR functions as a host, let’s discuss the others.
When a program written in a .Net language, such as C# or VB.Net is compiled and linked into an EXE file a standard Windows PE file is created. This PE file includes a small amount of compiled Windows code that starts up the CLR, causes the CLR to invoke the JIT compiler and execute the code. This small piece of Windows code is essential because the CLR isn’t really built into Windows but on top of it.
ASP.Net is another environment that hosts the CLR. The worker process host DLLs written to implement the code behind ASP.Net pages. There can be additional hosts and that’s where we come to SQL Server 2005.
SQL Server 2005 hosts the .Net CLR and will load and execute DLLs, which can also be referred to as assemblies. Right now assemblies maybe created in C# or VB.Net. Other .Net languages will probably be supported in the future.
Before doing any .Net coding in SQL Server you must enable the CLR. Along with many other features in SQL Server 2005, the CLR is “off by default”. This is done in an effort to limit security vulnerabilities. Out first piece of code is the script that you’ll need to enable at the CLR. Here it is:
EXEC sp_configure 'show advanced options' , '1'; go reconfigure; go EXEC sp_configure 'clr enabled' , '1' go reconfigure; go
Now let’s write a User-Defined Function (UDF). You don’t have to use Visual Studio to write .Net code for SQL Server but it makes the job much easier. Since this is a newsletter, it’s difficult to show Visual Studio in action so we’ll have to stick to the text.
The first step is to create a “SQL Server Project” in your preferred language, either C# or VB.Net. For most of this newsletter we’ll be using VB.Net. Although I use both languages customer request have led me to use a lot of VB.Net on recent projects. When you have the project created add an item and you to pick one of the types of .Net based database objects that you can create:
I picked a User-Defined Function and Visual Studio used a template to create the framework of a UDF. I’ve changed the name of the function and added some functionality and here’s the function:
Among the key things to pay attention to are the attributes between the angle brackets <> on the line before the function declaration. Attributes are .Net constructs that are added to the Metadata in the assembly that we’re creating. Metadata is non-code information about the code. In this case the attribute specifies that the function RegExMatch is a SqlFunction. The function it self is a simple function that takes an Input string and a pattern, written in the .Net regular expression syntax. Regular expressions are powerful pattern matchers similar to the LIKE operator in SQL but an order of magnitude more powerful. I’ve picked regular expressions for the function because it’s a function that couldn’t be duplicated in T-SQL. As you can see in VB.Net it’s just two lines.
I’ve let Visual Studio compile and deploy the function to the SQL Server. In the process the Assembly (the DLL) is copied into SQL Server and the bits reside in a system table. As part of process of deploying our function into the SQL Server, Visual Studio writes and runs a small bit of T-SQL that creates the SQL UDF and associates it with the .Net function. We’ll look into that in more depth in a future article.
The T-SQL UDF has the same name of the function, ReExMatch. Here’s a script that uses the function. The regular expression pattern that’s in the script matches capitol vowels followed by the letter B. Here’s the script:
select dbo.RegExMatch('EB5cde', '[AEIOU]B') [Test 1] , dbo.RegExMatch('CBdef', '[AEIOU]B') [Test 2] , dbo.RegExMatch('ODBdef', '[AEIOU]B') [Test 3] , dbo.RegExMatch('UBdef', '[AEIOU]B') [Test 4] , dbo.RegExMatch('KBdef', '[AEIOU]B') [Test 5](Results)
Test 1 Test 2 Test 3 Test 4 Test 5 ------ ------ ------ ------ ------ 1 0 0 1 0
This is just the beginning. As mentioned above, there are several other types of .Net database objects and we’ll explore them in the weeks to come.