No method in the DataReader object closes its underlying connection (310369)

SYMPTOMS

The DataReader object does not include a property to retrieve its underlying connection. Although you can close DataReader and then close the OleDbConnection or SqlConnection object on which DataReader was opened, DataReader does not have a built-in connection.

Thus, if you retrieve a DataReader from a component method, you cannot close the underlying connection. That is, if you retrieve a DataReader object from a component, the DataReader stays connected, and there is no way to disconnect it.

RESOLUTION

To resolve this problem, configure the ExecuteReader method of a SqlCommand or OleDbCommand object so that the DataReader connection is automatically closed when you close the DataReader. If you pass the System.Data.CommandBehavior.CloseConnection flag to the ExecuteReader method, the DataReader connection is closed when the DataReader is closed.

STATUS

This behavior is by design.

For security purposes, a component may not want to expose a connection to unknown code. A malicious user could build an application that uses the connection to compromise data on the server. Thus, the DataReader does not have a property to expose or manipulate its underlying connection.

This contrasts with the ADODB.Recordset object, which does include an ActiveConnection property.

MORE INFORMATION

To pass the System.Data.CommandBehavior.CloseConnection flag to the ExecuteReader method, use the following code when you open the DataReader:

MyDataReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

REFERENCES

For more information, refer to the following topics in the Microsoft .NET Software Development Kit (SDK) documentation or the Microsoft Visual Studio .NET Online Help documentation:

OleDbCommand.ExecuteReader Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbCommandClassExecuteReaderTopic.asp

CommandBehavior Enumeration
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommandBehaviorClassTopic.asp