(untagged)

Fast declarative typesafety with DynamicMethods for SQL

tcassisi

0.00/5 (No votes)

16 Oct 2013

Generating typed field accessors and applying them to object mapping and SQL Stored Procedures

Download source - 58.1 KB

Introduction

Enhancing existing large code bases has its own set of challenges but one hugely painful one is when the code eschews typesafety for short term expediency particularly around the different forms of object-to-database and object-to-datagrid mappings.

This article uses some advanced techniques based around the sparsely documented TypedReference and DynamicMethod types to put some typesafety back in:

Fully compiled code for setting, getting and normalising public fields of classes or structures without boxing;
Special handling of nullable types including user defined structures and "embedded" nulls;
Normalisation of empty strings, NaN, Infinity, DateTime MinValue and more;
Declarative typesafe wrappers for calling SQL Server Stored Procedures and reading the results;
Optimised support for Table Valued Parameters eliminating the inefficient DataTable normally required;
Full support for SQL Server T-SQL type rules including handling of subnormal floating point numbers like NaN;
Lightweight Database Manager class with support for cancelling all SQL calls via a CancellationToken.

Motivation

This section shows examples taken from the Database side of things and then some performance numbers:

The Simple

Given a stored procedure with output parameters:

PROCEDURE dbo.uspGetNVPIds
    @idCount int = 1,
    @first_value bigint OUTPUT,
    @increment bigint OUTPUT

Then declare a mapping structure (field order must match):

public struct uspGetNVPIds
{
    public int idCount;

    [DB.Parameter(ParameterDirection.Output)]
    public long first_value, increment;
}

And call it via DB, a simple Database Manager:

//Cancellation support
var token = CancellationToken.None;

//Ask for next 5 ids from the sequence
//1. Setup
var args = new DB.Parameters<uspGetNVPIds>("dbo.uspGetNVPIds");
args.Data.idCount = 5;

//2. Execute
DB.ExecuteNonQuery(token, ref args);

//3. Read outputs
var first_value = args.Data.first_value;
var increment = args.Data.increment;

The Complex

Given a stored procedure with one result set and no arguments:

PROCEDURE dbo.uspNVPLoad AS
    select d.Id, d.Name, d.Value, d.updatedAt, d.updatedBy, 
           CAST(d.[version] AS BIGINT) as [Version]
    from tNVPData d

Then declare a mapping structure (field order does not matter):

public struct uspNVPLoad
{
    public long Id;
    public string Name, Value;
    public long Version;
    //Skip the other values (updatedAt, updatedBy)
}

And call it via DB again:

//Cancellation support
var token = CancellationToken.None;

//1. Setup
//Nothing to do because there are no arguments

//2. Execute
using (var reader = DB.ExecuteReader<uspNVPLoad>(token, "dbo.uspNVPLoad"))
{
    while (reader.Read())
    {
        //These will matchup by name
        var id = reader.Row.Id;
        var name = reader.Row.Name;
        var value = reader.Row.Value;
        var dbVersion = reader.Row.Version;

        //Do something with them
    }
}

And The Painful

Given a typical hand-written bulk updater and corresponding UDT:

CREATE TYPE dbo.udtNVPairs AS TABLE
(
    Id BIGINT NOT NULL PRIMARY KEY,
    TraceId INT,
    Name NVarChar(255) NOT NULL,
    Value NVarChar(255) NOT NULL
)

PROCEDURE dbo.uspNVPSaveMany
    @values dbo.udtNVPairs readonly,
    @updatedBy nvarchar(16) OUTPUT,
    @updatedAt smalldatetime OUTPUT
AS
    --ETC
    OUTPUT src.TraceId, cast(inserted.[version] as bigint) as [Version]

Then declare mapping structures:

public struct udtNVPairs
{
    //Order and type must match UDT but names can be anything
    public long Id;
    public int TraceId;
    public string Name, Value;
}

public struct uspNVPSaveMany
{
    //TVP
    public DB.Table<udtNVPairs> values;

    [DB.Parameter("@updatedBy", ParameterDirection.Output)]
    public string LastSavedBy;
    [DB.Parameter("@updatedAt", ParameterDirection.Output)]
    public DateTime LastSavedAtUtc;

    public struct Result
    {
        public int TraceId;
        public long Version;
    }
}

And call it via DB again:

//Cancellation support
var token = CancellationToken.None;

//Create the TVP data
var table = new DB.Table<udtNVPairs>(Data.Count);
for (int i = 0; i < Data.Count; i++)
{
    //So can match up date with records later
    table.Row.TraceId = i;
    table.Row.Name = "Data";
    //ETC
                
    //Commit the new row
    table.RowCommitAsNew();
}

