One or many DataSet

[gjetthvemjeger]

Hi,
I am working with WB.NET and SQL Server.
I have some trouble grasping what a DataSet in VB.NET is. Do the
DataSet mirror the whole database used as the data store for one
application or is it recommended to use multiple Dataset for this
database (one application).

Example:
My DataSet have 3 tables: Table1, Table2 and Table3.
Table2 has 2 columns, CustomerID and OrderID
Table1 has 1 column CustomerID
Table3 has 1 column OrderID.
For GridList1 I need to define the CustomerID as the PrimaryKey in
Table2 and link it to Table1's CustomerID
For GridList2 I need to define the OrderID as The PrimaryKey in Table2
and link it to Table3's OrderID.

Then I have one Table (Table2) with two different PrimaryKeys depending
on witch Grid I want to display my data in.

Do this work or do I have to make two different DataSets, one for
Table1 and Table 2 and one for Table2 and Table3?

My tables have quite a lot of data. When I fill my Datasets with data
from the database, can I choose witch table in the DataSet I want to
fill or do I have to fill the whole DataSet at once?

Is it best practice to keep the amount of DataSets small but with many
tables or it is best practice to have many DataSets but a small amount
of tables in each one of them?


Thanks in advance )

 

[W.G. Ryan - MVP]

Hello: If you would, check out my reply in the thread title "
When to use new DataSet? When to perform DataSet resident joins?"
Sigfried Heintze on 10/26. That should give you background for the
question, but if taht doesn't address specifically what you are looking for,
let me know and I'll take it from there.





(e-mail address removed)> wrote in message

 

[Cor Ligthert [MVP]]

Bill,

I was not aware of your reply. Your message in that thread of writing XML
dataset was something, I did want to point on, I closed the message from the
OP to see where it was, than I saw that you had given already an answer and
had nothing to add to it.

By the way Siegfried mail address is from Norway. 10/26 does not always
direct flash a light here in Europe. We call that 26/10 or better in the
newsgroups 26 October or October 26 what will be as well understood. (With
what I don't say that this OP did not understood it).




Cor

 

[W.G. Ryan - MVP]

Bill,

I was not aware of your reply. Your message in that thread of writing XML
dataset was something, I did want to point on, I closed the message from
the OP to see where it was, than I saw that you had given already an
answer and had nothing to add to it.

--Are you speaking of the XML Data Adapter thread? Since it comes up, I'm
thinking of posting an article on one of my sites with the code we use at
work and how we handle everything from the various applications. Until
recently, a lot of the components of System.Xml were weak points for me, but
I decided to try to change that. Learning XPath, XQuery and XSLT has
definitely changed the way I handle many data related items b/c prior to
learning them better, it seemed that a lot of things were either impossible,
or too inconvenient to want to do. Not sure if you're interested but I'm
actually trying to write my own XML db in the spirit of
http://exist.sourceforge.net/. But I'm probably late since Sql Server
2005's support for XML is so thorough and robust

By the way Siegfried mail address is from Norway. 10/26 does not always
direct flash a light here in Europe. We call that 26/10 or better in the
newsgroups 26 October or October 26 what will be as well understood. (With
what I don't say that this OP did not understood it).

-I knew you guys had the metric system over there, but with at least 26
months in the year, that's got to get confusing. And if if think my date
formatting is bad, you should have heard the laughs when I tried to
pronounce Scheveningen, which in my terrible English accent sounded like
Sheennniveninigen ;-) I'll keep the date thing in mind though, thanks for
the heads up.

 

[Cor Ligthert [MVP]]

Bill,

No the part where you were telling that if you use an datatable off line you
can better place them separately in a dataset. I found that a good
explanation.

I have tried about 4 years ago XLST to use with webpages and Datasets. It
took me some time and I could do it easier with JavaScript (LoadDoc),
probably do I have therefore (probably not with a reason) something against
that.

I never tried it with a real XML document, I will try that again.

If you know that you mean with 10/26 a month, than it is not difficult
anymore, however it can sound as well a kind of code number therefore
October 26 will not be any problem. To be clear, I never had real paid any
attention to it until I heard how some Americans were saying 9/11.

You are not the only one who cannot pronounce "Scheveningen" almost nobody
who's mothers tongue is not Dutch can say that. A same word is by instance
"Leeuw". The Flemish Belgians let the people from the French army they had
concurred say that word. "Vlaamse Leeuw" and if they could not they knew it
where French and cut of their heads.

I first thought you had been here, however you have probably spoken with
Frans. You know that AFAIK his mother tongue is not Dutch so I have to
correct myself, Dutch and Fries.



Cor

 

[harbinger20000]

Cor Ligthert [MVP] wrote:

You are not the only one who cannot pronounce "Scheveningen" almost nobody
who's mothers tongue is not Dutch can say that. A same word is by instance
"Leeuw". The Flemish Belgians let the people from the French army they had
concurred say that word. "Vlaamse Leeuw" and if they could not they knew it
where French and cut of their heads.

I'm sorry to butt in but that wasn't the catchphrase they used to
detect Frenchies. It was "Schild en Vriend", or more correctly "Schilde
ende Vrinde" (Old Flemish - meaning "Shield and Friend"). And cutting
off their heads is a bit harsh, don't you think? We just clobbered them
to death with a Goedendag (typical Flemish weapon of the day, we didn't
have waffle irons back then). It's much more civilized.

Okay, I'll butt back out now. (I just happened to pass by, don't mind
me..)

