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.
Coding the CLR Functions
Ideally, you should use CLR classes only for operations such as complex math functions that require lots of CPU cycles. I explained the reasons for this rule in "Developing CLR-Based Stored Procedures." In a nutshell, T-SQL is a better choice for most stored procedures and SQL Server functions unless you need them to perform operations not really suited for T-SQL. The example code you downloaded builds three CLR functions to generate an RSA encryption key. The code then uses the key to encrypt and decrypt a data string—in this case, a credit card number. T-SQL can't perform these encryption operations without access to the RSA encryption functions in the .NET Framework, so these operations are ideal for a CLR implementation. The CLR function can access selected parts of the .NET Framework, including the RSA encryption functions.
With the CLR assembly in place, you're ready to code your CLR assembly's function prototype, which names the function, specifies its input and output parameters, and specifies the data type of the value the function returns. The code at callout A in Listing 1 shows the prototype for the GetKeys() function, the first of three classes (in this case, Visual Basic .NET functions) that the example implements to provide encryption services. The GetKeys() function generates RSA encryption keys and returns them to the calling procedure. This encryption approach lets you use the public key to encrypt a value and the private key to encrypt or decrypt a value—as long as that value was encrypted with the matching public key. The Visual Studio 2005 IDE adds the tag at the beginning of callout A to tell the compiler that SQL Server needs to be able to call this function.
Because SQL Server calls this Visual Basic .NET function the same way it does a typical T-SQL stored procedure or function, the GetKeys() function can have input and output parameters as well as a return value. Your function might not return anything to SQL Server after it executes. But in most cases, you'll want to pass at least a return value (integer) and some sort of exception string so that if the CLR procedure fails to execute, you'll have information about what went wrong. Note that the three parameters in the function prototype at callout A are all coded ByRef. This designation is required for all parameters that the function returns. The GetKeys() function also returns an integer (SqlInt32) to indicate its success (0) or failure (-1). To simplify the debugging process, I developed the function code in a separate assembly that includes a test harness that calls the function and processes the returned values. Once the function code is working, you can simply paste the code into the SQL Server function.
The next block of code in callout A generates the RSA private and public encryption keys. Note that this function returns its values by using the function Return operator and by setting the values of the output arguments.
The Encrypt() function at callout B encrypts the inbound value (the credit card number) and returns an encrypted byte array. Encrypt() has two input arguments (which the application passes into the function by using ByVal), two output arguments (which the function returns by using ByRef), and a return-value integer. Note that the function returns the encrypted value as a byte array by using the SqlTypes.SqlBinary data type. I used this approach to bypass problems I encountered when attempting to pass back the value in a Unicode string.
The code for the Decrypt() function at callout C is similar to the code for the Encrypt() function. Callout C's code simply reverses the process by using the RSA private key to decrypt the byte array. The Decrypt() function passes back to SQL Server a decrypted string or an exception message to indicate that the code couldn't decrypt the value by using the given key.
Building the Project and Creating Scripts
Now that you've coded the assembly's functions, you can right-click the project and choose Build. After the code compiles, you're ready to deploy the CLR assembly. You'll want to test the functions, and I did so by writing another application that exercised them without using SQL Server. However, you can also test and debug the functions after they're installed, as I described in "Developing CLR-Based Stored Procedures." You need to deploy the assembly manually, as I explain in a moment. Don't be tempted to right-click the project in the Visual Studio Solution Explorer and click Deploy, as you might if this were an ordinary Windows Forms or ASP.NET application. If you try to use this automatic deploy method, Visual Studio runs through the build cycle (which creates the DLL), then starts a series of operations on the selected SQL Server that include dropping all stored procedures on the server that have links to the assembly. This means you'll have to go back and recreate all your stored procedures manually.
Prev. page
1
[2]
3
next page 
