Implement User-defined Functions in SQL Server 2005 with Managed Code

One of the excellent features of SQL Server 2005 is its integration with the .NET common language runtime (CLR), which extends SQL Server’s capability in several important ways. The integration enables developers to create database objects such as stored procedures, user-defined functions, and triggers by using modern object-oriented languages such as VB.NET and C#. This article explains how to create user-defined functions using C#. It then tests the user-defined function using the SQL Server integration features of Visual Studio 2005. Finally, it creates a Windows Forms client application to test the user-defined function.

Introduction

Although Transact-SQL (T-SQL), the existing data-access and manipulation language, is well suited for set-oriented, data-access operations, it also has limitations. Microsoft designed it more than a decade ago, and it is a procedural language rather than an object-oriented language. The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. The SQL Server engine hosts the CLR in process. All managed code that executes in the server runs within the confines of the CLR. The managed code accesses the database using ADO.NET and the new SQL Server .NET Data Provider.

User-defined functions that return scalar types must return a .NET data type that can be implicitly converted to a SQL Server data type. Scalar functions written with the .NET Framework can significantly outperform T-SQL in certain scenarios because, unlike T-SQL, .NET functions are created using compiled code. User-defined functions can also return table types, in which case the function must return a result set.

Before learning to create user-defined functions, read the following section to gain an understanding of the advantages that CLR integration with SQL Server offers.

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. Both Visual Basic .NET and C# offer full support for arrays, structured exception handling, and collections. With these languages, developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks. Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can now be easily organized into classes and namespaces.

Managed code is better suited than T-SQL for number crunching and complicated execution logic, and it features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers now have access to thousands of pre-built classes and routines, which can be accessed easily from any stored procedure, trigger, or user-defined function. The BCL includes classes that provide functionality for improved string functioning, advanced math operations, file access, cryptography, and more. While many of these classes are available for use from within SQL CLR code, those that are not appropriate for server-side use (for example, windowing classes) are not available.

One of the benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks to ensure that the code is safe to run. This process is known as verification. For example, it checks the code to ensure that memory that has not be been written to is not read. The CLR also prevents buffer overflows. By default, both Visual Basic .NET and C# always produce safe code. However, C# programmers have the option of using the unsafe keyword to produce unsafe code that, for example, directly accesses memory.

What Is an Assembly?

An assembly is the unit of deployment for .NET objects with the database. To create objects, you must write managed code that implements the functionalities of the user-defined function. Once the code is written, you compile it into a .NET assembly and then deploy it onto SQL Server. You can accomplish this in one of the following two ways:

• The most common and simple way is by using Visual Studio 2005 to create a new SQL Server Project. After creating the project, you then can very easily create the user-defined function and deploy it onto SQL Server with the click of a button, thereby eliminating the manual registration step.
• Create a Visual Studio Class library project and compile the class library into an assembly. Once the assembly is created, you then can register the assembly with SQL Server and then associate the definition of the user-defined function with a specific method in the assembly.

The second approach is a manual step and the first approach is an automated process that eliminates most of the steps. The following sections look at each of these methods.

Manual Registration and Deployment of Assembly

To utilize the manual registration and deployment of assembly approach, you need to go through the following steps:

1. Create the managed class that implements the functionalities of the user-defined function.
2. Compile that class using the .NET language compiler to produce an assembly.
3. Register the assembly with SQL Server using the Create Assembly statement.
4. Associate the definition of the user-defined function with the specific method in the class.

After you create the assembly, you then can load it into SQL Server using the T-SQL CREATE ASSEMBLY command:

CREATE ASSEMBLY MyAssembly FROM 'C:TempMyAssembly.dll'

The CREATE ASSEMBLY command takes a parameter that contains the path to the assembly you will load into SQL Server. This can be a local path, but more often it is 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 also have the ability to specify one of three different levels of security in which your code can run:

• SAFE—SAFE is the default permission set and works for the majority of scenarios. When code in an assembly runs under the SAFE permission set, it can do computation and data access only within the server via the in-process managed provider.
• EXTERNAL_ACCESS—This is a code permission set that addresses scenarios where the code needs to access resources outside the server (such as the files, network, Registry, and environment variables). Whenever 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 needs to have EXTERNAL ACCESS permission.
• UNSAFE—Assemblies can call into unmanaged code, and they are not verified to be type-safe.

To specify a given security level, you modify the syntax of the CREATE ASSEMBLY statement:

CREATE ASSEMBLY <AssemblyName>
FROM '<Path>'
WITH PERMISSION_SET = <Permission_Set>

For example, to specify that an assembly should run with EXTERNAL_ACCESS permissions, you simply change the permission set that is specified when loading the assembly:

CREATE ASSEMBLY MyAssembly
FROM 'C:TestMyAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

After registering the assembly, you also can remove the assembly by using the DROP ASSEMBLY command:

DROP ASSEMBLY MyAssembly

Because an assembly is persisted in the database when the source code for that assembly changes and the assembly is recompiled, you first must drop the assembly from the database (using the DROP ASSEMBLY command) and then re-add it (using the CREATE ASSEMBLY command) before the updates will be reflected in SQL Server database objects.

