Some newbie questions

[Guest]

Hi All

Wondering if someone might have a few seconds to clear up some newbie
queries I have with VB.NET and ADO.NET. I know their are limitless
situations that require different approaches but would love to know some of
the fundementals as a place to start. Have been through quite a few examples
but are unsure about the following.

1. For a simple database with a couple of tables do you create a
dataadapter for each table and then create a dataset that includes each of
these tables?

2. Do you use the same adapters and datasets througout the application on
different forms? If so how do you make them available to the other forms if
you have used the wizard to create them?

3. Is it best to reference rows/records by ID number in the table or the
row index?

4. Should you link tables in the database using views/queries in the
database or by creating a separate datatable in the dataset that joins the
tables from the database?

5. Any other advice on best practices for learing this stuff.

Appreciate any assistance.
ilr

 

[Mark Rae]

1. For a simple database with a couple of tables do you create a
dataadapter for each table and then create a dataset that includes each of
these tables?

IMO, no. You should consider using a Database Abastraction Layer such as the
one supplied by Microsoft:
http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

2. Do you use the same adapters and datasets througout the application on
different forms? If so how do you make them available to the other forms
if
you have used the wizard to create them?

You don't - you use a Database Abstraction Layer.

3. Is it best to reference rows/records by ID number in the table or the
row index?

It's always best to reference records by their primary key, whatever they
may be.

4. Should you link tables in the database using views/queries in the
database or by creating a separate datatable in the dataset that joins the
tables from the database?

Not sure what you mean here. When you talk about "linking" tables, do you
mean establishing relationships between tables by means of primary and
foreign keys? If so, this should be done at the database engine level. Or,
if you mean joining tables in queries, then you should be doing this in
views or stored procedures.

5. Any other advice on best practices for learing this stuff.

Use parameterised stored procedures - don't use dynamic SQL.

Buy this and read it from cover to cover:
http://www.amazon.com/exec/obidos/t...103-5747795-8651010?v=glance&s=books&n=507846

 

[Cor Ligthert [MVP]]

Hi,

Let me first start with telling, what you don't ask by the way, that there
is not a best or even reaching that method. For reading one value from a
datatabase you can use as well the executescalar, for reading sequential by
instance for a report you can use too the datareader and than there is the
dataadapter.

1. For a simple database with a couple of tables do you create a
dataadapter for each table and then create a dataset that includes each of
these tables?

It is your own choise, both have its pro's and contra's. The designer create
standard for every dataset its own dataadapter, however you are free to use
one and replace everytime the selectcommand, the insertcommand, the
deletecommand and the updatecommand and use one. The last has not any
benefit above using more adapters.

2. Do you use the same adapters and datasets througout the application on
different forms? If so how do you make them available to the other forms
if
you have used the wizard to create them?

As everything in Net is it again up to you. By instance when I need a
dataset and a selected dataset for by instance a combobox, than I make 2
datasets. One full in columns however few rows and one full(ore selected
with a where clause) and only 3 columns.

3. Is it best to reference rows/records by ID number in the table or the
row index?

I don't know how to get records by ID number than by using a find. The last
consumes of course more time than using a row by index. If you mean the
item, than indexing using the column is the quickest.

4. Should you link tables in the database using views/queries in the
database or by creating a separate datatable in the dataset that joins the
tables from the database?

AFAIK is the relational information from the database not transported to the
dataset. You should create always seperatly your relations and therefore use
as much tables as there are relations.

5. Any other advice on best practices for learing this stuff.

Start your project with not using stored procedures, those you can implement
very simple forever (and do that) at the end from your project when you know
that everything is right

Use the designer at least to show you how it can be done. A good approach is
to do that not on a form, however in a seperated component that you have
added as item.

For the rest, try and learn from your errors, every sample or book starts to
learn you how to use it, for the rest is your situation the basic how you
make it. Otherwise a simple generator could do everything.

I hope this helps,

Cor

 

[Sahil Malik [MVP]]

Answers inline ---


--

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

Hi All

Wondering if someone might have a few seconds to clear up some newbie
queries I have with VB.NET and ADO.NET. I know their are limitless
situations that require different approaches but would love to know some
of
the fundementals as a place to start. Have been through quite a few
examples
but are unsure about the following.

1. For a simple database with a couple of tables do you create a
dataadapter for each table and then create a dataset that includes each of
these tables?

Not necessarily true. In fact, don't create a one size fits all dataset -
that is probably a bad idea. One or more data adapters depends on your
architecture.