//1. Setup
var args = new DB.Parameters<uspNVPSaveMany>("dbo.uspNVPSaveMany");
args.Data.values = table;

//2. Execute
using (var reader = DB.ExecuteReader<uspNVPSaveMany.Result, uspNVPSaveMany>(token, ref args))
{
    while (reader.Read())
    {
        //These will matchup by name
        var traceId = reader.Row.TraceId;
        var version = reader.Row.Version;
        //ETC
    }
}
//Want output parameters so transfer them once reader has closed
args.ReadAnyOutputs();

var lastSavedBy = args.Data.LastSavedBy;
var lastSavedAt = args.Data.LastSavedAtUtc;

Normalisation

This occurs on every set and get and all fields will be normalised to their Primary value including those embedded within nullable types as follows:

Type	Primary	Alternatives
`float`	NaN	PositiveInfinity, NegativeInfinity
`double`	NaN	PositiveInfinity, NegativeInfinity
`char`	'\0'
`DateTime`	`default(DateTime)`	`<= DateTime.MinValue`
`long`	`long.MaxValue`
`int`	`int.MaxValue`
`decimal`	`decimal.MaxValue`
`string`	`null`	`string.Empty`

Note that this table is about the DynamicField class used; the additional wrappers on top for SQL will also handle required string fields in the appropriate way by normalising to string.Empty.

The basic rule is if something matches then it is regarded as empty so the return result will be the Primary empty value.

The following example will result in normalisation as per above and the SQL wrappers will then send DBNull (recall that SQL does not allow subnormals):

struct SubNormals
{
    [FieldOptional]
    public double One = double.PositiveInfinity;
    public double? Two = double.NegativeInfinity;
    
    //This will throw before it is passed to the SQL
    public double Required = double.NaN;
}

The above mapping is essential in financial applications where subnormals are common. When dealing with a high volume of such calculations it is far better to avoid nullable types so the attributed approach is preferred.

Performance

It is difficult to get repeatable timings especially the "startup" time for first use of DynamicMethods within an AppDomain so these values are ignored here. Also the timings are presented only for the non-Database examples to avoid the impact of the delay-access behaviour of ADO.NET.

The timings for the original non-typesafe and the new approach from the samples are:

AddingTypeSafety - setup for New is 42ms and Original is only 8ms but subsequent runs for both are 0ms
DTOsToDataGrid - setup for New is 37ms and Original is only 12ms but subsequent runs for both are 0ms

Conclusion

The setup to use this approach is around four times slower but the compiled code and metadata caching makes subsequent runs no slower than direct manual coding. Note that the Database side of things would perform similarly but perhaps with the Reader being a little slower to setup each time.

