Database Design

Build SQL CLR Objects With .NET

Author Doug Thews introduces you to writing stored procedures and UDFs in .NET 2.0 and SQL Server 2005.

Technology Toolbox: VB.NET, Visual Studio .NET 2005, SQL Server 2005

One of the most anticipated features in SQL Server 2005 (code-named Yukon) and Visual Studio .NET 2005 (code-named Whidbey) is the capability to develop stored procedures, user-defined functions (UDFs), and user-defined data types in .NET 2.0. SQL Server 2005 now supports developing UDFs, user-defined procedures (UDPs), user-defined triggers, and user-defined data types in any .NET 2.0 Common Language Runtime (CLR)-compliant language.

In this article, I'll provide an introduction to writing stored procedures and functions in .NET 2.0. While the scope of CLR integration with SQL Server 2005 could take up an entire book, I'll focus on how the CLR works within SQL Server 2005 and how to start writing and deploying CLR code to SQL Server 2005.

The code in this article is based on beta versions for both Visual Studio .NET 2005 (beta 1) and SQL Server 2005 (beta 2). As with any beta product, there is a slight chance that some of the functionality or namespaces might change before the final products are released. I've tried to be as careful as possible: I've worked with the internal beta teams at Microsoft and have purposely stayed away from various features and internals that are likely to change between now and both products' final release.

Getting both SQL Server 2005 and VS.NET 2005 installed on the same machine is pretty straightforward, with one exception. The beta 2 build of SQL Server 2005 uses a minor-version upgrade of the .NET 2.0 Framework. Therefore, you should install SQL Server 2005 first so that the higher .NET Framework is installed. (The VS.NET 2005 install won't overwrite it because it detects that the .NET 2.0 Framework is installed already.) If you install VS.NET 2005 first, you'll need to uninstall the .NET 2.0 Framework and then install SQL Server 2005. Microsoft says that both versions will use the same framework by the time they're released, so this should only be an issue for developers who work with the betas.

Once you've completed the installations, it's time to write some code to see what you can do. To test out how the CLR inside of SQL Server 2005 actually works, I'll show you how to write a simple stored procedure that just returns the current version of SQL Server 2005.

First, bring up VS.NET 2005 and create an empty solution. I've called mine VSM122004 and included it with the downloadable source code for this article. Next, select Add | Project for the solution, and select SQL Server Project from under the SQL Server project types (see Figure 1).

For purposes of this exercise, I'll be using the SampleProcedures project to hold all user-defined stored procedures, the SampleFunctions project to hold all UDFs, and the SampleTriggers project to hold all user-defined triggers.

Once you add a new project, VS.NET 2005 asks you to create a database reference for that specific SQL Server project (see Figure 2). It's not mandatory you add a database reference because assemblies run in-process with SQL Server 2005 (meaning they don't need their own connections), but when you add one, it helps you browse for SQL Server objects in the IDE.

Create a Stored Procedure Class File
Now that you've created the project, create a simple stored procedure class file by selecting Add | Stored Procedure and call it SimpleStoredProc.vb. Notice the IDE creates a stub:

Partial Public Class StoredProcedures
<SqlProcedure()> _
	Public Shared Sub SimpleStoredProc()
	' Add your code here
End Function

Any Function or Sub must be declared as Public Shared within the class to be deployed as a SQL CLR object. The IDE also generates a Partial Public Class called Stored Procedures. If you create another stored procedure using the previous steps, you'll get another Partial Class. When they're compiled, these classes are merged into a single class called StoredProcedures, which enables you to keep your stored procedures in a single class file, or separate them into functionality-specific class files. The end result is a single assembly per project to deploy to SQL Server 2005.

Don't be confused about the terminology of "function" and "procedure" in SQL Server vs. VB.NET. In SQL Server 2005, a stored procedure can take parameters by value or by reference and perform some action, possibly returning a set of rows to the caller. UDFs in SQL Server 2005 (scalar UDFs and table-valued functions, or TVFs) take parameters by value only and can return either a scalar value (such as a string) or a table value (such as a set of rows and columns), as well as return a set of rows (like the stored procedure does).

