DAAB: Closing DB connections in XmlReader vs. DbDataReader

May 14, 2007 at 6:49 PM
I read in the documentation that closing/disposing the DbDataReader object indicate to the underlying ADO.NET call to automatically close the db connection. So I can use the using statement like below.

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("Select Name, Address From Customers");
 
// This will close the db connection
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
  // Process results
} 
But in the case of XmlReader, the connection is not automatically closed when the XmlReader object is closed! You will have to explicitly close the connection

// This will not close the db connection
using (XmlReader xmlReader = db.ExecuteXmlReader(dbCommand))
{
  // Process results
} 
Does anyone know the reason why?


May 17, 2007 at 3:25 PM
It mentions why in the documentation:

"The connection must remain open while you read data from the XmlReader. The ExecuteXmlReader method of the SqlCommand object currently does not support the CommandBehavior.CloseConnection enumerated value, so you must explicitly close the connection when you finish with the reader but only if there is no active transaction."

SqlCommand.ExecuteXmlReader does not support CommandBehavior.CloseConnection which would normall close the connection when you close the reader.

Regards,

Dave

__________________________________

David Hayden
Microsoft MVP C#