The Connection Object
(Page 1 of 15 )
ADO (ActivX Data Objects) gives us a way to manage data from all kinds of data stores, not just relational databases. This chapter looks at how to work with the Connection object and the methods, properties, collections and events that this object makes available to us. (From the book
ADO Programmer's Reference by David Sussman, ISBN: 1590593421, Apress, 2004.)
The Connection object is what connects the consumer to the provider; it’s the link between the program and the data. As you’ve already seen, the flat model of ADO means that Connection objects don’t need to be created explicitly. Instead, you can pass a connection string directly to a Command or Recordset object, and ADO will create the Connection object for you. However, explicitly creating a Connection object is worthwhile if you are planning to retrieve data from the data source more than once, because you won’t have to establish a fresh connection each time.
Connection Pooling In the newsgroups during the early days of ADO, there was a lot of talk about connection pooling and whether the benefits really are worthwhile. One of the most time-consuming operations you can perform is the act of connecting to a data store, so anything that speeds this up is defined as "a good thing."
Connection pooling means that ADO will not actually destroy Connection objects unless it really needs to. The signal for destroying a Connection object is dictated by a timeout value; if the connection hasn’t been reused within the allotted time, it is destroyed. Here’s how it works. Suppose you open a connection, perform some data access, and close the connection. From your point of view, the connection is closed. But underneath, OLE DB keeps the connection in a pool, ready for it to be used again. If you decide that you need to open a connection to the same data store again, you will be given a Connection object from the pool of connections, and ADO doesn’t have to perform all of the expensive data store stuff again. You may not necessarily get the exact same connection object back, but you’ll get one that matches the same connection details that you previously used. In fact, existing objects will be given to anyone who requests them, so connection pooling is even more effective in a multi-user system.
One important point to note about connection pooling is that connections will be reused only if they match the exact connection details. So on multi-user systems, if you specify the same data store but use different user names and passwords, you will create a new connection rather than having one reused from the pool. This may seem like a disadvantage, but pooling must be done this way to avoid breaking security; it just wouldn’t be right to reuse a connection based on different user details. You could, of course, create a generic user and perform all of your data access through this user, thus maximizing the use of pooling. Under version 2.8 an access token check is performed before the SID when creating pooled sessions. This ensures that delegated credentials are used correctly.
You should also realize that connection pooling is not the same as connection sharing. An individual connection is not shared among multiple connection requests. Pooling means that closed connections are reused; open connections are not.
You can test to see whether connection pooling really is working by using a tool to monitor the active connections to a data store. For example, with Microsoft SQL Server you can use SQL Trace. Executing the following code shows only one connection being opened:
For iLoop = 1 To 5
objConn.Open strConn
objConn.Close
Next
However, if you turn off connection pooling, you’ll find that the five connections are opened and then immediately closed, one after another.
Although you probably wouldn’t want to, you can turn off connection pooling by adding the attribute OLE DB Services=-2 to the end of the connection string:
strConn = " . . .; OLE DB Services = -2"
Alternatively, you can achieve the same result by setting the Connection object’s Properties entry equal to -2 as in this example:
objConn.Properties("OLE DB Services") = -2
This property takes values from the DBPROPVAL_OS constants (see Appendix B). If you’ve already flicked to the back of the book to find the constant with a value of -2, you’ll notice there isn’t one. There is, however, a constant to turn on connection pooling, so to turn it off you must use some binary arithmetic. Take the value for turning on connection pooling and perform a logical NOT operation on it.
DBPROPVAL_OS_RESOURCEPOOLING has a value of 1, so:
DBPROPVAL_OS_RESOURCEPOOLING = 00000001
NOT DBPROPVAL_OS_RESOURCEPOOLING = 11111110
and this equals -2.
If you’re using an include file that contains these constants, you can use this format:
objConn.Properties("OLE DB Services") = _
DBPROPVAL_OS_ENABLEALL
AND _ (NOT DBPROPVAL_OS_RESOURCEPOOLING)
This says we want all services enabled, apart from resource pooling.
The Properties collection is discussed in more detail in Chapter 8.
For ODBC connections, the ODBC Control Panel applet controls connection pooling.
 This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now. |
Next: Connection State >>
More ASP.NET Articles
More By Apress Publishing
/ 19 
