Changing populated DataTable column data types

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.

---

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.

---

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

---

If you are populating from csv file then read schema first in datatable then change the datatype of column then populate the table. Example: I'm using XML file for importing data.

       DataSet dstemp = new DataSet();
       dstemp.ReadXmlSchema(@"D:\path of file\filename.xml");
       dstemp.Tables[0].Columns["Student_id"].DataType = typeof(Guid);
       dstemp.ReadXml(@"D:\path of file\filename.xml");

I think it should work for you.

---

Just like "Eddie Monge Jr" or "Gisway" can't get it.

but with the correct column order.

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{}
        }
        newcolumn.SetOrdinal(column.Ordinal);
        table.Columns.Remove(columnname);
        newcolumn.ColumnName = columnname;
    }
    catch (Exception){
        return false;
    }

    return true;
}

---

I created an improved version of Gisway's/Yuri Galanter's solution, which addresses the following points:

• Don't eat exceptions, fail early
• Retain nullability/AllowDBNull of the original column
• Work with the column object directly, no need for the table object as parameter
• Change naming to typical .Net conventions
• Improve docs
• Include a guid in the temporary column name to really avoid collisions
• Refactor to be extension methods

As the project I needed this for is in VB.Net, my solution is written (and tested) in it too, sorry - converting shouldn't be hard though.

' following methods will be defined in a module, which is why they aren't Shared
' based on https://codecorner.galanter.net/2013/08/02/ado-net-datatable-change-column-datatype-after-table-is-populated-with-data/ 
' and https://stackoverflow.com/a/15692087/1200847 

''' <summary> 
''' Converts DataType of a DataTable's column to a new type by creating a copy of the column with the new type and removing the old one. 
''' </summary> 
''' <param name="table">DataTable containing the column</param> 
''' <param name="columnName">Name of the column</param> 
''' <param name="newType">New type of the column</param> 
<Extension()> 
Public Sub ChangeColumnDataType(table As DataTable, columnName As String, newType As Type) 
    If Not table.Columns.Contains(columnName) Then Throw New ArgumentException($"No column of the given table is named ""{columnName}"".") 
    Dim oldCol As DataColumn = table.Columns(columnName) 
    oldCol.ChangeDataType(newType) 
End Sub 

''' <summary> 
''' Converts DataType of a DataTable's column to a new type by creating a copy of the column with the new type and removing the old one. 
''' </summary> 
''' <param name="column">The column whichs type should be changed</param> 
''' <param name="newType">New type of the column</param> 
<Extension()> 
Public Sub ChangeDataType(column As DataColumn, newType As Type) 
    Dim table = column.Table 
    If column.DataType Is newType Then Return 

    Dim tempColName = "temporary-327b8efdb7984e4d82d514230b92a137" 
    Dim newCol As New DataColumn(tempColName, newType) 
    newCol.AllowDBNull = column.AllowDBNull 

    table.Columns.Add(newCol) 
    newCol.SetOrdinal(table.Columns.IndexOf(column)) 

    For Each row As DataRow In table.Rows 
        row(tempColName) = Convert.ChangeType(row(column), newType) 
    Next 
    table.Columns.Remove(column) 
    newCol.ColumnName = column.ColumnName 
End Sub

If you i.e. have an int column which really should be a bool column, use it like this:

table.Columns("TrueOrFalse").ChangeDataType(GetType(Boolean)) 

Important: As this changes the DataTable you possible want to do this right after loading the data and accept the changes afterwards. This way change tracking, databinding etc. works normally afterwards:

table.AcceptChanges()

---

If the non-nullability of your column isn't configured correctly when loading the data, as it was the case for my Oracle NUMBER(1,0) NOT NULL column, you may want to insert code like this:

table.Columns("TrueOrFalse").AllowDBNull = False 
table.Columns("TrueOrFalse").DefaultValue = 0