I'm slowly warming up to the Adapter model and have a question regarding opening and closing connections:

I've got a site that has 5-6 controls (user and/or server) controls per page. For the most part, each control contains some bit of information that must come from the database. Has anyone opened and closed connections in the begin_request and end_request methods of the global.asax?

I realize that performance gains could come from caching the cotnrols but let's put that aside for the moment. If I could go from 5-6 connection requests to one, where would I do it and how? We all know from the ASP days that you want to open and close your connections as close as possible to when you need them, but if you have to hit the DB several times over a request and a request is primarily pulling info from the DB, then why wouldn't begin and end be a good place to put them?

Haven't gone down this route, but looking to see if anyone else has.

Thanks, Matt

When connection pooling is used (and normally this is the case with ADO.NET), creating a full, open connection is 20ms or less. .

You could pass along an adapter object to your controls, and by creating teh adapter, set the KeepConnectionOpen flag to true. This means that actions performed by the controls using the adapter will not open and close the connection but will keep it open.

Otis wrote:

When connection pooling is used (and normally this is the case with ADO.NET), creating a full, open connection is 20ms or less. .

You could pass along an adapter object to your controls, and by creating teh adapter, set the KeepConnectionOpen flag to true. This means that actions performed by the controls using the adapter will not open and close the connection but will keep it open.

Actually, after thinking about it a little, I wouldn't open it on begin_request but might implement some type of Singleton pattern to manage it. If it hasn't been created yet, then create it. If it has already been created and opened, then serve it back to the requestor. Then have some code in a couple different places (end_request, on_error, etc.) to clean up any open connections.

I agree with the 20ms statement but have seen where using one open connection for a recursive call versus opening it each time, can save a fair amount of time. I will experiment with the above approach.

Follow-up question: Do connections time out or clean up on their own?

Connections will timeout if the setting is set for the connection to timeout. Eventually an ADO.NET connection will also be garbage collected. I have seen bad programming practices that lead to random connection failures. This typically comes from when you have a high volume of traffic and developers arent calling Connection.Close. Sometimes, you will have more connections than SQL Server allows (I think 250 or so is the default) and your next connection bombs out. Eventually, GC kicks in and the resources are freed from SQL Server.

As far as using a connection from a singleton object, im not sure I would go there, as you might be asking for threading troubles. However, you could (if its done) use COM+ to maintain an object that is pooled and supports JITA that served your connections. The latest versions of the runtime were supposed to support COM+ and the adapter pattern. I was actually helping with the beta, but got so busy that I havnt had a chance to go back and cover any ground on it.

Frans, did the new COM+ stuff make it into the runtime for the adapter?

Devildog74 wrote:

Connections will timeout if the setting is set for the connection to timeout. Eventually an ADO.NET connection will also be garbage collected. I have seen bad programming practices that lead to random connection failures. This typically comes from when you have a high volume of traffic and developers arent calling Connection.Close. Sometimes, you will have more connections than SQL Server allows (I think 250 or so is the default) and your next connection bombs out. Eventually, GC kicks in and the resources are freed from SQL Server.

Yes it is important to call Close() as that will give the open connection back to the pool. It's not harmful to keep a connection open in a method with a couple of actions, it can be harmful to tuck away an open connection in the session object for example.

Frans, did the new COM+ stuff make it into the runtime for the adapter?

Yes, I altered it in such a way that you could easily write your own IComPlusAdapterContext implementation or override the base class, without hassling with attribute conflicts, I was able to remove the attributes from the base class so no misery with registration anymore