CODEWITHSUNDEEP
.netado.net
Alexander Gornostaev
Alexander Gornostaev

Reputation: 65

Convert SqlDbType to .net Type

There are a lot of examples how do we convert SqlDbType enumeration from standard :net Classes or instances. I've not found any backward solution in internet. It seems to be much less common but the should be (I think) a simpler way then just a huge switch case over all 31 enumeration member.

Has anyone an idea how convert SqlDbType into .net Type in a better way then just switch over all possible enum values?

Thanks to Tim Schmelter comment. It seems thats is only solution.

Upvotes: 3

Views: 1674

Answers (1)

McGuireV10
McGuireV10

Reputation: 9956

It's definitely odd that .NET doesn't include this mapping (and the SqlTypes are kind of a mess, too), but the "better way" is a dictionary keyed on the enum. This also makes it easy to test for validity if your project only supports a subset of the available column types. I maintain a project that deals with SQL table layouts defined on the fly. This way my library consumers only need to think in terms of SqlDbType without worrying about the internal DataColumn mappings.

internal static readonly Dictionary<SqlDbType, Type> equivalentSystemType = new Dictionary<SqlDbType, Type>
{
    { SqlDbType.BigInt, typeof(long) },
    { SqlDbType.Binary, typeof(byte[]) },
    { SqlDbType.Bit, typeof(bool) },
    { SqlDbType.Char, typeof(string) },
    { SqlDbType.Date, typeof(DateTime) },
    { SqlDbType.DateTime, typeof(DateTime) },
    { SqlDbType.DateTime2, typeof(DateTime) }, // SQL2008+
    { SqlDbType.DateTimeOffset, typeof(DateTimeOffset) }, // SQL2008+
    { SqlDbType.Decimal, typeof(decimal) },
    { SqlDbType.Float, typeof(double) },
    { SqlDbType.Image, typeof(byte[]) },
    { SqlDbType.Int, typeof(int) },
    { SqlDbType.Money, typeof(decimal) },
    { SqlDbType.NChar, typeof(string) },
    { SqlDbType.NVarChar, typeof(string) },
    { SqlDbType.Real, typeof(float) },
    { SqlDbType.SmallDateTime, typeof(DateTime) },
    { SqlDbType.SmallInt, typeof(short) },
    { SqlDbType.SmallMoney, typeof(decimal) },
    { SqlDbType.Time, typeof(TimeSpan) }, // SQL2008+
    { SqlDbType.TinyInt, typeof(byte) },
    { SqlDbType.UniqueIdentifier, typeof(Guid) },
    { SqlDbType.VarBinary, typeof(byte[]) },
    { SqlDbType.VarChar, typeof(string) },
    { SqlDbType.Xml, typeof(SqlXml) }
    // omitted special types: timestamp
    // omitted deprecated types: ntext, text
    // not supported by enum: numeric, FILESTREAM, rowversion, sql_variant
};

Upvotes: 2

Related Questions