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\
SqlMag\Math.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:\Projects\SqlMag\Math.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.
Prev. page
[1]
2
next page 
