Big Database Connection Problem with ASP .NET

[eboschi]

Hi all, i'm new to .net programming and i have some problems with database connection. I have written two web application. Both of them use ADODB Connection to Sql Server 2000 database. The problem is in second one.

The first application is used for order management. I have defined in Main.aspx page a variable called localSqlConnection. Every time i enter this page (only one time per session) i create connection to local database and then i save this variable in Session object. Whenever i need connection to database in other project aspx page i retrieve connection from db, casting it to SqlConnection class and all works correctly. This first application is used by a little group of users

The second application is used by a greater number of users. It is used for request management. Before starting coding i suspect that connection saved in session is'nt a good solution for large numbers so I modify my code removing the use of Session Obect. In each aspx page i put a method that create/open connection and a method that close connection to database. I call this two method every time Page_Load and Page_Unload event was raised. Each method of Page Class uses my local page variable. It seems to works but something was wrong because a lot of time i receive database connection error !!! When it appens i launch the iisreset command on web server and all restart working, but this is not an acceptable solution.

So, what i can try ? I can't modify all my code for open and close connection every time i have to make a database operation. It's a lot of code.
Is there any solutions ?
Thanks in advance
Emanuele

[kenobewan]

I believe that this is an application design problem. May help to use your connections from a class or config file. You may want to look at data caching if performance is an issue. HTH.

[eboschi]

I believe that this is an application design problem. May help to use your connections from a class or config file. You may want to look at data caching if performance is an issue. HTH.

It's seems not to be a performance problem. I have created specific index on my target table. Of course i will look about a new class dedicated to create connection and maintain connection.

The problem, i think, is that connection will be instantiated but, whenever we have delay in result retrieving, all the other connection request are queued and my asp server will not serve them. And when this connection queue became too long no new connection was released. I just put a timeout on my query but it does'nt work anyway.

So what is the right solution ? create and open connection before query and close it immediately after result became availlable ?

Thanks
emanuele

[kenobewan]

I was thinking that the large number of connections that end up open was the problem. Is this one of those course assignments that fail to reflect the real world? By all means store the connection variable in a session, but only open it when it is executed and then close immediately. Good luck!