Dapper. Map to SQL Column with spaces in column names

Question

I've managed to get something up and running today as small sandbox/POC project, but have seemed to bump my head on one issue...

Question:

Is there a way to get dapper to map to SQL column names with spaces in them.

I have something to this effect as my result set.

For example:

SELECT 001 AS [Col 1], 
       901 AS [Col 2],
       00454345345345435349 AS [Col 3],
       03453453453454353458 AS [Col 4] 
FROM [Some Schema].[Some Table]

And my class would look like this

    public class ClassA
    {          
        public string Col1 { get; set; }    

        public string Col2 { get; set; }

        ///... etc
     }

My implementation looks like this at the moment

 public Tuple<IList<TClass>, IList<TClass2>> QueryMultiple<TClass, TClass2>(object parameters)
 {
      List<TClass> output1;
      List<TClass2> output2;

      using (var data = this.Connection.QueryMultiple(this.GlobalParameter.RpcProcedureName, parameters, CommandType.StoredProcedure))
      {
           output1 = data.Read<TClass>().ToList();
           output2 = data.Read<TClass2>().ToList();
      }

      var result = new Tuple<IList<TClass>, IList<TClass2>>(output1, output2);
      return result;
  }

Note: The SQL cant be modified in any way.

Currently I'm going through the dapper code, and my only foreseeable solution is to add some code to "persuade" the column comparison, but not having much luck so far.

I've seen on StackOverflow that there are things like dapper extensions, but I'm hoping I can get this done without adding an extention, if not. I'll take whatever is quickest to implement.

Answer 1

There's a nuget package Dapper.FluentMap that allows you to add column name mappings (including spaces). It's similar to EntityFramework.

// Entity class.
public class Customer
{
    public string Name { get; set; }
}

// Mapper class.
public class CustomerMapper : EntityMap<Customer>
{
    public CustomerMapper()
    {
        Map(p => p.Name).ToColumn("Customer Name");
    }
}

// Initialise like so - 
FluentMapper.Initialize(a => a.AddMap(new CustomerMapper()));

see https://github.com/henkmollema/Dapper-FluentMap for more.

Answer 2

One option here would be to go via the dynamic / non-generic API, and then fetch the values out via the IDictionary<string,object> API per row, but that might be a bit tedious.

As an alternative, you can create a custom mapper, and tell dapper about it; for example:

SqlMapper.SetTypeMap(typeof(ClassA), new RemoveSpacesMap());

with:

class RemoveSpacesMap : Dapper.SqlMapper.ITypeMap
{

    System.Reflection.ConstructorInfo SqlMapper.ITypeMap.FindConstructor(string[] names, Type[] types)
    {
        return null;
    }

    SqlMapper.IMemberMap SqlMapper.ITypeMap.GetConstructorParameter(System.Reflection.ConstructorInfo constructor, string columnName)
    {
        return null;
    }

    SqlMapper.IMemberMap SqlMapper.ITypeMap.GetMember(string columnName)
    {
        var prop = typeof(ClassA).GetProperty(columnName.Replace(" ", ""));
        return prop == null ? null : new PropertyMemberMap(columnName, prop);
    }
    class PropertyMemberMap : Dapper.SqlMapper.IMemberMap
    {
        private string columnName;
        private PropertyInfo property;
        public PropertyMemberMap(string columnName, PropertyInfo property)
        {
            this.columnName = columnName;
            this.property = property;
        }
        string SqlMapper.IMemberMap.ColumnName
        {
            get { throw new NotImplementedException(); }
        }

        System.Reflection.FieldInfo SqlMapper.IMemberMap.Field
        {
            get { return null; }
        }

        Type SqlMapper.IMemberMap.MemberType
        {
            get { return property.PropertyType; }
        }

        System.Reflection.ParameterInfo SqlMapper.IMemberMap.Parameter
        {
            get { return null; }
        }

        System.Reflection.PropertyInfo SqlMapper.IMemberMap.Property
        {
            get { return property; }
        }
    }
}

Answer 3

I had a similar problem when trying to get mapped results from a call to the system sp_spaceused procedure. Marc's code didn't quite work for me as it complained about not being able to find a default constructor. I also made my version generic so it could theoretically be re-used. This may not be the fastest performing piece of code, but it works for me and in our situation these calls are made infrequently.

class TitleCaseMap<T> : SqlMapper.ITypeMap where T: new()
{
    ConstructorInfo SqlMapper.ITypeMap.FindConstructor(string[] names, Type[] types)
    {
        return typeof(T).GetConstructor(Type.EmptyTypes);
    }

    SqlMapper.IMemberMap SqlMapper.ITypeMap.GetConstructorParameter(ConstructorInfo constructor, string columnName)
    {
        return null;
    }

    SqlMapper.IMemberMap SqlMapper.ITypeMap.GetMember(string columnName)
    {
        string reformattedColumnName = string.Empty;

        foreach (string word in columnName.Replace("_", " ").Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries))
        {
            reformattedColumnName += char.ToUpper(word[0]) + word.Substring(1).ToLower();
        }

