SQL Server 2005 allows you to develop CLR stored procedures (as well as other routines) using a .NET language of your choice. The previous chapter provided the background about CLR routines, gave advice on when to develop CLR routines versus T-SQL ones, and described the technicalities of how to develop CLR routines. Remember to read Appendix A for instructions on developing, building, deploying, and testing your .NET code. Here I’d just like to give a couple of examples of CLR stored procedures that apply functionality outside the reach of T-SQL code.

The first example is a CLR procedure called usp_GetEnvInfo. This stored procedure collects information from environment variables and returns it in table format. The environment variables that this procedure will return include: Machine Name, Processors, OS Version, CLR Version.

Note that, to collect information from environment variables, the assembly needs external access to operating system resources. By default assemblies are created (using the CREATE ASSEMBLY command) with the most restrictive PERMISSION_SET option – SAFE; meaning that they’re limited to accessing database resources only. This is the recommended option to obtain maximum security and stability. The permission set options EXTERNAL_ACCESS and UNSAFE (specified in the CREATE ASSEMBLY or ALTER ASSEMBLY commands, or in the Project | Properties dialog in Visual Studio under the Database tab) allow external access to system resources such as files, the network, environment variables, or the registry. To allow EXTERNAL_ACCESS and UNSAFE assemblies to run, you also need to set the database option TRUSTWORTHY to ON. Allowing EXTERNAL_ACCESS or UNSAFE assemblies to run represents a security risk and should be avoided. I will describe a safer alternative shortly, but first I’ll demonstrate this option. To set the TRUSTWORTHY option of the CLRUtilities database to ON and to change the permission set of the CLRUtilities assembly to EXTERNAL_ACCESS you would run the following code:

-- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS
ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON;
GO
-- Alter assembly with PERMISSION_SET = EXTERNAL_ACCESS
ALTER ASSEMBLY CLRUtilities
WITH PERMISSION_SET = EXTERNAL_ACCESS;

At this point you will be able to run the usp_GetEnvInfo stored procedure. Keep in mind though, that UNSAFE assemblies have complete freedom and can compromise the robustness of SQL Server and the security of the system. EXTERNAL_ACCESS assemblies get the same reliability and stability protection as SAFE assemblies, but from a security perspective they’re like UNSAFE assemblies.

A more secure alternative is to sign the assembly with a strong-named key file or Authenticode with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate) and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies). For example, suppose that you have code in the CLRUtilities assembly that needs to run with the EXTERNAL_ACCESS permission set. You can sign the assembly with a strong-named key file from the Project | Properties dialog in Visual Studio under the Signing tab. Then run the following code to create an asymmetric key from the executable .dll file and a corresponding login with the EXTERNAL_ACCESS ASSEMBLY permission.

-- Create an asymmetric key from the signed assembly
-- Note: you have to sign the assembly using a strong name key file
USE master
GO
CREATE ASYMMETRIC KEY CLRUtilitiesKey
  FROM EXECUTABLE FILE =
    'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'
-- Create login and grant it with external access permission
CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin
GO

For more details about securing your assemblies, please refer to Books Online and to the following URL: http://msdn2.microsoft.com/en-us/library/ms345106.aspx.

Example 7-10 shows the definition of the usp_GetEnvInfo stored procedure using C# code.

// Stored procedure that returns environment info in tabular format
[SqlProcedure]
public static void usp_GetEnvInfo()
{
    // Create a record - object representation of a row
    // Include the metadata for the SQL table
    SqlDataRecord record = new SqlDataRecord(
        new SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20),
        new SqlMetaData("Value", SqlDbType.NVarChar, 256));
    // Marks the beginning of the result set to be sent back to the client
    // The record parameter is used to construct the metadata
    // for the result set
    SqlContext.Pipe.SendResultsStart(record);
    // Populate some records and send them through the pipe
    record.SetSqlString(0, @"Machine Name");
    record.SetSqlString(1, Environment.MachineName);
    SqlContext.Pipe.SendResultsRow(record);
    record.SetSqlString(0, @"Processors");
    record.SetSqlString(1, Environment.ProcessorCount.ToString());
    SqlContext.Pipe.SendResultsRow(record);
    record.SetSqlString(0, @"OS Version");
    record.SetSqlString(1, Environment.OSVersion.ToString());
    SqlContext.Pipe.SendResultsRow(record);
    record.SetSqlString(0, @"CLR Version");
    record.SetSqlString(1, Environment.Version.ToString());
    SqlContext.Pipe.SendResultsRow(record);
    // End of result set
    SqlContext.Pipe.SendResultsEnd();
}

