Yukon Engine: CLR Integration II

Yukon Engine: CLR Integration II

  • Comments 12

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.

  • Here's a question (forgive me if the answer to this is coming next time). Many times in the past have i wanted to perform a kind of 'multi-insert' from my app server to my db server. Unfortunately, for relatively small amounts of data (i.e. not worthy of bcp), the only real answer has been to perform several multiple inserts within one txn. This i don't like becuase of the extra overhead incurred each time i perform a network operation. Is there any way around this in Yukon? Can i do something like a remoting call into some managed code 'residing' within Yukon which then controls the multi-insert (and hence perform one network round-trip as opposed to many)? thanks, Simon.
  • Simon; if you’re using ADO.Net, the next release allows you to set the update batch size of the data adapter. This will allow you to update multiple rows in a single network call.
  • Cool! - thanks for that. How does that work? I didn't realise that TDS spported this natively (not that my TDS knowledge is all that..) Presumably by the 'next release' you mean Whidbey?
  • This works by the update commands being sent as a batch in a single call. If you run SQL Profiler, you’ll see a single row with multiple update commands. I believe this will ship in ADO.Net 2.0 and could be in included in Whidbey. Perhaps Tim could confirm this?
  • Yup - exactly right! Thanks, Tim...
  • Should I be able to catalogue 2 versions of the same assembly in Yukon? I've created a strong named version of the YukonCLR class. When I do a 'create assembly' and 'select * form sys.assemblies', I can see the version, culture and public_key columns filled in. If I now try and do a 'create assembly' on version 2 of YukonCLR, I receive the error 'Assembly YukonCLR already exists in database'. Is this expected behaviour?
  • You're not doing anything wrong, Tim, but side-by-side versioning of assemblies in the same database isn't supported (for beta 1 at least). Remember that assemblies are catalogued on a per-database basis, though, so you can have different versions of an assembly used by different databases. Given that caveat, hopefully the lack of SxS isn't too great an issue. Interested to get your feedback on whether that's a big limitation or not, though. Would adding SxS be worth the added complexity of having to specify a version to be referenced in every CREATE PROCEDURE / FUNCTION / TRIGGER / TYPE statement?
  • I think it’s too early to say whether or not this would be a limitation. The greatest advantage of SxS execution is that assemblies can be updated with the reassurance that functions and SP’s that did work will continue to do so. I accept this adds extra complexity when Functions, SP’s etc are created and is likely to confuse those who are unfamiliar with assembly versioning. Personally, I would favour SxS execution but at this stage, I think the lack of it isn’t too big an issue. Building upon what you said earlier, I’ve tried calling CLR functions across databases and it works! For example, if I create v2 of fn_encrypt_data in Master, I can call it from any other database by supplying the fully qualified name; ie SELECT Master.dbo.fn_encrypt_data('Yukon demo'). Perhaps this could be useful under certain circumstances?
Page 1 of 1 (12 items)