Using ADO Stand-Alone Recordsets as an Alternative to Arrays or Dictionaries
By Roman Koch
Introduction
If you need to deal with a large amount of dynamic data in a VB-type program, you often
need to work with arrays or with the Dictionary object. While both methods have their
merits, I often felt that I would prefer a more flexible approach to deal with data in
memory. Then I learned about ADO stand-alone recordsets - giving you all the power of
the Recordset object when dealing with data in memory.
Simply speaking, a stand-alone recordset has no source - no Command object, no SQL statement, no table name. It's an in-memory collection of records and fields. You create and use a stand-alone recordset in the same way you do with sourced recordsets:
|
The above code includes the adovbs.inc
constants, hence the use of the
constant adChar
. To learn more about adovbs.inc
constants be sure
to read: ADOVBS.inc
- Use It!
What makes stand-alone recordsets so attractive? You can use all
the ADO recordset methods and properties to work with your in-memory
data. You can sort and filter the records, you can use the Seek
,
Delete
or AddNew
method, you can clone the thing and even
save it to disk - even in XML format, if you have ADO 2.1! (For more information on XML, be
sure to check out the XML Article Index.)
Additionally, when you are working with ActiveX objects, for example the ADO Data Control, you can set the RecordSource property of the control to your stand-alone recordset and then use the power of a linked DataGrid control to work with your in-memory data!
On the other hand, stand-alone recordsets are slower than arrays or Dictionary objects, and they also eat more memory. You will need to balance the power of in-memory recordsets against the higher price in speed and memory consumption. For more information on using stand alone Recordsets be sure to read Ryan S.'s article: Using Custom Recordsets.
The DirList.ASP
Sample Program
DirList.ASP
demonstrates how you can use a stand-alone recordset.
DirList.ASP
reads a directory and displays the files and some file
attributes in a table. On subsequent calls to DirList.ASP
, the table
is sorted and filtered. DirList achieves this by modifying the
properties of the underlying stand-alone recordset. Here is what
DirList.ASP
does in detail:
-
1. If
DirList.ASP
is called without any parameters, it creates
the stand-alone recordset and fills it with information
about the files in the directory where DirList.ASP
resides
(the FileSystemObject is used to get information about the
files).
2. If DirList.ASP
is called with a parameter, it modifies the
properties of the stand-alone recordset as indicated by
the parameter command.
3. It then processes the the recordset to display the directory
listing in a table format. It also provides a couple of "command"
links to call DirList.ASP
with sorting and filtering parameters.
Keep in mind that DirList.ASP
is just a demonstration program - it does
little error checking and is not intended for productive use. There's also a
live demo that you can play with before
downloading the
code.
Happy Programming!
Roman Koch
http://www.romankoch.ch
Attachments:
DirList.asp
in ZIP format