Data Access and Transactions

Tip 5: Restrict Connections Across Pages

If your provider doesn’t support automatic connection pooling, find a balance between the greatest number of connections on the one hand, and the hidden costs of creating and destroying connections on the other.

Storing Connection objects (or any of the database access components, for that matter) in the ASP Application object is not recommended. Unless your OLE DB provider supports the Both threading model, you will cause the Web server to serialize all user requests for the application—not a way to improve performance. Access does not support this, though SQL Server does; see tip 1. Even if your OLE DB provider supports free threading, you must be cautious when storing any components in the Application object.

If you must hold connections open, it is better to create them as individual users require, and store them in the user’s Session object. Like the Application object, Apartment threaded providers such as Access lock the session to a single thread for all requests. Because sessions must time out before the server resources are finally released, applications that store connections in the Session object should also provide a means for terminating the session. This allows other clients to use the connection. For example, you could provide a Log Off button to explicitly end the user’s session and release the active connection. (See Developing Web Applications in this book.)

The following example opens a connection at the start of the user’s session. The connection is automatically closed when the session ends.

<OBJECT ID=cnSession RUNAT=Server SCOPE=Session
CLASSID="clsid:00000514-0000-0010-8000-00AA006D2EA4">
</OBJECT>
Sub Session_OnStart
'Open ADO connection to "UsersDB1" database.
cnSession.Open "UsersDB1", "userdblogin","userdbpassword"
End Sub

Remember, although you can utilize a single connection across more than one page, doing so holds the connection open and defeats the advantages of connection pooling.