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?

Edit: 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)
{
    SqlClient.SqlParameter p1;
    System.ComponentModel.TypeConverter tc;
    p1 = new SqlClient.SqlParameter();
    tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
    if (tc.CanConvertFrom(theType)) {
     p1.DbType = tc.ConvertFrom(theType.Name);
    }
    else {
     //Try brute force
     try {
      p1.DbType = tc.ConvertFrom(theType.Name);
     }
     catch (Exception ex) {
      //Do Nothing
     }
    }
    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 1

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 2

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.