Some Fleming.

 

[Cor Ligthert [MVP]]

Hi,

So you see how those stupids Dutch don't know nothing from the Flemish
history I thought forever that it was. "Voor Vlaanderen den Leeuw".

What you told now I will remember it is more obvious.

)

Cor

 

[Sahil Malik [MVP]]

Here is a quote from Chapter 14 of my book ----

"So what is a good size of a DataSet? This question is just as difficult as
how much butter on a toast is enough. While seemingly simple, the exact
boundaries cannot be clearly defined.

As a rough yardstick, you can say that a large single table (why not just
use a DataTable?), two medium sized tables, or three small tables should be
a good guideline in most situations. Though, it is perfectly acceptable to
bend these rules, if you know what you are doing. Do remember however, the
structural complexity of the DataSet hurts performance more than the sheer
size of it."

Then I have one Table (Table2) with two different PrimaryKeys depending
on witch Grid I want to display my data in.

<-- How can one table have two primary keys?? :-/

My tables have quite a lot of data. When I fill my Datasets with data
from the database, can I choose witch table in the DataSet I want to
fill or do I have to fill the whole DataSet at once?

You can certainly choose.

Is it best practice to keep the amount of DataSets small but with many
tables or it is best practice to have many DataSets but a small amount
of tables in each one of them?

See the quote from my book. The reason a "best practise" doesn't exist on
this, is because .. it truly depends on your situation. Just remember, a
dataset is not a database, and it shouldn't be abused as one either. Please
also read -
http://codebetter.com/blogs/sahil.malik/archive/2005/08/04/130264.aspx



- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[gjetthvemjeger]

Thanks for your comments Sahil, but I have some more questions....

<-- As a rough yardstick, you can say that a large single table (why
not just
use a DataTable?), two medium sized tables, or three small tables
should be
a good guideline in most situations.

When you say a large single table, then you mean a table with lots of
columns, the "design" is large?
Or do you mean that a table is large when it lists a large set of data
form the database?


<-- How can one table have two primary keys?? :-/

My fault, Table2 is really a view not a Table.

Table1 has 1 column CustomerID
ViewA has 2 columns CustomerID,OrderID
Table3 has 1 column CompanyID, OrderID

My Grids is hierarchical Grids.
In the first level in my Grid I list Table1 and in the second level I
list ViewA.
Then I have another Grid on the same page that in the first level shows
the OrderID's for one CustomerID, depending on witch CustomerID the
user have selected in the grid above.
To create a relation between ViewA and Table3 I have to define the
OrderID as the PrimaryKey in ViewA, the Grid can then list all the
CompanyID's for the selected OrderID.
The items in my columns CustomerID and OrderID is not unique. But
because I select just one specific OrderID from the ViewA I can define
OrderID as a PrimaryKey when I list my data in the Grid (Select * from
ViewA where OrderID=2) Then Grid level 2 shows only CompanyID for that
specific OrderID.

Is this wrong?

Can one view or one table be represented twice in one DataSet?
Can one DataSet contain Table1 once, ViewA twice and Table3 once?
And can I define OrderID as the PrimeryKey in ViewA for the relation
between ViewA and Table3 but not for the relation between Table1 and
ViewA in the same DataSet?

 

[Sahil Malik [MVP]]

DataSet structure complexity hurts your performance more than the sheer size
of it - by that definition, the number of rows isn't as important as the
number of columns.

Do realize however that the internal logic inside datatable/dataset is quite
different in .NET 2.0 and 1.1. This means, things such as removing rows,
getchanges, merge etc. that could get exponentially bad with the number of
rows/columns/structure of the dataset - degrade much more gracefully in .NET
2.0.

There is a lot more that meets the eye, or can be described as a simple
statement such as the one I pulled from my book. Which is why it is a "rough
yardstick" not "gospel"

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx