Using Table Value Parameters (2nd edition)

Home / Using Table Value Parameters (2nd edition)

In a previous post, I demoed how to use Table Valued Parameters (TVP’s) with EF. This works great, but, if you’ll notice, it only suppports one-column TVP’s with a simple scalar list of values. I expanded this a bit.


Again, the context of this is that I’m using Entity Framework, but I’m replacing certain generated queries and code-based logic with stored procedures to get better performance. There are many cases where Entity Framework, and ORM’s in general, perform really poorly. A typical example is inserting or updating 1000+ records which have children, grand-children, and great-grand children. This sort of operation can result in 16,000+ SQL calls. If each one of those calls takes just 1/10 of a second, you’re still waiting for nearly three minutes. Is that user friendly? But, I digress .. At any rate, one way to mitigate the problem is to pass the data for this typical work-flow to a stored procedure as a TVP. The TVP, though, has to have all the data/columns necessary to do the work. A simple list of scalar values won’t cut it a lot of times.

From the previous post, you’ll recall that the end result was an IEnumerable<SqlDataRecord< that is passed along as a SqlParameter with a type of SqlDbType.Structured. This doesn’t change. What does change though is that now, instead of a simple list of primitives (ints, strings, etc), the CreateTableInput<T> method needs to support any class. To achieve this, the method is extended to handle both the simple primitive/scalar case as well as any T.

