Changing populated DataTable column data types

Question

Hi,

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

Be sure to set the datatypes of the datatable you're filling up.

E.g.:

    DataTable table = new DataTable("countries");
    table.Columns.Add("country_code", typeof (string));
    table.Columns.Add("country_name", typeof (string));
    //...
    //Fill table

Or you could change the column types if they are compatible:

table.Columns["country_code"].DataType = typeof(string);

Answer 2

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.