Databases Label

Converting from Type to SqlDbType

StackOverflow user Simone Salvo asked how to do a smart conversion between .Net System.Type and System.Data.SqlDbType, which is not really a trivial task.

For my answer, I wrote the following somewhat naïve code:

public class SqlHelper
{
    private static Dictionary<Type, SqlDbType> typeMap;

    // Create and populate the dictionary in the static constructor
    static SqlHelper()
    {
        typeMap = new Dictionary<Type, SqlDbType>();

        typeMap[typeof(string)]         = SqlDbType.NVarChar;
        typeMap[typeof(char[])]         = SqlDbType.NVarChar;
        typeMap[typeof(byte)]           = SqlDbType.TinyInt;
        typeMap[typeof(short)]          = SqlDbType.SmallInt;
        typeMap[typeof(int)]            = SqlDbType.Int;
        typeMap[typeof(long)]           = SqlDbType.BigInt;
        typeMap[typeof(byte[])]         = SqlDbType.Image;
        typeMap[typeof(bool)]           = SqlDbType.Bit;
        typeMap[typeof(DateTime)]       = SqlDbType.DateTime2;
        typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
        typeMap[typeof(decimal)]        = SqlDbType.Money;
        typeMap[typeof(float)]          = SqlDbType.Real;
        typeMap[typeof(double)]         = SqlDbType.Float;
        typeMap[typeof(TimeSpan)]       = SqlDbType.Time;
        /* ... and so on ... */
    }

    // Non-generic argument-based method
    public static SqlDbType GetDbType(Type giveType)
    {
        if (typeMap.ContainsKey(giveType))
        {
            return typeMap[giveType];
        }

        throw new ArgumentException($"{giveType.FullName} is not a supported .NET class");
    }

    // Generic version
    public static SqlDbType GetDbType<T>()
    {
        return GetDbType(typeof(T));
    }
}

Caveat

The above code works fine in most cases, but comes with some problems:

  • How do you pick the correct SqlDbType value for strings? It depends on how you are using/storing that string in the database and in your application's business logic. The alternatives are:
    • Char: Fixed-length non-Unicode string of no more than 8 000 characters
    • NChar: Fixed-length Unicode string of no more than 8 000 characters
    • VarChar: Variable-length non-Unicode string of no more than 8 000 characters
    • NVarChar: Variable-length Unicode string of no more than 8 000 characters
    • Text: Non-Unicode stream of no more than 2 147 483 647 characters
    • NText: Unicode stream of no more than 1 073 741 823 characters
    • Xml: SQL Server native XML data type
  • How do you pick the correct value for blobs? There are a few alternatives there too:
    • Binary: Fixed-length stream of no more than 8 000 bytes
    • VarBinary: Variable-length stream of no more than 8 000 bytes
    • Image: Variable-length stream of no more than 2 147 483 647 bytes
  • Also for dates or timestamps there are a few different ways to go:
    • Date: Dates from 1 AD to 9999 without time of day
    • DateTime: Timestamps from 1753 to 9999 with 10/3 ms precision
    • DateTime2: Timestamps from 1 AD to 9999 with 100 ns precision
    • SmallDateTime: Timestamps from 1900 to 2079 with one minute precision
    • DateTimeOffset: Timestamps from 1 AD to 9999 with 100 ns precision, including time zone information

The best thing would almost always be to let some ORM tool do the heavy lifting.

Posted by Anders Tornblad on Category C Sharp Labels
Tweet this