Note that because .NET is optimised for fields and not properties (e.g. you can't take the address of properties) and because this solution uses structs and avoids Linq, it is also highly memory efficient both in the terse IL generated and in the (almost zero) heap usage.

Object Mapping

In this section are two examples of real-world code that revolves around object rather than other more typesafe approaches form the core of the timing tests just presented.

Fields on Objects

The existing code is maintaining an OO hierarchy for the objects but uses a basic non-typesafe object bag for the field storage as follows:

//Existing code is using untyped objects indexed by an enumeration
public enum FieldName
{
    FieldString,
    FieldDouble,
    FieldDateTime,
}

//Assume a lot of these kinds of collections exist within the OO hierarchy
readonly IDictionary<FieldName, object> Fields = 
new SortedList<FieldName, object>
{
    {FieldName.FieldString, "MyFieldString"},
    {FieldName.FieldDouble, 1.23},
    //FieldDateTime is not present
};

//Existing accessors look like this
public T GetFieldAs<T>(FieldName name) where T : class
{
    object res;
    Fields.TryGetValue(name, out res);
    return res as T;
}

In the methods of the OO hierarchy all the fields needed by that method then need to be extracted as follows:

public bool OldExtractValues(out FieldName? errorField)
{
    var firstField = GetFieldAs<string>(FieldName.FieldString);
    if (string.IsNullOrEmpty(firstField))
    {
        errorField = FieldName.FieldString;
        return false;
    }

    var secondField = GetField<double>(FieldName.FieldDouble);
    if (!secondField.HasValue)
    {
        errorField = FieldName.FieldDouble;
        return false;
    }
    //Repeat the above for each field needed

    //Now use the fields to do some business task
    //e.g.
    var someBusinessTask = firstField + secondField.ToString();
}

This can be replaced by a struct with the fields marked up within the OO hierarchy as follows:

public struct Values
{
    public string FieldString;
    public double FieldDouble;
    [FieldOptional]
    public DateTime FieldDateTime;
}

Then the methods can be simplified to focus on the business logic:

public static bool NewExtractValues(out FieldName? errorField)
{
    //All structs so no GC pressure
    var values = new Extractor<Values>();
    if (!values.Extract(Fields, out errorField)) return false;

    //Now use the fields to do some business task
    //e.g.
    var someBusinessTask = values.Fields.FieldString + values.Fields.FieldDouble.ToString();
}

And this shared routine wraps the DynamicFields class from this article:

//Common helper
public struct Extractor<T> where T : struct
{
    public T Fields;

    public bool Extract(IDictionary<FieldName, object> fields, out FieldName? errorField)
    {
        //Instance rather than using static in order to easily search by name
        var dynamic = new DynamicFields<T>();
        var ptr = __makeref(Fields);
        foreach (var field in dynamic)
        {
            //Will throw if T has a field that is not a valid FieldName enum member
            var key = Enum<FieldName>.Parse(field.Name);
            object value;
            fields.TryGetValue(key, out value);
            var code = field.TrySetUntyped(ptr, value);
            if (code > DynamicField.Code.IsEmpty)
            {
                errorField = key;
                return false;
            }
        }
        errorField = null;
        return true;
    }
}

Fields to DataGrid

The existing code has a Data Provider emitting DTOs in an OO hierarchy. Fields with the same name generally have the same type and other parts of the code (e.g. a DataGrid) expect boxed normalised values.

//Existing DTOs
public abstract class DTO
{
    //etc
}

public class Data1DTO : DTO
{
    public string FieldString;
    [FieldOptional]
    public double FieldOptionalDouble;
    public int FieldInteger;

    public Data1DTO()
        : base("Data1")
    {
    }
}

public class Data2DTO : DTO
{
    [FieldOptional]
    public string FieldString;
    public DateTime FieldDate;

    //etc
}

//Assume a lot of these came from a data provider
readonly DTO[] Data = new DTO[] 
{
    new Data1DTO { FieldString = "Data1Field1", FieldInteger = 1, 
                   FieldOptionalDouble = 1.23 },
    new Data1DTO { FieldString = "Data1Field2", FieldInteger = 2, 
                   FieldOptionalDouble = double.NaN },
    new Data2DTO { FieldString = "Data2Field1", FieldDate = DateTime.Today },
    new Data2DTO { FieldDate = DateTime.Today.AddDays(7) },
};

There is an existing map generated either at run-time using reflection or at compile time using T4 etc.

public enum OldFieldNames
{
    FieldString,
    FieldInteger,
    FieldDate,
    FieldOptionalDouble,
};

static readonly IDictionary<int, IDictionary<OldFieldNames, Func<DTO, object>>> OldMap
= new SortedList<int, IDictionary<OldFieldNames, Func<DTO, object>>>()
{
    {typeof(Data1DTO).MetadataToken, new SortedList<OldFieldNames, Func<DTO, object>>{
        {OldFieldNames.FieldString, dto => ((Data1DTO)dto).FieldString},
        {OldFieldNames.FieldInteger, dto => ((Data1DTO)dto).FieldInteger},
        {OldFieldNames.FieldOptionalDouble, dto => ((Data1DTO)dto).FieldOptionalDouble},
    }},
    {typeof(Data2DTO).MetadataToken, new SortedList<OldFieldNames, Func<DTO, object>>{
        {OldFieldNames.FieldString, dto => ((Data2DTO)dto).FieldString},
        {OldFieldNames.FieldDate, dto => ((Data2DTO)dto).FieldDate},
    }},
};

The existing approach to obtaining fields and columns etc is roughly like this (see sample code for full details):

public static object OldGetField(DTO dto, OldFieldNames field)
{
    object res = null;
    IDictionary<OldFieldNames, Func<DTO, object>> fields;
    if (OldMap.TryGetValue(dto.TypeId, out fields))
    {
        Func<DTO, object> extractor;
        if (fields.TryGetValue(field, out extractor))
        {
            res = extractor(dto);
        }
    }
    return res;
}

public static object[] OldGetColumn(OldFieldNames field)
{
    var res = new object[Data.Length];
    //etc
}

public static object[][] OldGetMatrix(params OldFieldNames[] fields)
{
    var res = new object[fields.Length][];
    //etc
}

A cleaner approach using the DynamicFields class from this article follows. Note that the same options for generating the NewMap below are still open:

static IDictionary<int, DynamicFields> NewMap;
static ISet<string> NewFieldNames;

static void NewCreateMap()
{
    //Done here so it can be timed
    NewMap = new SortedList<int, DynamicFields>()
    {
        {typeof(Data1DTO).MetadataToken, new DynamicFields(typeof(Data1DTO), typeof(DTO))},
        {typeof(Data2DTO).MetadataToken, new DynamicFields(typeof(Data2DTO), typeof(DTO))},
    };

    //Create the unique field list
    NewFieldNames = new SortedSet<string>();
    foreach (var pair in NewMap)
    {
        foreach (var field in pair.Value)
        {
            NewFieldNames.Add(field.Name);
        }
    }
}

Extraction with normalisation can now be done in one step:

public static object NewGetField(DTO dto, DynamicField.SearchKey key)
{
    object res = null;
    DynamicFields fields;
    if (NewMap.TryGetValue(dto.TypeId, out fields))
    {
        DynamicField field;
        if (fields.TryGet(key, out field) >= 0)
        {
            //Use the templated approach to hide the public use of TypedReferences
            res = field.GetUntyped(dto);
        }
    }
    return res;
}

//Other routines are similar to before

The DynamicField

This forms the core of the approach and is responsible for dissecting the Type and telling the DynamicGenerator to emit the IL for the DynamicMethods needed. It looks for one or more of the following attributes on each public field:

FieldOptionalAttribute - one included in the source.

Cached Values

The class makes the following data available post construction:

// Short name of the field owner
public readonly string ParentName;
// Short name of the field
public string Name { get; protected set; }
// Type of the field owner
public readonly Type ParentType;
// Type of the instance used to access this field. 
// Normally same as ParentType but for fields on derived classes this will be the 
// base class type if a base class instance will be used to access them.
public readonly Type RootType;
// Type of this field
public readonly Type Type;
// FieldInfo of this field
public readonly FieldInfo Field;
// Type of this field. If it was nullable, this is the underlying type
public readonly Type TypeNormalised;
// If IsGeneric is the Generic template otherwise null
public readonly Type TypeGenericTemplate;
// TypeCode of this field. If it was nullable, this is the underlying type's code.
public readonly TypeCode TypeCodeNormalised;
// Field is nullable (see IsNullableType). 
// Or field is: custom struct with HasValue property and the FieldOptional attribute (see IsUDT). 
// Or field is: DateTime/double/char/etc/reference-type with the FieldOptional attribute (see IsValueType).
public readonly bool IsOptional;
// Type is System.Nullable based
public readonly bool IsNullableType;
// Is an embedded structure
public readonly bool IsUDT;
// Any attributes, empty array if none
public readonly Attribute[] Attributes;

Result Codes

The setters and getters can return codes that take into account the definition of empty for that type and whether it is semantically optional or not:

// The result code for advanced getting/setting
public enum Code
{
    // Success - set/get was not empty (double, int, char, DateTime, etc treated specially, otherwise, means not null or HasValue)
    Ok = 0,
    // Success - set/get was empty (double, int, char, DateTime, etc treated specially, otherwise, means null or not HasValue).
    // When setting/getting, value is normalised to single empty representation.
    IsEmpty,
    // Failure - set/get failed due to type mismatch
    TypeMismatch,
    // Success - set/get was empty (double, int, char, DateTime, etc treated specially, otherwise, means null or not HasValue).
    // However, the field itself is required.
    NotOptional,
}

Getters

The getters exist in specialised typed and untyped versions and rely on TypedReferences:

public Code TryGetUntyped(TypedReference root, out object res)
{
    var code = _GetterBoxed(root, out res);
    return MapResult(code);
}
public Code TryGet(TypedReference root, out T res)
{
    var code = _Getter(root, out res);
    return MapResult(code);
}

Setters

The setters are similar:

public Code TrySetUntyped(TypedReference root, object value)
{
    var code = _SetterBoxed(root, value);
    return MapResult(code);
}
public Code TrySet(TypedReference root, T value)
{
    var code = _Setter(root, value);
    return MapResult(code);
}

What is a TypedReference?

Officially a TypedReference is just a managed pointer combined with the Type of the value being pointed to. They are first class citizens of the IL world but remain largely undocumented in the C# language even though it has support for them.

C#

An example of the C# language support for them is more helpful:

struct Test1
{
    public double Field;
}

struct Test2
{
    public Test1 Embedded;
}

static void Test()
{
    Test2 instance = new Test2();
    //0. Work with the embedded field
    TypedReference ptr = __makeref(instance.Embedded.Field);

    //1. Can extract type (double)
    Type typeOfPtr = __reftype(ptr);
    //2. Can read value 
    double myValue = __refvalue(ptr, double);
    //3. Can write value
    __refvalue(ptr, double) = 1.35;
}

IL

Note that there is no boxing/unboxing and this compiles straight down to concise, verifiable IL using the mkrefany and refanyval opcodes:

.locals init ([0] valuetype Test2 'instance', [1] typedref ptr)
ldloca.s   'instance'
initobj    Test2

//ptr = __makeref(instance.Embedded.Field)
ldloca.s   'instance'
ldflda     valuetype Test1 Test2::Embedded
ldflda     float64 Test1::Field
mkrefany   System.Double
stloc.1

//= __refvalue(ptr, double)
ldloc.1
refanyval  System.Double
ldind.r8

//__refvalue(ptr, double) = 1.35
ldloc.1
refanyval  System.Double
ldc.r8     1.3500000000000001
stind.r8

The DynamicGenerator

This is responsible for generating the DynamicMethods and creating delegates for subsequent use by the DynamicFields. Some highlights include:

Dynamic Methods

The main thing to note in CreateMethod is that when creating a DynamicMethod there is a complex set of security rules applied to both a) what types are visible and b) whether the IL inside the method must be verifiable or not. The best approach is to neutralise both issues as follows:

