Data Access and Transactions

Tip 4: Share Active Connections

Don’t use more database connections than you need. Share connections wherever possible.

Whenever you specify a connection string, rather than a connection variable, you are requesting a new connection to the server. Instead of a connection string, create a single connection variable, and use it with the ActiveConnection property of the Command and Recordset objects.

If your application needs to perform more than one database operation, this approach is a good choice, because it creates the Connection separately from other ADO objects. The object can be reused as necessary and closed when all operations have been performed. Simply specify your existing Connection object when opening Recordset and Command objects. For example, the following script stores a reference to an open database connection in a global variable, and uses it to perform two separate queries against the same database:

<%@ LANGUAGE=VBScript EnableSessionState=False %>
<HTML>
<HEAD>
<TITLE>Products and Customers</TITLE>
</HEAD>
<BODY BGCOLOR=#FFFFFF>
<H2>Products</H2>
<%
   Set cn = Server.CreateObject("ADODB.Connection")
   cn.ConnectionTimeout = 20
   cn.Open "DSN=AdvWorks"
   
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.CursorType = adOpenForwardOnly
   rs.LockType = adLockReadOnly
   rs.Open "Products", cn, , , adCmdTableDirect
   Do Until rs.EOF
   Response.Write rs("Product Name") & "<BR>"
   rs.MoveNext
   Loop
   rs.Close
%>
<H2>Customers</H2>
<%
   rs.Open "Customers", cn, , , adCmdTableDirect
   Do Until rs.EOF
   Response.Write rs("Company Name") & "<BR>"
   rs.MoveNext
   Loop
   
   rs.Close
   cn.Close
%>
</BODY>
</HTML>