How to Change Datatype of a Datacolumn in a Datatable

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

How to change data type of a column in DataTable

// Create a temporary table
DataTable dtNew = new DataTable();
for(int i=0;dt.Columns.Count;i++)
{
    dtNew.Columns.Add(dt.Columns[i].ColumnName,typeof(string));
}


// Add data to new table
for(int i=0;dt.Rows.Count;i++)
{
   dtNew.Rows.Add();
   for(int j=0;dt.Columns.Count;j++)
   {
      dtNew.Rows[i][j] = dt.Rows[i][j];
   }        
}

dt=null;
dt=dtNew.Copy();

Changing populated DataTable column data types

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.

C# Change DataTable Column DataType

Here's an outline of how I would do it. Basically use TryParse methods of the primitive types to determine the highest count. Then add a new column with the new type, converting as many values as possible. Ones that can't be converted will be left as DBNull.Value.

public class TypeCount {
    public int IntCount;
    public int LongCount;
    public int DoubleCount;
    public int DecimalCount;
    public int DateCount;
    // etc;

    // provide your own logic to determine the best type
    public Type BestType {
        get {
            int[] counts = new int[] { IntCount, LongCount, DoubleCount, DecimalCount, DateCount };
            Type[] types = new Type[] { typeof(int), typeof(long), typeof(double), typeof(decimal), typeof(DateTime) };
            Type bt = typeof(String);
            int max = 0;
            for (int i = 0; i < counts.Length; i++) {
                if (counts[i] > max) {
                    bt = types[i];
                    max = counts[i];
                }
            }
            return bt;
        }
    }
}

public static void TryParse(String s, NumberStyles ns, DateTimeStyles dts, IFormatProvider fp, String[] dateFormats, TypeCount counts) {
    if (String.IsNullOrEmpty(s))
        return;

    long l;
    int i;
    double d;
    decimal m;

    // could test byte and short too if needed
    if (int.TryParse(s, ns, fp, out i)) {
        counts.IntCount++;
        counts.LongCount++; // if int parses, then long also parses
    }
    else if (long.TryParse(s, ns, fp, out l))
        counts.LongCount++;

    // etc.

    foreach (String f in dateFormats) {
        DateTime date;
        if (DateTime.TryParseExact(s, f, fp, dts, out date))
            counts.DateCount++;
    }
}

public static void ConvertColumns(DataTable table) {
    IFormatProvider fp = CultureInfo.InvariantCulture;
    NumberStyles ns = NumberStyles.Any;
    DateTimeStyles dts = DateTimeStyles.None;
    String[] dateFormats = new String[] { "yyyy-MM-dd", "MM/dd/yyyy" };

    for (int i = 0; i < table.Columns.Count; i++) {
        DataColumn col = table.Columns[i];
        if (col.DataType != typeof(String))
            continue;

        TypeCount counts = new TypeCount();
        for (int j = 0; j < table.Rows.Count; j++) {
            String s = table.Rows[j][col] as String;
            TryParse(s, ns, dts, fp, dateFormats, counts);
        }

        Type bestType = counts.BestType;
        DataColumn temp = null;
        if (bestType == typeof(int)) {
            temp = table.Columns.Add("temp", typeof(int));
            for (int j = 0; j < table.Rows.Count; j++) {
                int val = 0;
                String s = table.Rows[j][col] as String;
                if (int.TryParse(s, ns, fp, out val))
                    table.Rows[j][temp] = val;
            }
        }
        //else if (bestType == ...) {}

        if (temp != null) {
            temp.SetOrdinal(col.Ordinal);
            table.Columns.Remove(col);
            temp.ColumnName = col.ColumnName;
        }
    }
}

How to change column format in DataTable C#

As Jeff mentioned in his answer, You cannot change the DataType after the Datatable is filled with data. What you can do is, take a clone of the Data table, change the column type and load data from the original data table to the cloned table as follows.

DataTable dtCloned = tableone.Clone();
dtCloned.Columns[1].DataType = typeof(string); //In your case you need to change WaitTime and AssistTime
dtCloned.Columns[2].DataType = typeof(string);

foreach (DataRow row in tableone.Rows)
{
    dtCloned.ImportRow(row);
}

Then you can use your code as,

dtCloned.Select().ToList().ForEach(row =>
{
    double xxx = Convert.ToDouble(row["WaitTime"]);
    row.SetField("WaitTime", secondsToTime(xxx));
});

---

Related Topics