Link Search Menu Expand Document

Enumeration


This is a feature that enables you to work on enumeration objects within the class object (property). The library supports various kind of transformation for enumerations.

Property String

Let us say you have a table named [dbo].[Person] with the following structure.

CREATE TABLE [dbo].[Person]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Gender] [nvarchar](16) NOT NULL,
    [CreatedDateUtc] [datetime2](5) NOT NULL,
    CONSTRAINT [CRIX_Person_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
)
ON [PRIMARY];
GO

Then, you can create this enumeration.

public enum Gender
{
    Unknown,
    Male,
    Female
}

And map it to the Gender column of the Person class.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public Gender Gender { get; set; } // Enumeration (As String)
    public DateTime CreatedDateUtc { get; set; }
}

The enumeration values will be saved in the database as string.

Property String (As Int)

You can force the value to be saved as int type if you are using the TypeMap.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    [TypeMap(DbType.Int32)]
    public Gender Gender { get; set; } // Enumeration (As Forced Int)
    public DateTime CreatedDateUtc { get; set; }
}

Or you can also use the type-level mapping via TypeMapper class.

TypeMapper.Map(typeof(Gender), DbType.Int32);

Property Int

Let us say you have a table named [dbo].[Person] with the following structure.

CREATE TABLE [dbo].[Person]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Gender] [int] NOT NULL,
    [CreatedDateUtc] [datetime2](5) NOT NULL,
    CONSTRAINT [CRIX_Person_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
)
ON [PRIMARY];
GO

Then, you can create this enumeration.

public enum Gender
{
    Unknown,
    Male,
    Female
}

And map it to the Gender column of the Person class.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public Gender Gender { get; set; } // Enumeration (As Int)
    public DateTime CreatedDateUtc { get; set; }
}

The enumeration values will be saved in the database as int.

Default Conversion

By default, the library is using the DbType.String as a conversion to all enumerations if being used to the non-model based operations (i.e.: ExecuteScalar, ExecuteNonQuery and ExecuteReader). However, you can always override the value by simply setting the Converter class property EnumDefaultDatabaseType to any DbType value.

Converter.EnumDefaultDatabaseType = DbType.Int32;

The library is intelligent enough to understand your table schema, if you call any model-based operations (i.e.: Query, Update, Merge, etc), the default conversion is not used as it has already projected the correct database type to be passed/parsed based the underlying table schema.

PropertyHandler

You can as well create a property handler to manually handle the enumerations transformation by implementing the IPropertyHandler.

public class PersonGenderPropertyHandler : IPropertyHandler<Gender?, string>
{
    public Gender? Get(string input, ClassProperty property)
    {
        if (!string.IsNullOrEmpty(input))
        {
            return (Gender)Enum.Parse(typeof(Gender), input);
        }
        return null;
    }

    public string Get(Gender? input, ClassProperty property)
    {
        return input?.ToString();
    }
}

And then map it via PropertyHandler.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    [PropertyHandler(typeof(PersonGenderPropertyHandler))]
    public Gender Gender { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

The enumeration auto-mapping is being disregard if you have the property handler mapped into the enumeration property. By using the property handler, you have a lot of control as a developer about the transformation.

Query Expression

You can as well use the enumeration in your query expression.

using (var connection = new SqlConnection(connectionString))
{
    var females = connection.Query<Person>(e => e.Gender == Gender.Female);
}

Or even in the raw-SQL.

using (var connection = new SqlConnection(connectionString))
{
    var param = new
    {
        Gender = Gender.Female
    };
    var females = connection.ExecuteQuery<Person>("SELECT * FROM [dbo].[Person] WHERE [Gender] = @Gender;", param);
}

Type Inference

As type inference is supported by the library, you can as well infer the enumeration directly when fetching a row from the database via ExecuteQuery.

using (var connection = new SqlConnection(connectionString))
{
    var genders = connection.ExecuteQuery<Gender>("SELECT [Gender] FROM [dbo].[Person];");
}