DataTable schema changes

  • Thread starter Thread starter Thomas Brown
  • Start date Start date
T

Thomas Brown

I need to handle dynamic changes to a table's schema, even when there
is already data present. It appears that the only thing supported by
DataTable is to remove a column so I am pulling all the data out into
a 2D array, performing my manipulations on the raw data, and then
clearing out the table's Rows and Columns collections and attempting
to recreate them. Basically,

Rows.Clear();
Columns.Clear();
RebuildColumns(/*...*/);
RebuildRows(/*...*/);

Where I rebuild the columns from a separate list of schema parameters
that I have available and then rebuild the rows from the 2D array of
raw data that I originally pulled from the table.

For a simple test I am trying to remove the third column from a four
column table (something that I suppose I could have done simply via
Columns.RemoveAt, but am trying this way as a test).

The problem is that when I'm adding the rows back, I'm getting an
exception thrown on adding the second row:
System.IndexOutOfRangeException
"Cannot find Column 3"
System.Data.DataTable.InsertRow

Can anyone shed some light on this for me? Thanks in advance.

-- Thomas Brown
 
Thomas Brown said:
I need to handle dynamic changes to a table's schema, even when there
is already data present. It appears that the only thing supported by
DataTable is to remove a column so I am pulling all the data out into
a 2D array, performing my manipulations on the raw data, and then
clearing out the table's Rows and Columns collections and attempting
to recreate them. Basically,

Rows.Clear();
Columns.Clear();
RebuildColumns(/*...*/);
RebuildRows(/*...*/);

Where I rebuild the columns from a separate list of schema parameters
that I have available and then rebuild the rows from the 2D array of
raw data that I originally pulled from the table.
Click to expand...

Don't store the data in an array. Leave it in a DataTable. You are
throwing away all of the metadata you will need to do the merge.

Copy the data over into a new dataset, then clear and rebuild the columns of
the original one, and then move the data back in. You may simply be able to
use DataSet.Merge for both operations, or you may have to process it
row-by-row.

David
 
David Browne said:
Don't store the data in an array. Leave it in a DataTable. You are
throwing away all of the metadata you will need to do the merge.

Copy the data over into a new dataset, then clear and rebuild the columns of
the original one, and then move the data back in. You may simply be able to
use DataSet.Merge for both operations, or you may have to process it
row-by-row.

David
Click to expand...


Hi David,

Thanks for the pointer to DataSet.Merge, I'll have to investigate a
bit to see if it's going to work well for me. What I'm worried about
(and not getting any help from the documentation) is what types of
schema changes are handled by the merge and which will force me to
perform row-by-row processing. What about changing column data types?
What about reordering columns? What about simply changing a column
name? It seems to me that if I'm going to be forced to do row-by-row
processing then I'm where I am right now.

BTW, I found the cause and solution to the specific problem I was
having... It turns out the table had some "live" indices that
survived the DataTable.Clear(), DataTable.PrimaryKey = null,
DataTable.Rows.Clear(), and DataTable.Columns.Clear() operations I use
to wipe the table clean. When I added the new schema and began adding
the new rows these old indices would cause the exception when trying
to add the new rows to the index. Why clearing the table out doesn't
clear these indices I don't know, but by appropriately accepting
changes and performing the entire operation in a
BeginLoadData/EndLoadData block they do seem to get properly cleared
and rebuilt. This is, I might point out, another very weakly
documented part of ADO.NET.

Anyway, thanks again!
-- Thomas Brown
 
Back
Top