If you're using VB.NET, you'll need to use a Function instead of a Sub if you want to return a value directly from the UDF. Of course, both SQL stored procedures and functions can also return a data resultset as well, but that is done through the SqlPipe class and not a traditional return parameter. I'll talk about how to do this later on in the article.

Now, you'll add some code so your stored procedure returns the SQL Server version information. I've included the complete class module that contains the definition of the SimpleStoredProc method (see Listing 1).

There's a lot here to talk about, so I've broken it down one step at a time. First, look at the <SqlProcedure()> decoration attribute for the method:

<SqlProcedure()> Public Shared Sub SimpleStoredProc()

This decoration is used to define information about the stored procedure object. The VS.NET 2005 IDE uses it to determine how the assembly is cataloged and how the stored procedure is created when deploying from within the IDE.

Take a look at the code within the method that will become your stored procedure. First, the current execution context is retrieved, which gives you the ability to run commands, look at the current connection, and perform anything related to the current SQL execution context. Remember, because this code is running in-process within SQL Server, you don't need to create a separate database connection—you're running under the context of the code that is calling the stored procedure. If you need access to the connection to do something like implement a transaction, you can access the current execution context's connection:

Dim objSqlConnection as SqlConnection = SqlContext.GetConnection()

The SqlContext class's GetCommand() method retrieves a command object for you, which you can now use to create your own database commands (just as you do in ADO.NET). In this example, the query just selects the version of SQL Server using the standard SQL statement:

SELECT @@VERSION

Finally, you'll need to get the query's output back to the caller after executing the SQL command. The standard way to return query results from a stored procedure is by using the SqlPipe class. You call the GetPipe() method to get the current context's pipe back to the caller, then use that pipe's Send() method to send back results to the stored procedure caller.

Catalog the Assembly in SQL Server 2005
Now that you've developed the stored procedure, it's necessary to catalog and create it inside SQL Server 2005. This is a two-phased approach. First, you need to catalog the assembly; and second, you need to create and map stored procedures, UDFs, or triggers to the methods inside the cataloged assemblies.

The SQL Data Definition Language (DDL) that does this for your SimpleStoredProc method looks like this:

CREATE ASSEMBLY MySQLSampleProcedures
FROM 
'C:\Development 
Area\VSM122004\SampleProcedures\bin\SampleP
rocedures.dll'
WITH PERMISSION_SET=SAFE

This creates an assembly inside of the AdventureWorks sample database that's created when you install SQL Server 2005 (assuming it's the current database when you run the script). I've given the assembly name used inside of SQL Server 2005 (MySQLSampleProcedures) a different name than the physical assembly name of the VS.NET 2005 project to show the naming differences between VS.NET 2005 and SQL Server 2005. Notice that assemblies are loaded within the current database. If you want to load a CLR object from an assembly in another database, you need to register that assembly in the current database, or change the CREATE [PROCEDURE|FUNCTION|TRIGGER] command to map to the assembly within the other database. (Additionally, the user ID cataloging the assembly must have access to the other assembly and database, and both assemblies must be cataloged under the same user ID or a common security role.)

The PERMISSION_SET property tells SQL Server 2005 what kind of security this object has. There are three possible choices (see Table 1).

If your code violates the permission set it was cataloged with, a SQL security exception will be thrown. Also, you can't access anything from the System.Windows.Forms namespace within a SQL cataloged assembly.

Once you catalog an assembly, SQL Server 2005 stores information about CLR assemblies in these tables (see Table 2). Perform this query in SqlQuery if you want to see your assembly:

SELECT Content FROM sys.assembly_files
WHERE name="<Path & Filename of Assembly 
Cataloged>"

This gives you your assembly's binary CLR code cataloged in SQL Server. You can also deploy your assembly with the VS.NET 2005 IDE. When you're deploying through the VS.NET 2005 IDE, the assembly, debug symbols (if DEBUG is ON), the actual source code, and the project file are imported. You can change the PERMISSION_SET value inside VS.NET 2005 by editing the Properties page of your SQL Server project. VS.NET 2005 will use this setting when it's deploying from within the IDE. For this article, you'll catalog and create your CLR objects manually to become more familiar with the inner workings of CLR objects within SQL Server 2005.

You can add your debug information manually after cataloging the assembly by using the ALTER ASSEMBLY statement:

