SQL Server 2005 is bringing many enhancements our way, but from an application developer's perspective, .NET integration is the most anticipated new feature. SQL Server 2005 can execute user-defined functions (UDFs), stored procedures, user-defined aggregates (UDAs), user-defined types (UDTs), and triggers written in common language runtime (CLR) languages such as Visual Basic .NET and C#. The scope of this remarkable shift in database programming could fill a book, and much of the functionality that the CLR offers isn't possible in T-SQL today. Let's look at how the CLR works in SQL Server 2005 and how to start writing and deploying CLR code in SQL Server 2005. We'll work through three examples that show you how to use the CLR to replace extended stored procedures, work with CLR triggers, and use CLR UDAs. These simple examples will help you see how you can start taking advantage of SQL Server 2005's enhancements.
CLR Integration: A Closer Look
The greatest advantage of CLR integration is that CLR languages such as Visual Basic and C# provide ways to interact with APIs such as the .NET Framework Base Class Libraries—BCLs—for string manipulation, use the cryptography API, create and work with regular expressions, and call external resources such as the Windows file system, Web services, and the Windows Event Viewer.
Another benefit is that the SQL Server database environment hosts the CLR, so loading and unloading assemblies happens in the Appdomain that SQL Server hosts. Appdomains are basically security boundaries that facilitate isolating, loading, and unloading managed code (i.e., code that runs inside a runtime environment). Using Appdomains is cleaner in implementation than using sp_OA× extended stored procedures. For example, because sp_OA× procedures can instantiate COM objects, a component could access SQL Server's internal memory structures and cause the SQL Server service to shut down. Introduction of Appdomains has eliminated such risks because managed code is isolated inside the Appdomain boundary, which prevents code from accessing SQL Server memory structures. With the CLR's compiled code, you can access system resources outside the SQL Server boundary and perform complex mathematical calculations that aren't possible with T-SQL. And with the option of using both T-SQL and CLR languages in SQL Server 2005, you can now meet any business requirement.
The first time you execute a call to managed code in SQL Server 2005, the call prompts SQL Server to load the CLR. After that, any managed code invocation runs in the CLR. The managed environment performs tasks such as memory allocation, exception handling, and enforcing SQL Server and CLR security policies. As I mentioned, through the CLR, managed code can access the BCLs that provide complete access to string manipulation, regular expressions, Web services infrastructure, file I/O, and the cryptography API. The BCLs bring the power, scalability, and security of the .NET Framework to database applications.
Figure 1 shows the SQL Server 2005 CLR architecture. The SQL OS (the SQL Server abstraction layer over the underlying OS resources) is primarily responsible for handling key processes such as transactions; enforcement of atomic, consistent, isolated, durable (ACID) rules; lock management; memory management; and hosting of the SQL Server Engine. The CLR resides inside the SQL Server Engine environment. A hosting layer facilitates interaction between the SQL OS and the CLR and is primarily responsible for assembly loading and unloading, memory management, enforcing the security model, determining the number of allocated threads and fibers, lock detection, and detecting execution contexts. The introduction of CLR languages in SQL Server wouldn't be possible without the changes Microsoft made to the SQL Server Engine to accommodate the CLR runtime.
Using Visual Studio 2005
Although you don't have to use Visual Studio 2005 to develop CLR-based objects, Visual Studio makes developing and deploying CLR objects easier, so I use it for the examples in this article. To work with the CLR in Visual Studio 2005, open the New Project window and select Database from the Project Types list. Then, select SQL Server Project from the Visual Studio installed templates list, as Figure 2 shows. Right-click the project you want, and select a new item. You'll see a list of templates for CLR objects you can create in SQL Server 2005. The Visual Studio code templates greatly simplify writing code for CLR-based database routines. For example, to deploy any function or sub as a SQL Server 2005 CLR object, you must declare it as Public Shared within the .NET class that encapsulates the implementation. The templates automatically add the class attributes that are required to deploy CLR objects.
Once you've built a project, Visual Studio compiles it into an assembly that encapsulates all the referenced assemblies. Deploying the assembly uploads its binary code into the SQL Server database that's associated with the project. SQL Server 2005 also contains new DDL statements for deployment; I discuss deployment later in this article.
Replacing Extended Stored Procedures
Creating extended stored procedures—traditionally the domain of C++ gurus—can be difficult, and using them can be risky. As I mentioned earlier, extended stored procedures run under the same security context as SQL Server and run inside the SQL Server process space, so a badly written, unmanaged extended stored procedure could bring down the SQL Server service. (For information about the complexity of extended stored procedures, see the Microsoft article "Creating Extended Stored Procedures" in Related Reading.) However, .NET code is type safe (i.e., it accesses types only in well-defined ways to allow consistent data-type definitions and increase code interoperability). It also runs within the Appdomain boundary, so it can't access random SQL Server memory locations and other .NET code. .NET code is also safer to deploy and use than extended stored procedures. To better understand this difference and see the power of BCLs, consider the following extended stored procedure:
exec xp_fixeddrives
This undocumented extended stored procedure returns the partitioned hard disk drives (HDDs) and the amount of free space available in each drive. If you were to rewrite this stored procedure in a CLR language such as Visual Basic .NET or C#, you would need to use the System.IO namespace, which contains the DriveInfo class that houses the information you're looking for about the each drive's free space. The DriveInfo class also exposes other properties, such as AvailableFreeSpace, DriveFormat, TotalSize, and VolumeLabel. So, when you replace the xp_fixeddrives extended stored procedure, you also get extended functionality: the ability to access additional drive information. This capability isn't possible with the extended stored procedures in SQL Server 2000 and earlier releases—unless you write your own extended stored procedures, which could be difficult.
Let's look more closely at how to create the CLR stored procedure that Listing 1 shows. You must prefix all stored procedure classes in Visual Studio 2005 with SQLProcedure as the method attribute. The Visual Studio templates automatically create this attribute. Next, you need to get an instance of the SQLPipe class, which will let the managed stored procedure return result sets to the caller that instantiated the stored procedure. An instance of the SQLPipe class is available to managed stored procedures through the SqlContext class. The SqlContext class gives an instance of the current running context of the code to maintain the current connection properties and transaction context under which the CLR objects were invoked. Both the SQLPipe class and the SqlContext class are defined in the System.Data.SqlServer namespace, which the Visual Studio templates automatically reference. Next, you form the metadata collection that defines the column data types that you'll send to the SqlDataRecord method and later query using SQLPipe. You use the SendResultsStart, SendResultsRow, and SendResultsEnd methods to start, send, and terminate the data transmission, respectively.
Using CLR Triggers
Another useful feature of the CLR is the ability to create CLR-based triggers. Let's say you need to validate a piece of data at the point of insertion—for example, an email address that a user enters. Currently, applications rely heavily on front-end validation for such requirements and give you multiple ways to validate data from the front end or the application layer. But a SQL Server 2005 T-SQL enhancement lets you invoke stored procedures as a Web service, so SQL Server 2005 application developers must build validation into the back-end layers. The CLR can help you build robust back-end systems. In this example, I show you how to create the CLR ValidateEmail trigger, which Listing 2 shows.
To accomplish this task, you need to use the regular expressions that the code at callout A in Listing 2 shows, which are available in the .NET Framework. A regular expression (regex or regexp for short) is a special text string for describing a search pattern; you find regular expressions in the System.Text.RegularExpressions namespace. In addition, as with stored procedures, you must prefix the trigger classes with the <SQLTrigger(Event, Name, Target)> method attribute, which you can see in the code at callout B. In this attribute, Event describes the operation that will invoke the trigger. Name is the name of the trigger during deployment. And Target is the name of the table you'll attach the trigger to for the specified event. The Visual Studio templates add sample attributes to the class and we need to change the parameter appropriately; the attributes are important during the deployment phase when Visual Studio 2005 is used.
Prev. page
[1]
2
next page 