static MethodInfo CreateMethod(out ILGenerator ilg, string name, Type returnType, Type rootType = null, params Type[] args)
{
    //If a type rootType or explicit module are not specified then the security restricts the use of unverifiable IL etc.
    var moduleForSecurity = rootType != null ? rootType.Module : typeof(DynamicGenerator).Module;

    //Skipping all visibility checks allows more flexibility in accessing private nested types for example
    var res = new DynamicMethod(name, returnType, args, moduleForSecurity, skipVisibility: true);
    ilg = res.GetILGenerator();
    return res;
}

static Delegate CreateDelegate(Type type, MethodInfo method)
{
    return ((DynamicMethod)method).CreateDelegate(type);
}

Fast Creators

It is useful to be able to create class instances on the fly without using reflection even if they have non-public constructors with arguments. The CreateCreator<> and related overloads perform this task and all end up here:

static Delegate _CreateCreator(Type @delegate, string name, Type retType, Type typeToCreate, Type rootType, params Type[] constructorArgs)
{
    ILGenerator il;
    var res = CreateMethod(out il, name, retType, rootType, constructorArgs);

    _EmitCreator(il, constructor, retType);

    return CreateDelegate(@delegate, res);
}

The emission of the IL can often be more elegant and easier to understand than the Linq Expressions alternative:

static void _EmitCreator(ILGenerator il, ConstructorInfo constructor, Type typeToReturn)
{
    var typeToCreate = constructor.DeclaringType;
    var argc = constructor.GetParameters().Length;

    for (int i = 0; i < argc; i++) il.Emit(OpCodes.Ldarg, i);
    il.Emit(OpCodes.Newobj, constructor);
    if (typeToReturn != null && typeToReturn != typeToCreate) il.Emit(OpCodes.Castclass, typeToReturn);
    il.Emit(OpCodes.Ret);
}

Using Callbacks

A callback approach is used in order to allow combining together different typed routines without generating everything through IL. The API is slightly different from the usual Getters and makes heavy use of TypedReferences:

public Code TryGet(TypedReference root, DynamicGenerator.DelegateGetterCallback callback, TypedReference state)
{
    var code = _GetterViaCallback(root, callback, state);
    return MapResult(code);
}

As an example, assume that there is a set of pre-defined delegates each one handling a specific type:

struct myState
{
    public Delegate SomeRoutineThatTakesATypedReference;
}

void MyGetterCallback(TypedReference state, TypedReference value, DynamicGenerator.Result code)
{
    //Do nothing on failure
    if (code == DynamicGenerator.Result.TypeMismatch) return;
 
    //Access myState
    myState typedState = __refvalue(state, myState);
    //And let it process the value
    typedState.SomeRoutineThatTakesATypedReference(value);
}

Then a routine like this can be created that accepts the instance (via the usual TypedReference) along with a DynamicField on it, chooses the right delegate and indirectly causes the normalised valued to be sent to it:

void Test(TypedReference root, DynamicField field)
{
    var state = new myState();
    state.SomeRoutineThatTakesATypedReference = null; //Set delegate based on type of this field

    //And access field
    var code = field.TryGet(root, MyGetterCallback, __makeref(state));
}