ALTER ASSEMBLY MySqlSampleProcedures
ADD FILE FROM
'C:\Development 
Area\VSM122004\SampleProcedures\bin\SampleProcedures.pdb'

This adds another row in the sys.assembly_files table, which is tied to the same assembly_id.

Use Statements to Change the Assembly
Once you catalog an assembly, its CLR code resides in SQL Server 2005, and the external file you used to create the assembly isn't referenced again. If you want to change the assembly, you can use the ALTER ASSEMBLY statement, or you can use the DROP ASSEMBLY statement and re-create the assembly after the desired changes are compiled. The CreateObjects.sql file in the Deployment project, included in the downloadable VS.NET 2005 solution for this article, contains all the DDL to catalog all the assemblies and CLR objects discussed in this article.

There is one strange bug to be aware of that happens only for VB.NET projects in VS.NET 2005. As you've probably noticed already, VS.NET 2005 now creates a lot of auto-generated code under the MyProjects folder (turn on View All Files to see MyProjects as a folder). If you attempt to rename either the assembly or root namespace under the MyProject properties, you'll get an error message about having a static variable/member when trying to compile and deploy your CLR object. So, for now, you'll probably want to keep the default properties created for you already when you create your SQL Server project inside VS.NET 2005.

In order to catalog an assembly, you must have the necessary permissions within SQL Server 2005. You must be logged into SQL Server with an integrated security account (SQL security accounts can't create assemblies). You catalog an assembly with the security granted to the integrated Windows account that created the assembly, so be careful which user ID you use. In addition, you need to catalog dependent assemblies under the same user ID or role. After you catalog an assembly, the owner can extend access permissions for that assembly to other IDs and roles.

Now that you've cataloged the assembly, you need to create your stored procedure definition and map it to the SimpleStoredProc() method in the assembly. You do this by using the standard CREATE PROCEDURE statement in SQL Server:

CREATE PROCEDURE usp_clr_GetSQLVersion
AS EXTERNAL NAME 
MySQLSampleProcedures.[SampleProcedures.StoredProcedures].SimpleStoredProc

This creates a user stored procedure called usp_clr_GetSQLVersion and binds it to the SimpleStoredProc method of the StoredProcedure class within the SampleProcedures namespace of the MySQLSampleProcedures assembly. Notice that the assembly name used is what you cataloged it as in the CREATE ASSEMBLY phase and not the name of the assembly of the CLR code itself. A word of caution, especially when working with VB.NET: Case is significant for EXTERNAL NAME, even though the language might not be case-sensitive (such as VB.NET). The names of the class and method must match the names in the source code exactly.

Now that you've created your stored procedure and mapped it to a method within a cataloged assembly, you can simply run it from within a SQLQuery command window (see Figure 3).

You might be wondering how SQL Server is running the CLR code. The .NET 2.0 runtime is loaded by the default AppDomain when the first SQL CLR object is executed. Each database has its own separate AppDomain to run CLR code in. This is why it's necessary to catalog an assembly within the database itself: The CLR code being run is isolated to that AppDomain. SQL Server 2005 beta 1 provided the capability to query active AppDomains (sys.fn_appdomains), but this is no longer available in beta 2.

Because calling a CLR object in SQL Server 2005 is just like invoking a normal stored procedure or function in ADO.NET, I haven't included an application that consumes a CLR object so I can dedicate more space to discussing the CLR objects themselves.

Develop Your First UDF
Now, I'll show you how to develop a UDF, and instead of returning the SQL version as a row set through a SqlPipe, you'll return the string as part of a scalar value from the function itself. One thing to remember about scalar UDFs is that they can only return a specific set of value types. They can only return a type from the namespace System.Data.SqlTypes, a native CLR data type that maps explicitly to a SqlType (an example of this would be the CLR data type "String" that maps to the SqlType "SqlString"), or a SQL user-defined data type.

Creating a UDF is the same as creating a stored procedure, except that you choose the User-Defined Function template instead of the Stored Procedure template when you add an item to your project. Take a look at the GetSqlVersion UDF (see Listing 2).

Notice that this example looks a lot like your first stored procedure, with the exception that you're specifying a UDF instead of a stored procedure (hence the need for the <SqlFunction()> decoration). One difference between UDFs and user stored procedures is you'll need to define what kind of data access is going to be done within your code. In your case, you'll need access to the in-process data provider, so you'll need to set the DataAccess property to allow access to the in-process context:

<SqlFunction(DataAccess:=DataAccessKind.Read)>

If you don't need data access from the in-proc, you can set the DataAccess property to DataAccessKind.None, which helps SQL Server 2005 optimize the UDF. Also, you can also set properties for a SqlFunction decoration (see Table 3).

The actual code to develop a UDF is similar to the code to develop a stored procedure, with the exception that you're returning a scalar string value back from the function instead of as a resultset. Keep in mind that you should convert your types explicitly before returning them to prevent any possibility of an Invalid Cast Exception being thrown.

Now it's time to catalog this assembly and create the UDF within SQL Server with some SQL DDL:

CREATE ASSEMBLY MySQLSampleFunctions
FROM 
'C:\Development 
Area\VSM122004\SampleFunctions\bin\SampleFunctions.dll'
WITH PERMISSION_SET=SAFE
GO
ALTER ASSEMBLY MySQLSampleFunctions
ADD FILE FROM 
'C:\Development 
Area\VSM122004\SampleFunctions\bin\SampleFunctions.pdb'
GO
CREATE FUNCTION udf_clr_GetSQLVersion() 
RETURNS NCHAR(255)
EXTERNAL NAME 
MySQLSampleFunctions.[SampleFunctions.
UserDefinedFunctions].GetSqlVersion
GO

Now that you've cataloged the assembly and created the UDF, you can execute the function within a SQLQuery window using some simple T-SQL code:

declare @mystring as NCHAR(255)
set @mystring = dbo.udf_clr_GetSQLVersion()
print @mystring

This figure shows you the results (see Figure 4).

It's also possible to create a TVF as a UDF. A TVF is basically the same thing as a scalar UDF, except that it returns a set of columns whose properties are predefined by the definition of the TDF. The main goal behind a TVF is that instead of returning a row through a SqlPipe, you return specific columns within a row set. So, instead of executing the stored procedure to get a set of rows back, the caller performs a query that looks like this:

SELECT * FROM udf_MyExampleTVF

Create a More Complex CLR Stored Procedure
Now that you've seen how it all works, it's time to create something more substantial than just returning the version of SQL Server being run. In the next example, you'll create a stored procedure that takes a filter string as a parameter and searches for all contacts in the AdventureWorks Person.Contacts table. The resultset is returned through a SqlPipe so the caller can manipulate it easily through ADO.NET. Consider the GetFilteredContacts CLR stored procedure (see Listing 3).

Notice that it looks similar to what you've done already. The only difference is that instead of sending a single value back through the SqlPipe, you're going to send a SqlDataReader object that contains the returned rows of the query. Notice that the method checks to make sure the input parameter is not null. This is especially important when passing in types that can't represent null (such as Single, Float, and so on). Once the stored procedure is compiled, it's time to catalog it and register the stored procedure using some more SQL DDL:

CREATE ASSEMBLY MySQLSampleProcedures
FROM 'C:\Development 
Area\VSM122004\SampleProcedures\bin\SampleProcedures.dll'
WITH PERMISSION_SET=SAFE
GO
ALTER ASSEMBLY MySQLSampleProcedures
ADD FILE FROM 'C:\Development 
Area\VSM122004\SampleProcedures\bin\SampleProcedures.pdb'
GO
CREATE PROCEDURE 
usp_clr_GetFilteredContacts(@strFilter 
NCHAR(255) = '')
AS EXTERNAL NAME 
MySQLSampleProcedures.[SampleProcedures.
StoredProcedures].GetFilteredContacts
GO

You'll need to define input parameters for stored procedures explicitly, and take care to match the types to the method signature within the assembly. If the types don't match, the CREATE PROCEDURE statement will fail. A default value for strFilter is also provided within the definition (an empty string), so this stored procedure can be called without any parameters to get all of the rows from Person.Contact, unfiltered. You can see what happens when you execute the GetFilteredContacts stored procedures, looking for any contacts with the last name "Smith" in them (see Figure 5).

Write CLR Triggers
As I mentioned earlier, you can also write database triggers in .NET 2.0. You use the same process to write triggers as you do for stored procedures and UDFs. As an example, I've created a sample generic trigger to use with a test table called dbo.Test_Table within the AdventureWorks database (see Listing 4).

Again, it looks similar to the stored procedure you wrote earlier. The biggest difference is that instead of accessing SqlContext, the trigger must access SqlTriggerContext to get the current execution context information. This provides the same context information as before, but also provides access to why the trigger was invoked (insert, update, delete—as well as many more types of events).

CLR triggers have access to the DELETED and INSERTED tables, just like a normal T-SQL trigger does. In this sample, the trigger action is determined and then the table that contains the affected rows is queried. From there, you can add in your own business or transactional logic.

Notice that there is a <SqlTrigger()> decoration that's commented out in the code. I included it in the source code to show you how you can tell the VS.NET 2005 IDE how to deploy this trigger to the database. Remember, the VS.NET 2005 IDE uses these decorations to determine how the CLR objects need to be cataloged and created.

Now that the trigger is created and compiled, you'll use some more SQL DDL to catalog and create your SQL CLR trigger object:

CREATE ASSEMBLY MySQLSampleTriggers
FROM 'C:\Development 
Area\VSM122004\SampleTriggers\bin\SampleTriggers.dll'
WITH PERMISSION_SET=SAFE
GO
ALTER ASSEMBLY MySQLSampleTriggers
ADD FILE FROM 'C:\Development 
Area\VSM122004\SampleTriggers\bin\SampleTriggers.pdb'
GO

CREATE TRIGGER TestTableTrigger
ON dbo.Test_Table
FOR INSERT, UPDATE, DELETE 
AS EXTERNAL NAME 
MySqlSampleTriggers.[SampleTriggers.Triggers].TestTableTrigger
GO

Debug Your SQL CLR Code
Now that you've developed some CLR objects and cataloged them in SQL Server 2005, you're probably going to want to debug the code at some point in time. You do this easily by attaching to an external process SQLSERVR.exe process (just like debugging an ISAPI filter or an NT Service in .NET). In this case, you'll be attaching to the SQL Server 2005 process (SQLSERVR.exe). Under Tools in the VS.NET 2005 IDE, select Attach to Process and select the instance of SQLSERVR.exe that corresponds to SQL Server 2005. (You might have multiple versions of SQL Server running, especially if you have Outlook 2003 with BCM installed on your machine, which automatically installs an instance of SQL Server 2000 and runs it in the background.)

Next, go into the code that you want to debug, insert your breakpoint, and perform an action that will cause your breakpoint to be hit (for example, perform a query that will fire a trigger). You can see debugging the TestTableTrigger in Listing 4 looks like after you enter an INSERT statement in a SQLQuery window (see Figure 6).

Be cautious when you exit the debugger after attaching to the SQLSERVR.exe process. Given you're working under the execution of the SQL Server engine, you should always use the VS.NET 2005 Continue toolbar button (the green VCR button) to continue executing your code after you're finished. Not doing so could stop the entire SQL Server engine from executing when you exit the debugger. A good practice is to make sure you select the Stop Debugging menu option under the Debug menu in the VS.NET 2005 IDE.

This article has given you a high-level overview of how to create .NET 2.0 CLR objects in SQL Server 2005, but I've only touched the surface of what's possible. Think of the things that you can now implement in your application's database tier. You can implement things such as structured transactions with detailed exception handling, plus access to outside resources (for example, Web services) to consume within your database objects. Not to mention, you have the ability to make calls to unmanaged COM code to take advantage of existing business logic or third-party extensions (although this should be done only when you've tested the unmanaged object thoroughly, because it can affect the SQL Server 2005 AppDomain and because the SQL CLR code must be marked as UNSAFE to allow calls to COM objects).

Microsoft claims the performance of .NET 2.0 CLR objects is comparable to that of standard T-SQL, but this claim should be taken with a grain of salt. My suggestion is that .NET 2.0 CLR objects work well when the requirements for the object call for complex or highly structured, nested code, or require access to outside resources. If it's a simple query or insert, then it's probably best to stick with T-SQL. However, what's exciting is the wealth of new and exciting features you can place in the database tier of your n-tier applications now that your database objects can be created with the feature-rich .NET 2.0 Framework and programming languages.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.