Typed DataSet confusion

[Rob Richardson]

Greetings!

I have a database with a table named Holdings. I has VB.Net create a .xsd
file for it so that I could use a typed dataset with it. The table has 21
rows. If I use ordinary datasets, I get a DataTable object with 21 rows.
If I use a typed dataset, I get a HoldingTable object with 0 rows. What am
I doing wrong? Here's the code:

Dim holdingsAdapter As New OleDbDataAdapter()
Dim holdingsSet As New HoldingsDataSet()
Dim holdingsTable As HoldingsDataSet.HoldingsDataTable
Dim holdingsRow As HoldingsDataSet.HoldingsRow

'Dim holdingsSet As New DataSet()
'Dim holdingsTable As DataTable
'Dim holdingsRow As DataRow

holdingsAdapter.SelectCommand = New OleDbCommand _
("SELECT * FROM Holdings", mDB)
holdingsAdapter.Fill(holdingsSet)
holdingsTable = holdingsSet.Tables(0)
MsgBox("The holdings table has " & holdingsTable.Rows.Count & " rows.")

This code is in a Try block, and no exceptions are thrown.

Also, I notice that my HoldingsDataSet object has a member named Holdings.
Is that member an instance of type HoldingsDataTable, like I think it is?

Thanks very much!

Rob

P.S. I have already been told that using "Tables(0)" defeats the purpose of
a typed dataset. That's not the point here. Once I know I can get the data
I need into the typed dataset, I'll switch over to using it the way it's
supposed to be used.

 

[William Ryan]

Rob:

Where you are using the WriteLine, check to see that DataSet.Tables.Count is
exactly 1 at this point. I suspect that you'll find that there are two
tables in there and you are filling the second one.

 

[Rob Richardson]

Well, I'm getting somewhere. In my original code, I used the following
line:

holdingsAdapter.Fill(holdingsSet)

I changed it to this:

holdingsAdapter.Fill(holdingsSet, "Holdings")

and I end up with 21 rows, as expected.

But I still don't understand. Can I only use a typed data set with an
entire table? Can't I use it with a query where the structure of the result
matches the schema, as it did with the query "SELECT * FROM Holdings", which
I used when I created the command object for the adapter's SelectCommand
property? Can I not use the SelectCommand property at all when filling a
typed dataset?

In my original code, I checked the return value of the Fill() code, and saw
that it was 21, as expected, but the table still had zero rows. Where did
those 21 rows go? What is the Holdings member of my HoldingsDataSet used
for? In a typed dataset, is the plain ordinary Tables collection used at
all?

I may be able to learn by rote how to get information into and out of a
typed dataset, but I have not found a complete, consistent explanation of
them. The help files are too fragmented to help me out of this morass. Is
there someplace on the web where I can get a complete, well-organized
description?

Thanks again!

Rob

 

[William Ryan]

Rob:

I think you'll notice as well that with the first code sninppet you end up
with two tables and with the second one, only one. In the first sample, the
first table will have 0 rows while the second one should contain what you
expect, hence, that's where the other 21 rows went.

I'm not sure what you are asking about with the Select command but as long
as the select criteria match, you can definitelyuse a select command. I'm
not sure there's any other way to fill it while still using a dataadapter.

I'm probably misunderstanding the question (It's been a long day) but I
think you'll see what's happening if you step through it using both
declarations...check the table count each way.

HTH,

Bill

 

[Ryan Trudelle-Schwarz]

Well, I'm getting somewhere. In my original code, I used the following
line:

holdingsAdapter.Fill(holdingsSet)

I changed it to this:

holdingsAdapter.Fill(holdingsSet, "Holdings")

and I end up with 21 rows, as expected.

Right, that's as it should be, sort of. Go back to the first version and
look at the table names, you should see some generic name ("Table" or some
such) as the second table. This is what is returned from the database, but
the adapter has no way to know it corresponds to "Holdings". This
correspondence is what you are adding by specifying the target table name.

The proper solution here is to add table mappings. In your case with one
table, you'd just need one line, someting like:

adapter.TableMappings.Add("Table", "Holdings")

if you had multiple tables, you'd add them using this naming convention:

0th result set == "Table"
1st result set == "Table1"
nth result set == "TableN"

This is a rather silly naming convention, but one we must live with

What is the Holdings member of my HoldingsDataSet used
for?

Think of it as a object structure. the data set is the database, the
Holdings property is then the table which corresponds to the Holdings table
in your database. This will be an instance of the HoldingsDataTable class
(assuming you haven't overridden the names). Generally, you should be
accessing the tables via these properties, as they allow you to access the
typed tables directly (otherwise, you need to cast up to the typed table
type in order to make use of the typed information).

So, with a database structure like so:

<SomeDB>
- Table1
- Table2

you would get the data structure like:

SomeDBDataSet
- Table1 Property
- Table2 Property

Table1 would then have a property called "Table1Items" or some such, which
indexes into the rows of the table. Each Table1Item would then have a typed
property for every field in the table...