Properly closing a database connection - VBScript

Question

I have a classic ASP (vbscript) web app that connect to an SQL server. On certain pages, I open multiple DB connections to pull data.

My question is:

Is it better to add a close connection function at the bottom of each page or to explicitly close the connection right after using it? Keep in mind, on these certain pages, I reopen a DB connection everytime I get data; I do not reuse the connection. IE:

Set DBread = Server.CreateObject("ADODB.Connection")
DBread.Mode = adModeRead
DBread.Open (SQL_DB_CONN)

When I close the connection, I use:

DBread.Close
Set DBread = Nothing

So, should I constantly open then close the connection OR constantly open connections, then close them once at the end of a page?

Answer 1

You should open one connection and make all queries at the top of your page then close the connection as soon as the last query has executed

Example:

Set DBread = Server.CreateObject("ADODB.Connection")
DBread.Mode = adModeRead
DBread.Open (SQL_DB_CONN)

'Make SQL Calls Here and Save rows of data by using the getrows function

DBread.Close
Set DBread = Nothing

'Process rows of data here

Answer 2

Keep your connections open for as short a time as possible. Connection pooling will worry about efficiency for you.

But, if you have sequential db operations, they can share the same connection. This will also allow you to use transactions.