How to change column format in DataTable C#

Question

In my application returns following datatable,

+-------------+----------+------------+-------+-------+----------+
| AccountName | WaitTime | AssistTime | FName | LName | FullName |
+-------------+----------+------------+-------+-------+----------+
| XXX         | 18       | 15         | Mary  | Sil   |          |
+-------------+----------+------------+-------+-------+----------+
| YYY         | 67       | 3          | Jany  | Joh   |          |
+-------------+----------+------------+-------+-------+----------+
| ZZZ         | 50       | 100        | Kate  | Ham   |          |
+-------------+----------+------------+-------+-------+----------+

In above datatable, WaitTime and AssistTime data coming as double value,Now I need to change the format of WaitTime and AssistTime columns format to 00:00:00 (hh:mm:ss) format. So I just write folowing code(please be noted this part of code).

DataTable tableone = ds.Tables[0];

tableone.Select().ToList().ForEach(row =>
{

    string FirstName = Convert.ToString(row["FName"], CultureInfo.InvariantCulture);
    string LastName = Convert.ToString(row["LName"], CultureInfo.InvariantCulture);
    double xxx = Convert.ToDouble(row["WaitTime"]);

    row.SetField("WaitTime",secondsToTime(xxx));
    row.SetField("FullName", string.Format("{0} {1}", FirstName, LastName));
});

private string secondsToTime(double seconds)
{
    TimeSpan t = TimeSpan.FromSeconds(seconds);
    string answer = string.Format("{0:D2}:{1:D2}:{2:D2}",
        t.Hours,
        t.Minutes,
        t.Seconds);
    return answer;
}

But above code gives this error,

System.ArgumentException: 'Input string was not in a correct format.Couldn't store <00:00:18> in WaitTime Column. Expected type is Decimal.' FormatException: Input string was not in a correct format.

I need following DataTable as formated one.

+-------------+----------+------------+-------+-------+----------+
| AccountName | WaitTime | AssistTime | FName | LName | FullName |
+-------------+----------+------------+-------+-------+----------+
| XXX         | 00:00:18 | 00:00:15   | Mary  | Sil   | Mary Sil |
+-------------+----------+------------+-------+-------+----------+
| YYY         | 00:01:07 | 00:00:03   | Jany  | Joh   | Jany Joh |
+-------------+----------+------------+-------+-------+----------+
| ZZZ         | 00:00:50 | 00:01:40   | Kate  | Ham   | Kate Ham |
+-------------+----------+------------+-------+-------+----------+

How can I do this? please help

What type is time in the database? Is it a number, or date object? It looks like your code is expecting an integer [row.SetField("WaitTime",secondsToTime(xxx));]. — jdweng, Jan 04 '20 at 18:10
@jdweng `WaitTime` and `AssistTime` are in decimal values in database. Can you please tell me what is difference of your code `[row.SetField("WaitTime",secondsToTime(xxx));]` than my code :) — Sachith Wickramaarachchi, Jan 04 '20 at 18:22

score 1 · Answer 1 · answered Jan 04 '20 at 18:43

you are successfully changing values from column WaitTime and AssistTime

now just follow the below steps

        DataTable dtTemp = new DataTable();

        dtTemp = dtOri.Clone();
        dtTemp.Columns["WaitTime"].DataType = typeof(TimeSpan);
        dtTemp.Columns["AssistTime"].DataType = typeof(TimeSpan);
        //you can change data type to string as well if you need
        //if you are changing datatype to string make sure to add ".ToString()" in below code e.g secondsToTime(xx).ToString()

        foreach (DataRow row in dtOri.Rows)
        {
            dtTemp.Rows.Add(new object[] {row[0], secondsToTime(Convert.ToDouble(row[1].ToString())), secondsToTime(Convert.ToDouble(row[2].ToString())), row[3],row[4],row[5]});
        }

        dtOri = dtTemp;

Sachith Wickramaarachchi · Accepted Answer · 2020-01-06T03:22:15.857

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

score 0 · Answer 3 · answered Jan 04 '20 at 18:19

0

The type of your WaitTime column is a decimal so you cannot set it to a TimeSpan. You cannot change the column types of DataTables after they have been populated so you'll have to change it at the source or create a clone.

See this answer https://stackoverflow.com/a/9028087/1532710

answered Jan 04 '20 at 18:19

Jeff Barnard

104
5

Can you please tell me, how can I do that using clone datatable, please help me. and actually I don't need set TimeSpan. `secondsToTime` function returns string value. can you please provide me a complete answer – Sachith Wickramaarachchi Jan 04 '20 at 18:21
Sorry I don't understand what you mean by clone database. A DataTable is a .Net in memory representation of your data. You can either define one manually with a designer, in code, or allow the framework to create one for you automatically from your data source. The other answers have shown one way to customize your data table by cloning it and changing the types. You could also change the column types at the root source (api service or database). – Jeff Barnard Jan 05 '20 at 20:06

How to change column format in DataTable C#

3 Answers3