Build SQL CLR Objects With .NET (Continued)

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.