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.

Answer 3

Something like this might be a good start. It's not comprehensive, but got me to where i needed to go:

Dictionary<Type, SqlDbType> GetSQLTypeConversionMap()
{
    var result = new Dictionary<Type, SqlDbType>();
    result.Add(typeof(string), SqlDbType.VarChar);
    result.Add(typeof(Int16), SqlDbType.Int);
    result.Add(typeof(Int32), SqlDbType.Int);
    result.Add(typeof(DateTime), SqlDbType.DateTime2);
    return result;  
}

Answer 4

I found a couple of options at Dot Net Pulse and CodeProject. I eventually went with the CodeProject code converted from VB.NET to C#:

private SqlDbType GetDBType(System.Type theType)
{
     System.Data.SqlClient.SqlParameter p1;
     System.ComponentModel.TypeConverter tc;
     p1 = new System.Data.SqlClient.SqlParameter();
     tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
     if (tc.CanConvertFrom(theType)) {
        p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
     } else {
        //Try brute force
        try {
           p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
        }
        catch (Exception) {
           //Do Nothing; will return NVarChar as default
        }
     }
     return p1.SqlDbType;
}

I was really hoping to find that there was some hidden secret System.Data.SqlClient method to do the conversion and I was just missing it.

Answer 5

You could copy the DataTable like

DataTable newdt = DataTable.Copy();

And Delete the columns with the different DataTypes and add them to your newdt.

Just one way i worked around it in the past.

Answer 6

Answered in this thread, but it's quite brief, so I'll re-quote it here:

You can convert TypeCode to DbType using method ConvertTypeCodeToDbType in System.Web.UI.WebControls.Parameter class: Parameter.ConvertTypeCodeToDbType Method. To get TypeCode you can use method Type.GetTypeCode(Type type).