DataTable schema changes

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

David
Tue Sep 16 11:32:07 CDT 2003


"Thomas Brown" <thomas_w_brown@countrywide.com> wrote in message
news:7e392c6f.0309160752.43fa9585@posting.google.com...
> 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.

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

thomas_w_brown
Wed Sep 17 11:55:57 CDT 2003

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in message news:<#TwIJAHfDHA.1712@TK2MSFTNGP11.phx.gbl>...
> "Thomas Brown" <thomas_w_brown@countrywide.com> wrote in message
> news:7e392c6f.0309160752.43fa9585@posting.google.com...
> > 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.
>
> 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


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