Changing the DateTimeMode of a few columns in a dataset after it has been populated with data

Question

Hi, I need to change the DateTimeMode of some columns in an already populated dataset. (I don't want to change it before it gets populated as it would mean making changes in several methods throughtout the application.)

Here's the stmt I am using (for a single column):

copy.Tables[0].Columns["DateColName"].DateTimeMode = DataSetDateTime.Utc;

However, it throws an error that you can't change the DateTimeMode if the dataset contains data. So the solution I am thinking is creating a clone of the dataset, changing the DateTimeMode of required columns and then re-loading 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 of doing this??

Answer 1

I just ran into this issue also. The DateTimeMode cannot be changed once the dataset has been filled, so the only solution I could find is to re-create the column with the correct DateTimeMode.

This code might help, you don't have to clone the entire dataset, just remove the column, modify 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 have to be careful, when you copy the row values back to the column to preserve the RowState.

Answer 2

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 on your object.