Implementing Managed code in SQL Server 2005 using Visual Studio.NET 2005

Supplemental files for this article can be found here.

Introduction to CLR (Common Language Runtime)

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

One of the benefits of managed code is type safety, or the assurance that code accesses types only in well-defined, permissible ways. Before managed code is executed, the CLR verifies that the code is safe. For example, the code is checked to ensure that no memory is read that has not previously been written. The CLR can also help ensure that code does not manipulate unmanaged memory.

Microsoft SQL Server 2005 (Yukon) now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, and user-defined functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.

SQL Server 2005 essentially acts as the operating system for the CLR when it is hosted inside SQL Server 2005. The CLR calls low-level routines implemented by SQL Server 2005 for threading, scheduling, synchronization, and memory management. These are the same primitives that the rest of the SQL Server 2005 engine uses.

In previous versions of SQL Server, database programmers were limited to using T-SQL when writing code on the server side. With CLR integration, database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Both Visual Basic .NET and C# offer full support for arrays, structured exception handling, and collections. With these languages, developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks. Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can now be easily organized into classes and namespaces. 

The next topic covers "Managed Code" and explains the ways in which it is different from "Un-Managed Code."

Introduction to Managed Code

The code that gets executed in a "managed" environment is referred to as Managed Code.  Code that you develop with a language compiler that targets the runtime (in this case, it is CLR) is called managed code; it benefits from features such as cross-language integration, cross-language exception handling, enhanced security, versioning and deployment support, a simplified model for component interaction, and debugging and profiling services. 

Managed and unmanaged programming models are dissimilar in many respects. The following figure (Fig 1) shows the defining characteristics of each model.

The following sections explain each of those characteristics in more detail.

Coding models

Unmanaged objects always communicate through interfaces; managed objects and classes can pass data directly without implementing interfaces. By default, COM interop generates a class interface to expose managed functionality through an interface to COM when the object or class does not implement one. 

Error handling mechanisms

COM methods usually return an HRESULT, indicating that the call succeeded or failed. Managed code incorporates exceptions. By default, COM interop maps managed exceptions to failure HRESULTs. 

Identities

GUIDs identify a specific unmanaged type and provide no location information for that type. Strong names consist of a unique assembly name in addition to a type name. Because the assembly name uniquely identifies the type, you can reuse a type name across multiple assemblies. An assembly also introduces publisher key, version, and location information to a managed type. Interoperation services generate GUIDs and are strong-named as required. 

Type compatibility

Types vary between managed and unmanaged code, and also among languages. 

Type definitions

If you are accustomed to working with type libraries, you know that they contain only public types. Moreover, a type library is optional. In the managed programming model, type information is mandatory for all types. Interoperation services provide tools that convert type libraries to metadata in assemblies, and metadata to type libraries. 

Type safety

Unmanaged compilers provide no type checking on pointer types, making the code susceptible to potentially harmful activity. In general, managed code requires a higher level of trust. Programmers can continue to use pointers in managed code, although the code has restrictions due to its unsafe behavior. Interoperation services prevent untrusted, managed code from accessing unmanaged code. 

Versioning

COM interfaces are immutable. If you change an interface, you must rename it with a new GUID. Managed types can evolve, keeping the same name. 

We have covered CLR, Managed Code and other terms. Let us examine the practical way of implementing Managed Code inside the SQL Server 2005 database. 

Creating Managed Code using Visual Studio.NET 2005

Note: Before proceeding further, you need to create a database named "emp" in SQL Server 2005 using SQL Server Management Studio with a simple table "emp" as show in the following figure:

Open Start >> Programs >> Microsoft Visual Studio 2005 Beta >> Microsoft Visual Studio 2005

Using Visual Studio.NET 2005, proceed with following steps:

