ith the functionality found in the .NET Framework Base Class Library (BCL), database developers now have access to thousands of pre-built classes and routines which can be easily accessed from stored procedures. The BCL includes classes that provide functionality for improved string functioning, advanced math operations, file access, cryptography, and more. .NET languages such as VB .NET and C# offer object-oriented capabilities such as encapsulation, inheritance and polymorphism.

SQL Server 2005’s (formerly code named Yukon) integration with the .NET CLR makes it possible for developers to author stored procedures, triggers, user defined functions, and create other database objects using a managed language such as VB.NET or C#. This excellent feature provides a number of benefits, including increased productivity, significant performance gains, and the ability to leverage the features of .NET Code Access Security to prevent assemblies from performing certain operations. This article takes a look at this new CLR integration feature so you can understand how to create stored procedures in SQL Server using a managed language. You’ll also see how to leverage.NET code access security to better control the assembly execution environment. Despite the benefits, it isn’t appropriate to use .NET code for every stored procedure you write, so you’ll see how to decide when to use T-SQL and when to use a .NET language for creating SQL Server stored procedures.

.NET CLR and SQL Server Integration
In previous versions of SQL Server, database programmers were limited to using Transact-SQL when creating server side objects such as stored procedures, triggers, user defined functions and so on. But the integration of SQL Server with .NET CLR?opens up an avenue of wholly new opportunities. Before discussing the features of the .NET CLR integration with SQL Server, its important understand the limitations of T-SQL for creating server side objects.

Transact-SQL (T-SQL) is an extension of the Structured Query Language as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using T-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. T-SQL is specifically designed for direct data access and manipulation. While T-SQL can be very useful for data access and management, it is not a full-fledged programming language in the way that Visual Basic .NET and C# are. For example, T-SQL does not support arrays, strongly typed objects, collections, for each loops, bit shifting, classes and so on. While it’s possible to simulate some of these constructs in T-SQL, managed code based languages such as VB.NET or C# have first-class support for them.

With CLR integration, things have changed dramatically. The CLR provides the execution environment for all the server side objects created using a .NET language. This means that database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. The new integration allows you to more easily organize and maintain your code investments, especially when working with large amounts of server code. Unlike traditional stored procedures, this approach lets developers create code utilizing object-oriented concepts such as encapsulation. By allowing the code to run under the control of .NET CLR, you can also leverage the code access security features of .NET. Before executing code, the CLR can check to see if the code is safe. This process is known as “verification.” During verification, the CLR performs several checks to ensure that the code is safe to run. For example, it checks the code to ensure that it doesn’t try to read from memory to which it has not written. The CLR will also prevent buffer overflows.

Creating a Managed Stored Procedure
To create a stored procedure using a managed language such as C# or VB.NET, follow these steps.

• Create a .NET class and implement the functionality of the stored procedure within that class
• Compile that class to produce a .NET assembly
• Register that assembly in SQL Server using the Create Assembly statement
• Create stored procedure definitions. As part of this, you also associate the stored procedure with the actual methods in the assembly.

After completing these steps, the stored procedures are configured and you can execute them just like any other stored procedure.

