Invalid operation. Connection is closed.

Question

Hello I have 3tier web application which is being used by different departments and installed on different server. Everyday i get error message Invalid operation. Connection is closed. We tried to alter the code and we used the keywords using when calling a procedure despite this the error still continue to occur. Can someone please HELP to solve out this issue???? I am using Oracle Client 9.2 and ASP.Net 2.0 C# language the web application is hosted on a Windows Server 2003 machine On a server where only 3 users may access the application at a time the error did not occur at all while on the other server around 15 users can be accessing the web application simultaneously and hence using only one oracle session. The error keep occuring nearly every minutes. Users access the web application on a thin client and then all others login on a remote server and then access the application found on another windows server. Please find a piece of code below: public static DocInfo Doc( string docid) { DB db = null ; DocInfo docObject = new DocInfo (); try { using (db = DBFactory .GetDB()) { using ( OracleDataReader dr = db.Query( DocProcessingDAC .GetFileForAdmin new NameValuePair ( p_doc_id  doc_id) )) { if (dr != null ) { while (dr.Read()) { scanDocInfo = new ScanDocInfo (dr); scanDocInfo.SpecialInstructionInfoCol = getDocInstructions(db scanDocInfo.DocId); } } } } return docObject; } catch ( Exception e) { ExceptionHandler .Handle(e web application  getting file ); return docObject; } }

Muhammad Gukhool · Answer

Hi Bruce Thank for your reponse! The db connection is getting open and closed from the DB class. when passing the connection details to the class constructor (db = DBFactory .GetDB()) the connection is getting opened and once the using clause comes to an end the connection is closed from the DB class. Please see code below: public class DB :System. IDisposable { /// /// Underlying connection to Oracle /// protected OracleConnection con; /// /// Create a DB object that connects to the /// database specified in the connection string. /// /// Connection String public DB( string conStr): base () { con = new OracleConnection (conStr); con.Open(); } public void Close() { con.Close(); //con.Dispose(); } Before using the using clause we were opening and closing the db connection from the method in the previous post and it was closed after the calling of the procedure has been done kindly revert back please

Bruce Roeser · Answer

Muhammed We ran into a similar situation some time ago. Rather than depending on the using{} construct I suggest that you open and manually close your db connection. I.E. db = FactoryDB.GetDB(); // Do stuff db.Close(); db.Dispose(); // If necessary We discovered with a large client that placing database open/close operations inside using{} had too long a latency and caused deadlocks. This is why you're not seeing the problem on your low-volume server. In the high-volume case the garbage collector is not getting around to closing your SQL connections as fast as you're opening them so you're running out of connections. You can temporarily fix this by adjusting the SQL database (or Oracle in your case) to handle more connections but that is only a Band-Aid on the problem. Manually closing your connections when you're done with them will ensure they don't just sit open. Automatic garbage collection is a fine mechanism but there is too much dependence on it. It is still good coding practice to clean up your own mess particularly when dealing with DB connections file handles and anything considered external to your program. HTH -bwr-