In my current day job, one of the things I need to do is load log files from devices into SQL Server. Nothing out of the ordinary, but the data volume made for an interesting time - because each log file has maybe 100 or 200 rows. So not the sort of volume that you'd like to bulk load, and definitely not the sort of volume that you'd want to insert with individual insert statements. Because we're using SQL Server 2008 I thought that table-valued parameters would be the obvious choice.

So I read up on it and became concerned after reading Bob Beachemin's Blog at SQL Skills about TVP use and plan compilation. That had me worried, and so I was pleased to find, when I checked again, that he had blogged about it again, but this time with more positive news.

So I decided to implement a solution for data loading based on the use of TVPs. Researching that some more, I found a blog by Leonard Lobel about passing the data in. His method of explitly implementing IEnumerable<SqlDataRecord> fit pretty exactly with the method in which I wanted to pass the data to SQL Server. So I sat down at home and came up with the following utility class (yes I am sad and write day job code in the evenings!) which I thought would be good to share with you...

The idea is that it's a simple wrapper around List<T> that provides the explicit implementation of IEnumerable<SqlDataRecord>, but while remaining generic in and of itself. This necessitated that the classes that would use the list would be able to represent themselves as a SqlDataRecord object, and that the generic list would infer this through the use of a generic type constraint.

So here's the interface:

So we have two methods, CreateDataRecord - which is the method that creates the SqlDataRecord object and populates it with columns, and PopulateDataRecord which is passed the existing SqlDataRecord and is expected to call the various set methods in order to set the values.

Let's have a look at the list class itself then...

No particular rocket-science here. Just a generic class with a type constraint, implementing the IEnumerator using the yield and yield break keyword. Job done.

Any questions?

Following on from my last post about returning error messages from the SQL CLR, I thought it would be good to put up just a very short post showing when you can and can't use SqlContext.Pipe:

I recently received a question from Dave Ballantyne (blog|twitter) about returning succinct error messages from CLR code.

Let's have a look at a basic example of some CLR code where you might want to throw an exception:

private const string _passwordSalt = "--^p@$$w0rD-sAlT-";[Microsoft.SqlServer.Server.SqlFunction]public static SqlString fn_getPasswordHashCode(string password){    using (SHA256Managed hashProvider = new SHA256Managed())    {        byte[] hash = hashProvider.ComputeHash(             Encoding.UTF8.GetBytes(_passwordSalt + password));        StringBuilder output = new StringBuilder();        foreach (byte b in hash)        {            output.Append(b.ToString("X2"));        }        return output.ToString();    }}

So, this returns us a hex string that contains the hash of a user-supplied password. However, this code will return the hash of the salt if an empty string is passed, and will return a hash even if the input is NULL. We may well want to avoid those situations, so we could modify the code in order to throw an exception that notifies the user of this fact:

[Microsoft.SqlServer.Server.SqlFunction]public static SqlString fn_getPasswordHashCode(string password){    if (string.IsNullOrEmpty(password))    {        throw new ArgumentException(            "Supplied password cannot be null or empty.", "password");    }    using (SHA256Managed hashProvider = new SHA256Managed())    {        byte[] hash = hashProvider.ComputeHash(            Encoding.UTF8.GetBytes(_passwordSalt + password));        StringBuilder output = new StringBuilder();        foreach (byte b in hash)        {            output.Append(b.ToString("X2"));        }        return output.ToString();    }}

This returns us a particularly verbose and ugly error message:

What this is doing is returning a static string which tells us the name of the assembly module being executed, as well as giving us the Message and StackTrace members.

My first point of call was to see if deriving from ArgumentException would allow us to control that output. So I created a class 'MyArgumentException':

private class MyArgumentException : ArgumentException{    public MyArgumentException(string message, string paramName)        : base(message, paramName)    {    }    public override string Message    {        get        {            return null;        }    }    public override string StackTrace    {        get        {            return null;        }    }}

Once the code was modified to throw MyArgumentException instead of ArgumentException, the error message was as follows:

So, still pretty ugly - obviously this wasn't the way to go. At this point I was due elsewhere so I emailed Dave back to say I didn't think it was possible, and went on my merry way.

The next day, however, I did some more digging, and found that some people had been using SqlContext.Pipe.ExecuteAndSend(SqlCommand) in order to be able to send better error messages.

So I tried that, in a stored procedure, and it did what I wanted - almost. It returned the error to the client, but also returned the 6522 error afterwards. So I posted a connect item - Allow SQL CLR Exception messages to be passed more succinctly to the client - and Niels Burglund posted that you can wrap the ExecuteAndSend call in a try {} catch {} block to eat the .NET exception - and then the job's done. I let Dave know, and thought 'I'll write a blog post about that'.

It was only while I was thinking about how to write this very post, that I realised that method of returning errors is great for Stored Procedures - but for User Defined Functions the Pipe is not available - meaning you can't ExecuteAndSend, meaning no nice error message goodness.

So, if you want to be able to return nice error messages from the CLR, go vote for that connect item. Then hope.