Implementing a .NET Class-Based Stored Procedure
Start by creating the VB.NET class that implements the stored procedure functionality. For this example, create a VB.NET class named AddressType that contains the following code (the downloadable code provides both VB.NET and C# examples).

   Imports  System.Data   Imports System.Data.Sql   Imports  System.Data.SqlServer      Public Class AddressType         Public Shared Sub _ GetAddressTypeDetailsByID _         (ByVal  _addressTypeID as Integer)         Dim sp as SqlPipe = _            SqlContext.GetPipe()         sp.Send("Address Type ID is " + _            addressTypeID.ToString() + _            "
")         Dim cmd as SqlCommand = _            SqlContext.GetCommand()         cmd.CommandText = "SELECT * " & _            "FROM Person.AddressType " & _            "Where AddressTypeID=" & _            addressTypeID.ToString()         Dim rdr as SqlDataReader = _            cmd.ExecuteReader()         sp.Send(rdr)      End Sub      End Class   

To execute .NET code in SQL server, you need to reference the System.Data.Sql and System.Data.SqlServer namespaces. The AddressType class contains one method, GetAddressTypeDetails, which uses the ID passed as a parameter to retrieve a row from the AddressType table in the AdventureWorks database that ships with SQL Server 2005. The code gets a reference to the SqlPipe object by invoking the GetPipe method of the SqlContext class. You then use that SqlPipe reference to return tabular results and messages to the client using the SqlPipe.Send method. By calling the various overloads of the Send method, you can transmit data through the pipe to the calling application. Some of the overloaded versions of the Send method are:

• Send(ISqlDataReader)? Allows us to send the tabular results in the form of a SqlDataReader object
• Send(ISqlDataRecord)?Allows us to send the results in the form of a SqlDataRecord object
• Send(ISqlError)?Makes it possible for us to send error information in the form of a SqlError object.
• Send(msg As String)?Using this method, we can send messages to the calling application.

Compile the Class Into a .NET Assembly
Compile the completed class to produce a .NET assembly, which you can then register with SQL Server. Figure 1 shows how to compile the VB.NET class with the VB command line compiler to produce the .NET assembly.

	?
Figure 1. Compiling the VB.NET Class: The figure shows a sample command line to compile the VB.NET AddressType class using the VB command line compiler

As shown in Figure 1, you need to reference the sqlaccess.dll because the code uses the classes contained in the System.Data.SqlServer namespaces. The code download for this article also contains the C# version of the complete code.

Register the Assembly with SQL Server
The deployment unit for managed code is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. SQL Server can load and host managed DLL assemblies. To load an assembly into SQL Server, you need to use the Create Assembly statement.

   CREATE ASSEMBLY AddressType   FROM 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnTestCLRProceduresCSharpAddressType.dll'

When you execute the above statement in the SQL Server Workbench, it will load the assembly into SQL Server. The FROM clause specifies the pathname of the assembly to load. This path can either be a UNC path or a physical file path that is local to the machine. The above statement will register the assembly with the SQL Server (the assembly name should be unique within a database). Running the Create Assembly statement causes SQL Server to load a copy of the assembly. Subsequently, if you want to change the assembly code, you need to first drop the assembly, then change the code, recompile the assembly, and finally, register the assembly with SQL Server again. To drop an assembly from SQL Server, you use the Drop Assembly statement. For example, to drop the assembly created earlier, use the following command.

   DROP ASSEMBLY AddressType

Creating Stored Procedure Definitions
After loading the assembly into SQL Server, you need to associate a stored procedure to a specific method of the class contained in the assembly. You create a stored procedure using the Create Procedure statement. SQL Server 2005 supports a new clause named External Name that lets you reference a method in a registered assembly (an external method). Referencing an external method hooks the stored procedure to that method in the assembly.

   CREATE PROCEDURE [dbo].[GetAddressTypeDetailsByID]      @addressTypeID int    AS   EXTERNAL NAME       [AddressType]:[AddressType]::      [GetAddressTypeDetailsByID]

The preceding code uses the GetAddressTypeDetailsByID method shown earlier in the AddressType class. The External Name clause uses the following syntax.

   [Assembly Name]:[Class Name]::[Method Name]

After creating the stored procedure, you can test it using the following statement, passing an ID of 1 as the @addressTypeID parameter.

   exec GetAddressTypeDetailsByID 1

	?
Figure 2. Results of the Stored Procedure: The figure shows representative results from executing the GetAddressTypeDetailsByID stored procedure using SQL Server Workbench

Invoking Managed Stored Procedures from Windows Forms Clients
Here’s a simple Windows Forms application to test the stored procedure created in the previous sections. Create a new project, add a command button and a data grid to the form. Name the command button btnGetByID, and name the data grid grdAddressType. Then modify the Click event of the command button to contain the following code.

   Private Sub btnGetByID_Click( _      ByVal _ sender As System.Object, _      ByVal e As System.EventArgs) _      Handles _ btnGetByID.Click      Dim connString as String = _          "server=localhost;uid=sa;" & _         "pwd=thiru;database=AdventureWorks;"      Dim conn as New SqlConnection _(connString)      Dim procName as String  = _ _         "GetAddressTypeDetailsByID"      Dim adapter as New _ SqlDataAdapter(procName,conn)      adapter.SelectCommand.CommandType = _  __         CommandType.StoredProcedure      Dim paramAddressTypeID as New  _SqlParameter _         ("@addressTypeID", SqlDbType.Int)      paramAddressTypeID.Direction = _          ParameterDirection.Input      paramAddressTypeID.Value = 1      adapter.SelectCommand.Parameters. _Add( _         paramAddressTypeID)      Dim addressTypeDataSet as New  _DataSet()      adapter.Fill(addressTypeDataSet)      grdAddressType.DataSource  _= _         addressTypeDataSet.Tables(0).DefaultView   End Sub

	?
Figure 3. Output of the Client Application: Executing the application results in the details of the AddressType being displayed in a DataGrid control

As you can see, the preceding code is straightforward?and identical to the way you invoke any other SQL Server stored procedure. The code declares a variable named connString and assigns the database connection string to it. Then it creates a new SqlConnection object, using the connection string as an argument to its constructor. Next it creates a SqlDataAdapter object and supplies the stored procedure name and the previously created SqlConnection object as its arguments. Because the stored procedure takes an addressTypeID as an argument, the code creates a SqlParameter object and then assigns appropriate values to its properties. Finally, it executes the stored procedure by invoking the Fill method of the SqlDataAdapter object. After getting the results in the form of a DataSet object, you then bind the results of the DataSet to the DataGrid control. If you run the application and click on the command button, you will get the output shown in Figure 3.

Invoking Nested Stored Procedures
You aren’t limited to running managed stored procedures one by one?you can chain or nest them as well. In this section you’ll see how to invoke a CLR-based stored procedure from within another CLR-based stored procedure. For the purposes of this example, you’ll create two stored procedures that, together, let you retrieve address type details based on the name of the address type. The capability is split into two stored procedures. The first procedure, GetAddressTypeIDByName, accepts the name of an address type and returns the corresponding address type ID. The second procedure, GetAddressTypeDetailsByName, accepts the name of an address type and returns the details of that address type. It does this in two steps. In the first step, it invokes the GetAddressTypeIDByName procedure to convert the name of the address type into an address type ID. It then uses ID to retrieve the corresponding address type record.

To start, create a new class named AddressTypeID and add a method named GetAddressTypeIDByName as shown in the following code.

   Imports System.Data.Sql   Imports System.Data.SqlServer      Public class AddressTypeID      Public Shared Function GetAddressTypeIDByName( _         ByVal name _As String) As Integer         Dim sp as SqlPipe =  _SqlContext.GetPipe()         Dim cmd as SqlCommand =  _SqlContext.GetCommand()         cmd.CommandText = "SELECT " & _" AddressTypeID " & _            "FROM " &_" Person.AddressType WHERE " & _             "Name='" + name + "'"         Dim addressTypeID As Integer = _CType( _            cmd.ExecuteScalar(),Integer)         Return addressTypeID      End Function   End Class

You can see that the code?is very similar to the previous stored procedure. It simply takes in the name of an address type and returns the corresponding address type identifier. Next, add a GetAddressDetailsByName method to the existing AddressType class as shown below.

   Public Shared Sub GetAddressTypeDetailsByName _      (ByVal name as String)      Dim sp as SqlPipe = SqlContext.GetPipe()      Dim cmd as SqlCommand = SqlContext.GetCommand()      cmd.CommandType = CommandType.StoredProcedure      cmd.CommandText = "GetAddressTypeIDByName"      Dim paramName as New SqlParameter("@name", _        SqlDbType.NVarChar,50)      paramName.Direction = ParameterDirection.Input      paramName.Value = name      cmd.Parameters.Add(paramName)      Dim paramID as New SqlParameter( _         "@ID",SqlDbType.Int)      paramID.Direction = ParameterDirection.ReturnValue      cmd.Parameters.Add(paramID)      cmd.ExecuteNonQuery()      ' Get the returned value from the stored procedure      Dim id as Integer  = CType( _         cmd.Parameters(1).Value, _Integer)      ' Use this id as an input value for the       ' execution of       '   next stored procedure      cmd.CommandType = CommandType.Text      cmd.CommandText = "SELECT * FROM " & _         "Person.AddressType " & _      "Where AddressTypeID=" + id.ToString()      Dim rdr as SqlDataReader = cmd.ExecuteReader()      sp.Send(rdr)   End Sub

The preceding code consists of two parts. The first part?invokes the GetAddressTypeIDByName stored procedure to convert the address type name into an address type ID. The second part retrieves the details of the address type using that ID. Finally the method returns the results back to the calling application using the Send method. Download the sample code to get this test client application and other code shown in this article.

Enforcing Permissions for Executing Assemblies
A managed assembly’s security is scoped to the user identity of the account that loaded it to SQL Server. Data and code (assemblies) owned by a particular user or role are isolated from data and code owned by another user or user role unless you explicitly grant access.

The owner of an assembly can, in turn, grant the permission to reference that assembly to other database users or roles. An assembly can succeed in invoking the methods of another assembly only if one of the following conditions is true.

• The referenced assembly is owned by the same user
• The user owning the referenced assembly has granted the permission to the user owning the referencing assembly

When loading an assembly into SQL Server, you have the ability to specify one of three different levels of security in which your code can run:

• SAFE?This is the default permission set. With this mode, the assembly can only do computation and data access within the server via the in-process managed provider.
• EXTERNAL_ACCESS?This permission set is typically useful in scenarios where the code needs to access resources outside the server such as files, network, registry and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.
• UNSAFE?SQL Server uses this permission set in situations where an assembly is not verifiably safe or requires additional access to restricted resources, such as the Win32 API.

To specify a given security level, you modify the syntax of the Create Assembly statement, adding the WITH PERMISSION_SET clause as shown below.

   CREATE ASSEMBLY    FROM    WITH PERMISSION_SET =