Build SQL CLR Objects With .NET (Continued)

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