Changing populated DataTable column data types

Question

I have a System.Data.DataTable which is populated by reading a CSV file which sets the datatype of each column to string.

I want to append the contents of the DataTable to an existing database table - currently this is done using SqlBulkCopy with the DataTable as the source.

However, the column data types of the DataTable need to be changed to match the schema of the target database table, handling null values.

I am not very familiar with ADO.NET so have been searching for a clean way of doing this?

Thanks.

Answer 1

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));