Wednesday, January 15, 2014

DataReader Problems Boxing and Unboxing

After I researched the problems and implemented the solutions in this post http://brookssoftwaredev.blogspot.com/2014/01/net-datareader-not-closing-connection.html I found that I was still getting timeouts while trying to obtain a connection from the pool.

I researched more and found that doing normal conversion like

int.Parse(reader["FieldName"]);

caused boxing and unboxing of the values.  I was doing this on several values for many records.

I did some research and found that the boxing and unboxing causes the garbage collector to bog down.  I found that the DataReader has special methods to avoid the boxing and unboxing.  These methods are things like GetCharGetDouble, GetDateTime, and GetInt32.  From what I read these stop the boxing and unboxing and prevent the garbage collector from thrashing.

Once I implemented those methods my timeouts on the connection pool went away.  It appears as though the garbage collector couldn't clean up the connections, since it was busy with all of the rogue objected from the DataReader boxing and unboxing.

I realized that using a DataReader left the connection open and there needed to be precautions taken, but there are several other things that you must deal with when using a DataReader, so use caution.

Saturday, January 11, 2014

.Net DataReader Not Closing Connection After Closed

As I research using the .Net DataReader I kept seeing information online that said things like "When the command is executed, the associated Connection object is closed when the associated DataReader object is closed."

There had been concerns that the data reader would tie up the connection too long.  There was also concern that we were closing the connection explicitly, especially since the creation of the reader was abstracted into another class.  The class using the reader would not have access to the command or connection that created it.

In various different places I have worked we have used this and were confident that the DataReader would clean itself and its connection up, based upon what we had read.

We did a small test page recently and it turns out that by default it leaves the connection open.

We opened a connection and created a command tied to that connection and then used that command to create a DataReader.  We did command.ExecuteReader() to create the reader.

As we researched further we found that ExecuteReader has an optional parameter of a CommandBehavior enumeration.  If you pass CommandBehavior.CloseConnection then it closes the connection when the reader closes.