Yukon Engine: CLR Integration II

In the last
instalment
, we discussed the support in Yukon for writing stored procedures, user-defined
functions and triggers using managed code.

At this point, you might be wondering whether these new capabilities are the death
knell for writing these objects using Transact-SQL. By no means! Both managed code
and T-SQL will continue to play an important part in database applications; managed
code support is intended to offer additional flexibility rather than replace what
already exists. The received wisdom thus far is that managed code should be used for
computationally-intensive activities or tasks that can take advantage of the base
class libraries in the .NET Framework, whereas T-SQL should be maintained for set-based
select and update operations.

But rather than being some complex design decision that will involve whole armies
of architects debating the merits of each technique until the early hours, the choice
in practice is reasonably intuitive. There are certain stored procedures I've written
in the past which felt like they took T-SQL some place that it wasn't intended for:
most of them have involved loops or similar iterative constructs. In the future, I'll
be writing these same tasks in C# and I can imagine them taking half the code and
a quarter of the effort. Similarly, if all you're doing in a stored procedure is wrapping
a complex multi-table joined SELECT statement, you'll
wind up writing twice the code if you build it in managed code, because you'll still
need the same statement but you'll have to wrap it up with objects such as SqlContext and SqlCommand that
will just add overhead and make debugging and deployment harder. (We'll talk about
the concept of SqlContext next time.)

That's enough general discussion. Let's see how this works out in practice. I've written
a very simple pair of functions that use the Triple DES cryptography class in the
Framework to encrypt and decrypt strings. As you look at the code below, note that
there's nothing specific to Yukon.

 using System.IO;
using System.Security.Cryptography;
using System.Text;

public class Encrypt
{
   // This is obviously not the right place to store
   // cryptographic secrets as these values could be
   // easily retrieved. In a real-world scenario, you'd
   // use a Cryptographic Service Provider (CSP) or 
   // similar to generate these values. It does however
   // make this demo easier to distribute!

   // 192-bit key and 64-bit initialization vector
   private static readonly byte[] ENCRYPTION_KEY = new byte[]
      { 0x00, 0x3A, 0x40, 0x30, 0x00, 0x3A, 0x40, 0x30, 
        0xFA, 0xE0, 0x01, 0x39, 0x8A, 0x21, 0x17, 0x97, 
        0x14, 0x85, 0xED, 0xCA, 0xFF, 0x44, 0x48, 0x9F };

   private static readonly byte[] ENCRYPTION_IV = new byte[] 
      { 0x10, 0xA9, 0x44, 0x24, 0x96, 0xD4, 0xFA, 0xFE };

   public static byte[] EncryptData(string input)
   {
      byte[] byteInput = new UnicodeEncoding().GetBytes(input);

      // Instantiate a DES instance with random key
      TripleDESCryptoServiceProvider desServiceProvider = 
         new TripleDESCryptoServiceProvider();

      desServiceProvider.KeySize = 192;

      // Create DES Encryptor from this instance
      ICryptoTransform desEncryptor = 
         desServiceProvider.CreateEncryptor(
            ENCRYPTION_KEY, ENCRYPTION_IV);

      // Create stream that transforms input using 
      // DES encryption
      MemoryStream ms = new MemoryStream();
      CryptoStream encryptStream = 
         new CryptoStream(ms, desEncryptor, 
            CryptoStreamMode.Write);

      // Write out DES-encrypted file
      encryptStream.Write(byteInput, 0, byteInput.Length);
      encryptStream.FlushFinalBlock();

      // Now write out MemoryStream to output buffer
      return ms.ToArray();
   }

   public static string DecryptData(byte[] input)
   {
      // Instantiate a DES instance
      TripleDESCryptoServiceProvider desServiceProvider = 
         new TripleDESCryptoServiceProvider();

      // Create DES Decryptor from our des instance
      ICryptoTransform desDecryptor = 
         desServiceProvider.CreateDecryptor(
            ENCRYPTION_KEY, ENCRYPTION_IV);

      // Create stream that transforms input using 
      // DES encryption
      MemoryStream msDecrypt = new MemoryStream(input);
      CryptoStream csDecrypt = 
         new CryptoStream(
            msDecrypt, desDecryptor, CryptoStreamMode.Read);

      byte[] byteOutput = new byte[input.Length];

      csDecrypt.Read(byteOutput, 0, byteOutput.Length);

      string stringOutput = 
         new UnicodeEncoding().GetString(byteOutput);

      return stringOutput.TrimEnd('\0');
   }
};

