How to update first column of a datatable for all rows using LINQQ or any other method?

Question

I have a data table with 4 columns datetime,id, message & status. I want to update the datetime column with a new datetime (updated datetime is in different time zone, from CST to UTC). First I was thinking to do it like I would take the datetime column and store it in a list and convert the list to utc time zone from cst.Then add the list to the same table and rearrange the columns. But this sounds stupid so is there any way to update the existing value with new values for one column of datatable.

I found the below code in one of the posts but not sure what exactly is happening.

var rowsToUpdate = 
dt.AsEnumerable().Where(r => r.Field<string>("datetime") == datetime);

foreach(var row in rowsToUpdate)
{
   row.SetField("datetime", utcDate);
}

The data in datatable is in CST timezone but I want that timezone to be converted to UTC timezone and update the table.

List<DateTime> lstCST = new List<DateTime>();
lstCST = datatable.AsEnumerable().Select(r=>r.Field<DateTime>("CSTDatetime")).ToList();
List<DateTime> lstUTC = new List<DateTime>();
DateTime dt = DateTime.Now;

foreach(var v in lstCST )
{
     dt= TimeZoneInfo.ConvertTimeToUtc(v);
     lstUTC.Add(dt);
}

Can anybody point me in the right direction on how to do this.

Answer 1

If you want to update all rows you don't need LINQ but TimeZoneInfo.ConvertTimeToUtc:

TimeZoneInfo cstZone = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time");

foreach (DataRow row in dataTable.Rows)
{
    DateTime cstDateTime = row.Field<DateTime>("CSTDatetime");
    DateTime utcDateTime = TimeZoneInfo.ConvertTimeToUtc(cstDateTime, cstZone);
    row.SetField("CSTDatetime", utcDateTime);
}

Answer 2

Look at this example:

DataTable table = new DataTable();

table.Columns.AddRange(new DataColumn[]
    {
        new DataColumn(),
        new DataColumn(),
        new DataColumn()
    });

table.Rows.Add(1, 2, 3);

foreach (DataRow dr in table.Rows)
{
    dr[0] = 99;
}

it will update all rows of the first column and set it to 99

Answer 3

To explain what the snippet does:

This is a filter:

var rowsToUpdate = 
dt.AsEnumerable().Where(r => r.Field<string>("datetime") == datetime);

It selects all rows where the "datetime" column has a specific value. In your case, I guess you can ignore this, you want to do this on all rows.

So instead of iterating on the filtered rows, iterate all rows.

foreach(var row in rowsToUpdate)
{
   row.SetField("datetime", utcDate);
}

The latter snippet sets the "datetime" value in each row of the selection to the value of utcDate. In your case, just replace this by first getting the "old" value, convert it and then use the above line to set the converted value.

@TimSchmelter already gave a ready-to-go solution so I won't repeat it. I rather wanted to explain what was seemingly unclear to you.