Note that this approach is the crux of how the SQL wrappers handle reading/writing values from/to the SQL classes without boxing.

The SQL Wrappers

Applying the described techniques to creating SQL wrappers involved a lot of spelunking through reverse engineered code for the SQL ADO.NET provider. This resulted in three classes: DB.Parameters for SqlParameters, DB.Table for Table Valued Parameters and DB.Reader for the DbReader itself.

DB.Field

The DB.Field class is built on top of the DynamicField and the DynamicGenerator. It looks for one or more of the following additional attributes on each public field:

SqlFacetAttribute - to control length of strings primarily;
DB.ParameterAttribute - to control the SqlParameter direction and name.

It contains the following information:

// The SQL TVP position
public readonly int Ordinal;
// The field name
public string Name { get; protected set; }
// The backing dynamic field
public readonly DynamicField Binder;
// The name of the sql parameter (use DB.ParameterAttribute/DB.ResultAttribute to customise)
public string SqlName;
// The sql db type
public readonly SqlDbType SqlType;
// Where applicable, the max character length (use SqlFacetAttribute on Strings for e.g.)
public readonly int SqlMaxLength;
// The direction of the parameter - see DB.ParameterAttribute
public readonly ParameterDirection SqlDirection;

// If this is an input or inputoutput parameter
public bool IsInput { get { return (((int)SqlDirection) & 1) != 0; } }
// If this is an output or resultvalue parameter
public bool IsOutput { get { return !IsInput; } }
// If this is a table valued parameter
public bool IsTVP { get { return SqlType == SqlDbType.Structured; } }

The callback technique described earlier is used to set values into a SqlDataRecord and also to create the right SqlType around the given typed value. Here is the callback for the former:

void SetValueCallback(TypedReference stateRef, TypedReference valueRef, DynamicGenerator.Result code)
{
    //Ignore if empty
    if (code == DynamicGenerator.Result.Ok)
    {
        var state = __refvalue(stateRef, StateForDataRecord);
        state.Setter(state, valueRef);
    }
}

The callback for the latter involves storing the creation of the (boxed) SqlType back into the state structure:

void CreateParameterCallback(TypedReference stateRef, TypedReference valueRef, DynamicGenerator.Result code)
{
    //Ignore if empty
    if (code == DynamicGenerator.Result.Ok)
    {
        var state = __refvalue(stateRef, StateForParameter);
        __refvalue(stateRef, StateForParameter).SqlValue = state.Creator(state, valueRef);
    }
}

This is then exposed to the other DB classes to extract fields as boxed SqlTypes as follows:

// Gets the current field value and if not empty creates a (boxed) Sql type
// Result will be null if value was empty but result code can be used instead.
// Note that required strings/char that are empty will be set to Sql empty strings and not reported as NotOptional.
// Returns the result code.
internal DynamicField.Code TryCreateSqlValue(TypedReference source, out object sqlValue)
{
    var state = new StateForParameter
    {
        IsNullable = Binder.IsNullableType,
        Creator = _map[Binder.TypeCodeNormalised].Value2,
        SqlValue = null
    };

    var res = Binder.TryGet(source, CreateParameterCallback, __makeref(state));
    if (res == DynamicField.Code.NotOptional && SqlType == SqlDbType.NVarChar)
    {
        //String.Empty has special meaning for non nullable sql string fields
        sqlValue = _SqlStringEmptyBoxed;
        return DynamicField.Code.Ok;
    }
    sqlValue = state.SqlValue;
    return res;
}

DB.Parameters<T>

This generic struct wraps a marked up one and uses DB.Fields to read/write values from/to SqlParameters. A rough overview of the key routine follows (see source for full details):

internal SqlParameter[] CreateParameters()
{
    _parameters = new SqlParameter[_metaData.Length];
    var ptr = __makeref(Data);
    for (int i = 0; i < _metaData.Length; i++)
    {
        var info = _metaData[i];
        var sql = _parameters[i];
        //... etc ...
        if (info.IsTVP)
        {
            code = info.Binder.TryGetUntyped(ptr, out value);
            //Cannot be set to DBNull
            if (code == DynamicField.Code.Ok)
            {
                sql.Value = (IEnumerable<SqlDataRecord>)value;
                continue;
            }
        }
        else
        {
            code = info.TryCreateSqlValue(ptr, out value);
            if (code == DynamicField.Code.IsEmpty)
            {
                //TODO: Setting value to null is different from setting SqlValue to null:
                //Use Value to send a NULL value as the value of the parameter. 
                //Use null or do not set SqlValue to use the default value for the parameter
                sql.Value = null;
                continue;
            }
            if (code == DynamicField.Code.Ok)
            {
                sql.SqlValue = value;
                continue;
            }
        }
        //... etc ...
    }
}