        var prop = typeof(T).GetProperty(reformattedColumnName);

        return prop == null ? null : new PropertyMemberMap(prop);
    }

    class PropertyMemberMap : SqlMapper.IMemberMap
    {
        private readonly PropertyInfo _property;

        public PropertyMemberMap(PropertyInfo property)
        {
            _property = property;
        }
        string SqlMapper.IMemberMap.ColumnName
        {
            get { throw new NotImplementedException(); }
        }

        FieldInfo SqlMapper.IMemberMap.Field
        {
            get { return null; }
        }

        Type SqlMapper.IMemberMap.MemberType
        {
            get { return _property.PropertyType; }
        }

        ParameterInfo SqlMapper.IMemberMap.Parameter
        {
            get { return null; }
        }

        PropertyInfo SqlMapper.IMemberMap.Property
        {
            get { return _property; }
        }
    }
}

Answer 4

I know this is an old question nevertheless i faced the same problem in my last project, so i just created an own mapper using attributes.

I defined an attribute class called ColumnNameAttribute.cs

using System;

namespace DapperHelper.Attributes
{
    [System.AttributeUsage(AttributeTargets.All, Inherited = true, AllowMultiple = true)]
    sealed class ColumNameAttribute : Attribute
    {
        private string _columName;

        public string ColumnName
        {
            get { return _columName; }
            set { _columName = value; }
        }

        public ColumNameAttribute(string columnName)
        {
            _columName = columnName;
        }
    }
}

After defining the attribute, i implemeted a dynamic mapper that uses the Query method from Dapper but works as the Query<T>:

using Dapper;
using DapperHelper.Attributes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web.Routing;

namespace DapperHelper.Tools
{
    public class DynamicMapper<T> :IDisposable where  T : class, new()
    {
        private readonly Dictionary<string, PropertyInfo> _propertiesMap;

        public DynamicMapper()
        {
            _propertiesMap = new Dictionary<string, PropertyInfo>();

            PropertyInfo[] propertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (propertyInfo.GetCustomAttribute(typeof(ColumNameAttribute)) is ColumNameAttribute columNameAttribute)
                {
                    _propertiesMap.Add(columNameAttribute.ColumnName, propertyInfo);
                }
                else
                {
                    _propertiesMap.Add(propertyInfo.Name, propertyInfo);
                }
            }
        }

        public List<T> QueryDynamic(IDbConnection dbConnection, string sqlQuery)
        {
            List<dynamic> results = dbConnection.Query(sqlQuery).ToList();

            List<T> output = new List<T>();

            foreach (dynamic dynObj in results)
            {
                output.Add(AssignPropertyValues(dynObj));
            }

            return output;
        }

        private T AssignPropertyValues(dynamic dynamicObject)
        {
            T output = new T();

            RouteValueDictionary dynamicObjProps = new RouteValueDictionary(dynamicObject);

            foreach (var propName in dynamicObjProps.Keys)
            {
                if (_propertiesMap.TryGetValue(propName, out PropertyInfo propertyMapped)
                    && dynamicObjProps.TryGetValue(propName, out object value))
                {
                    propertyMapped.SetValue(output, value);
                }
            }

            return output;
        }


        public void Dispose()
        {
            _propertiesMap.Clear(); 
        }
    }
}

To use it, you have to refer to your Model class and define the attribute:

 using DapperHelper.Attributes;

namespace Testing
    {
        public class Sample
        {
            public int SomeColumnData { get; set; }

            [ColumnName("Your Column Name")]
            public string SpecialColumn{ get; set; }

        }
    }

and then you can implement something like this:

DynamicMapper<Sample> mapper = new DynamicMapper<Sample>();

List<Sample> samples = mapper.QueryDynamic(connection, "SELECT * FROM Samples");

I hope it can help someone looking for an alternative.

Answer 5

Create a Model class (e.g, Employee)

public class Employee
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Now create a Mapper class to Map the columns which are retrieved from DB (like SQL).

For example, if you are retrieving a List of Employees in .Net core (Blazor), your code might look something like below:

public async Task<IEnumerable<Employee>> GetEmployeeData()
{
    return await _db.QueryFromSPAsync<Employee, dynamic>("NameOfYourStoredProcedure",new { });
}

// Employee Mapper class

public class EmployeeMapper : EntityMap<Employee>
{
    public EmployeeMapper()
    {
        Map(p => p.ID).ToColumn("ID");
        Map(p => p.FirstName).ToColumn("First Name");
        Map(p => p.LastName).ToColumn("Last Name");
    }
}

You can initialise the Mapper as below:

//In your Program.cs

FluentMapper.Initialize(Map =>
    {
        Map.AddMap(new SLAInsuredMapper());
    }
);

Note: Don't forget to install Dapper.FluentMap NuGet package.