• Go to File >> New Project.
  
  
• Select Project type as Visual Basic >> Database and template as SQL Server Project.  Name the project as SampleSqlServerProject (shown in Fig2) and click on the OK button.
  
  

  

  
  
  
• Provide Server name, security and database information as shown below (Fig 3) and click OK. Make sure to test the connection before proceeding further.
  
  

  

  
  
  
• Right click on "SampleSqlServerProject" in the solution explorer and go to Add >> user-defined function (Fig 4).
  

  

  
  
  
• You will be presented with an "add new item" wizard.  Select "User-Defined Function" within the templates, give the name as "getEmployee.vb" and click on the "Add" button (Fig 5).
  

  

  
  
  
• Make changes to the code as follows (in bold):

ImportsSystem

ImportsSystem.Data.Sql

ImportsSystem.Data.Sqlserver

ImportsSystem.Data.SqlTypes

 

PartialPublicClass UserDefinedFunctions

    <SqlFunction()> _

    PublicSharedFunction getEmployee(ByValEmpNoAsSqlString)AsSqlString

       ' Add your code here

       DimcmdAsSqlCommand = SqlContext.GetCommand

        cmd.CommandText = "SELECT ename FROM dbo.emp WHERE  EmpNo='" & EmpNo.ToString & "'"

       DimrecAsSqlDataRecord = cmd.ExecuteRow

       Dimename AsString = rec.GetString(0)

       Returnename

    EndFunction

EndClass

• Right click on "SampleSqlServerProject" in the solution explorer and select "rebuild."
  
  
• Right click on "SampleSqlServerProject" in the solution explorer and select "deploy."

Executing the Managed Code from Visual Studio 2005

There are several ways to execute the CLR-based stored function. You can execute it from Visual Studio.NET 2005 or even from SQL Server Management Studio.  But in this article, we concentrate on Visual Studio.NET 2005 for execution.

Method 1

If we carefully examine the solution explorer (within the same project "SampleSqlServerProject" of Visual Studio), we should be able to see a special folder named "TestScripts." This folder gets automatically created by Visual Studio with some sample script in "Test.sql" file within the same folder. You can use this file to really test your managed code. It basically contains T-SQL commands to test the managed code. Make changes to the code in "Test.sql" as follows:

selectdbo.getEmployee('1002')

Go to the next line, right click and select "Run to Cursor."  This again deploys and executes the script (Fig 6). 

You should be able to see your output in the output window as follows (Fig 7).

Method 2

Press Ctrl+Alt+S to show server explorer and open Data Connections >> <instance.emp.dbo> >> functions >> getEmployee

Right click on "getEmployee" and select execute (Fig 8).

You will be presented with a "Run Function" dialog that has a grid of parameters. Type "1002" in the value column and press OK (Fig 9). If you examine the output window, it should be similar to the output in Fig 7.

Transact SQL vs. Managed Code

When writing stored procedures, triggers, and user-defined functions, one decision you must make is whether to use traditional Transact-SQL, or a .NET Framework language such as Visual Basic .NET or Visual C# .NET. Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.

Transact-SQL is specifically designed for direct data access and manipulation in the database. While Transact-SQL excels at data access and management, it is not a full-fledged programming language. For example, Transact-SQL does not support arrays, collections, for each loops, bit shifting, or classes. Microsoft designed it more than a decade ago, and it is a procedural language rather than an object-oriented language. While some of these constructs can be simulated in Transact-SQL, managed code has integrated support for these constructs. Depending on the scenario, these features can provide a compelling reason to implement certain database functionality in managed code.

Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Library, you have access to thousands of pre-built classes and routines. These can be easily accessed from any stored procedure, trigger or user defined function. The Base Class Library (BCL) includes classes that provide functionality for string manipulation, advanced math operations, file access, cryptography, and more.

One of the benefits of managed code is type safety, or the assurance that code accesses types only in well-defined, permissible ways. Before managed code is executed, the CLR verifies that the code is safe. For example, the code is checked to ensure that no memory is read that has not previously been written. The CLR can also help ensure that code does not manipulate unmanaged memory.

Remarks: The entire source code of this article exists in the form of a downloadable zip file. I developed this tiny application using Microsoft SQL Server 2005 Beta 2 and Visual Studio.NET 2005 Beta 1.  

I leave it to the programmers for further enhancements. Any doubts, comments, suggestions, bugs, errors or feedback are welcomed at jag_chat@yahoo.com