Speedup Database Access using GetRows

Introduction
In the tutorials so far we have learned from connecting to the database via ASP to inerting, updating and selecting data from the database.

The area we waste a lot of database time is when we show records to the user and in the mean time keep our database connection open. Following example illustrates this point :

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Our_Query", connStr
While Not rs.EOF
	Response.Write rs("column1") & " " & rs("column2") & "<br>"
	rs.MoveNext
Wend
rs.Close
Set rs = Nothing

Notice that while the above code appears to be just few lines, we have kept the connection to database patent by using rs("column_name") fields directly and use a While .... Wend loop to display all the records.

In this tutorial we will learn a very useful Recordset.GetRows method which will allow us to close the database connection straight away. We will do this by retrieving all the records in a dynamic array and closing the database after this. This will not only allow the database to listen to more requests at one time but will also allow our ASP-Database connection to speed up and we will be able to show records in less time and do more with our ASP pages than simply wasting server time on keeping database open and busy.

What is an Array ?
Before answering that you tell what is a variable ? Yes you answered it right, a variable is some thing which can hold some value for a certain perdiod of time, and this value can be changed ( since it is a variable, right! ).

So a variable can a hold a single value of certain type e.g. String, integer, object etc. This type of variable is called a 'scalar variable'. What if we make a variable hold more than one value ? yes it is possible. This variable which can hold more than one value is called an 'array variable'. Following is how you will create scalar and array variables :

' Declaring a scalar variable
Dim yourName
' Now assigning it a value
	yourName = "John Doe"
' Accessing the variable
	Response.Write yourName
' Will output : John Doe
' Assigning another value to yourName variable
	yourName = "David Maxis"
' Now showing the updated variable
Response.Write yourName
' Will output : David Maxis

' Declaring an array variable
Dim otherNames(3)
' Now assigning values
	otherNames(0) = "First Name"
	otherNames(1) = "Second Name"
	otherNames(2) = "Third Name"
' Accessing its values
	Response.Write otherNames(1)
' Will output : Second Name
' Changing the value of one item in the array
	otherNames(1) = "Another Name"
' Accessing the updated value
	Response.Write otherNames(1)
' Will output : Another Name

There are a few points to notice in the above code. You will mostly be familiar with declaring scalar variables and how to assign, change and access their values. But dealing with array variables might a bit new to you. Array variables are declared the same way as that of scalar variables except that you add a parenthesis () following array variable name.

In order to create an Array of 3 variables, you do the following :

	Dim otherNames(2;

Notice that in Arrays, counting starts from (0). So (0) means 1 as far as Arrays are concerned. This is important so remember it, you will be using this when we use GetRows method to retrieve the database records as a two dimensional array.

So far we have seen what are single-dimension arrays. These are the ones in which the variables start from top to bottom like a database with one column and many rows. Arrays can be two-dimensional and actually as many dimensional as you want. On the upcoming pages we will be dealing with two dimensional arrays in which one dimension will represent columns and the other one, rows.

Some Useful Array Methods
Following are some commonly used VBScript Array methods :

• IsArray

  IsArray(varname)

  Returns True if varname is an Array, otherwise returns False.
• Erase

  Erase varname

  Erases the contents of the Array.
• UBound

  UBound(arrayname[, dimension])

  Returns the largest available subscript for the indicated dimension of an Array.
• LBound

  LBound(arrayname[, dimension])

  Returns the smallest available subscript for the indicated dimension of an Array.

Our Access Database
We will now create a test database to test try our GetRows method.

Open Microsoft Access and creat a new database. Save it as paging.mdb . Now create a new table in design view as show below :

Names - Table

Save this table as Names.

Now populate this table as shown below :

Names - Table

We are now ready to build our ASP page to show these records in one of the fastest ways possible.

Our Access Database
Create a new ASP page and save it as getrows.asp in the same directory where you have kept the paging.mdb database. Then copy the following code into it and rehit the 'save' button.

<%
	Option Explicit
	Response.Buffer = True
%>
<html>
<head>
	<style>
	body, p, td { font-family:Verdana;font-size:8pt; }
	</style>
</head>
<body>
<%
	' ADO Constants
	Const adCmdTableDirect = &H0200

	' Connection String
	Dim connStr
	connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
	Server.MapPath("paging.mdb")

	' Recordset Object
	Dim rs, ds
	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.Open "Names", connStr, , , adCmdTableDirect

	ds = rs.GetRows

	rs.Close
	Set rs = Nothing

	' Now Showing Records from our DataSet
	Dim i, j
	' Creating table to show records
	Response.Write "<table align=""center"" border=""1""" & _
		" width=""70%"""
	Response.Write " cellspacing=""1"" cellpadding=""3""" & _
		" bordercolor=""silver"">"
	Response.Write vbcrlf
	Response.Write "<tr><td colspan=""" & (UBound(ds, 1) + 1)
	Response.Write """ align=""center"">Names</td></tr>"
	Response.Write "<tr><td>ID</td><td>First " & _
		"Name</td><td>Last Name</td></tr>"

	' Showing Each Row
	For i = 0 To UBound(ds, 2)
	Response.Write "<tr>" & vbcrlf
' Showing Each Column
For j = 0 To UBound(ds, 1)
	Response.Write "<td>"
	Response.Write ds(j, i)
	Response.Write "</td>"
Next
Response.Write "</tr>" & vbcrlf
	Next

	Response.Write "</table>"

	Erase ds

%>
</body>
</html>

Explanation
I will only touch the parts of the code which I haven't talked about before.

rs.Open "Names", connStr, , , adCmdTableDirect

ds = rs.GetRows

rs.Close
Set rs = Nothing

Notice the difference between the code above and the one which I showed earlier on. In the code above as soon as we open the database connection, we retrieve all of it's records into a two dimensional array ds and then close the database connection. This not only increases execution speed of the ASP page but also allows the database to fulfill more requests in less time.