Insert enum as string using Dapper.Contrib

Question

I've just started using Dapper.Contrib to help me with inserts and gets but since my enums are stored as strings in the database (for several reasons) I'm having trouble with inserts. Dapper works seamlessly with string enums when reading, but inserts will always put the ordinal value into the database.

I've read many proposals to Dapper for that, and quite a few issues opened but didn't find a working solution. My simplified class looks like the following:

public class Person {
  public long Id { get; set; }
  public string Name { get; set; }
  public Gender Gender { get; set; }
}

public enum Gender { Female, Male, TransWoman, TransMan }

I was expecting I could configure Dapper.Contrib to issue Inserts using enum names instead of ordinal values, so that the code bellow would magically work and insert 'Male' in the varchar(20) database field Gender:

void InsertPersonFelipe(SqlConnection conn) {
  var person = new Person { Name = "Felipe", Gender = Gender.Male };
  conn.Insert(person);
}

Is there a way to add custom mapping for typeof(Gender)?

Or, better yet, does Dapper.Contrib provides a configuration to make it use enum names instead of their ordinal values?

@rsilva I've given up on this, and starded writing my inserts by hand instead of using Dapper.Contrib, and then I just use my enum ToString method... it wasn't such a big deal for me, but it would be nice if someone answered this ;-) — Loudenvier, Nov 20 '17 at 22:59

score 0 · Answer 1 · answered Nov 23 '18 at 19:21

I've written an extension method to handle translating the enum into a string and takes into account the Table and Computed attributes in Dapper.Contrib. You could just as easily take these out if you didn't want to reference Dapper.Contrib.

Usage:

  using (var sql = new SqlConnection(_connString))
  {
      sql.Open();
      sql.InsertB(person);
  }

Extension method:

    public static long InsertB<T>(this SqlConnection sqlConnection, T obj)
    {
        Dictionary<string, object> propertyValuesMap = new Dictionary<string, object>();

        var columns = new StringBuilder();
        var values = new StringBuilder();
        var tableName = ((TableAttribute)obj.GetType().GetCustomAttribute(typeof(TableAttribute))).Name;
        var relevantProperties = obj.GetType().GetProperties().Where(x => !Attribute.IsDefined(x, typeof(ComputedAttribute))).ToList();

        for (int i = 0; i < relevantProperties.Count(); i++)
        {
            object val = null;
            var propertyInfo = relevantProperties[i];
            if (propertyInfo.PropertyType.IsEnum)
            {
                val = Enum.GetName(propertyInfo.PropertyType, propertyInfo.GetValue(obj));
            }
            else
            {
                val = propertyInfo.GetValue(obj);
            }

            propertyValuesMap.Add(propertyInfo.Name, val);
            var propName = i == relevantProperties.Count() - 1 ? $"{propertyInfo.Name}" : $"{propertyInfo.Name},";
            columns.Append(propName);
            values.Append($"@{propName}");
        }

        return sqlConnection.Execute($"Insert Into {tableName} ({columns}) values ({values})", propertyValuesMap);
    }

do u need to exclude collection & non-primitive type properties? — Ruslan, Jan 18 '21 at 14:21

score 0 · Answer 2 · edited Jun 28 '21 at 08:23

I rewrote Dan's answer to be a little more modern C# and to not try to insert ID (because I had autoincrementing identity columns), as well as take a tablename instead of looking at attribute.

    public static long InsertB<T>(this SqlConnection sqlConnection, T obj, string tableName)
    {
        Dictionary<string, object> propertyValuesMap = new Dictionary<string, object>();
        var columnList = new List<String>();
        var valueList = new List<String>();
        var relevantProperties = obj.GetType().GetProperties().Where(x => !Attribute.IsDefined(x, typeof(ComputedAttribute))).ToList();

        foreach (var propertyInfo in relevantProperties)
        {
            if (propertyInfo.Name.ToLower() == "id") continue; // do not try to insert id
            
            var val = propertyInfo.PropertyType.IsEnum
                ? Enum.GetName(propertyInfo.PropertyType, propertyInfo.GetValue(obj))
                : propertyInfo.GetValue(obj);
            
            propertyValuesMap.Add(propertyInfo.Name, val);
            columnList.Add(propertyInfo.Name);
            valueList.Add($"@{propertyInfo.Name}");
        }

        return sqlConnection.Execute($"Insert Into {tableName} ({String.Join(", ", columnList)}) values ({String.Join(", ", valueList)})", propertyValuesMap);
    }

Insert enum as string using Dapper.Contrib

2 Answers2