So far, you have completed the creation, compilation, and registration steps. Now, you need to associate the SQL Server function definition to the appropriate method in the previously registered assembly. The following example illustrates how you can use the CREATE FUNCTION statement to create a .NET user-defined function:

CREATE FUNCTION MyFunction()
RETURNS INT AS
EXTERNAL NAME
MyAssembly:[MyClass]::MyFunction

For user-defined functions, the CREATE FUNCTION statement has been extended with the EXTERNAL NAME clause, which essentially links the user-defined function name to the appropriate method in the .NET assembly. In this example, the MyFunction method uses the assembly named MyAssembly. Within that assembly, it’s using the MyFunction method inside the class named MyClass. The syntax of the External name is as follows:

Assembly Name:[AssemblyNamespace.TypeName]::MethodName

Now that you have seen the manual approach, see how Visual Studio can aid you in eliminating this manual step.

Implementing User-Defined Functions Using Visual Studio 2005

So far, you have seen the steps involved in creating and deploying a SQL Server user-defined function using the manual steps. This section shows you how to use Visual Studio 2005 to automate the compilation and deployment of the user-defined functions.

To start, create a new SQL Server Project using the File->New Project menu in Visual Studio 2005 and specify the project name as UDFExamples. (See the following screenshot.)

Because you are creating a database project, you need to associate a data source with the project. To this end, Visual Studio automatically prompts you to either select an existing database reference or add a new database reference. For the purposes of this example, use a database called Test. (See the following screenshot.)

Once you’ve created the project, select Project->Add User-Defined Function from the menu. In the Add New Item dialog box, enter Add.cs and click the Add button. (See the following screenshot.)

After you’ve created the class, modify the code in the class to look like the following:

using System;
using System.Data.Sql;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
   [SqlFunction]
   public static SqlInt32 Add(int x, int y)
   {
      return x + y;
   }
};

The above code starts by importing the required namespaces. It then declares a partial class named UserDefinedFunctions. (The partial class is a new feature in .NET Framework 2.0. It gives you the ability to split a single class into more than one source code file. This feature is very helpful when more than one user works on the same class. Without this concept, you would have to use source code management features such as check-in/check-out and merge changes for multiple programmers to work on a single class. With partial types, each team member can work on a separate part of the class, and the system will handle merging the separate code files back into a single class at compile time.)

After that, the code declares a static method named Add that takes in two parameters. The Add method is decorated with the SqlFunction attribute, which specifies that the Add method be exposed as a user-defined function to the consumers. Inside the Add method, it simply returns the result of the addition of the two numbers to the caller.

Now that the user-defined function is created, deploying it is very simple and straightforward. Before deploying it, you need to build the project. Select Build->Build UDFExamples from the menu, which compiles all the classes in the project. Any compilation errors will be displayed in the Error List pane.

Once the project is built, you then can deploy it on SQL Server by selecting Build->Deploy UDFExamples from the menu. This will not only register the assembly but also deploy the user-defined function in SQL Server.

Testing the Function

To test the function, bring up Server Explorer by selecting View->Server Explorer from the menu. In the Data Connections node, open the data connection you specified earlier. Then, open the Functions node. Right-click on this node and select Execute from the context menu. This will bring up the following dialog box, which you can use to enter values for the Add method’s parameters.

Enter the values for the @x and @y parameters as shown above and click OK. You will see the following output in the Output dialog box.

So far, you learned how to create a simple user-defined function and test it using Visual Studio 2005. The next section looks at an advanced user-defined function that performs data access from within a user-defined function.

Creating a User-Defined Function that Performs Data Access

This section examines one more scalar user-defined function example that retrieves data from a SQL Server table and returns it to the caller. First, consider the important namespaces related to accessing data in the SQL Server 2005 database.

Understanding the In-process Managed Provider

A .NET routine can easily access data stored in the instance of SQL Server in which it runs. The data the routine can access is determined by the user context in which the code is running. The in-proc provider is optimized for working with data inside the SQL Server process. By using the classes and methods of the in-process managed provider, you can easily submit queries to the database, execute DML and DDL statements, and return result sets and messages to client applications. The System.Data.SqlServer namespace groups the types that make up the in-proc 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.

Three important classes in the System.Data.SqlServer namespace are specific to the in-proc provider:

• SqlContext—This class encapsulates the other extensions. It also provides the transaction and database connection, which are part of the environment in which the routine executes.
• SqlPipe—This class enables routines to send tabular results and messages to the client. This class is conceptually similar to the Response class found in ASP.NET in that it can be used to send messages to the callers.
• SqlTriggerContext—This class provides information on the context in which a trigger is run, including the inserted and deleted tables accessible from T-SQL triggers.

Implementation of User-Defined Function

For the purposes of this example, create a new table whose declaration looks like the following:

CREATE TABLE Users(ID int, Name Varchar(50))

