Anonymous Types As Object Templates

/// <summary>
/// Helps working with database connections
/// </summary>
public class DataConnection {

    #region Constructors

    /// <summary>
    /// Creates a new DataConnection for the connection string
    /// </summary>
    public DataConnection(string connection) {
        this.ConnectionString = connection;
    }

    #endregion

    #region Properties

    /// <summary>
    /// The connection string to use with this DataConnection
    /// </summary>
    public string ConnectionString { get; private set; }

    #endregion

    #region Performing Queries

    /// <summary>
    /// Performs a query for the connection without any parameters
    /// </summary>
    public IEnumerable<T> Query<T>(string query, T template)
        where T : class {
        return this.Query(query, (object)null, template);
    }

    /// <summary>
    /// Performs a query for the connection with the provided paramters
    /// </summary>
    public IEnumerable<T> Query<U, T>(string query, U parameters, T template)
        where T : class 
        where U : class {

        //prepare the connection and command
        Type type = template.GetType();

        //create the connection - (thanks @johnsheehan about the 'using' tip)
        using (SqlConnection connection = new SqlConnection(this.ConnectionString)) {
            using (SqlCommand command = new SqlCommand(query, connection)) {

                //assign each of the properties
                if (parameters != null) {
                    this._UsingProperties(
                        parameters,
                        (name, value) => command.Parameters.AddWithValue(name, value));
                }

                //execute the query
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                //start reading each of the records
                List<T> results = new List<T>();
                Dictionary<string, int> fields = null;
                while (reader.Read()) {

                    //prepare the fields for the first time if needed
                    fields = fields == null ? this._ExtractFields(reader) : fields;

                    //generate the record
                    T record = this._CreateAnonymousResult(reader, fields, type, template);
                    results.Add(record);
                }

                //return the results for the query
                return results.AsEnumerable();
            }

        }

    }

    #endregion

    #region Helpers

    //creates a new anonymous type from 
    private T _CreateAnonymousResult<T>(SqlDataReader reader, 
    Dictionary<string, int> 
    fields, Type templateType, 
    T template) where T : class {

        //create a container to queue up each record
        List<object> values = new List<object>();

        //use the template to find values
        this._UsingProperties(template, (name, @default) => {

            //try and find the field name
            if (fields.ContainsKey(name)) {

                //check if this is a value
                int index = fields[name];
                object value = reader[index];
                Type convert = @default.GetType();

                //try and copy the va;ue
                if (value != null) {
                    values.Add(Convert.ChangeType(value, convert));
                }
                //not the same type, use the default
                else {
                    value.Equals(@default);
                }

            }
            //no field was found, just use the default
            else {
                values.Add(@default);
            }

        });

        //with each of the values, invoke the anonymous constructor
        //which accepts each of the values on the template
        try {
            return Activator.CreateInstance(templateType, values.ToArray()) as T;
        }
        catch (Exception e) {
            Console.WriteLine(e.Message);
            return template;
        }

    }

    //reads a set of records to determine the field names and positions
    private Dictionary<string, int> _ExtractFields(SqlDataReader reader) {
        Dictionary<string, int> fields = 
      new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        for (int i = 0; i < reader.FieldCount; i++) {
            fields.Add(reader.GetName(i), i);
        }
        return fields;
    }

    //performs an action with each of the properties on an object
    private void _UsingProperties(object obj, Action<string, object> with) {

        //if there isn't anything to work with, just cancel
        if (obj == null) { return; }

        //get the type and peform an action for each property
        Type type = obj.GetType();
        foreach (PropertyInfo prop in type.GetProperties()) {
            with(prop.Name, prop.GetValue(obj, null));
        }

    }

    #endregion

}

//set up the connection
DataConnection data = new DataConnection(CONNECTION_DATA);

//perform the query
var results = data.Query(
    "select * from orders where orderId > @orderId",
    new { orderId = 11000 },
    new {
        orderId = -1,
        shipName = "missing",
        shipRegion = "none"
    });

//access properties
var record = results.First();
int id = record.orderId; //intellisense!