Chapter 11. SQL Server 2005 and .NET Integration
On June 22, 2000, Microsoft announced a new development platform named “.NET” (pronounced “dot-net”) at the Forum 2000 conference in Redmond, Washington. The .NET platform vision was then shared with around 6,500 developers at the eighth Microsoft Professional Developer Conference (PDC) in Orlando, Florida, in July 2000. Bill Gates addressed the developers by saying, “The transition to .NET is as dramatic a transition as the move from MS-DOS to Windows.” Announcements about a new programming language, C# (pronounced “c-sharp”); a new development toolkit, Visual Studio .NET; and several other features of the .NET Framework were made around the same time. Since then, Microsoft .NET has become the premier platform for building Windows, web-based, and mobile applications. For the past five years, developers have leveraged the Visual Studio .NET toolkit and the object-oriented classes provided by the .NET Framework library to build Windows Forms applications, ASP.NET web applications, mobile applications, and XML web services. As a matter of fact, almost all GUI tools provided with SQL Server 2005 are built using the Microsoft .NET Framework.
The .NET Framework is now integrated with the SQL Server 2005 database engine, allowing developers to write stored procedures, functions, triggers, userdefined aggregates, and user-defined types by using .NET languages such as C# and Visual Basic .NET. This chapter explores the .NET integration feature in detail from a database administrator’s perspective. In this chapter you will learn about the benefits and challenges of allowing .NET code to run inside SQL Server 2005. Two examples in the final section of this chapter illustrate how to write stored procedure and functions using C#.
Let’s begin with an overview of the .NET Framework and the common language runtime (CLR). If you are not familiar with .NET, then carefully read the following pages in order to better understand the integration of the .NET Framework with SQL Server 2005.
What Is the .NET Framework?
Introduced in June 2000, the Microsoft .NET Framework is the next-generation, revolutionary development platform for building Windows, web, and mobile applications.
Prior to the introduction of the .NET platform, developers used programming languages such as C++ and Visual Basic to build Windows applications using the non-object-oriented Win32 API and object-oriented APIs such as the Microsoft Foundation Class (MFC) library. Web applications were built using Active Server Pages (ASP) in the VBScript and JScript programming languages. There was not good support for building XML web services or mobile applications. The Component Object Model (COM) was used as a cross-language component-based development platform. This generation of developer technologies combined with the limited cross-language development and debugging facility restricted developers’ productivity. Microsoft .NET changes this by introducing the next generation of tools, infrastructure, and technologies to significantly improve developers’ productivity.
Here are some of the benefits offered by the .NET Framework:
• Cross-language development and language interoperability—Regardless of what .NET language you use to write the code, the compiler generates Microsoft Intermediate Language (MSIL) at the compile time. The MSIL instructions are then compiled to native platform language code at execution time. While building .NET solutions, you can write code using your choice of language, such as VB .NET, inherit from or make use of classes written by some other developer, possibly in a different .NET language, such as C#, and still be able to debug across languages by using Visual Studio .NET. The Common Type System (CTS) and Common Language Specification (CLS) defined by the .NET Framework facilitate cross-language development and debugging.
• .NET Framework base class library—The .NET Framework comes with a unified object-oriented class library that provides the functionality earlier provided by the Win32 API and more. This extensive library provides consistent and very easy-to-use classes for data access, XML processing, graphical device interface (GDI), I/O, security and cryptography, network programming, serialization, distributed applications, web applications and web services, collections, and more.
• Garbage collection—As discussed in the next section, the CLR takes care of de-allocating objects, freeing memory, and hence avoiding the memory leaks common in C/C++ programming. CLR tracks the code’s use of objects and ensures that objects are not freed while still in use and that objects are freed when no longer in use. Memory management is one of the primary benefits of the CLR’s managed execution mode, which greatly simplifies the writing of .NET code because the developer does not have to worry about releasing the memory. The garbage collection algorithm intelligently frees objects and manages the memory.
• Code access security and type verification—Based on the security policies or permissions defined at the enterprise level, machine level, and user level, the same .NET code can or cannot perform an action, depending on the origin or identity of the code. For instance, a .NET assembly executed from a local drive can perform a particular action, but the same assembly if downloaded and executed from a network or an Internet location cannot perform the same action. During the MSIL-to-native code compilation at runtime, the .NET Framework can verify the code to ensure that it is accessing the types in a safe manner (that is, the objects are used the way they were intended to be used, preventing an object’s state from being corrupted). Type safety ensures that memory structures are accessed only in well-defined ways. Code access security and type verification are two important features that aid in building secure and reliable applications.
• Self-describing assemblies—Assemblies in the .NET Framework are .dll and .exe files that are the fundamental unit for packaging, deployment, and versioning. Assemblies are also important in .NET with respect to security because many of the security restrictions are enforced at the assembly boundary. In addition to MSIL code, every .NET assembly contains metadata, which is information that describes the assembly. This includes version, culture, and public key information; referenced assemblies; information about classes and class members and their visibility; and so on. By looking at assembly metadata, the CLR knows exactly what other assemblies are required by the assembly. An assembly may consist of multiple files, including resource files for internationalization.
• Interoperability with Win32 and COM code—Microsoft realized that it is critical to allow the calling of classic Win32 or COM objects from within .NET. Although not recommended, it is possible to call a Win32 API from within .NET managed code by using a technique known as PInvoke and to call a COM object from within .NET managed code by using a technique known as COM Interop.
• Object-oriented programming—A .NET assembly consists of one or more classes or types. Every type in the .NET Framework is directly or indirectly derived from the Object class in the System namespace. A .NET Framework application can implement object-oriented programming features such as encapsulation, inheritance, and polymorphism.
The .NET Framework bundles everything you need to build and execute .NET applications. It includes the Framework Class Library, CLR, and language compilers, such as the C# compiler (csc.exe) and the VB .NET compiler (vbc.exe), which you can use to turn your source code into an assembly that contains MSIL instructions, metadata, and resources. Visual Studio .NET is an integrated development environment (IDE) for building and debugging .NET applications. The .NET Framework is free and is already installed with operating systems such as Windows XP SP2 and Windows Server 2003. However, Visual Studio .NET is a commercial development tool that must be purchased. The next version of this tool, Visual Studio .NET 2005, codenamed “Whidbey,” includes the templates that allow you to create SQL Server 2005 stored procedures, functions, aggregates, triggers, and types, using managed languages such as C# and VB .NET.
Let’s now look at the CLR in a little more detail.
What Is the CLR?
At the heart of the .NET Framework is the execution environment called the CLR. The CLR provides several important services to the hosting application. It is responsible for memory management, object lifetime management, thread management, type safety, security, and I/O management. Any code running under the CLR execution environment is termed managed code. If you call a Win32 function or a COM object from within .NET code, it is referred to as unmanaged code because the CLR does not control Win32 API or COM object code.
The CLR architecture allows it to be hosted by another program. A process can load (that is, host) the .NET runtime and use it to run code in a managed environment. Internet Explorer, ASP.NET, and SQL Server 2005 are examples of processes that host the CLR. The CLR hosting facility offers several options that the host can provide to control the behavior of CLR functions, such as garbage collection and assembly loading. The CLR 2.0 hosting facility has undergone a major overhaul to support the security, stability, reliability, and performance requirements of the SQL Server 2005 host. This is discussed in the following section.
.NET Framework Integration
With SQL Server 2000, Transact-SQL (T-SQL) was the primary language that database administrators and developers used to interact with SQL Server. Stored procedure, triggers, user-defined functions, batches, and so on are written using the T-SQL language. If something was not possible using T-SQL, developers had an option of writing an extended stored procedure by using C++ and the Open Data Services (ODS) API. However, writing extended stored procedure is not a trivial task. On top of that, because an extended stored procedure ran directly in SQL Server 2000 process space, it could possibly compromise the reliability and stability of the server. Memory leaks were other common problems with extended stored procedures, including those from Microsoft (for example, see Microsoft Knowledge Base articles 300414 and 164523).
T-SQL is still the primary language that database administrators and developers use to interact with SQL Server 2005. However, this release now provides a simpler, secure, reliable, and efficient way of extending the T-SQL language. The .NET integration with SQL Server 2005 allows developers to write stored procedures, functions, triggers, aggregates, and types, using any of the .NET languages, such as C# or Visual Basic .NET. As explained earlier, the .NET code is executed under the CLR execution environment. Therefore, in order to allow stored procedures, functions, and triggers written using .NET to run from within the database engine, SQL Server 2005 hosts the CLR, which in turn runs the managed code.
Extending the T-SQL Language
Developers can use any .NET programming language, such as Visual Basic .NET or C#, and any SQL Server project template in Visual Studio .NET 2005 to create a CLR assembly that contains methods decorated to be mapped to stored procedures, functions, or triggers.
This assembly is then imported into a SQL Server 2005 database by using the CREATE ASSEMBLY DDL statement. CREATE ASSEMBLY permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, as well as to users with CREATE ASSEMBLY permission. The Windows login of the user executing CREATE ASSEMBLY must have read permission on the share and the files being loaded in the statement.
Note
The current release does not support assembly encryption or obfuscation.
After the assembly is imported into the database, the assembly .dll file on the disk is no longer required. It is recommended that you import into the database the source code and other files required for the assembly by using the ALTER ASSEMBLY...ADD FILE statement. While creating the assembly using the CREATE ASSEMBLY statement or re-importing the assembly using the ALTER ASSEMBLY statement, you can specify what an assembly can and cannot do. For example, you can use the WITH PERMISSION_SET clause with the CREATE or ALTER ASSEMBLY statement to put the assembly in one of following three permission buckets:
• SAFE—This is the most restrictive, recommended, and default permission set. It allows an assembly to perform internal computations and data access—and nothing else.
• EXTERNAL_ACCESS—This is the next level after SAFE, and it adds the ability to access external resources, such as files. Members of the sysadmin server role and principals who have login-level EXTERNAL_ACCESS permission can put assemblies in the EXTERNAL_ACCESS permission bucket.
• UNSAFE—The UNSAFE permission set allows assemblies unrestricted access to resources, both within and outside SQL Server. Assemblies can even call unmanaged code. Assemblies should be put in the UNSAFE permission bucket only after thorough consideration and analysis. Only the sysadmin role members can import assemblies with this permission set.
When the assembly bits are available in the database, you can create a stored procedure or a function or a trigger and use the AS EXTERNAL NAME clause with the CREATE PROCEDURE, FUNCTION, or TRIGGER DDL statement and specify the assembly name, class name, and method name to which the module maps. After the stored procedure, function, or trigger is created, it can be invoked like any other T-SQL module.
When you call a managed stored procedure, function, or trigger, SQL Server 2005 first checks whether clr enabled systemwide configuration is enabled. If it is disabled, you see an error message similar to the following:
Msg 6263, Level 16, State 1, Line 3
Execution of user code in the .NET Framework is disabled.
Use sp_configure "clr enabled" to enable execution of user code in the
.NET Framework.
clr enabled is an advanced option, and you need to enable the show advanced options option and then run RECONFIGURE to see the clr enabled option. After you change the clr enabled option, you must run RECONFIGURE to activate the option. If the clr enabled option is enabled, SQL Server checks whether the assembly containing the mapped method is already loaded in memory. If it is not, the assembly is loaded from the bits imported in the database, and the method is invoked. The .NET CLR code access security integrated with the SQL Server authentication- and authorization-based security model ensures that the method can perform only the operations for which it is granted permissions.
You can drop an assembly from the database by using the DROP ASSEMBLY statement. You cannot drop an assembly unless all the objects (stored procedures, functions, triggers, types, and aggregates) that depend on the assembly are dropped and all the other assemblies that reference this assembly are dropped.
You can use the ALTER ASSEMBLY statement to modify the properties of an assembly, to refresh it to the latest or current version, or to add or remove files associated with the assembly. ALTER ASSEMBLY and DROP ASSEMBLY permissions default to the assembly owners and members of the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles. These permissions are not transferable.
The Programmability folder under the database in the Object Explorer tree in Management Studio lists the assemblies and modules such as procedures and functions. You can right-click an assembly and select View Dependencies to see objects that depend on the selected assembly and objects on which the selected assembly depends. The Object Dependencies dialog is shown in Figure 11.1.
Figure 11.1. SQL Server Management Studio allows you to view objects that depend on an imported assembly and objects on which the assembly depends.
Similarly, you can right-click an object, such as a stored procedure or a function, and select View Dependencies to see the assemblies and other objects on which the selected object depends.
SQLCLR Design Goals
CLR hosting in SQL Server 2005 is architected to meet the following design goals:
• Security—The .NET code running inside SQL Server must follow authentication and authorization rules when accessing database objects such as tables and columns. In addition, database administrators should be able to control access to operating system resources such as files and the network from the .NET code running in the database. Database administrators should be able to enable or disable running .NET code from the database engine.
• Reliability—The .NET code running inside SQL Server should not be allowed to perform operations that compromise data integrity and transactional correctness. It should not be allowed to perform operations such as popping up a message box requesting a user response, exiting the process, overwriting DBMS memory buffers or internal data structures, causing stack overflow, and so on.
• Performance and scalability—The .NET code running inside SQL Server must perform as well as or better than an equivalent implementation through T-SQL. The .NET code should be restricted from calling APIs for threading, memory, and synchronization primitives to ensure the scalability of the system.
Meeting the above design goals was not easy because SQL Server 2005 and the CLR have different internal models for security, memory management, thread scheduling, and management schemes. To make SQL Server and the CLR work together, while achieving these design goals, the hosting API in .NET Framework 2.0 is enhanced to enable the runtime host, such as SQL Server 2005, to either control or make recommendation on how the resource should be managed.
SQL Server 2005 as a host has control over memory. It can reject the CLR memory request and ask the CLR to reduce its memory use if required. The .NET runtime calls SQL Server for allocating and de-allocating the memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure that the CLR and SQL Server are not competing against each other for memory.
As explained in detail in Chapter 2, “SQL Server 2005 Database Engine Architectural Changes,” to minimize context switching, SQL Server uses cooperative thread scheduling, where a thread must voluntarily give up control of the processor. On the other hand, the CLR supports the preemptive threading model, where the processor takes control back from the thread when the time slice is over. To ensure stability, the CLR calls SQL Server APIs for creating threads, both for running user code and for its own internal use. In order to synchronize between multiple threads, the CLR calls SQL Server synchronization objects. This allows the SQL Server scheduler to schedule other tasks when a thread is waiting on a synchronization object.
The CLR defines the notion of application domain (appdomain), which can be thought of as a lightweight process. Unlike Win32 processes, which are isolated by having different memory address spaces, the isolation in appdomains is achieved by .NET keeping control over the use of memory. The .NET CLR ensures that appdomains do not access each other’s memory. One Win32 process may include multiple CLR appdomains. SQLCLR leverages the notion of appdomains to make the SQLCLR integration reliable when running inside a SQL Server host. Programmers and database administrators do not have control over when and how appdomains are created. The sys.dm_clr_appdomains dynamic management view (DMV) can be used to view a list of appdomains in the SQL Server process space.
To increase the reliability of the .NET code running inside SQL Server, the classes and functions in the .NET Framework base class library have been updated to include attributes called host protection attributes, which can be used by hosts such as SQL Server 2005 to indicate which API can or cannot be called by the .NET code running in the host.
In summary, the SQL Server engine performs all the memory and thread or task management for the hosted CLR programs. The notion of appdomains and host protection attributes increases the reliability of SQLCLR code.
Figure 11.2 shows the SQLCLR architecture.
Figure 11.2. SQLOS and hosting layer components inside the SQL Server 2005 engine ensure the reliability, safety, and performance of the hosted CLR code.
The SQLOS is responsible for connection management, memory management, thread management, and synchronization services. The hosting layer provides coordination of assembly loading, deadlock detection, execution context, security, and memory management. This is explained in detail in Chapter 2.
Before looking at some SQLCLR examples, let’s quickly review the benefits and challenges of integrating SQL Server 2005 and the CLR.
SQLCLR Integration Benefits
Here are some of the advantages of allowing .NET code to run inside the database engine:
• Enhanced programming model—The SQLCLR integration allows you to use any .NET programming language and the familiar ADO.NET data access methods to extend the T-SQL language. .NET features such as the object-oriented programming model and structural exception handling facilitate writing code that is manageable and robust.
• Access to the .NET Framework base class library—Managed modules (stored procedure, functions, and so on) have access to hundreds of classes in the .NET Framework base class library to performs tasks related to mathematical or statistical calculation, cryptography, disk I/O, XML and string manipulation, and so on.
• Performance—T-SQL is an interpreted language, whereas .NET code is just-in-time compiled the first time it is run. The compiled code is cached until the appdomain resides in memory. Several innovative techniques, such as fast transitions between SQL and CLR, the new streaming table-valued functions, binary format normalization of SQLCLR user-defined types, and so on, have been implemented to ensure that SQLCLR code performs as well as or better than an equivalent implementation through T-SQL.
• A replacement for extended stored procedures—As mentioned earlier, SQLCLR is an excellent replacement for extended stored procedure. You as a database administrator have control over what a SQLCLR module can and cannot do. The SAFE and EXTERNAL_ACCESS permission buckets guarantee that there will not be any memory leaks and that the module cannot compromise the stability and integrity of the database.
• Security and reliability—The SQLCLR integration feature is turned off by default. The .NET CLR code-access security integrated with SQL Server authentication- and authorization-based security gives you complete control over what a SQLCLR module can and cannot do. SQL Server 2005 as a host controls access to memory, thread management, and synchronization. This, in addition to the use of appdomains and host protection attributes, increase the reliability of SQLCLR code. SQL Server 2005 provides several catalog views, DMVs, profiler events, and Performance Monitor counters that you can use to track and trace SQLCLR activity. The ability to create external access or an unsafe SQLCLR module is restricted to few server and database roles or to principals that are granted permission to do so. In summary, SQL Server 2005 goes to great lengths to ensure the safety and reliability of the server.
• Extending type systems and aggregates—If SQL Server 2005 does not contain a built-in type or aggregate that you need, you can use SQLCLR to implement your own type or aggregate.
• Developing and debugging using Visual Studio .NET—The Visual Studio .NET 2005 IDE has been enhanced to simplify the development, debugging, and deployment of SQLCLR objects. The SQL Server Project template simplifies creating assemblies that you can import into SQL Server. Visual Studio .NET allows cross-language debugging. The deployment feature in Visual Studio .NET 2005 allows you to deploy managed modules directly to the database with a single click. It takes care of dropping existing object, re-importing the assembly, and re-creating the objects.
SQLCLR Integration Challenges
Here are some of the challenges of allowing .NET code to run inside the database engine:
• Deciding between T-SQL and .NET—T-SQL is best suited for data access with little or no procedural logic. SQLCLR is best suited for computational, procedural, CPUintensive logic. Another reason to use SQLCLR is to make use of classes and functions that are available in the .NET Framework base class library. SQL Server 2005 introduces several significant improvements to the T-SQL language, such as recursive common table expression (CTE) queries, ranking functions, and new relational operators. You should be sure to consider these enhancements and see if you can implement the task at hand efficiently by using T-SQL before you start writing stored a procedure, trigger, or function using .NET managed code. You should continue to leverage T-SQL’s set-oriented processing wherever possible. And you should consider writing CLR code as an efficient alternative for logic that cannot be expressed declaratively in query language. The CLR can be used to replace server-side cursors and in some such situation, it can gain performance improvements.
• Deciding between the middle or client tier and the server tier—If you want to centralize data validation or avoid frequent round-trips to the database server, or if you need to process large amounts of data while needing a small portion of it for application use, it makes sense to move the code from the middle or client tier to the server tier. However, you must consider the increased load on the server, which might affect the overall performance and scalability of the server.
SQLCLR Integration Examples
The following sections illustrate how to write stored procedures and functions using C#. You need Visual Studio .NET 2005 “in order” to compile and build the code, or you can use the .dll files available with the code download for this book.
The following sections also highlight the use of Performance Monitor, Profiler, catalog views, and DMVs to track and trace SQLCLR activity.
Saving XML Data to a File
SQL Server 2005 allows you to read XML data from a file by using the OPENROWSET T-SQL function, but it does not offer a functionality to save XML type data into a disk file. Here’s how you implement that by using a C# managed stored procedure:
Note
The clr enabled sp_configure option should be enabled for this example to work. You can run the sp_configure command or use SQL Server Surface Area Configuration tool to enable the clr enabled option.
- Start Visual Studio .NET 2005 and create a new C# SQL Server project by selecting File | New | Project and then selecting the Database Project types under Visual C#. Name the project
SQLCLRDemo. - When Visual Studio .NET 2005 prompt you to define a SQL Server 2005 database connection where the managed objects is deployed, do so. This connection information is used for deploying and debugging managed objects. You can click the Add New Reference button and specify the database connection or click Cancel. You can later specify or change this connection by selecting Project | Properties and then using the Database tab.
- Right-click the
SQLCLRDemoproject in the Solution Explorer window, select Add | Stored Procedure, and name the fileSaveXMLToFile.cs. Then click Add button. - Type the following C# source code for the stored procedure:
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
public partial class StoredProcedures
{
[SqlProcedure]
public static void SaveXMLToFile(SqlXml XMLData, String DestFile,
Boolean Append)
{
StreamWriter writer = new StreamWriter(DestFile, Append);
writer.Write(XMLData.Value);
writer.Close();
SqlContext.Pipe.Send(String.Format
("XML text successfully saved to file '{0}'", DestFile));
}
};This C# code uses the
StreamWriterclass from theSystem.IOnamespace to write input XML text to the specified file. - Build the solution by pressing Ctrl+Shift+B. The
SqlProcedureattribute with theSaveXMLToFilefunction indicates that this function will be mapped to a T-SQL stored procedure. This function is present in a class namedStoredProcedures. You can change the classname to anything you would like. - Import the previously created assembly into a SQL Server 2005 database and map a T-SQL stored procedure to the
SaveXMLToFilemethod in the assembly. Developers can then deploy the assembly to a SQL Server 2005 database by using the Build | Deploy Solution menu item in the Visual Studio .NET 2005 IDE. This option runs a script that drops any existing stored procedures, triggers, functions, types, and aggregates in the assembly; drops the assembly; re-imports the assembly; and then re-creates the modules defined in the assembly. In addition to importing the binary assembly, the deployment option in Visual Studio 2005 also imports the source code files and a.pdbfile that is used by the Visual Studio .NET 2005 debugger. You can look at thesys.assembly_filescatalog view to see all the files imported into the database by the Visual Studio .NET 2005 IDE. The other option is to manually run T-SQL statements by using SQL Server Management Studio or usingSQLCMD.exeto import the assembly and create the T-SQL modules, mapping them to methods in the assembly. You should use the second approach here. - Start SQL Server Management Studio and launch Query Editor to connect to a SQL Server 2005 instance by using a Windows NT authenticated login or
sa. Run the following T-SQL statements (being sure to update the path to assembly file):
USE AdventureWorks;
GO
CREATE ASSEMBLY SQLCLRDemo
FROM 'E:SQLCLRDemoSQLCLRDemoinDebugSQLCLRDemo.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE SCHEMA SqlClr;
GO
CREATE PROCEDURE SqlClr.uspSaveXMLToFile
@XMLData XML,
@DestFile NVARCHAR(255),
@Append BIT
AS EXTERNAL NAME [SQLCLRDemo].[StoredProcedures].[SaveXMLToFile];
GO - Try to invoke the managed stored procedure from step 7. To do so, launch a new Query Editor window and run the following T-SQL commands to save XML type data into a disk file:
USE AdventureWorks;
GO
DECLARE @XMLData xml;
SELECT @XMLData = Demographics FROM Sales.Store
WHERE CustomerID = 9
EXEC SqlClr.uspSaveXMLToFile @XMLData, N'C:1.xml', 0;
GOThese T-SQL statements extract the XML data from a table into a variable that is then passed to the managed stored procedure. The C# code then saves the XML data into a disk file.
- After running the statements in step 8, open the
c:1.xmlfile to see the XML column value saved into the file. Note that invoking a managed C# stored procedure is no different from invoking a T-SQL stored procedure.
Let’s look at one more example of executing .NET managed code from within SQL Server. This time, you are going to write two user-defined functions that can be used to encode a string using base64 encoding and decode a base64encoded string to plain text.
Base-64 Encoding and Decoding
In this section you’ll continue to use the SQLCLRDemo C# project you created in the previous section. You follow these steps to implement base-64 encoding and decoding functions, using C# and calling them from within a T-SQL batch:
- Right-click the
SQLCLRDemoproject in Visual Studio .NET 2005 and select Add | User Defined Function. Name the classBase64Helper.cs. This class will generate a function namedBase64Helper, change that toEncodeToBase64, and add one more function calledDecodeFromBase64in the same class. Write theEncodeToBase64andDecodeFromBase64methods, as shown here:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Security.Cryptography;
public partial class UserDefinedFunctions
{
[SqlFunction]
public static SqlString EncodeToBase64(String PlainText)
{
Byte[] byteData = new Byte[PlainText.Length];
ASCIIEncoding enc = new ASCIIEncoding();
byteData = enc.GetBytes(PlainText);
return (SqlString)Convert.ToBase64String(byteData);
}
[SqlFunction]
public static SqlString DecodeFromBase64(String EncodedString)
{
Byte[] byteData = Convert.FromBase64String(EncodedString);
ASCIIEncoding enc = new ASCIIEncoding();
return enc.GetString(byteData);
}
}; - Build the solution by pressing Ctrl+Shift+B and then run the following DDL statement in Management Studio to refresh the assembly in the database:
ALTER ASSEMBLY SQLCLRDemo
FROM 'E:SQLCLRDemoSQLCLRDemoinDebugSQLCLRDemo.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO - Create the scalar-valued user-defined functions and map them to the CLR methods just created:
USE AdventureWorks;
GO
CREATE FUNCTION SqlClr.ufnEncodeToBase64(@PlainText NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME
[SQLCLRDemo].[UserDefinedFunctions].[EncodeToBase64];
GO
CREATE FUNCTION SqlClr.ufnDecodeFromBase64
(@EncodedString NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME
[SQLCLRDemo].[UserDefinedFunctions].[DecodeFromBase64];
GO - Try out the SQLCLR functions from step 3. The following T-SQL statements add a column to the
Sales.CreditCardtable, use theSqlClr.ufnEncodeToBase64function to encode the data in the existing column namedCardNumber, save the encoded data into the new column, and use theSqlClr.ufnDecodeFromBase64function to decode the new column data:
ALTER TABLE Sales.CreditCard ADD EncryptedCC VARCHAR(255);
GO
UPDATE Sales.CreditCard
SET EncryptedCC = SqlClr.ufnEncodeToBase64(CardNumber);
GO
SELECT * FROM Sales.CreditCard;
GO
SELECT CardNumber, SqlClr.ufnDecodeFromBase64(EncryptedCC)
FROM Sales.CreditCard;
GO
ALTER TABLE Sales.CreditCard DROP COLUMN EncryptedCC;
GO
SQLCLR Metadata
SQL Server 2005 provides several catalog views that you can use to obtain information about imported CLR assemblies and objects. Table 11.1 shows some of these catalog views.
Table 11.1. SQLCLR Catalog Views
Monitoring SQLCLR Activity
You can use various DMVs, Profiler trace, and Performance Monitor counters to monitor SQLCLR activity. For instance, you can use the sys.dm_os_loaded_modules and sys.dm_os_memory_clerks DMVs as illustrated in the following code to find out about all the .NET DLLs loaded in the SQL Server address space and the amount of memory allocated by the .NET CLR:
SELECT name, description, * FROM sys.dm_os_loaded_modules
WHERE description LIKE '%.NET%';
SELECT * FROM sys.dm_os_memory_clerks
WHERE TYPE LIKE '%CLR%';
Table 11.2 lists some other DMVs that you can access to track SQLCLR activity.
Table 11.2. SQLCLR DMVs
SQL Profiler provides a new event class called CLR that can be used to trace events such as loading a .NET assembly. Here is how you can use Profiler to trace SQLCLR activity:
- Start SQL Profiler, connect to a SQL Server 2005 instance, and select “Blank” from the “Use the Template” box.
- Select the Event Selections tab, scroll to the bottom of the tab, select “the Assembly Load” event under the CLR event class, and click the Run button to begin tracing.
- Execute any CLR procedure or functions, and you should see the Assembly Load event in the Profiler. If this event is not raised in the Profiler, the assembly is already loaded. You can use the
sys.dm_clr_loaded_assembliesDMV and thesys.assembliescatalog view to determine whether an assembly is already loaded. You can drop the objects, drop the assembly, re-import the assembly, and run the stored procedure or functions. Alternatively, you can start the Profiler trace and run theSQLCLRDemo.sqlscript provided with the code download for this book. This script contains the T-SQL code to import an assembly and create and execute CLR procedures and functions. In this case, you should see the assembly load event in the Profiler.
You can use several .NET and SQLCLR Performance Monitor counters to track .NET activity. The SQLServer:CLR or MSSQL$Instance_name:CLR performance objects provide counters such as CLR Execution that you can use to determine total execution time (milliseconds) in the CLR.
Summary
The .NET integration with SQL Server 2005 is one of the top developer productivity features introduced in SQL Server 2005. It allows you to write stored procedures, functions, and triggers, using any .NET managed language, such as C# or Visual Basic .NET. In addition, you can create your own type or aggregate by using the .NET Framework. SQL Server 2005 does everything possible to ensure that SQLCLR code does not compromise the reliability and safety of the database engine. You have complete control over enabling or disabling the running of .NET code inside SQL Server and determining what a managed module can and cannot do.
This chapter starts with an overview of the .NET Framework and the CLR. Next, you learned about CLR hosting in SQL Server 2005 and the integration design goals. The examples provided illustrate how to write procedures and functions by using C# and calling them from a T-SQL script. You also saw how to trace and track SQLCLR activity by using catalog views, DMVs, Profiler, and Performance Monitor.
Chapter 12, “SQL Server Integration Services Overview,” shifts the focus from programming features to business intelligence features and discusses the first step in the BI process: integrating data by using SQL Server Integration Services (SSIS; formerly known as Data Transformation Services [DTS]).