DataTable.New() and Rows.Add() not working right

[Joe Bonavita]

I'm using a SqlReader to read results from a query and insert them into a
DataTable. The DataTable is from a DataSet which contains an Xml that was
read in using ReadXml().

My problem is, only 1 row gets written correctly. The others don't have any
attributes and are not in the proper hierarchy in the Xml yet they're all
created from the same loop.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] =
sqlReader.GetString(indexPT);
...
...
// set more rows
dtProducts.Rows.Add(dr);
}

ds.WriteXml(@"X:\temp\Test.xml");

 

[Jon Skeet [C# MVP]]

I'm using a SqlReader to read results from a query and insert them into a
DataTable. The DataTable is from a DataSet which contains an Xml that was
read in using ReadXml().

My problem is, only 1 row gets written correctly. The others don't have any
attributes and are not in the proper hierarchy in the Xml yet they're all
created from the same loop.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] =
sqlReader.GetString(indexPT);
...
...
// set more rows
dtProducts.Rows.Add(dr);
}

ds.WriteXml(@"X:\temp\Test.xml");

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

(I would suggest using Northwind as a sample database, as most people
have access to that in some form or other.)

 

[Joe]

one problem fixed. It seems that setting dr.ItemArray[index] =
sqlReader.GetString(anotherIndex) wasn't setting the value.

To correct this I created an object array and used the LoadDataRow method.
Now my nodes are created but they're still in the wrong place. They're being
created at the root level.

 

[Cor Ligthert]

Hi Joe,

Why that ItemArray and not the Item, when I understand what you want is it
to find the proper column by the proper item you read with the datareader.


while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.Item["Col1"] = sqlReader.GetString(0);
\\ set more Items
dtProducts.Rows.Add(dr);
}
ds.WriteXml(@"X:\temp\Test.xml");

I hope this helps?

Cor

 

[Joe]

fixed the next problem. I had to call DataRow.SetParentRow() before adding
the row. Now everything is good .

one problem fixed. It seems that setting dr.ItemArray[index] =
sqlReader.GetString(anotherIndex) wasn't setting the value.

To correct this I created an object array and used the LoadDataRow method.
Now my nodes are created but they're still in the wrong place. They're being
created at the root level.

I'm using a SqlReader to read results from a query and insert them into a
DataTable. The DataTable is from a DataSet which contains an Xml that was
read in using ReadXml().

My problem is, only 1 row gets written correctly. The others don't have any
attributes and are not in the proper hierarchy in the Xml yet they're all
created from the same loop.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] =
sqlReader.GetString(indexPT);
...
...
// set more rows
dtProducts.Rows.Add(dr);
}

ds.WriteXml(@"X:\temp\Test.xml");

 

[Jon Skeet [C# MVP]]

one problem fixed. It seems that setting dr.ItemArray[index] =
sqlReader.GetString(anotherIndex) wasn't setting the value.

Ah - presumably ItemArray is creating a copy of the array each time
instead of returning the underlying array. Kinda reasonable

To correct this I created an object array and used the LoadDataRow
method. Now my nodes are created but they're still in the wrong
place. They're being created at the root level.

So are your rows themselves correct, it's just the conversion to XML
which is incorrect? If so, it sounds like it might be your data
relations which are causing problems - where are you defining them? As
before, if you could give a short but complete program which
demonstrates the problem, that would be great - and if you've now got
the code for loading a table working, your complete program needn't use
a real database at all, just creating your DataTables manually instead.

 

[Cor Ligthert]

Hi Joe,

Why that ItemArray and not the Item, when I understand what you want is it
to find the proper column by the proper item you read with the datareader.

That last part of the text belongs not with the code I povided. When I was
looking at your code I saw it was even simpler and I was making it myself as
well only difficult.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.Item["Col1"] = sqlReader.GetString(0);
\\ set more Items
dtProducts.Rows.Add(dr);
}
ds.WriteXml(@"X:\temp\Test.xml");

I hope this helps?

Cor