C# Looging DataTable and formatting values based on datatypes

Question

I am getting query results in DataTable and need to format ints and dates in DataTable to a specific format. I can not format it in SQL server due to some limitations.

I have written below code to format it but it is not updating the DataTable.

public DataTable formatDT(DataTable DT)
{
    string datatype, columnName;
    decimal sysInt;
    DateTime DateTime;
    int r = 0;
    foreach (DataRow dr in DT.Rows)
    {
        for (int i = 0; i < DT.Columns.Count; i++)
        {
            columnName = DT.Columns[i].ColumnName;
            if (DT.Columns[i].ColumnName.ToString() == "full_count")
            {
                continue;
            }
            datatype = Convert.ToString(DT.Columns[i].DataType);
            switch (datatype)
            {
                case "System.Int":
                case "System.Int32":
                case "System.Int64":
                    sysInt = Convert.ToInt32(dr[columnName]);
                    DT.Rows[r][columnName] = sysInt.ToString("N0");
                    break;

                case "System.DateTime":
                    DateTime = Convert.ToDateTime(dr[columnName]);
                    DT.Rows[r][columnName] = DateTime.ToString("yyyy-MM-dd");
                    logger.Error(DT.Rows[r][columnName] + "<<<>>>>" + DateTime.ToString("yyyy-MM-dd"));      //Even after assing 'DT.Rows[r][columnName]' is shows old values and not the formatted one
                    break;

                case "System.Decimal":
                case "System.Double":
                    sysInt = Convert.ToInt32(dr[columnName]);
                    DT.Rows[r][columnName] = sysInt.ToString("N2");
                    break;
                default:
                    DT.Rows[r][columnName] = dr[columnName].ToString();
                    break;
            }
        }
        r++;
    }
    DT.AcceptChanges();
    return DT;
}

Any help appreciated.

I tried clone and update cloned datatable to update but due to datetime column i cant clone it. — Suresh Kamrushi, May 13 '20 at 06:55

score 0 · Answer 1 · answered May 13 '20 at 04:27

Assuming you have debugged the code and ToString functions fine, and you can see the resulting values in sysInt OR DateTime, can you please try following

sysInt = Convert.ToInt32(dr[columnName]);
dr[columnName] = sysInt.ToString("N0");

I faced similar issue few months back and if I remember correctly it had something to do with dynamic population of dataRow and dataTable. e.g. when I debugged the values were parsed fine, but after assigning them to datatable they looked fine in debugger but actually the data table was still empty.

score 0 · Answer 2 · answered May 13 '20 at 07:09

Finally able to do it. Few thoughts/understanding:

1) Problem with above approach (in question) is that, if you modified/format values that does not match with column datatype, it wont accept. [Tried appending hard coded char in string datatype it works]

2) Tried to clone it, but getting below error:

"datetime" does not contain a definition for "clone"

Lastly created new DataTable and adding each row values in it, of course after formatting it like below:

public DataTable formatDT(DataTable DT)
        {
            string datatype, columnName;
            decimal sysInt;
            DateTime DateTime;
            int r = 0;
            DataTable dt = new DataTable();
            DataColumn dataColumn;

            foreach (DataRow dr in DT.Rows)
            {
                DataRow eachrow = dt.NewRow();
                for (int i = 0; i < DT.Columns.Count; i++)
                {
                    columnName = DT.Columns[i].ColumnName;
                    if(r == 0)
                    {
                        dataColumn = new DataColumn(columnName);
                        dataColumn.DataType = typeof(String);
                        dt.Columns.Add(dataColumn);
                    }

                    if (DT.Columns[i].ColumnName.ToString() == "full_count")
                    {
                        continue;
                    }
                    datatype = Convert.ToString(DT.Columns[i].DataType);
                    switch (datatype)
                    {
                        case "System.Int":
                        case "System.Int32":
                        case "System.Int64":
                            sysInt = Convert.ToInt32(dr[columnName]);
                            eachrow[i] = sysInt.ToString("N0");
                            break;

                        case "System.DateTime":
                            DateTime = Convert.ToDateTime(dr[columnName]);
                            eachrow[i] = DateTime.ToString("yyyy-MM-dd");                            
                            break;

                        case "System.Decimal":
                        case "System.Double":
                            sysInt = Convert.ToInt32(dr[columnName]);
                            eachrow[i] = sysInt.ToString("N2");
                            break;
                        default:
                            eachrow[i] = dr[columnName].ToString();
                            break;
                    }
                }
                dt.Rows.Add(eachrow);
                r++;
            }
            return dt;
        }

There might be a better and concise way of doing it but as of now I am going with this. Please share if there is better and faster approach to it.

RJCoder · Answer 3 · 2020-10-01T06:56:48.487

This is to remind you that if you are iterating a DataTable using foreach , you can not modify or update its values or types. C# doesn't allow that. Instead you what you can do is to declare another DataTable and try to add rows to it and return it. This is what i think you have done it already in your second approach as is in your answer.
Or you can use System.Reflection or return this table as a List. Like following.

   public static List<T> MakeList<T>(System.Data.DataTable tble)
        {
            return tble.AsEnumerable().Select(row =>
                {
                    var properties = typeof(T).GetProperties();
                    var ob = Activator.CreateInstance<T>();
                    foreach (var pro in properties)
                    {
                        try
                        {
                           //Here you can apply your conversion
                            pro.SetValue(ob, row[pro.Name] != null 
                                ? row[pro.Name].ToString() : string.Empty);
                        }
                        catch (Exception e) { throw e; }
                    }
                    return ob;
                }).ToList();
        }

Just create a class of DataTable columns and pass object of that class like this. For

class example{
public a{get;set;}
public b {get;set;}
}

List<example> ls = MakeList<example>(dt);

How To Change DataType of a DataColumn in a DataTable?

C# Looging DataTable and formatting values based on datatypes

3 Answers3