通过

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.

Comments

  • Anonymous
    December 18, 2003
    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.
  • Anonymous
    December 19, 2003
    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.
  • Anonymous
    December 19, 2003
    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?
  • Anonymous
    December 21, 2003
    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?
  • Anonymous
    December 22, 2003
    Yup - exactly right! Thanks, Tim...
  • Anonymous
    December 23, 2003
    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?
  • Anonymous
    December 23, 2003
    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?
  • Anonymous
    December 24, 2003
    The comment has been removed