Creating UDFs in SQL Server 2005

One excellent feature of SQL Server 2005 is its integration with the .NET Common Language Runtime (CLR). A key benefit of this integration is that developers can now create database objects such as stored procedures, user-defined functions (UDFs), and triggers by using modern object-oriented (OO) languages such as Visual Basic .NET (VB.NET) and C#. Let's look at how to create UDFs by using C# and how to leverage Visual Studio 2005 (VS 2005) in simplifying the deployment of UDFs in SQL Server 2005.

Advantages of CLR Integration

In previous versions of SQL Server, database programmers were limited to using T-SQL when writing code on the server side. With CLR integration, database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Developers can leverage CLR integration to write code that has more complex logic and is better suited for computational tasks by using languages such as VB.NET and C#. Both VB.NET and C# are modern programming languages that offer full support for arrays, structured exception handling, and collections. They also offer OO capabilities such as encapsulation, inheritance, and polymorphism, so the code you write with them can be easily organized into classes and namespaces.

Another benefit of code managed by the CLR is type safety. Before managed code is executed, the CLR performs several checks to verify that the code is safe to run. For example, the code is checked to ensure that no memory is read from that hasn't been written to.The CLR also prevents buffer overflows. By default, bothVB.NET and C# always produce safe code. However, programmers have the option of using the unsafe keyword to produce unsafe code that can, for example, directly access memory. For additional information about the differences between T-SQL and managed code, see the sidebar "T-SQL Code or Managed Code?".

4 Steps to a UDF

There are two ways to create a UDF object that you can use in SQL Server 2005.You can take the manual approach: Create the managed class that implements the functionalities of the UDF, compile the class into a .NET assembly, register the assembly with SQL Server, and associate the definition of the UDF with the class method. We'll use the manual method to create a UDF that performs a simple addition operation.

Alternatively, you can use VS 2005 to create a new SQL Server project. After starting the project, you can easily create the UDF and deploy it onto a SQL Server system with the click of a button.We'll use the VS 2005 method to create a UDF that retrieves the name of a category that the user of the function specifies by number.

Create the Managed Class

For the purpose of showing the manual approach, let's create a simple class named Math that has only one method named Add. The Math class code, which Listing 1 shows, starts by importing the required namespaces. After that, it declares a partial class named Math. Partial classes are a new feature in .NET Framework 2.0 that lets you split a single class into multiple source code files. Each team member can work on a separate part of the class, and the system handles merging the separate code files back into a single class at compile time.

Next, the code declares a static method named Add that takes two parameters.The Add method is decorated with the Sql-Function attribute, which indicates that the Add method should be exposed as a UDF to the client application consumer.The Add method simply returns the result of the addition of the two numbers to the caller.

Compile the Class

To compile the class, type a command similar to the following (of course, use a file path appropriate for your system):

csc /target:library D:Projects   SqlMagMath.cs 

This command will result in the creation of an assembly named Math.dll.

Register the Assembly with SQL Server 2005

After you create the assembly, you copy it to a location that the SQL Server system can access.Then you can load it into SQL Server by using the T-SQL CREATE ASSEMBLY command, as follows:

CREATE ASSEMBLY MathAssembly   FROM 'D:ProjectsSqlMagMath.dll' WITH PERMISSION_SET = SAFE 

The CREATE ASSEMBLY command takes a parameter that contains the path to the assembly that will be loaded into SQL Server.This can be a local path, but more often it's a path to a networked file share.When the CREATE ASSEMBLY command is executed, the assembly is copied into the master database.

When loading an assembly into SQL Server, you can specify one of three security levels in which your code can run:

SAFE. This is the default permission and works for the majority of scenarios. When code in an assembly runs under SAFE permission, it uses the in-process managed provider to compute and access data only within the server on which the assembly is running (more about the in-process provider in a moment).

EXTERNAL_ACCESS. This permission level addresses scenarios in which the code needs to access resources outside the server such as files, the network, the registry, and environment variables. When the server accesses an external resource, it impersonates the security context of the user calling the managed code. To create an EXTERNAL_ ACCESS assembly, the creator must have EXTERNAL_ACCESS permission.

UNSAFE. Assemblies with this permission level can call unmanaged code. Because of the privileges UNSAFE assemblies run under, they can also potentially subvert the security system of either SQL Server or the CLR. Only members of the Sysadmin fixed server role can create UNSAFE assemblies.

You can remove a registered assembly by using the DROP ASSEMBLY command, as follows:

DROP ASSEMBLY MathAssembly 

To change an assembly, you must unregister it (by using the DROP ASSEMBLY command), make the change, then re-register the changed assembly (by using the CREATE ASSEMBLY command). Because an assembly persists in the database when the source code for that assembly changes and the assembly is recompiled, the assembly must be dropped from the database and re-added before updates will be reflected in SQL Server database objects.

Associate the C# Method with a SQL Server UDF

So far, we've completed the creation, compilation, and registration steps. Now we need to create the UDF by associating a SQL Server function definition to the appropriate method in the registered assembly. We use the CREATE FUNCTION statement to create a SQL Server UDF, as follows:

