A generic list for passing table-valued parameters to SQL Server

May 21, 2011 21:11 by Matt Whitfield

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:

using System;
using Microsoft.SqlServer.Server;

namespace TVPTest
    /// <summary>
    /// IDataRecordTransformable is the interface to an object that can
    /// transform itself into a SqlDataRecord, for use with the
    /// <see cref="SqlDataRecordList&lt;T&gt;"/> class in order to load
    /// a data stream into a table-valued parameter
    /// </summary>
    public interface IDataRecordTransformable
        /// <summary>
        /// Creates a SqlDataRecord object that represents the meta-data
        /// of each table row
        /// </summary>
        /// <returns>A SqlDataRecord object</returns>
        SqlDataRecord CreateDataRecord();

        /// <summary>
        /// Populates an existing SqlDataRecord object with the values
        /// from the current row
        /// </summary>
        /// <param name="dataRecord">The SqlDataRecord to populate</param>
        void PopulateDataRecord(SqlDataRecord dataRecord);

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...

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

namespace TVPTest
    /// <summary>
    /// SqlDataRecordList represents a list of objects that are
    /// <see cref="IDataRecordTransformable"/>, and therefore can be used
    /// to represent the data passed to a table-valued parameter
    /// </summary>
    /// <typeparam name="T">The type of object in the list</typeparam>
    public class SqlDataRecordList<T> : List<T>, IEnumerable<SqlDataRecord>
        where T : IDataRecordTransformable
        /// <summary>
        /// The enumerator for the SqlDataRecord list, used with
        /// SqlDbType.Structured
        /// </summary>
        /// <returns>An IEnumerator object to enumerate the
        /// SqlDataRecord list</returns>
        IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
            // declare the data record that we will populate
            SqlDataRecord dataRecord = null;

            // if we have entries
            if (this.Count > 0)
                // create the meta-data based on the first entry
                dataRecord = this[0].CreateDataRecord();

                // and yield return each record
                foreach (T entry in this)
                    // populate it

                    // and yield it for IEnumerable
                    yield return dataRecord;
                // no elements present
                yield break;

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?

Tags: , ,


So when is SqlContext.Pipe available?

December 18, 2010 22:12 by Matt Whitfield

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:

Stored ProceduresAvailable
User Defined FunctionsNot available
User Defined AggregatesNot available
User Defined TypesNot available



Returning succinct exception messages from CLR code in SQL Server

December 17, 2010 14:15 by Matt Whitfield

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:

Line 1: Msg 6522, Level 16, State 2:A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_getPasswordHashCode": System.ArgumentException: Supplied password cannot be null or empty.Parameter name: passwordSystem.ArgumentException: at UserDefinedFunctions.fn_getPasswordHashCode(String password).

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:

Line 1: Msg 6522, Level 16, State 2:A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_getPasswordHashCode": MyArgumentException: MyArgumentException: .

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.

Tags: , ,

clr | workarounds

SQL Southampton User Group

December 13, 2010 00:53 by Matt Whitfield

Last week I attended the first SQL Southampton user group meeting, and I have to say it was a hugely enjoyable occasion.

First of all, thanks to Mark Pryce-Mayer (Twitter | Blog) who put in a lot of care and effort into setting up the user group - even going as far as extensive 'biscuit research'.

We had a small room at St. Andrew's Church on the Avenue in Southampton, and it worked out really well. I gave my presentation 'CLR Demystified' that I gave at PASS Scania and SQL Bits 7, but it was nice to give it to a much smaller audience who could ask questions and engage with me during the presentation. For those of you who haven't seen it - it's a pretty packed presentation - covering everything the CLR has to offer SQL Server in just one session.

What was really interesting was giving that presentation with no projector. I spent the first half bent over my laptop with everyone crowded round - but in the second half I sat down, put the laptop on my knee and presented that way. I felt like Santa at story time - well, it is nearly Christmas!

However, what was really good was to meet some people who I hadn't met before (or, at least, hadn't had the chance to talk to properly) and have a good chat. I had already had the pleasure of meeting Adrian Hills (Twitter | Blog), but it was just a really good opportunity to meet some more people, and I enjoyed having a good chat with Christian Bolton and Andrew Fryer too.