In this procedure, you can see the usage of some specific extensions to ADO.NET for usage within SQL Server CLR routines. These are defined in the Microsoft.SqlServer.Server namespace in .NET 2.0.

When you call a stored procedure from SQL Server, you are already connected. You don’t have to open a new connection; you need access to the caller’s context from the code running in the server. The caller’s context is abstracted in a SqlContext object. Before using the SqlContext object, you should test whether it is available by using its IsAvailable property.

The procedure retrieves some environmental data from the operating system. The data can be retrieved by the properties of an Environment object, which can be found in the System namespace. But the data you get is in text format. In the CLR procedure, you can see how to generate a row set for any possible format. The routine’s code stores data in a SqlDataRecord object, which represents a single row of data. It defines the schema for this single row by using the SqlMetaData objects.

SELECT statements in a T-SQL stored procedure send the results to the connected caller’s "pipe." This is the most effective way of sending results to the caller. The same technique is exposed to CLR routines running in SQL Server. Results can be sent to the connected pipe using the send methods of the SqlPipe object. You can instantiate the SqlPipe object with the Pipe property of the SqlContext object.

Example 7-11 shows the definition of the usp_GetEnvInfo stored procedure using Visual Basic code.

' Stored procedure that returns environment info in tabular format
<SqlProcedure()> _
Public Shared Sub usp_GetEnvInfo()
    ' Create a record - object representation of a row
    ' Include the metadata for the SQL table
    Dim record As New SqlDataRecord( _
        New SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20), _
        New SqlMetaData("Value", SqlDbType.NVarChar, 256))
    ' Marks the beginning of the result set to be sent back to the client
    ' The record parameter is used to construct the metadata for
    ' the result set
    SqlContext.Pipe.SendResultsStart(record)
    '' Populate some records and send them through the pipe
    record.SetSqlString(0, "Machine Name")
    record.SetSqlString(1, Environment.MachineName)
    SqlContext.Pipe.SendResultsRow(record)
    record.SetSqlString(0, "Processors")
    record.SetSqlString(1, Environment.ProcessorCount.ToString())
    SqlContext.Pipe.SendResultsRow(record)
    record.SetSqlString(0, "OS Version")
    record.SetSqlString(1, Environment.OSVersion.ToString())
    SqlContext.Pipe.SendResultsRow(record)
    record.SetSqlString(0, "CLR Version")
    record.SetSqlString(1, Environment.Version.ToString())
    SqlContext.Pipe.SendResultsRow(record)
    ' End of result set
    SqlContext.Pipe.SendResultsEnd()
End Sub

Run the following code to register the C# version of the usp_GetEnvInfo stored procedure in the CLRUtilities database:

USE CLRUtilities;
GO
IF OBJECT_ID('dbo.usp_GetEnvInfo') IS NOT NULL
  DROP PROC usp_GetEnvInfo;
GO
CREATE PROCEDURE dbo.usp_GetEnvInfo
AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetEnvInfo;

Use the following code to register the stored procedure in case you used Visual Basic to develop it:

CREATE PROCEDURE dbo.usp_GetEnvInfo
AS EXTERNAL NAME
  CLRUtilities.[CLRUtilities.CLRUtilities].usp_GetEnvInfo;

Run the following code to test the usp_GetEnvInfo procedure, generating the output shown in Table 7-15:

EXEC dbo.usp_GetEnvInfo;

The second example for a CLR procedure creates the usp_GetAssemblyInfo stored procedure, which returns information about an input assembly.

Example 7-12 shows the definition of the usp_GetAssemblyInfo stored procedure using C# code.