2. Do you use the same adapters and datasets througout the application on
different forms? If so how do you make them available to the other forms
if
you have used the wizard to create them?

In an enterprise application, I rarely use wizards. Sharing them simply
means exposing them as a property or something like that, but like I said,
enterprise applications cannot be thought of in a drag drop way. I suggest
you should read on how to create all those objects programatically (see my
book).

3. Is it best to reference rows/records by ID number in the table or the
row index?

By Int32 is faster than a string, but you can perform the translation
easily.

4. Should you link tables in the database using views/queries in the
database or by creating a separate datatable in the dataset that joins the
tables from the database?

Depends on your architecture. A one on one mapping is simpler when it comes
to updating data back into the db, and specifying commands for that. But if
you need a tabular join between various tables - then I guess it doesn't
work huh?

5. Any other advice on best practices for learing this stuff.

Tonnes . Please see my book.
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

Appreciate any assistance.
ilr

You're welcome.


--

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

 

[W.G. Ryan - MVP]

Hi All

Wondering if someone might have a few seconds to clear up some newbie
queries I have with VB.NET and ADO.NET. I know their are limitless
situations that require different approaches but would love to know some
of
the fundementals as a place to start. Have been through quite a few
examples
but are unsure about the following.

1. For a simple database with a couple of tables do you create a
dataadapter for each table and then create a dataset that includes each of
these tables?

--Only if you need to do all crud operations on a table in most instances.
Technically you may want to create an adapter for each table, but depending
on your back end, you may want to use Output parameters for scalar values or
if you just need individual values (you can use ExecuteScalar) or use a
DataReader. If the tables are related to each other, than sticking them in
the same dataset is the way to go, otherwise probably not. Also, some
people chose to have many datasets for each query just for strong typing so
that if the schema changes, the code breaks. This again depends on the
scenario.

2. Do you use the same adapters and datasets througout the application on
different forms? If so how do you make them available to the other forms
if
you have used the wizard to create them?

Usign the wizard is probably not the best way to go. Check out the Data
Access Application block or the Enterprise library. I don't know of one
professional application that used the wizard. Mainly b/c you are mixing
your UI logic and Data logic in the same layer and typically the wizards hit
the database directly which negates the possibility of using remoting or a
communications layer.

3. Is it best to reference rows/records by ID number in the table or the
row index?

For columns, definitely use the Index or as bill Vaughn suggests in his
wonderful book, create an enum so you get the clarity of using column names
and the speed of using indexes. As far as the rows go (which is what your
question was), you can use foreach syntax in many cases if you're iteratign
through the whole collection. Using indexes though is another way to allow
you to iterate which may or may not be the case if you use the id.

4. Should you link tables in the database using views/queries in the
database or by creating a separate datatable in the dataset that joins the
tables from the database?

If you need to update the data, YES. Pull the data from each db table into
a different datatable and then use a DataRelation from there.

5. Any other advice on best practices for learing this stuff.

The Enterprise library is a great way.

 

[Cor Ligthert [MVP]]

Bill,

For columns, definitely use the Index or as bill Vaughn suggests in his
wonderful book, create an enum so you get the clarity of using column
names and the speed of using indexes. As far as the rows go (which is
what your question was), you can use foreach syntax in many cases if
you're iteratign through the whole collection. Using indexes though is
another way to allow you to iterate which may or may not be the case if
you use the id.

The only reason that the generated strongly typed dataset is in some cases
quicker is that it is using the datacolumn itself as a column index. I did
not read it, however I had the idea from some investigation together with
Jay B., from Jay, ) that David Sceppa has explained that in his book.

Some tests from us (Net 1.x) showed that the datacolumn was the fastest
method to use, with second the index and thirth the string

Cor

 

[Guest]

Thanks Everyone.

I really appreciate you all taking the time to reply to my queries.

Looks like the questions I ask are a lot more complex than I expected . I
think I need to go back to do a lot more reading on the basics.

At this stage I am really only playing with this stuff and creating basic
applications in order to try and understand it all. Most of the samples I
have seen don't really explain some of the queries I had.

I will take all of your suggestions on board, do some more reading and try
them out.

Regards
ilr

 

[W.G. Ryan - MVP]

If you would, what page is that on in Sceppa's book, that a foreach is
faster than the index. I've seen quite a bit to the contrary but David
knows what he's talking about. I remember him talking about middle tier
components using untyped datasets and then merging them client side with
typed ones for performance but I missed the iteration part.

