In Classic asp, can I store a database connection

Question

Can I store a database connection in the Session object?

Answer 1

As said by CJM, there is no need to store a connection in a Session object : connection pooling is much better.

Answer 2

It is generally not recommended to do so, a connection string in the Application variable, with a nice helper function/class is a much preferred method. Here is some reference. (Dead link removed because it now leads to a phishy site)

Answer 3

In general, I wouldn't store any objects in Application variables (and certainly not in session variables).

When it comes to database connections, it's a definite no-no; besides, there is absolutely no need.

If you are use ADO to communicate with the database, if you use the same connection string (yes, by all means, store this in an Application variable) for all your database connections, 'connection pooling' will be implemented behind the scenes. This means that when you release a connection, it isn't actually destroyed - it is put to one side for the next guys who wants the same connection. So next time you request the same connection, it is pulled 'off the shelf' rather than having to be explicitly created and instantiated - which is a quite a nice efficiency improvement.

Answer 4

I seem to recall doing so will have the effect of single threading your application which would be a bad thing.

Answer 5

From this link http://support.microsoft.com/default.aspx/kb/243543

You shouldnt store database connection in Session.

From what I understand, if you do then subsequent ASP requests for the same user must use the same thread.

Therefore if you have a busy site its likely that 'your' thread will already be being used by someone else, so you will have to wait for it to become available.

Multiply this up by lots more users and you will get everyone waiting for everyone elses thread and a not very responsive site.