Alternative to reflection

c#, generics, reflection

Solution

If you want to get rid of reflection, you may find inspiration in the code below.

Here all access to objects to store in database as well as the sql property value assignment is handled by a runtime compiled expression build from the data type.

The table holding the values is assumed to be `test` and the field names are assumed to be identical to the property values.

For each property a `Mapping<T>` is constructed. It will hold a `FieldName` containing the database field, a `SqlParameter` which is supposed to be inserted correctly into a SQL `INSERT` statement (example in `main`) and finally if contains the compiled action, that can take an instance of the input `T` object and assign the value to the `SqlParameters` property `Value`. Construction of a collection of these mappings are done in the `Mapper<T>` class. Code is inlined for explanation.

Finally the `main` method shows how to bind the stuff together.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;

namespace ExpTest
{
    class Program
    {
        public class Mapping<T>
        {
            public Mapping(string fieldname, SqlParameter sqlParameter, Action<T, SqlParameter> assigner)
            {
                FieldName = fieldname;
                SqlParameter = sqlParameter;
                SqlParameterAssignment = assigner;
            }
            public string FieldName { get; private set; }
            public SqlParameter SqlParameter { get; private set; }
            public Action<T, SqlParameter> SqlParameterAssignment { get; private set; }
        }

        public class Mapper<T>
        {
            public IEnumerable<Mapping<T>> GetMappingElements()
            {
                foreach (var reflectionProperty in typeof(T).GetProperties())
                {
                    // Input parameters to the created assignment action
                    var accessor = Expression.Parameter(typeof(T), "input");
                    var sqlParmAccessor = Expression.Parameter(typeof(SqlParameter), "sqlParm");

                    // Access the property (compiled later, but use reflection to locate property)
                    var property = Expression.Property(accessor, reflectionProperty);

                    // Cast the property to ensure it is assignable to SqlProperty.Value 
                    // Should contain branching for DBNull.Value when property == null
                    var castPropertyToObject = Expression.Convert(property, typeof(object));


                    // The sql parameter
                    var sqlParm = new SqlParameter(reflectionProperty.Name, null);

                    // input parameter for assignment action
                    var sqlValueProp = Expression.Property(sqlParmAccessor, "Value");

                    // Expression assigning the retrieved property from input object 
                    // to the sql parameters 'Value' property
                    var dbnull = Expression.Constant(DBNull.Value);
                    var coalesce = Expression.Coalesce(castPropertyToObject, dbnull);
                    var assign = Expression.Assign(sqlValueProp, coalesce);

                    // Compile into action (removes reflection and makes real CLR object)
                    var assigner = Expression.Lambda<Action<T, SqlParameter>>(assign, accessor, sqlParmAccessor).Compile();

                    yield return
                        new Mapping<T>(reflectionProperty.Name, // Table name
                            sqlParm, // The constructed sql parameter
                            assigner); // The action assigning from the input <T> 

                }
            }
        }

        public static void Main(string[] args)
        {
            var sqlStuff = (new Mapper<Data>().GetMappingElements()).ToList();

            var sqlFieldsList = string.Join(", ", sqlStuff.Select(x => x.FieldName));
            var sqlValuesList = string.Join(", ", sqlStuff.Select(x => '@' + x.SqlParameter.ParameterName));

            var sqlStmt = string.Format("INSERT INTO test ({0}) VALUES ({1})", sqlFieldsList, sqlValuesList);

            var dataObjects = Enumerable.Range(1, 100).Select(id => new Data { Foo = 1.0 / id, ID = id, Title = null });

            var sw = Stopwatch.StartNew();

            using (SqlConnection cnn = new SqlConnection(@"server=.\sqlexpress;database=test;integrated security=SSPI"))
            {
                cnn.Open();

                SqlCommand cmd = new SqlCommand(sqlStmt, cnn);
                cmd.Parameters.AddRange(sqlStuff.Select(x => x.SqlParameter).ToArray());

                dataObjects.ToList()
                    .ForEach(dto =>
                        {
                            sqlStuff.ForEach(x => x.SqlParameterAssignment(dto, x.SqlParameter));
                            cmd.ExecuteNonQuery();
                        });
            }


            Console.WriteLine("Done in: " + sw.Elapsed);
        }
    }

    public class Data
    {
        public string Title { get; set; }
        public int ID { get; set; }
        public double Foo { get; set; }
    }
}

Problem

Original source