Anders Tornblad

All about the code

Label archive for sql

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:
    • Binary: Fixed-length stream of no more than 8 000 bytes

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