Well, I have this web application that I built - the one running the Oracle database - and we kept getting either a TNS Listener error or Connection Timeout errors. Frankly, I'm of the opinion that they're the same error, it's just that some are getting reported out of the .NET native OracleClient classes (which I utilized) and some are getting reported out of Oracle's implementation of the same (which my co-worker utilized).
Anyways, we kept getting these errors, which resolved down to the connection pool was getting filled up with connections. Bad for business to say the least.
What it ended up being, was we had forgotten a parameter on the Command.ExecuteReader function.
Normally, you don't need to pass in any parameters to this function, as most people use it directly there in the code with the connection and command objects, so they can destroy them all at the same time.
The problem comes in with the fact that since we're passing the DataReader out of the method which generates it, so it can be used elsewhere, we can't close down or destroy the connection and command objects as that would destroy the DataReader as well.
Which is where that parameter comes in.
It's called the CommandBehavior enumeration, and it is used like this:
Function GetValue As Data.IDataReaderReally, quite a simple solution when I remember to add it in.
Dim comm As New OracleClient.OracleConnection
Dim conn As New OracleClient.OracleConnection
' Snip Connection/Command stuff
comm.CommandText = "EXEC MyPackage.StoredProcedure"
GetValue = comm.ExecuteReader(CommandBehavior.CloseConnection)
Anyways, the CommandBehavior enumeration has a couple different options, but as noted, the one we're interested in here, is the CloseConnection option. What this does is destroys the underlying connection and command objects of a DataReader when the DataReader itself is destroyed.
And since I added it into the ExecuteReader calls in my data retrieval functions, the connection pool isn't being filled up, so I'm not getting all those errors.