Convert DataColumn.DataType to SqlDbType

Question

Is there a converter for going from DataColumn.DataType to SqlDbType? Or do I have to write a method to do it?

Answer 1

Here's my solution, which uses built-in .NET functionality:

/// <summary>
/// Get the equivalent SQL data type of the given type.
/// </summary>
/// <param name="type">Type to get the SQL type equivalent of</param>
public static SqlDbType GetSqlType(Type type)
{
    if (type == typeof(string))
        return SqlDbType.NVarChar;

    if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
        type = Nullable.GetUnderlyingType(type);

    var param = new SqlParameter("", Activator.CreateInstance(type));
    return param.SqlDbType;
}

Beware that this will always set strings to NVarChar (any generic solution to this problem will have the same gotcha, because there's no way to know the correct SqlDbType). When using this for parameterized SELECT or UPDATE queries against columns that are not NVarChar, SqlServer's performance drags when comparing NChar/NVarChar to Char/VarChar types because it is converting values for every comparison. This bit me hard recently (a process went from taking 4 minutes to 140+ minutes), so always be explicit about your char parameter types when you can! I would imagine other similar types may have the same issue, but none that caused me a problem (yet).

Answer 2

There are no existing methods to do this - you will need to roll a function to do this or cache a Dictionary<Type, SqlDbType> for lookups.