DB.Reader<T>

This generic struct wraps a marked up one and uses DB.Fields to stream values from the underlying DbReader. A rough overview of the key routine follows (see source for full details):

public bool Read()
{
    //... etc ...
    var ptr = __makeref(Row);
    var state = new StateForGetter();

    for (int i = 0; i < _metaData.Length; i++)
    {
        var md = _metaData[i];
        if (_reader.IsDBNull(i))
        {
            if (md.IsOptional)
            {
                code = md.TrySetUntyped(ptr, null);
            }
            else
            {
                code = DynamicField.Code.NotOptional;
            }
        }
        else
        {
            state.Ordinal = i;
            state.Field = md;
            code = _map[md.TypeCodeNormalised](state, ptr);
            //... etc ...
        }
        if (code <= DynamicField.Code.IsEmpty) continue;
        //... etc ...
    }
}

The approach used here relies on generics and the .NET TypeCode to choose the right delegate to call. Note how the TypedReference is simply passed through to the DynamicField:

//Taken from SqlMetaData InferFromValue and the Constructors
static readonly IDictionary<TypeCode, SqlGetterDelegate> _map =
    new SortedList<TypeCode, SqlGetterDelegate>
        (Enum<TypeCode>.Count, Enum<TypeCode>.Comparer)
{
    {TypeCode.Boolean,  (s, tr) => s.Set<bool>    (tr, s.Reader.GetBoolean(s.Ordinal))},
    {TypeCode.Byte,     (s, tr) => s.Set<byte>    (tr, s.Reader.GetByte(s.Ordinal))},
    {TypeCode.Char,     (s, tr) => s.Set<char>    (tr, s.Reader.GetString(s.Ordinal)[0])},
    {TypeCode.DateTime, (s, tr) => s.Set<DateTime>(tr, s.Reader.GetDateTime(s.Ordinal))},
    {TypeCode.Decimal,  (s, tr) => s.Set<decimal> (tr, s.Reader.GetDecimal(s.Ordinal))},
    {TypeCode.Double,   (s, tr) => s.Set<double>  (tr, s.Reader.GetDouble(s.Ordinal))},
    {TypeCode.Int16,    (s, tr) => s.Set<short>   (tr, s.Reader.GetInt16(s.Ordinal))},
    {TypeCode.Int32,    (s, tr) => s.Set<int>     (tr, s.Reader.GetInt32(s.Ordinal))},
    {TypeCode.Int64,    (s, tr) => s.Set<long>    (tr, s.Reader.GetInt64(s.Ordinal))},
    {TypeCode.Single,   (s, tr) => s.Set<float>   (tr, s.Reader.GetFloat(s.Ordinal))},
    {TypeCode.String,   (s, tr) => s.Set<string>  (tr, s.Reader.GetString(s.Ordinal))},
};
delegate DynamicField.Code SqlGetterDelegate(StateForGetter state, TypedReference tr);

struct StateForGetter
{
    public SqlDataReader Reader;
    public int Ordinal;
    public DynamicField Field;

    public DynamicField.Code Set<FT>(TypedReference ptr, FT value)
    {
        return ((DynamicField<FT>)Field).TrySet(ptr, value);
    }
}

DB.Table<T>

This generic struct wraps a Row and uses DB.Fields to store values for later playback to the SqlDataRecord - i.e. this is a Table Value Parameter. Note that the normal approach uses the inefficient DataTable but this relies on a List<T> of Rows and therefore has to manage the SqlMetaData manually.

A rough overview follows (see source for full details):

public class Table<T> : IEnumerable<SqlDataRecord>, IEnumerator<SqlDataRecord>
{
    // The current row when enumerating.
    // The new row to fill in when adding.
    public T Row;

    readonly List<T> _rows;
    readonly DB.Field[] _metaData;
    int _current;

    //... etc ...

    DB.Field.SqlDataRecordExtended _record;
    Table<T> PrepareSqlDataRecord()
    {
        if (_record == null)
        {
            var count = _metaData.Length;
            var md = new SqlMetaData[count];
            for (int i = 0; i < count; i++)
            {
                DB.Field info = _metaData[i];
                var m = info.SqlMaxLength != 0 ? 
                          new SqlMetaData(info.Name, info.SqlType, info.SqlMaxLength) 
                        : new SqlMetaData(info.Name, info.SqlType);
                //Sql ordinal differs from index of field in this structure
                var ord = info.Ordinal;
                md[ord] = m;
            }
            _record = new DB.Field.SqlDataRecordExtended(md);
        }
        ReadReset();
        return this;
    }