In the user-defined function, you return the name of the user based on the user’s supplied ID. To create this function, select Project->Add User-Defined Function from the menu and specify the name of the user-defined function file as GetUserNameByID.cs. Once the file is created, modify the code in the class to look like the following:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString GetUserNameByID(int id)
    {
        SqlCommand cmd    = SqlContext.GetCommand();
        cmd.CommandText   = "SELECT Name FROM Users 
            WHERE ID      = " + id.ToString() ;
        SqlDataRecord rec = cmd.ExecuteRow();
        string name       = rec.GetString(0);
        return name;
    }
};

In the declaration section, the System.Data.SqlServer is imported so that you can access types in the in-proc provider. Next, the function is decorated with the [SqlFunction] custom attribute, which is found in the System.Data.Sql namespace. On the next line, the target method is declared as a public static method. The SqlContext class, located in the System.Data.SqlServer namespace, is then used to access the SqlCommand object with a connection to the SQL Server instance already set up.

The following two lines of code should look familiar to developers who have written client applications that use the types found in the System.Data.SqlClient namespace:

cmd.CommandText = "SELECT Name FROM Users
                   WHERE ID = " + id.ToString() ;
   SqlDataRecord rec = cmd.ExecuteRow();

The appropriate command text is specified by setting the CommandText property of the SqlCommand object returned by the call to SqlContext.GetCommand. Next, the ExecuteRow method of the SqlCommand object is called. This returns a value of type SqlDataRecord. SqlDataRecord is a new object that is introduced in ADO.NET 2.0. You can use this object to represent a specific record in the database. Once you have the record, you then can access the first string value column in the SqlDataRecord object by using the GetString method.

Now that you’ve created the user-defined function, you can build and deploy it using Visual Studio 2005. Once the deployment is completed, you can then test it, which the next section covers.

Testing the User-Defined Function Using a Windows Forms Application

In this example, you will test the function from a Windows forms client application that is created using Visual C#. (See the following screenshot.) Name the project UDFExamplesClientApp.

Next, add a label control, a textbox control, and a command button to the form. In the Click event of the command button, add the following lines of code:

private void btnInvoke_Click(object sender, EventArgs e)
{
   using (SqlConnection connection = new SqlConnection())
   {
      using (SqlCommand command = new SqlCommand())
      {
         connection.ConnectionString =
            @"Server=(local)SQLExpress;Integrated Security=
            True;Database=Test;Pooling=False";
         //Set the SqlCommand object's properties
         command.CommandType = CommandType.Text;
         string userID = txtUserID.Text;
         command.CommandText = "SELECT dbo.GetUserNameByID(" + 
            userID + ")";
         command.Connection = connection;
         connection.Open();
         DataSet userNameDataSet = new DataSet();
         SqlDataAdapter adapter = new SqlDataAdapter(command);
         adapter.Fill(userNameDataSet);
         string userName =
            (string)userNameDataSet.Tables[0].Rows[0][0];
         lblResult.Text = userName; 
      }
   }
}

With the above code, you do the following:

1. Create an instance of SqlConnection in a using block and then create the SqlCommand object.
2. Set the ConnectionString property of the SqlConnection object to a valid connection string. (Because you are using the SQL Server Express that is supplied with Visual Studio 2005, you specify integrated authentication in the connection string.)
3. Set the CommandType property of the SqlCommand object to CommandType.Text to indicate that you want to execute a SQL statement.
4. Set the CommandText property to the name of the user-defined function. (To the user-defined function, you also supplied the value entered by the user in the textbox as an argument.)
5. Create instances of the DataSet, and SqlDataAdapter objects. (To the constructor of the SqlDataAdapter object, you also supplied the previously created SqlCommand object as an argument.)
6. Execute the user-defined function by invoking the Fill method of the SqlDataAdapter object.

Once the user-defined function is executed and the dataset populated with the results of the query execution, you then can retrieve the result by navigating through the DataTable that is contained in the DataSet object.

If you execute the above code and click the command button, you will see the following screen. The label control displays the user name returned by the user-defined function.

Choosing Between T-SQL and Managed Code

When writing stored procedures, triggers, and user-defined functions, programmers now will have to decide whether to use traditional T-SQL or a .NET language such as Visual Basic .NET or C#. The correct decision depends upon the particular situation. In some cases, you should use T-SQL; in others, you should use managed code.

T-SQL is best in situations where the code will mostly perform data access with little or no procedural logic. Managed code is best suited for CPU-intensive functions and procedures that feature complex logic, or where you want to leverage the .NET Framework’s Base Class Library. Code placement is another important fact to consider. You can run both T-SQL and in-process managed code on the server. This functionality places code and data close together, and allows you to take advantage of the processing power of the server.

On the other hand, you may wish to avoid placing processor-intensive tasks on your database server. Most client machines today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client. While T-SQL code cannot run on a client machine, the SQL Server in-process provider was designed to be as similar as possible to client-side managed ADO.NET, enhancing the portability of code between server and client.

From T-SQL to Managed Code

With the release of SQL Server 2005 Beta 2, database programmers can now take advantage of the rich functionality of the .NET Base Class Library and the CLR. By uing CLR integration, you can create your user-defined functions using the .NET language of your choice. This will allow you to utilize the .NET Framework, which provides thousands of classes and methods on the server-side. Many tasks that were awkward or difficult to perform in T-SQL now can be easily accomplished using managed code.

About the Author

Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.