This post originated from an RSS feed registered with .NET Buzz
by Tim Sneath.
Original Post: Yukon Engine: CLR Integration II
Feed Title: Tim Sneath's Blog
Feed URL: /msdnerror.htm?aspxerrorpath=/tims/Rss.aspx
Feed Description: Random mumblings on Microsoft, .NET, and other topics.
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'FT')
DROP FUNCTION dbo.fn_encrypt_data
IF EXISTS (SELECT * FROM sys.assembly_modules
WHERE name=N'fn_decrypt_data' AND [type]=N'FT')
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:
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.
Previous Topic