Anyway, as far as dataset performance goes, typed datasets are the way to go
if that's your primary concern. I read this from Bill Vaughn a while ago
and tested it out, definitely was true in each case I ran through.

<<This code is more human-readable and considerably faster to execute than
its untyped equivalent. That's the advantage of strongly typed DataSets. The
disadvantage is that once the class is generated (at design time), it
assumes that the inbound data schema won't change at some point in the
future. If your data structures are still in flux, it might seem pretty
tedious to use this approach to generate code to return data. In addition,
while this approach can be used to generate more complex DataSets, you'll
find that as your SELECT queries get more complex, you start running up
against the DataAdapter stops ["stops" in the engineering sense of devices
to prevent a mechanism from moving too far in one direction or
another.-Ed.]. That is, if your SelectCommand query contains a DISTINCT
clause, a JOIN clause, or any other syntax that makes it unclear as to where
the data is sourced, the DACW can give up trying to generate the DataAdapter
or the action commands used to update the data.>>

 

[Cor Ligthert [MVP]]

Bill,

We are talking in my idea about columns. Rows as the OP ask, you can only
get by index or by a find (where I see a rowfilter, a select or whatever
than as the same). (or I miss something). In a discussion about the typed
and the untyped dataset. (As you know do I not see any difference than that
a strongly typed dataset is inherited from the DataSet). with Jay B. I
thought me to remember that he told me that he had read something in Davids
book. I don't have Davids book.

There are 6 overloaded methods to index an item in a datarow. In fact that
are 3 because the rest is filtering the rowstatus.

http://msdn.microsoft.com/library/d...html/frlrfsystemdatadatarowclassitemtopic.asp

To my suprise was the datacolumn indexer the fastest. Looking deeper in it,
I thought me to remember, was it because the indexer gets the information
from the datacolumn before he takes the item. The string was faremost the
slowest, I thought because it was first searching the columname.

I hope this explains it more.

Cor

 

[W.G. Ryan - MVP]

Bill,

We are talking in my idea about columns. Rows as the OP ask, you can only
get by index or by a find (where I see a rowfilter, a select or whatever
than as the same). (or I miss something).

--Depends. You can get them using foreach syntax as well.
In this example though, the question was between referring to to value via
Table.Rows[WhateverIndex][ColumnIndexOrName] or looking for the ID value
which is just like any other value that was returned from the db. If
you're saying that referencing it via index is the fastest, then I agree
totally.

In a discussion about the typed

and the untyped dataset. (As you know do I not see any difference than
that a strongly typed dataset is inherited from the DataSet). with Jay B.
I thought me to remember that he told me that he had read something in
Davids book. I don't have Davids book.

-- Performance is notably different. While it's true that typed datasets
inherit from DataSet, DataSets inherit from Object if you go far enough up
the chain. I suspect it's for similar reasons that Strongly Typed
collections are quicker than non typed collections. When you iterate
through each element, there isn't the same amount of checking that needs to
be done internally [this is just a guess but I think it's a pretty safe
assumption). The performance differences between the two are notable in
absolute terms, but they both happen so fast that from a human's point of
view, the difference is negligible. I'm not sure I follow you though about
Jay, is he saying that he's seen better performance with typed or untyped
datasets.

There are 6 overloaded methods to index an item in a datarow. In fact that
are 3 because the rest is filtering the rowstatus.

http://msdn.microsoft.com/library/d...html/frlrfsystemdatadatarowclassitemtopic.asp

To my suprise was the datacolumn indexer the fastest. Looking deeper in
it, I thought me to remember, was it because the indexer gets the
information from the datacolumn before he takes the item. The string was
faremost the slowest, I thought because it was first searching the
columname.

--I'd expect the string to be the slowest but the indexer part is what's
curious. In VB.nET for instance, the second one is faster which intuitively
is what you'd expect. But in C#, the first one is faster (yes, it's hard to
believe but if you check the IL, it is)

Dim TestTable As New DataTable("MyTable")

For i As Int32 = 0 To TestTable.Rows.Count
Debug.WriteLine(i.ToString())
Next
Dim x As Int32 = TestTable.Rows.Count
For z As Int32 = 0 To x
Debug.WriteLine(x.ToString) ' This is faster in VB.NET as you'd expect,
but in C# it's slower
Next

The same holds for columns so what's really 'faster' depends on many factors
and what's true in C# may not be the same for VB.NET and vice versa.
But in respect to the Columnindex, the index is faster b/c it doesn't have
to perform string lookups each time.
Next