<< T-SQL Tuesday #18 - CTEs - The permission hierarchy problem | What's new in Denali CTP3, DMV-wise? >>

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
                    entry.PopulateDataRecord(dataRecord);

                    // and yield it for IEnumerable
                    yield return dataRecord;
                }
            }
            else
            {
                // 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?

16c25cfd-d0a6-427e-9784-bb47f1743d63|2|4.5

Tags: clr, table-types, objects

clr

Returning succinct exception messages from CLR code in SQL ServerA look at how to return error messages more succinctly from CLR code in SQL ServerThe excuses my kids use to avoid bed, and how it affects you and your SQL ServerA christmas joke postWhy the SQL Server Community is awesomeMy take on my impressions on the SQL Server community after attending PASS Scania and SQLBits 7

Comments

5/22/2011 4:01:56 PM #

Matt,Do you think a form that works with DataRecord would stil lbe useful to you? It might be useful to populating DataGridView too.

Justin Dearing

5/22/2011 4:41:28 PM #

I haven't really considered that use - as i was just focused on filling tvp's - but yes, could be useful, definitely...

Matt Whitfield

6/6/2011 1:29:21 PM #

This might be better as an extension method:public static IEnumerable<SqlDataRecord> AsTableValuedParameter<T>(this IEnumerable<T> source) where T : IDataRecordTransformable{ if (null == source) throw new ArgumentNullException("source"); return AsTableValuedParameterIterator(source);}private static IEnumerable<SqlDataRecord> AsTableValuedParameterIterator<T>(IEnumerable<T> source) where T : IDataRecordTransformable{ using (var iterator = source.GetEnumerator()) { if (iterator.MoveNext()) { SqlDataRecord dataRecord = iterator.Current.CreateDataRecord(); iterator.Current.PopulateDataRecord(dataRecord); yield return dataRecord; while (iterator.MoveNext()) { iterator.Current.PopulateDataRecord(dataRecord); yield return dataRecord; } } }}

Richard

6/6/2011 10:38:50 PM #

I think it's a bit of six of one and half a dozen of the other, really... I like the way you've altered the flow of the yield section though.

mattw@atlantis-interactive.co.uk

6/7/2011 12:44:15 PM #

The SqlDataRecordList<T> class will only work if you use it for all your collections, or copy your collections to a new list. The extension method will work for anything that implements IEnumerable<T>, including the results of LINQ queries.With the class:var list = new SqlDataRecordList<Customer>();list.AddRange(customers.Where(c => c.State == "OR"));IEnumerable<SqlDataRecord> parameterValue = list;With the extension method:var parameterValue = customers .Where(c => c.State == "OR") .AsTableValuedParameter();

Richard

6/7/2011 6:33:56 PM #

Orvar parameterValue = new SqlDataRecordList<Customer>(customers.Where(c => c.State == "OR"));But I honestly don't see that there's a massive difference.

mattw@atlantis-interactive.co.uk

6/7/2011 10:14:49 PM #

Using the list approach, you need to specify at least one, possibly two, explicit generic type names, depending on whether the SqlParameter will work with a variable with a compile-time type of SqlDataRecordList<Customer> instead of IEnumerable<SqlDataRecord>: IEnumerable<SqlDataRecord> parameterValue = new SqlDataRecordList<Customer>(customers.Where(c => c.State == "OR"));With the extension method, there's no need to specify the type names, as the compiler will infer them. IOW, there's no need for "new SqlDataRecordList<Customer>", or "IEnumerable<SqlDataRecord>". That's either 31 or 54 characters less.Also, with the list approach, you're taking a copy of the data before sending it to the server. The extension method generates a lazy sequence, which means the values can be streamed to the server without taking a copy of the list.

Richard

6/8/2011 5:16:36 PM #

If 31 or 54 characters count as a massive difference for you, that's great.As for taking a copy, yes it would take a copy if you use the list approach from LINQ, but I personally wouldn't be too concerned about that. For instance on a quick test, over 1000 iterations, the list approach takes 1703ms and the extension approach takes 1598ms.So, if that counts as a massive difference, then that's great too...