Changing DateTimeMode of multiple columns in datatable after it has been populated with data
I need to change the DateTimeMode of some columns in an already populated dataset. (I don't want to change it until it is populated, as that would mean making changes to multiple methods through the app.)
Here's the stmt I am using (for one column):
copy.Tables[0].Columns["DateColName"].DateTimeMode = DataSetDateTime.Utc;
However, it throws an error that cannot change the DateTimeMode if the dataset contains data. So the solution I'm thinking of is creating a clone of the dataset, changing the DateTimeMode of the required columns, and reloading the data back.
DataSet copy = dsdata.Clone();
copy.Tables[0].Columns["DateColName"].DateTimeMode = DataSetDateTime.Utc;
copy.Load(dsdata.CreateDataReader(), LoadOption.OverwriteChanges, "TableName");
Is there a better way to do this?
a source to share
try this, cheers
private void SetUtcDateTime()
{
var ds = new DataSet { Locale = CultureInfo.InvariantCulture };
foreach (DataTable source in DataSet.Tables)
{
bool containsDate = false;
var target = source.Clone();
foreach (DataColumn col in target.Columns)
{
if (col.DataType == System.Type.GetType("System.DateTime"))
{
col.DateTimeMode = DataSetDateTime.Utc;
containsDate = true;
}
}
if (containsDate)
{
foreach (DataRow row in source.Rows)
target.ImportRow(row);
ds.Tables.Add(target);
}
else
{
ds.Tables.Add(source.Copy());
}
}
DataSet.Tables.Clear();
DataSet = ds;
}
where 'DataSet' is a public property for your object.
a source to share
I faced this problem too. The DateTimeMode cannot be changed after the dataset is populated, so the only solution I could find is to re-create the column with the correct DateTimeMode.
This code might help, you don't need to clone the entire dataset, just drop the column, change it and add it back to the table.
private static void SetDateTimeMode(DataTable table, DataColumn col, DataSetDateTime mode)
{
var rowValues = new object[table.Rows.Count];
for (int i = 0; i < rowValues.Length; i++)
{
// ignore deleted rows
if (table.Rows[i].RowState == DataRowState.Deleted) continue;
rowValues[i] = table.Rows[i][col];
}
// we must remove and re-add the row because DateTimeMode cannot be
// changed on a column that has data.
table.Columns.Remove(col);
col.DateTimeMode = mode;
table.Columns.Add(col);
// write back each row value
for (int i = 0; i < rowValues.Length; i++)
{
// ignore deleted rows
if (table.Rows[i].RowState == DataRowState.Deleted) continue;
var rowState = table.Rows[i].RowState;
table.Rows[i][col] = rowValues[i];
// preserve unchanged rowstate
if (rowState == DataRowState.Unchanged)
table.Rows[i].AcceptChanges();
}
}
You just need to be careful when you copy the row values back to the column in order to preserve the RowState.
a source to share