When I use a DataAdapter and call its Fill method to populate data into a
DataTable, most of the DataTable's schema information is missing: all the
column MaxLength values are set to -1, Nullable is set to True for all
columns, Identity to False for all columns, etc. This is a real nuisance.
I'm therefore trying to find a way to get a DataTable filled from a
DataAdapter with the schema information also present.

My initial approach to this was to call ExecuteReader on the DataAdapter's
SelectCommand, and then call GetSchemaTable upon the reader. This gives me
all the column information I need. I then manually create the DataTable's
columns, adding in all the appropriate values. Finally I loop through
calling reader.Read() to get all the row data, and add the rows to the
DataTable too.

This works, but the resulting DataTable isn't associated with the
DataAdapter (I didn't call DataAdapter.Fill at any stage). When I try to
update this table using DataAdapter.Update(DataTable), all kinds of odd
things happen and my changes aren't written to the database.

So question 1: is there some way I can properly associate this
manually-created-and-populated DataTable with the DataAdapter?


Failing that, I tried another approach. After calling DataAdapter.Fill() and
passing it my DataTable, I then called the
SelectCommand.ExecuteReader.GetSchemaTable method and populated all the
schema information into the existing DataTable. This works fine, but my call
to ExecuteReader results in the SelectCommand query being executed twice. As
some of my queries are complex and slow to run, I can't afford to do this. I
tried performing this step prior to calling the Fill method, but the
DataAdapter complains if I leave the reader open, and re-executes the query
if I close it.

So question 2: is there a way for me to obtain the reader object that
DataAdapter.Fill used without having to re-execute the query? Is there any
other way to get the schema information without re-executing the query?

My thanks in advance,

--

(O)enone

"(O)enone,

Have you tried the dataadapter's FillSchema method?

Kerry Moorman


"(O)enone" wrote:

Kerry Moorman wrote:
I have, and it works -- but the minor downside is that it makes a second
database call in order to determine the schema, whereas using the datareader
doesn't. However having run profiler against SQL Server (the DBMS I am
using) it uses the FMTONLY option to stop it actually executing the query,
which appears to be very fast indeed. I'll investigate this further.

Since posting my original message I have also found a way to achieve this
using the datareader approach (which does make just a single call to the
database). I was very close in my previous attempt, but after looping
through the datareader's data and adding the rows to the table, I needed to
call DataTable.AcceptChanges(). Obvious now I think of it but it had me
scratching my head before.

So now I have gone from no solution to two different working solutions!

Thanks for your help,

--

(O)enone