What's the diff. between DataSet and DataTable and other Basic Q's

[Burning_Ranger]

I have a database with several tables all linked together using
relationships, I am trying to build a system in VB .NET utilising this
database.

I have some Q's:
1. I've followed some ADO.NET tutorials and they all use databases with
1 table. The tutorials use a DataTable to hold a copy of the table in
memory, but I get the impression now that DataSet's are the thing to
use. How does a DataSet differ from a DataTable?

2. My Database has referential integrity set, will ADO.NET recognise
that the tables have relationships and update data automatically that
is linked by referential integrity or do I have to specify the
relationships again in VB.

3. If I'm using a DataTable and use the following code to reference a
particular field of a particular record, what's the equivalent if I'm
using a DataTable?

txtCompany.Text =
dtCustomer.Rows(intRowPos)("CustomerCompany").ToString

4. The books I have tend to all use wizards to make the ADO.NET
connection and tend to use bound controls. I've read that this is not a
good way to use ADO.NET, isn't it?


If someone could point me to a good tutorial that shows how to build
basic app utilising a database, DataSets etc or something similar
(without wizards or bound controls), that would be appreciated. Thanks.

 

[Marina Levit [MVP]]

1. A dataset is a collection of one or more datatables. A datatable is what
actually contains data
2. I believe you would have to specify those relationships yourself
3. Huh?
4. It is not a good way to write real applications. I recommend you code it
yourself

I think if you do a google search you will find a ton of good examples,
tutorials, faqs, etc.

 

[Burning_Ranger]

Sorry, number 3 should have been:

3. If I'm using a DataTable and use the following code to reference a
particular field of a particular record, what's the equivalent if I'm
using a DataSet

But never mind I worked it out.

Regarding question 1, I've been manipulating the table in my DataSet
this way:

dsCustomer.Tables("Customer")

Is this okay, or should I be using DataTables and then populating the
DataSet with the DataTables once I'm done?

Thanks for the answer's BTW.

 

[Hendrik]

1. As said above - a Dataset contains a collection of DataTables.

2. No, the dataset will not automatically "detect" your relationships.
You have to specify it yourself. Easiest is to do it in the dataset
designer. Click on the left of a column (in a table) and drag to
another column in anther table. A screen will appear to specify the
details.

3. This depends. If you are using a Typed Dataset (designed in the
designer) - specify the dataset, and you will be able to specify the
table directly ie myDataset.myTable1 (Typed dataset will expose the
tables). Otherwise myDataset.Tables("myTable").Rows(x) should work (my
VB is a bit rusty)

4. Personally I never use the wizards - I prefer to code it. Also in
code set the bound properties. One problem (although more in VS2003) is
that when using the wizard, you specify the connection (SQLConnection).
What happened to me is that in the end you have 100 pages with, yes,
100 connections defined. Now deploy and try changing your connection
string... If security is not a huge issue, put the connection string in
the app.config file. Make only one instance of a Connection Class and
reference that throughout the application.

This is a old example I found in C# :

SqlCommand cmd = new SqlCommand("SELECT * FROM [user],
myConnection);
SqlDataAdapter Adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

Adapter.Fill(ds);
Adapter.Dispose();

dataGrid1.DataSource = ds;
dataGrid1.DataMember = ds.Tables[0].TableName;

Hope this helps

 

[Burning_Ranger]

Thanks, I've actually been doing it without wizards so far but most of
the books I bought are useless unless you're doing everything with
wizards and/or taking the (initially) easy route.

2. Sorry, where is the DataSet Designer (I'm using VB .NET 2003)? I'm
coding all the ADO stuff by hand, not using wizards.

3. Currently I'm using untyped DataSets. Does one have an advantage
over the other? (other than typed being quicker to write).Any reason
not to use typed?

4. I'm coding by hand and so far have yet to come across a situation
that needed more than 1 connection.

Thanks for all the information.

 

[Aziz]

I think I've found the DataSet designer. Is it the XSD Schema after
you've created DataAdapterss and Generated a Dataset?

So is it just a case of using the Data control wizards to create
DataAdapters out of all my tables, assigning them to a DataSet and then
going into the XSD schema and dragging the PKs and FKs to each other
and configuring the settings for each relation? Will my tables then
have 'referential integrity'?

 

[Marina Levit [MVP]]

I am not in favor of using the wizards, so I don't use them and don't the
answers to your questions.

 

[Hendrik]

"I think I've found the DataSet designer. Is it the XSD Schema after
you've created DataAdapterss and Generated a Dataset? " - Yes that is
the designer. You can even use XML (there is an XML table bottom left.)

You can use the wizards as you describe above. Or just design the
dataset without dragging/dropping new dataAdapters. (Add new item to
project - dataset). Once the dataset is designed (with all your
relations) you can use it in code. If the dataset's name is myDataset1:

Dim myData as new myDataset1. Then in code you can populate this
dataset.

If your dataset has relations, you must fill the dataset's tables in
the correct order (ie must fill parent values first, so that child
values has corresponding parent values). The dataset will check for
this integrity. You can also "disable" the relations for time being :
myDataset1.EnforceContraints = false.

Enjoy!

 

[Aziz]

Thanks Hendrik.

Just one more Q, when I create a DataSet on a particular Windows Form,
will I have to create the DataAdapters and relationships for all my
tables, even the ones that arn't directly used in the form? (all my
tables are linked together in some manner).

 

[Hendrik]

I'm not too sure what you mean.

Relationships is defined between tables in the dataset designer.
Remember, you do not have to make the dataSet schema look exactly like
the dataBase schema. If you have contraints / indexes on the database
it does not mean you must have it on the dataset as well. Only add the
tables in the dataset which is used on the specific form. Keep in mind
that the typed dataSet is fixed, so if you change the tables in the
dataBase, you must manually go change it in the dataSet as well.

Personally, I use typed datasets. I never drag/drop an adapter or
connection - this I do in code.

You only need one DataAdapter per form. Here is a code example: (its C#
but you will get the idea):

SqlDataAdapter adapReport;

//DatasetSyncReports is my Typed dataSet
DatasetSyncReports dsSyncReport = new DatasetSyncReports();

adapReport = new SqlDataAdapter("SELECT * FROM Report" ,
mSession.Connection);
adapReport.SelectCommand.Transaction = t;
adapReport.Fill(dsSyncReport.Report); //FILL THE Report Table

//Use same adapter - just change the command text
adapReport.CommandText = "SELECT * FROM ReportSection";
adapReport.SelectCommand.Transaction = t;
adapReport.Fill(dsSyncReport.ReportSection); //FILL THE ReportSection
Table

adapReport.CommandText = "SELECT * FROM L_ReportSectionQuestion";
adapReport.SelectCommand.Transaction = t;
adapReport.Fill(dsSyncReport.L_ReportSectionQuestion); //FILL THE
Question Table