No matter what your job, if you work with SQL Server, you need to understand the Common Language Runtime (CLR), the engine that facilitates the execution of all managed code in the .NET Framework. The CLR is one of the most innovative additions to the next SQL Server release, SQL Server 2005, which Microsoft expects to release next year. Regardless of whether you're a DBA, a developer, or a programmer, your role as a SQL Server expert is changing, and you need to understand how the CLR works.
In "Developing CLR-Based Stored Procedures," May 2004, InstantDoc ID 42208, I explained the fundamental architecture behind CLR-based stored procedures and introduced an application to test the procedures. This month, I show you how to code and call CLR procedures and functions. I created this article's application and CLR procedures by using the alpha builds of Visual Studio 2005 (formerly code-named Whidbey) and SQL Server 2005 that Microsoft released at the October 2003 Professional Developers Conference (PDC). (Microsoft planned to release beta 2 of SQL Server 2005 in late June 2004.) Many of the underlying implementation details in the current beta have changed since the alpha release, but the fundamental concepts remain the same. For example, the current SQL Server 2005 beta no longer integrates with Visual Studio, but the two products are expected to converge this summer. As these two tools march arm-in-arm toward their release to manufacturing (RTM), I expect Visual Studio to incorporate the SQL Server 2005 toolset and permit even tighter CLR development than the earlier alphas. SQL Server 2005's new SQL Server Management Studio (formerly called the SQL Server Workbench) uses the Visual Studio 2005 IDE as a foundation to simplify the developer's job of creating data-centric applications and the DBA's job of managing the data that data-centric developers are accessing.
To demonstrate how to code and call CLR procedures, I developed an example application that calls CLR stored procedures to generate an RSA encryption key and encrypt and decrypt credit card numbers and expiration dates. When the user clicks Submit, the code calls a stored procedure that, in turn, calls a CLR assembly. The assembly generates a private RSA encryption key and uses this key to encrypt the string that holds the credit card number. The Encrypt() function then saves the encrypted data (now in the form of a byte array) and the expiration date to the database. The Windows Forms application program (which I wrote in Visual Basic .NET) stores the private encryption key and displays it in the client application to assist in debugging the application. The program doesn't keep the encryption key on the server; this way, only the user can decrypt the server-side value—and only if the client-side application persists the value locally.
When the user wants to retrieve the credit card number so that some server-side function can use it, the client-side application calls the FetchCCNo stored procedure, which uses the encryption key, the client ID, and the expiration date to look up the customer record by ID and expiration date. FetchCCNo then decrypts the value by using the Decrypt CLR-based procedure. Figure 1 shows the interface to the application, complete with sample results. For this example and to help debug the application, the FetchCCNo stored procedure returns the decrypted credit card number to the client application, but I expect that the server-side application would typically use the unlocked value to perform some other operation.
Note that this article's example calls the CLR stored procedures from T-SQL stored procedures. I found this method more convenient than engineering direct calls to the CLR assembly functions, and I expect that's what most developers will end up doing anyway. Another method, which I don't discuss in this article, would be to code the CLR procedures as functions and embed them in T-SQL code.
You can download the scripts I use for this article's example at InstantDoc ID 42838. Many of the example scripts I use point to the Biblio test database; you'll need to alter these scripts to point to your own test database. Let's start by looking at how to create a CLR-based assembly and code CLR functions, then walk through the creation of the project and the scripts I use.
Creating a New CLR-Based Assembly in Visual Studio 2005
To implement the CLR-based stored procedures I use in this example, you start by building the assembly code in Visual Studio. When you start the alpha version of Visual Studio 2005, you have several new Project types to choose from—including one type to create SQL Server 2005-hosted CLR assemblies. Start a new Project by clicking File, New Project, and choose SQL Server Project. Be sure to name your Project so that you'll recognize where to insert its name in the scripts you use to install the resulting assembly in SQL Server. I usually begin each Project name with an identifier; in this case, I used the letters asy to indicate that the Project is an assembly.
When you click OK in the New Project dialog box, you get the Add Database Reference dialog box that Figure 2 shows. This reference links the Visual Studio 2005 IDE to a specific SQL Server 2005 server that will host the CLR assembly you're creating.
Next, you need to choose a Project template. Select Project, Add New Item to open the dialog box that Figure 3 shows. In this dialog box, you can choose one of the new types of assemblies associated with the SQL Server Project type that Visual Studio 2005 can create for you: Stored Procedure, User-Defined Function, User-Defined Type, Trigger, or Aggregate. For this article's example, I selected Stored Procedure. Before you click Open, be sure to set a filename that you can easily recognize later. I prefix my stored-procedure class files with cls to prevent confusion later. (A class is simply a block of code that exposes functions and properties.) After you click Open, you have a new assembly prototype (which Figure 4 shows) that Visual Studio 2005 has preset to import all of the namespaces you're likely to need. A namespace is a unique name that addresses a class in the .NET Framework. The namespaces you import help the compiler identify the classes that your code references. Importing doesn't add code to your assembly—it simply provides a way to address the parts of the .NET Framework you intend to reference in your code.
Prev. page
[1]
2
3
next page 