CREATE FUNCTION dbo.Addition(  @x int, @y int) RETURNS INT AS EXTERNAL NAME [MathAssembly].[Math].[Add] 

For UDFs, we extend the CREATE FUNCTION statement with the EXTERNAL NAME clause, which essentially links the UDF name to the appropriate method in the .NET assembly. In this example, the Addition UDF is using the assembly named MathAssembly. Within that assembly, it's using the Add method inside the Math class. The three parts of the UDF name are each enclosed in brackets to ensure that they don't conflict with any predefined classes and namespaces in .NET Framework 2.0.

Before executing the UDF, open SQL Server Management Studio and execute the following SQL script to enable managed code execution on the SQL Server system:

EXEC sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE; GO 

If you execute the above UDF from within SQL Server Management Studio, you'll see output similar to that in the lower-right pane in Figure 1.

Using VS 2005 to Create UDFs

I've explained the manual steps you can follow to create and deploy a SQL Server UDF. Now, I'll show you how to use VS 2005 to automate the compilation and deployment of a UDF.

To begin, open VS 2005, select New Project from the File menu, and specify the project name as UserDefinedFunctions, as Figure 2 shows. Because you're creating a database project,VS 2005 will automatically prompt you to either select an existing database reference or add a new database reference. For the purposes of this example, we'll use the Northwind database.

Next, select Add User-Defined Function from VS 2005's Project menu. In the Add New Item dialog box, specify the name of the class as Category.cs and click Add. After creating the class, rename the created class to Category and modify it to look like the code in Listing 2.

Category.cs contains the code required to access data from within a UDF. The code first imports the System.Data.SqlClient namespace so that it can access the types in the new in-process SQL Server .NET data provider. A .NET routine can easily access data stored in the instance of SQL Server in which it runs.The data that the routine can access is determined by the user context in which the code is running. The in-process provider is optimized for working with data inside the SQL Server process. Using the classes and methods of the in-process provider, you can easily submit queries to the database, execute Data Manipulation Language (DML) and Data Definition Language (DDL) statements, and return result sets and messages to client applications.The System.Data.Sql namespace groups the types that make up the in-process provider. This namespace shares many similarities and interfaces with ADO.NET's SqlClient namespace, which is used by developers accessing SQL Server data from managed client and middle-tier applications. Because of this similarity, you can easily migrate code from client applications to server libraries and back again.

Next, at callout A in Listing 2, the UDF is decorated with the SqlFunction custom attribute,which I explained earlier and which is found in the Microsoft.SqlServer.Server namespace. If a function is performing any type of data access,it must set the named parameter DataAccess to DataAccessKind.Read. (The enumeration DataAccessKind accepts only two values—None or Read—because you can't perform updates to a database from a CLR-based UDF.) On the next line, the function is declared as a public static method.

Next, Category.cs establishes a connection to the database by creating an instance of the SqlConnection object, passing in the appropriate connection string. Note that the connection string passed to the constructor of the SqlConnection object is "context connection=true", which indicates that the code will use the context of the logged-on user to open the connection to the database.

The code then opens the connection to the database using the Open() method, creates an instance of the SqlCommand object, and sets its properties appropriately.The UDF also creates a SqlParameter named paramCategoryID to pass the values to the @ CategoryID parameter, sets paramCategoryID's properties, and adds it to the SqlParameter-Collection of the SqlCommand object. Finally, Category.cs executes the SQL statement by calling the ExecuteScalar method of the SqlCommand object and returns the output of the SQL statement directly to the caller.

Before deploying your new UDF,you need to compile, or build, the project. To do so, select Build UserDefinedFunctions from VS 2005's Build menu.VS 2005 will compile all the classes in the project and will report any compilation errors in the Error List pane. After the project is built, you can deploy it on the SQL Server system by selecting Deploy UserDefinedFunctions from VS 2005's Build menu. This action not only registers the assembly on the SQL Server system but also deploys the UDF in SQL Server.

To test the UDF, open Server Explorer by selecting Server Explorer from VS 2005 Studio's View menu. Under the Data Connections node in Server Explorer, open the data connection we specified earlier. Then right-click the Functions node and select Execute from the context menu to open the Run Function dialog box that Figure 3 shows. Enter "1" as the value for the Add method's @CategoryID parameter as shown in Figure 3, and click OK.The function will be executed, and you'll see the output that Figure 4 shows. Note that you can also use VS 2005 to debug your UDF by stepping into the UDF code.

With the upcoming release of SQL Server 2005, you can take advantage of the CLR to create UDFs by using the .NET language of your choice. The .NET languages give you access to the .NET Framework, which provides a rich object model made up of thousands of classes and methods on the server side. Many tasks that were awkward or difficult to perform in TSQL can now be easily accomplished by using managed code.

Thiru Thangarathinam ([email protected]) specializes in architecting, designing, and developing distributed enterprise-class applications by using .NET-related technologies. He is a frequent contributor to leading technology-related online publications, and his most recent book is Professional ASP.NET 2.0 XML (Wrox Press).