Changing populated DataTable column data types

I wrote this generic function to do the job, it works very well for me:

public static bool ChangeColumnDataType(DataTable table, string columnname, Type newtype)
{
    if (table.Columns.Contains(columnname) == false)
        return false;

    DataColumn column= table.Columns[columnname];
    if (column.DataType == newtype)
        return true;

    try
    {
        DataColumn newcolumn = new DataColumn("temporary", newtype);
        table.Columns.Add(newcolumn);
        foreach (DataRow row in table.Rows)
        {
            try
            {
                row["temporary"] = Convert.ChangeType(row[columnname], newtype);
            }
            catch
            {
            }
        }
        table.Columns.Remove(columnname);
        newcolumn.ColumnName = columnname;
    }
    catch (Exception)
    {
        return false;
    }

    return true;
}

You can just copy the code and put it in a class(MyClass here) , and use it like this as an example:

MyClass.ChangeColumnDataType(table, "GEOST", typeof (int));

You cannot change the DataType of a DataColumn after populating it with data. It's not a read-only property, but you will receive an exception at runtime if you attempt to change it after it already has data.

From the documentation:

An exception is generated when changing this property after the column has begun storing data.

So you will have to either ensure the correct column types in the beginning (if possible), or create a new DataTable specifically for the import and copy data from the original DataTable.

You could also write a custom IDataReader class that reads from your DataTable and performs just-in-time conversion and pass that to the SqlBulkCopy - it would be a lot more efficient, but it's obviously not a quick fix.