How to use an Enum as string in Dapper?

.net c# dapper dapper-extensions

Question

I'm attempting to utilizeDapper and Dapper-Extensions the serialization of myenums as seen in the databasestring .

They are currently serialized as integers (inside aVARCHAR field) in its place.

Is there a method for doing this? Is there a special type mapping I can include?

If I can't make this work, I may have to return to EF.

1
8
5/16/2016 11:13:49 PM

Accepted Answer

Marc Gravell's response is appreciated.

The inserts must be done manually as the only option.

Additionally utilizing the post How can I enter an identity into Dapper and then return it?

Below is my response.

Just keep in mind that selections are made automatically while using Dapper (Extensions).GetList<T> , there is no mapping to the enum back required.

public enum ComponentType
{
    First,
    Second,
    Third
}

public class Info
{
    public int Id { get; set; }
    public ComponentType InfoComponentType { get; set; }

    public static void SaveList(List<Info> infoList)
    {
        string ConnectionString = GetConnectionString();

        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();

            foreach (Info info in infoList)
            {
                string sql = @"INSERT INTO [Info] ([InfoComponentType]) 
                               VALUES (@InfoComponentType);
                               SELECT CAST(SCOPE_IDENTITY() AS INT)";

                int id = conn.Query<int>(sql, new
                {
                    InfoComponentType = info.InfoComponentType.ToString()
                }).Single();

                info.Id = id;
            }

            conn.Close();
        }
    }
}
1
5/23/2017 12:15:07 PM

Popular Answer

There is a route that I believe to be more reliable and orderly.

The technique I provide requires some more code but will work for any enumeration. Additionally, a custom type handler must be added to Dapper. However, if this response receives a few votes, I'll update the Dapper source code to automatically include this fix in the type handling and submit a pull request.

This is a solution that I really used in practice.

Here we go.

The struct that will be used as an enumeration comes first (it is not a class since it just stores a string reference):

public struct Country
{
    string value;

    public static Country BE => "BE";
    public static Country NL => "NL";
    public static Country DE => "DE";
    public static Country GB => "GB";

    private Country(string value)
    {
        this.value = value;
    }

    public static implicit operator Country(string value)
    {
        return new Country(value);
    }

    public static implicit operator string(Country country)
    {
        return country.value;
    }
}

A type handler for this struct is now required.

public class CountryHandler : SqlMapper.ITypeHandler
{
    public object Parse(Type destinationType, object value)
    {
        if (destinationType == typeof(Country))
            return (Country)((string)value);
        else return null;
    }

    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.DbType = DbType.String;
        parameter.Value = (string)((dynamic)value);
    }
}

We must register the type handler with Dapper at some point during the application's initialization.

Dapper.SqlMapper.AddTypeHandler(typeof(Country), new CountryHandler());

Country may now be used simply as a "enum". For illustration:

public class Address
{
     public string Street { get; set; }
     public Country Country { get; set; }
}

var addr = new Address { Street = "Sesamestreet", Country = Country.GB };

The enumeration is supported in memory by a string rather than an integer, which is obviously a drawback.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow