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.

No comments: