I have an application, and it's common practice to build our applications with external data layers, that implement an interface, utilizing common data types and interfaces to send data back and forth. One common one is the IDataReader.

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.IDataReader
   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)
End Function

Really, quite a simple solution when I remember to add it in.

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.