Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How To Change DataType of a DataColumn in a DataTable?

You cannot change the DataType after the Datatable is filled with data. However, you can clone the Data table, change the column type and load data from previous data table to the cloned table as shown below.

DataTable dtCloned = dt.Clone();
dtCloned.Columns[0].DataType = typeof(Int32);
foreach (DataRow row in dt.Rows) 
{
    dtCloned.ImportRow(row);
}

While it is true that you cannot change the type of the column after the DataTable is filled, you can change it after you call FillSchema, but before you call Fill. For example, say the 3rd column is the one you want to convert from double to Int32, you could use:

adapter.FillSchema(table, SchemaType.Source);
table.Columns[2].DataType = typeof (Int32);
adapter.Fill(table);

Old post, but I thought I'd weigh in, with a DataTable extension that can convert a single column at a time, to a given type:

public static class DataTableExt
{
    public static void ConvertColumnType(this DataTable dt, string columnName, Type newType)
    {
        using (DataColumn dc = new DataColumn(columnName + "_new", newType))
        {
            // Add the new column which has the new type, and move it to the ordinal of the old column
            int ordinal = dt.Columns[columnName].Ordinal;
            dt.Columns.Add(dc);
            dc.SetOrdinal(ordinal);

            // Get and convert the values of the old column, and insert them into the new
            foreach (DataRow dr in dt.Rows)
                dr[dc.ColumnName] = Convert.ChangeType(dr[columnName], newType);

            // Remove the old column
            dt.Columns.Remove(columnName);

            // Give the new column the old column's name
            dc.ColumnName = columnName;
        }
    }
}

It can then be called like this:

MyTable.ConvertColumnType("MyColumnName", typeof(int));

Of course using whatever type you desire, as long as each value in the column can actually be converted to the new type.


I've taken a bit of a different approach. I needed to parse a datetime from an excel import that was in the OA date format. This methodology is simple enough to build from... in essence,

  1. Add column of type you want
  2. Rip through the rows converting the value
  3. Delete the original column and rename to the new to match the old

    private void ChangeColumnType(System.Data.DataTable dt, string p, Type type){
            dt.Columns.Add(p + "_new", type);
            foreach (System.Data.DataRow dr in dt.Rows)
            {   // Will need switch Case for others if Date is not the only one.
                dr[p + "_new"] =DateTime.FromOADate(double.Parse(dr[p].ToString())); // dr[p].ToString();
            }
            dt.Columns.Remove(p);
            dt.Columns[p + "_new"].ColumnName = p;
        }