Browse DevX
Sign up for e-mail newsletters from DevX


Writing Managed Stored Procedures in SQL Server 2005

Are you tired of struggling with T-SQL to encapsulate the logic your applications need to get data into and out of SQL Server? Now you can bypass SQL altogether, and write logic that runs directly within SQL Server in your favorite .NET language.

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 = _
         sp.Send("Address Type ID is " + _
            addressTypeID.ToString() + _
         Dim cmd as SqlCommand = _
         cmd.CommandText = "SELECT * " & _
            "FROM Person.AddressType " & _
            "Where AddressTypeID=" & _
         Dim rdr as SqlDataReader = _
      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.

Thanks for your registration, follow us on our social networks to keep up-to-date