// Stored procedure that returns assembly info
// uses Reflection
[SqlProcedure]
public static void usp_GetAssemblyInfo(SqlString asmName)
{
    // Retrieve the clr name of the assembly
    String clrName = null;
    // Get the context
    using (SqlConnection connection =
             new SqlConnection("Context connection = true"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand())
        {
            // Get the assembly and load it
            command.Connection = connection;
            command.CommandText =
              "SELECT clr_name FROM sys.assemblies WHERE name = @asmName";
            command.Parameters.Add("@asmName", SqlDbType.NVarChar);
            command.Parameters[0].Value = asmName;
            clrName = (String)command.ExecuteScalar();
            if (clrName == null)
            {
                throw new ArgumentException("Invalid assembly name!");
            }
            Assembly myAsm = Assembly.Load(clrName);
            // Create a record - object representation of a row
            // Include the metadata for the SQL table
            SqlDataRecord record = new SqlDataRecord(
                new SqlMetaData("Type", SqlDbType.NVarChar, 50),
                new SqlMetaData("Name", SqlDbType.NVarChar, 256));
            // Marks the beginning of the result set to be sent back
            // to the client
            // The record parameter is used to construct the metadata
            // for the result set
            SqlContext.Pipe.SendResultsStart(record);
            // Get all types in the assembly
            Type[] typesArr = myAsm.GetTypes();
            foreach (Type t in typesArr)
            {
                // Type in a SQL database should be a class or
                // a structure
                if (t.IsClass == true)
                {
                    record.SetSqlString(0, @"Class");
                }
                else
                {
                    record.SetSqlString(0, @"Structure");
                }
                record.SetSqlString(1, t.FullName);
                SqlContext.Pipe.SendResultsRow(record);
                // Find all public static methods
                MethodInfo[] miArr = t.GetMethods();
                foreach (MethodInfo mi in miArr)
                {
                    if (mi.IsPublic && mi.IsStatic)
                    {
                        record.SetSqlString(0, @"  Method");
                        record.SetSqlString(1, mi.Name);
                        SqlContext.Pipe.SendResultsRow(record);
                    }
                }
            }
            // End of result set
            SqlContext.Pipe.SendResultsEnd();
        }
    }
}

A DBA could have a problem finding out exactly what part of a particular .NET assembly is loaded to the database. Fortunately, this problem can be easily mitigated. All .NET assemblies include metadata, describing all types (classes and structures) defined within it, including all public methods and properties of the types. In .NET, the System.Reflection namespace contains classes and interfaces that provide a managed view of loaded types.

For a very detailed overview of a .NET assembly stored in the file system, you can use the Reflector for .NET, a very sophisticated tool created by Lutz Roeder. Because it is downloadable for free from his site at http://www.aisto.com/roeder/dotnet/, it is very popular among .NET developers. Also, Miles Trochesset wrote in his blog at http://blogs.msdn.com/sqlclr/archive/2005/11/21/495438.aspx a SQL Server CLR DDL trigger that is fired on the CREATE ASSEMBLY statement. The trigger automatically registers all CLR objects from the assembly, including UDTs, UDAs, UDFs, SPs and triggers. I guess it is going to be very popular among database developers. I used both tools as a starting point to create my simplified version of a SQL Server CLR stored procedure. I thought that a DBA might prefer to read the assembly metadata from a stored procedure, not from an external tool, like Lutz Roeder’s Reflector for .NET is, and also that a DBA might want just to read the metadata first, not immediately to register all CLR objects from the assembly, like Miles Trochesset’s trigger does.

The usp_GetAssemblyInfo procedure has to load an assembly from the sys.assemblies catalog view. To achieve this task, it has to execute a SqlCommand. SqlCommand needs a connection. In the usp_GetEnvInfo procedure’s code you saw the usage of the SqlContext class; now you need an explicit SqlConnection object. You can get the context of the caller’s connection by using a new connection string option, ″Context connection = true″.

As in the usp_GetEnvInfo procedure, you want to get the results in tabular format. Again you use the SqlDataRecord and SqlMetaData objects to shape the row returned. Remember that the SqlPipe object gives you the best performance to return the row to the caller.

