First of all lets clarify the two issues…
Recordsets
dim Conn,querystring,RS
Set Conn = Server.CreateObject(“ADODB.Connection”)
Conn.Open DBName
querystring = “SELECT name,email,tel FROM users”
Set RS = Conn.execute(querystring)
if not RS.EOF then
do while not RS.EOF
response.write(“Name: ” & RS(“name”).value & “
“)
response.write(“Email: ” & RS(“email”).value & “
“)
response.write(“Telephone: ” & RS(“tel”).value & “
“)
RS.movenext
loop
end if
RS.Close
Conn.Close
Set Conn = nothing
Recordsets opens a connection to the database, it then calls a query which opens the recordset and leaves it open. Then you move through the recordset (by the movenext command) and when you want you can call specific fields to display. When you have finished you close the recordset and close the connection.
GetRows
dim users,users_eof,record,Conn,querystring,RS
const user_name = 0
const user_email = 1
const user_tel = 2
users_eof = true
Set Conn = Server.CreateObject(“ADODB.Connection”)
Conn.Open DBName
querystring = “SELECT name,email,tel FROM users”
Set RS = Conn.execute(querystring)
if not RS.EOF then
users = RS.getrows()
users_eof = false
end if
RS.Close
Conn.Close
Set Conn = nothing
if not users_eof then
for record=0 to UBound(users,2)
response.write(“Name: ” & users(user_name,record) & “
“)
response.write(“Email: ” & users(user_email,record) & “
“)
response.write(“Telephone: ” users(user_tel,record) & “
“)
next
end if
Getrows first starts by opening a connection to the database and calling a query which loads a recordset. Then the getrows command pulls the entire contents of the recordset into a two-dimensional array. We then close the recordset and the database connection as we no longer need it. We are now working from the memory using the array variable. The first dimension of the array contains the fields (E.g. Name, Email and Tel). The second dimension of the array contains the records. Think of it as a spreadsheet with columns headed Name, Email and Tel, and the rows down the spreadsheet are the records from the database. We then traverse through the array by starting zero and going to the last item in the second-dimension (UBound(users,2)). We use “const user_name = 0” so that when we display “users(user_name,record)” we can see what it is referencing instead of “users(0,record)” which would be meaningless to anyone else.
Above you can see two working examples of each method. Upon first inspection you will notice that Getrows has more lines of code which will instinctively put you off. But the main difference between the two are: Recordsets keeps the database connection open, GetRows closes the database connection as soon as possible. Also Recordsets is constantly making calls to the database (movenext, eof, value) each of these is a call to the database which needs to be processed and returned by the database. With GetRows the contents is called within one database call, yes it is a lot more data being sent across than a single “value” command but making the call once is better than making 20+ calls which in the end returns the same data. Because the returned array is then stored in memory, it is then accessed a lot faster than a database call would be.
Probably the most useful function with getrows however that I have noticed is the ability to transfer the array around different functions. The array can be generated within one function which is then passed back to the master ASP page, this page can then pass the array into another function which performs a calculation on the array or anything.
Since using Getrows I have noticed I am using functions a lot more and by doing so my code has become a lot neater. And of course this has also saved me coding time. Although the original getrows takes a few extra lines to code, by including everything into functions I can reuse that function somewhere else over and over again. I often find that I reuse a function which I originally thought would only be used in one place.
I have also noticed a speeding up of the processing time for compiling the page each time it is loaded, although this is generally only noticable on page which have heavy ASP and database coding.
As you can see I am biased towards Getrows. The simplest answer is try it for yourself and I think (like me) you will start coding in Getrows all the time.<
