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.