Before you can read the metadata of an assembly, you have to load it. The rest is quite easy. The GetTypes method of a loaded assembly can be used to retrieve a collection of all types defined in the assembly. The code retrieves this collection in an array. Then it loops through the array, and for each type it uses the GetMethods method to retrieve all public methods in an array of the MethodInfo objects. This procedure retrieves type and method names only. The Reflection classes allow you to get other metadata information as well–for example, the names and types of input parameters. Example 7-13 shows the definition of the usp_GetAssemblyInfo stored procedure using Visual Basic code.

' Stored procedure that returns assembly info
' uses Reflection
<SqlProcedure()> _
Public Shared Sub usp_GetAssemblyInfo(ByVal asmName As SqlString)
    ' Retrieve the clr name of the assembly
    Dim clrName As String = Nothing
    ' Get the context
    Using connection As New SqlConnection("Context connection = true")
        connection.Open()
        Using command As New SqlCommand
            ' Get the assembly and load it
            command.Connection = connection
            command.CommandText = _
              "SELECT clr_name FROM sys.assemblies WHERE name = @asmName"
            command.Parameters.Add("@asmName", SqlDbType.NVarChar)
            command.Parameters(0).Value = asmName
            clrName = CStr(command.ExecuteScalar())
            If (clrName = Nothing) Then
                Throw New ArgumentException("Invalid assembly name!")
            End If
            Dim myAsm As Assembly = Assembly.Load(clrName)
            ' Create a record - object representation of a row
            ' Include the metadata for the SQL table
            Dim record As New SqlDataRecord( _
                New SqlMetaData("Type", SqlDbType.NVarChar, 50), _
                New SqlMetaData("Name", SqlDbType.NVarChar, 256))
            ' Marks the beginning of the result set to be sent back
            ' to the client
            ' The record parameter is used to construct the metadata
            ' for the result set
            SqlContext.Pipe.SendResultsStart(record)
            ' Get all types in the assembly
            Dim typesArr() As Type = myAsm.GetTypes()
            For Each t As Type In typesArr
                ' Type in a SQL database should be a class or a structure
                If (t.IsClass = True) Then
                    record.SetSqlString(0, "Class")
                Else
                    record.SetSqlString(0, "Structure")
                End If
                record.SetSqlString(1, t.FullName)
                SqlContext.Pipe.SendResultsRow(record)
                ' Find all public static methods
                Dim miArr() As MethodInfo = t.GetMethods
                For Each mi As MethodInfo In miArr
                    If (mi.IsPublic And mi.IsStatic) Then
                        record.SetSqlString(0, "  Method")
                        record.SetSqlString(1, mi.Name)
                        SqlContext.Pipe.SendResultsRow(record)
                    End If
                Next
            Next
            ' End of result set
            SqlContext.Pipe.SendResultsEnd()
        End Using
    End Using
End Sub

Run the following code to register the C# version of the usp_GetAssemblyInfo stored procedure in the CLRUtilities database:

IF OBJECT_ID('dbo.usp_GetAssemblyInfo') IS NOT NULL
  DROP PROC usp_GetAssemblyInfo;
GO
CREATE PROCEDURE usp_GetAssemblyInfo
  @asmName AS sysname
AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetAssemblyInfo;

And in case you used Visual Basic to develop the stored procedure, use the following code to register it:

CREATE PROCEDURE usp_GetAssemblyInfo
  @asmName AS sysname
AS EXTERNAL NAME
  CLRUtilities.[CLRUtilities.CLRUtilities].usp_GetAssemblyInfo;

Run the following code to test the usp_GetAssemblyInfo procedure, providing it with the CLRUtilities assembly name as input:

EXEC usp_GetAssemblyInfo N'CLRUtilities';

You get the output shown in Table 7-16 with the assembly name and the names of all methods (routines) defined within it. You should recognize the routine names except for one–trg_GenericDMLAudit–a CLR trigger that I’ll describe in the next chapter.

When you’re done, run the following code for cleanup:

USE CLRUtilities;
GO
IF OBJECT_ID('dbo.usp_GetEnvInfo') IS NOT NULL
  DROP PROC dbo.usp_GetEnvInfo;
GO
IF OBJECT_ID('dbo.usp_GetAssemblyInfo') IS NOT NULL
  DROP PROC dbo.usp_GetAssemblyInfo;