We help IT Professionals succeed at work.

Read result set into array of Dictionary's.

Mick Barry
Mick Barry asked
on
2,171 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.
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
> This is not very efficient in terms of performance

WHat are the inefficient parts?

Commented:
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)
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
Thats what I thought, is there any way to determine the column names if GetRows is used?

Commented:
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
%>
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.