    bool System.Collections.IEnumerator.MoveNext()
    {
        if (ReadMoveNext())
        {
            var ptr = __makeref(Row);
            for (int i = 0; i < _metaData.Length; i++)
            {
                DB.Field info = _metaData[i];
                var code = info.TrySetValue(_record, ptr);
                if (code == DynamicField.Code.Ok) continue;
                if (code == DynamicField.Code.IsEmpty)
                {
                    //Re-using record so have to reset value
                    _record.SetDBNull(info.Ordinal);
                    continue;
                }
                //Error case
                //... etc ...
            }
        }
    }
}

The Database Manager

The DB static class is a lightweight singleton class to contain the other types and to provide simple helper routines.

Initialisation

This wraps a SqlConnectionStringBuilder with the normal settings required for Enterprise environments:

/// <summary>
/// Initialises the specified data source.
/// </summary>
/// <param name="displayName">The display name.</param>
/// <param name="dataSource">The data source.</param>
/// <param name="initialCatalog">The initial catalog.</param>
/// <param name="applicationName">Name of the application.</param>
public static void Initialise(string displayName, string dataSource, string initialCatalog, string applicationName)
{
    //See source for details
}

Execution

The following generic typed routines are available, all with or without DB.Parameters and cancellation support:

ExecuteNonQuery
ExecuteScalar
ExecuteReader - returning a typed DB.Reader
ExecuteReaderRaw - returning the usual SqlDataReader

For very simple fixed-position private stored procedures it can be easier to just use ExecuteReaderRaw in which case the following extension routines become useful:

// Extension method: handles the db null translation - additionally, translates empty and whitespace strings to null
internal static string GetOptionalString(this SqlDataReader @this, int index)
{
    var res = @this[index];
    if (res != DBNull.Value)
    {
        var str = (string)res;
        if (!string.IsNullOrWhiteSpace(str)) return str;
    }
    return null;
}

// Extension method: handles the db null translation
internal static T? GetOptionalValue<T>(this SqlDataReader @this, int index) where T : struct
{
    var res = @this[index];
    return res != DBNull.Value ? (T?)res : default(T?);
}

TVP Helpers

Table Valued Parameters only have to match the position and type of each field defined in the SQL UDT. As a result, the following helper routines can provide the benefits of typesafe streaming without the typing overhead:

// Creates a table value parameter based on a collection of values
public static Table<KeyStruct<T>> CreateTVP<T>(int count, IEnumerable<T> items)
{
    var table = new Table<KeyStruct<T>>(count);
    foreach (var item in items)
    {
        table.Row.Key = item;
        table.RowCommitAsNew();
    }
    return table;
}

Surrogates

The examples describe a situation where there might be a high number of instances of some Key class and only a subset of the fields within that class are required to be streamed into a TVP. In this case, the DB.Table class can use the Key instances directly as follows:

//Used as a surrogate to allow mapping onto the public Key
public struct udtKeySurrogate
{
    //Cache the mapping
    public readonly static DB.Field[] MD = DB.Table<Key>.CreateMetaData<udtKeySurrogate>();

    //Names match Key; order matches Sql Udt
    public long Value;
}

public struct uspNVPLoadById
{
    //The normal key
    [DB.Parameter("@ids")]
    public DB.Table<Key> Keys;
    
    //... etc ...
}

In this unique case a helper on DB allows for easy streaming of the data:

var keys = new List<Key>(/* lots of keys */);
    
//Create the TVP data directly off the Keys
//i.e. table will refer to the same Key instances and will not duplicate all the data again
var table = DB.CreateTVP(keys, udtKeySurrogate.MD);

Then setup continues as usual (see the examples for the full details):

//1. Setup
var args = new DB.Parameters<uspNVPLoadById>("dbo.uspNVPLoadById");
args.Data.Keys = table;

Running the Examples

There are two examples showing the timing of the AddingTypeSafety and DTOsToDataGrid situations which do not require a Database.

To run the other samples, the following should be done:

Using Visual Studio 2012 will work, otherwise, the Data Tools may need to be installed on 2010;
Double click the SQL.publish.xml file;
If this says it cannot connect, then locate sqllocaldb.exe and run it with 'start' and 'Projects' as the arguments.

The SqlLocalDB management utility is in the SqlLocalDB.MSI on MSDN.

References

History

1.0 - Initial write up
1.1 - Added section on subnormals and memory efficiency

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here