So what do you need to do to use these functions from Yukon? The first thing of course
is to compile this class into a .NET assembly: use a command like:

    csc /t:library funcEncrypt.cs

Take a note of the location of the compiled DLL; you'll need it later. To use it in
Yukon, there are two steps: cataloguing the assembly itself and cataloguing any entry
points that you'll use. Here's the Transact-SQL commands that perform these steps:

    CREATE ASSEMBLY YukonCLR
      FROM 'c:\code\yukon\yukonclr.dll'
      WITH PERMISSION_SET = EXTERNAL_ACCESS
   GO

   CREATE FUNCTION dbo.fn_encrypt_data
   (@input nvarchar(4000))
      RETURNS varbinary(8000) 
      AS EXTERNAL NAME YukonCLR:Encrypt::EncryptData
   GO

   CREATE FUNCTION dbo.fn_decrypt_data
   (@input varbinary(8000))
      RETURNS nvarchar(4000) 
      AS EXTERNAL NAME YukonCLR:Encrypt::DecryptData
   GO

Note the data types used - for the moment, at least, you're limited to 8000 bytes
for input and output: the new data types of nvarchar(max) and varbinary(max) aren't
supported by the engine in Beta 1 for interacting with managed code. Also notice the assembly:class::method syntax
for describing where each function in the assembly is stored. Lastly, be aware that
you can give the functions an alias in the SQL environment rather than their name
as defined in managed code, but the assembly itself must be named identically to the
actual assembly name.

For each CREATE statement, there is an analogue for ALTER and DROP;
here's the standard way to drop the assembly and associated objects (including a check
for their prior existence):

    IF EXISTS (SELECT * FROM sys.assembly_modules 
      WHERE name=N'fn_encrypt_data' AND [type]=N'FS')
   DROP FUNCTION dbo.fn_encrypt_data
 
   IF EXISTS (SELECT * FROM sys.assembly_modules 
      WHERE name=N'fn_decrypt_data' AND [type]=N'FS')
   DROP FUNCTION dbo.fn_decrypt_data

   IF EXISTS (SELECT * FROM sys.assemblies 
      WHERE name = N'YukonCLR')
   DROP ASSEMBLY YukonCLR
   GO

To test that these functions work successfully, try executing the following:

    SELECT dbo.fn_encrypt_data('Yukon demo')

As a further test, let's use both functions together to prove the roundtrip works:

    SELECT dbo.fn_decrypt_data(
      dbo.fn_encrypt_data('Hello world!'))

So far, we've only seen how you build user-defined functions. Actually, stored procedures
work in exactly the same way, except that you use CREATE PROCEDURE rather
than CREATE FUNCTION.

If you examined the DROP statements above closely, you'll
have seen mention of a few system views. You can investigate much of the underlying
storage of managed code in Yukon by browsing several system views. Firstly,

    SELECT * FROM sys.assemblies

This shows the .NET assemblies that are catalogued in a particular database, with
its full strong name and some other metadata (if you want to use the same assembly
from two different databases, you'll have to catalogue it twice). Now try the following
statement:

    SELECT * FROM sys.assembly_files

You'll see here the assembly ID, along with the actual binary code of the assembly
itself. Lastly, the following view shows the functions that are catalogued in the
database:

    SELECT * FROM sys.assembly_modules

In the next instalment, we'll look at some of the help that Visual Studio "Whidbey"
gives you when building managed code for Yukon to help you with deployment and debugging.
We'll also look at some more complex examples of stored procedures and functions that
access data using the SqlContext object. In the meantime,
feel free to add a comment if you've got a question, you don't think I've explained
something very well or you've spotted a mistake.