I am really looking forward to the next user group meeting, and I am sure everyone else is too.

Tags: , , , ,


Finding the age of a person in T-SQL

December 1, 2010 00:28 by Matt Whitfield

Ok, sounds simple, right? How old are you? Easy - it's the difference in years between your date of birth and the current date...

So, expressed in T-SQL, that's:

DECLARE @dob [datetime], @currentdate [datetime]SET @dob = '19791101'SET @currentdate = '20101027'SELECT DATEDIFF(yy, @dob, @currentdate)


Well, no. DATEDIFF will return the difference between the year numbers, but won't consider the lower-order parts of the date. So, someone who was born in November, like me, would show up as being a year older than they are from January onwards. So we need to take into account the lower-order parts of the date. What we need to do is make sure that the day of the year of the current date is greater than or equal to the day of the year of the date of birth. Again, expressed in T-SQL, that's:

SELECT DATEDIFF(yy, @dob, @currentdate) -        CASE WHEN DATEPART(y, @dob) > DATEPART(y, @currentdate)             THEN 1             ELSE 0             END

I.e. we subtract one from the number that DATEDIFF(yy) returns if the current day of the year is less than the dat of the year on which the date of birth falls. Great, job done. Almost.

What about leap years? Let's consider someone who was born on the first of march, 1980. Using the code above, they are always a year younger than they actually are on their birthday. Why is this? Because the day of the year for the 1st of March in a leap year is 61, wheras the day of the year for the 1st of March in a non-leap year is 60. Rats.

So we need to delve a little deeper, and consider the actual month and day parts. If the month of the date of birth is greater than the month of the current date, then we subtract a year. If the month of the date of birth is less than the month of the current date, then we're all good, otherwise we need to look at the day of the current month. So that leaves us with:

SELECT DATEDIFF(yy, @dob, @currentdate) -   CASE WHEN DATEPART(m, @dob) > DATEPART(m, @currentdate)        THEN 1        WHEN DATEPART(m, @dob) < DATEPART(m, @currentdate)        THEN 0       ELSE CASE WHEN DATEPART(d, @dob) > DATEPART(d, @currentdate)             THEN 1             ELSE 0             END       END

However, that's a little bit onerous. Another way around it is to subtract the number of years difference from the current date, and then compare the resulting dates. In T-SQL this is then:

SELECT DATEDIFF(yy, @dob, @currentdate) -   CASE WHEN @dob > DATEADD(yy, DATEDIFF(yy, @dob, @currentdate) * -1, @currentdate)       THEN 1 ELSE 0 END

So there it is, reliable logic with which to determine exactly how old someone is in SQL Server. So, we'd want to put that in a scalar function, right? NO!

Scalar functions just suck performance out of any query. CLR Scalar functions are much more performant. How would this look in CLR code?

[Microsoft.SqlServer.Server.SqlFunction]public static int fn_GetAge(DateTime dateOfBirth, DateTime currentDate){    int yearsDifference = currentDate.Year - dateOfBirth.Year;    return yearsDifference -            ((dateOfBirth > currentDate.AddYears(yearsDifference * -1)) ? 1 : 0);}

Simple, succinct, performant. That's the way to do it.

Tags: , ,


CLR Demystified @ PASS Scania and SQL Bits 7

September 8, 2010 02:02 by Matt Whitfield

I'm privileged enough to be presenting at two conferences in the next month, at PASS Scania Community Connection 2010 on September 10th and at SQL Bits 7 on October 2nd. At both of these conferences I will be presenting a session on the CLR in SQL Server - covering how to create the various object types and talking a bit about how we can extract bleeding edge performance by using CLR in certain situations...

So, this post is to give a place for the file reference to live - and you can get the CLR Demystified presentation and source code here.

I'm looking forward to catching up with a few friends and spending a while chatting etc. But, most of all, I'm looking forward to learning from the community. I've seen Peter Larsson's presentation for the PASS Scania day, and I must say I'm humbled. Unfortunately he will be speaking in Swedish, so I'd better learn fast!

Tags: , ,


Schema Inspector

Tag cloud


<<  April 2014  >>

View posts in large calendar