Solved

Read result set into array of Dictionary's.

Posted on 2004-04-05
7
2,140 Views
Last Modified: 2012-06-21
Whats the most efficient way to read a database queury result set into an array of Dictionary objects.
ie. each array element contains a Dictionary representing a row, and the dictionary stores column name=column value pairs.
0
Comment
Question by:objects
  • 3
  • 3
7 Comments
 
LVL 28

Accepted Solution

by:
sybe earned 400 total points
ID: 10763442
I am afraid that you are stuck with this procedure:

Query -> Recordset
    Loop through Recordset
    For each record:
           create new element in Array
           make element dictionary
                for each field in recordset
                        add element to ditcionary


This is not very efficient in terms of performance (the code is quite short though), but at the moment i don't see another solution

<%
Set oRS = connection.Execute("SELECT * FROM tablename")
Do While Not oRS.EOF
    ReDim Preserve aResult(Ubound(aResult)+1)
    Set aResult(Ubound(aResult)) = Server.CreateObject("Scripting.Dictionary")
    For i = 0 To oRS.Field.Count - 1
        aResult(Ubound(aResult))(oRS.Fields(i).Name = oRS(i).Value
    Next
Loop
%>

                 
0
 
LVL 92

Author Comment

by:objects
ID: 10763529
> This is not very efficient in terms of performance

WHat are the inefficient parts?
0
 
LVL 28

Expert Comment

by:sybe
ID: 10763709
there's lots of inefficiency. If you do a GetRows() on the recordset, you have a 2-dimensional array, which is much faster then an array of Dictionaries.

Also ReDim Preserve is not a very efficient procedure, especially not with an array of Dictionaries. You could speed that up by ReDimming the array before looping through the recordset - if you know the number of records (oRS.RecordCount)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 92

Author Comment

by:objects
ID: 10763864
Thats what I thought, is there any way to determine the column names if GetRows is used?
0
 
LVL 28

Expert Comment

by:sybe
ID: 10763916
no, GetRows() itself does not give the column names. But you could of course create a special array for the column names.

<%
Set oRS = connection.Execute("SELECT * FROM tablename")
Set aData = oRS.GetRows()
ReDim aColumnNames(oRS.Fields.Count-1)
For i = 0 To oRS.Fields.Count-1
    aColumnNames(i) = oRS.Fields(i).Name
Next
oRS.Close
Set oRS = Nothing

' aData holds the data from the recordset
' aColumnNames holds the fieldnames
%>
0
 
LVL 15

Assisted Solution

by:deighc
deighc earned 100 total points
ID: 10765065
I agree with @sybe - there's alot of overhead in "saving" a recordset into a dictionary object. In fact I suspect that this would give inferior performance to a disconnected recordset.

For ultimate performance arrays are the best option but, again, there's a downside with the field names as you already know. It's very annoying when you add or remove fields to your query to have to update you array indexes. You can define constants to make this easier but it's still a bit of a nuisance.

Another option I've used with some sucess is saving a recordset into an XML DOM and accessing the data that way. If you use the newer versions of MSXML the performance is very good. The XML contains not only the data but a full definition of the database columns, so it's very flexible.
0
 
LVL 92

Author Comment

by:objects
ID: 10779994
0

Featured Post

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add server behaviors to Dreamweaver CC 2015 2 159
Connection String to remote Server not working 3 88
ASP Focus problem 3 61
Error in query expression 3 43
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question