private static IEnumerable<SqlDataRecord> CreateTableInput<T>(string name, IEnumerable<T> items, List<string> excludedProps = null)
{
    var type = typeof(T);
    var records = new List<SqlDataRecord>();

    if (AuditAndMappingExtensions.IsPrimitiveType(type))
    {
        var sqlType = TypeConvertor.ToSqlDbType(type);
        foreach (T value in items)
        {
            var metaData = new SqlMetaData[] { new SqlMetaData(name, sqlType) };
            SqlDataRecord record = new SqlDataRecord(metaData);
            record.SetValue(0, value);
            records.Add(record);
        }
    }
    else
    {
        var allProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
            (!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
            (AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
            .ToList();

        var props = type.GetProperties()
            .Select(prop =>
            {
                var displayAttribute = (DisplayAttribute)prop.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault();
                var maxLengthAttribute = (MaxLengthAttribute)prop.GetCustomAttributes(typeof(MaxLengthAttribute), false).FirstOrDefault();

                return new
                {
                    Name = prop.Name,
                    DisplayName = displayAttribute == null ? prop.Name : displayAttribute.Name,
                    Order = displayAttribute == null || !displayAttribute.GetOrder().HasValue ? 999 : displayAttribute.Order,
                    ShortName = displayAttribute == null ? string.Empty : displayAttribute.ShortName,
                    PropertyInfo = prop,
                    PropertyType = prop.PropertyType,
                    HasDisplayName = displayAttribute != null,
                    SqlDbType = TypeConvertor.ToSqlDbType(prop.PropertyType),
                    MaxLength = maxLengthAttribute?.Length ?? 200,
                    IsStringType = prop.PropertyType == typeof(string)
                };
            })
            .OrderBy(prop => prop.Order)
            .Where(prop => !string.IsNullOrEmpty(prop.DisplayName))
            .ToList();

        var metaData = props.Select(x => { var meta = x.IsStringType ? new SqlMetaData(x.Name, x.SqlDbType, x.MaxLength) : new SqlMetaData(x.Name, x.SqlDbType); return meta; }).ToArray();

        foreach (T item in items)
        {
            SqlDataRecord record = new SqlDataRecord(metaData);
            var position = 0;
            props.Select(p => { var propValue = p.PropertyInfo.GetValue(item, null); record.SetValue(position, propValue); position++; return propValue; }).ToList();
            records.Add(record);
        }
    }

    return records.AsEnumerable<SqlDataRecord>();
}

You can see it’s a bit of reflection to get the properties of T when T is not a primitive. The only thing special about the relection is that I do look for Display and MaxLength attributes. If there is a Display attribute with a blank name, the property is excluded. If the Property is a string, and MaxLength is specified, then that maxlength is used for defining the SqlColumn. From there, we’re create the SqlMetaData, converting the C# type for the property to the appropriate SqlDbType, creating a SqlDataRecord per item, and using all of the previous information to create each column.

With the above code in place, it’s possible to call any stored procedure that takes a TVP using any type T. In my repository pattern, I expose a method that, behind the scenes, using EF’s SqlQuery<T> to execute any arbitrary stored procedure. It uses the method listed above in conjunction with a bit of other magic to convert a dynamic to the appropriate SqlParamters. In my case, bringing it all together looks like this:

// Define our table valued parameter
var input = items.Select(x => new MyInputType()
{
    Prop1 = x.Prop1,
    Prop2 = x.Prop2,
    Prop3 = x.Prop3,
    Prop4 = x.Prop4,
}).ToList();


// Create our dynamic
dynamic procParams = new ExpandoObject().Init(new
{
    myProperty = new ExpandoObject().Init(new
    {
        IsTable = true,
        TableType = "MyInputType",
        ColumnName = "None",
        Values = input
    })
});

var returnVals = (List<MyReturnType>)_myRepo.GetFromSproc<MyReturnType>(procParams, "myStoredProcedure");

Here are my “mapping” extensions that I use for determining if a property is primitive or nullable:

public static class AuditAndMappingExtensions
{
    public static bool IsPrimitiveType(this Type t)
    {
        var isPrimitive = t.IsPrimitive || t.IsValueType || t == typeof(string) || t == typeof(decimal);
        return isPrimitive;
    }

    public static bool IsNullablePrimitive(this Type t)
    {
        var isNullable = t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>);
        if (isNullable)
        {
            var underlyingType = Nullable.GetUnderlyingType(t);
            return IsPrimitiveType(underlyingType);
        }
        return isNullable;
    }

    public static bool IsNullableEnum(Type t)
    {
        Type u = Nullable.GetUnderlyingType(t);
        return (u != null) && u.IsEnum;
    }
}

Here’s the class I use for determining the SqlDbType from the CLR type.:

public class TypeConvertor
{
    private struct DbTypeMapEntry
    {
        public Type Type;
        public DbType DbType;
        public SqlDbType SqlDbType;
        public DbTypeMapEntry(Type type, DbType dbType, SqlDbType sqlDbType)
        {
            this.Type = type;
            this.DbType = dbType;
            this.SqlDbType = sqlDbType;
        }
    };

    private static ArrayList _DbTypeList = new ArrayList();

    #region Constructors

    static TypeConvertor()
    {
        DbTypeMapEntry dbTypeMapEntry = new DbTypeMapEntry(typeof(bool), DbType.Boolean, SqlDbType.Bit);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(byte), DbType.Double, SqlDbType.TinyInt);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(byte[]), DbType.Binary, SqlDbType.Image);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(DateTime), DbType.DateTime, SqlDbType.DateTime);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(Decimal), DbType.Decimal, SqlDbType.Decimal);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(double), DbType.Double, SqlDbType.Float);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(Guid), DbType.Guid, SqlDbType.UniqueIdentifier);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(Int16), DbType.Int16, SqlDbType.SmallInt);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(Int32), DbType.Int32, SqlDbType.Int);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(Int64), DbType.Int64, SqlDbType.BigInt);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(object), DbType.Object, SqlDbType.Variant);
        _DbTypeList.Add(dbTypeMapEntry);

        dbTypeMapEntry = new DbTypeMapEntry(typeof(string), DbType.String, SqlDbType.VarChar);
        _DbTypeList.Add(dbTypeMapEntry);
    }

    private TypeConvertor() { }

    #endregion

    #region Methods

    /// <summary>
    /// Convert db type to .Net data type
    /// </summary>
    /// <param name="dbType"></param>
    /// <returns></returns>
    public static Type ToNetType(DbType dbType)
    {
        DbTypeMapEntry entry = Find(dbType);
        return entry.Type;
    }

    /// <summary>
    /// Convert TSQL type to .Net data type
    /// </summary>
    /// <param name="sqlDbType"></param>
    /// <returns></returns>
    public static Type ToNetType(SqlDbType sqlDbType)
    {
        DbTypeMapEntry entry = Find(sqlDbType);
        return entry.Type;
    }

    /// <summary>
    /// Convert .Net type to Db type
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public static DbType ToDbType(Type type)
    {
        DbTypeMapEntry entry = Find(type);
        return entry.DbType;
    }

    /// <summary>
    /// Convert TSQL data type to DbType
    /// </summary>
    /// <param name="sqlDbType"></param>
    /// <returns></returns>
    public static DbType ToDbType(SqlDbType sqlDbType)
    {
        DbTypeMapEntry entry = Find(sqlDbType);
        return entry.DbType;
    }

    /// <summary>
    /// Convert .Net type to TSQL data type
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public static SqlDbType ToSqlDbType(Type type)
    {
        DbTypeMapEntry entry = Find(type);
        return entry.SqlDbType;
    }

    /// <summary>
    /// Convert DbType type to TSQL data type
    /// </summary>
    /// <param name="dbType"></param>
    /// <returns></returns>
    public static SqlDbType ToSqlDbType(DbType dbType)
    {
        DbTypeMapEntry entry = Find(dbType);
        return entry.SqlDbType;
    }

    private static DbTypeMapEntry Find(Type type)
    {
        object retObj = null;
        for (int i = 0; i < _DbTypeList.Count; i++)
        {
            DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
            if (entry.Type == (Nullable.GetUnderlyingType(type) ?? type))
            {
                retObj = entry;
                break;
            }
        }
        if (retObj == null)
        {
            throw
            new ApplicationException("Referenced an unsupported Type");
        }

        return (DbTypeMapEntry)retObj;
    }

    private static DbTypeMapEntry Find(DbType dbType)
    {
        object retObj = null;
        for (int i = 0; i < _DbTypeList.Count; i++)
        {
            DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
            if (entry.DbType == dbType)
            {
                retObj = entry;
                break;
            }
        }
        if (retObj == null)
        {
            throw
            new ApplicationException("Referenced an unsupported DbType");
        }

        return (DbTypeMapEntry)retObj;
    }
    private static DbTypeMapEntry Find(SqlDbType sqlDbType)
    {
        object retObj = null;
        for (int i = 0; i < _DbTypeList.Count; i++)
        {
            DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
            if (entry.SqlDbType == sqlDbType)
            {
                retObj = entry;
                break;
            }
        }
        if (retObj == null)
        {
            throw
            new ApplicationException("Referenced an unsupported SqlDbType");
        }

        return (DbTypeMapEntry)retObj;
    }

    #endregion
}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.