[dba-VB] Moving through an ADO recordset

In ADO.NET you can mirror your database in datasets. Datasets are different
from recordsets in that they are more similar to database containers.
Recordsets in DAO are the table equivalents in ADO.NET. Thus, in one dataset
object you can have multiple tables contained inside that dataset. You can
define relations between those tables and you can perform filtering, sorting
and yes update the records in the Dataset.

The approach I have taken in .NET is to connect to the database and retrieve
records from a table and fill a table object in a dataset object. I can then
return the dataset object to my app or return a single table. I use
collections such as Hashtables and Arraylists to iterate through the
records.

Here's a link that explains datasets in ADO.NET

http://builder.com.com/5100-6388-1050109-2.html



-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Saturday, April 28, 2007 6:59 AM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] Moving through an ADO recordset

In Access (because I was a DAO kinda guy) for smallish recordsets I would
often create a class to hold a record (properties for each field I needed to
work with) and a class to hold a collection of these classes.  I would then
read the data out into the record class instances, storing each record class
instance in a collection in a "table" class.  
 
For example, I have an application already built in Access which calculates
the population in all the zips which fall within a radius around a zip code.
My brute force approach was to read about 42 thousand records from a table
of zip codes, pulling just the zip, city, lat, long, population and house
hold count.  I would then store each record in a zip class instance which
has a property for each of those fields in the record.  The ZIPS (plural)
class has a collection to hold the zip (singular) class.  The ZIPS class
opens the recordset and enters a loop creating zip class instances and
passing the recordset off to the zip class instance which loads the fields
into the properties, and on return the ZIPS class stores the populated zip
class instance into a collection.  Once loaded I have 42K zip class
instances in a collection in a ZIPS class, keyed on the zipcode.  
 
Having done that I can now ask the user for a zip code and a radius.  Using
a formula which calculates the distance between two points (lat/long) I
iterate through each and every zip class in the ZIPS collection storing in
an "InRadius" collection all the zip classes where the distance between the
user supplied zip's lat / long and the zip class being evaluated is less
than the radius.  Having done that, when I have examined every zip class in
the big zip collection, I now have a collection containing pointers to only
those zips within the radius.
 
I now iterate that collection adding up the populations and house hold count
of each zip class and display it in text boxes in the calculator form.
 
It is a trivial application to write and other than the initial load of the
zip classes from the recordset is pretty darned fast to calc the populations
- of course depending on the radius.
 
As an exercise in .Net I decided to port that application.  There is one
huge difference however, DAO is gone and I am faced with the ADO object (s)
etc.  The upside is that the ADO widget is actually a "disconnected"
recordset, i.e. it reads it all into memory for me.  This is a "read only"
recordset, i.e. I am not updating records and writing them back to the
table.  
 
This leads me to question whether it makes any sense to do the Zip/ZIPS
class thing or just sweep through the ADO recordset itself.  Are there
record objects?  Could I run directly through the recordset pulling pointers
to record objects and storing those pointers in my collection?  Could I add
a field to my recordset called selected and sweep through the ADO recordset
setting the SELECTED flag in each record that is within the radius, and then
just apply a filter to the ADO recordset to "pull out" only the SELECTED
records to sum them?  And importantly, if doing it this way is possible, is
it faster or slower than iterating my own collection?  I would have to clear
a selected flag between uses whereas a collection is just re-dimed to empty
it and get it ready to fill again.
 
Moving from one tool to another means re-examining the method used to ensure
that it is still an efficient method in the new tool.  Does anyone have any
experience in iterating a ADO recordset in this manner?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.6.1/778 - Release Date: 4/27/2007
1:39 PM