Return sqldatareader problem

[Badgers]

Hi,

I have a function which returns an sqldatreader, I know within the function it works, but when it returns to the calling function it says the reader is closed?

CALLING CODE

Public Sub PopulateSectors()

Dim odataaccess As CustomControls.DataOperations.DataAccess
Dim oDataReader As SqlDataReader

Try
oDataAccess = New CustomControls.DataOperations.DataAccess
oDataReader = oDataAccess.PopulateSectors

lstSectors.DataSource = oDataReader
lstSectors.DataBind()

Catch ex As Exception
Throw New ApplicationException(ex.Message, ex)
Finally
odataaccess = Nothing
End Try
End Sub



CALLED CODE

Public Function PopulateSectors() As SqlDataReader

Dim oConnection As SqlConnection
Dim oCommmand As SqlCommand
Dim oDr As SqlDataReader

Try
'Set connection object
oConnection = New SqlConnection(ConfigurationManager.AppSettings("sqlCommunications"))

oConnection.Open()

oCommmand = New SqlCommand
oCommmand.Connection = oConnection
oCommmand.CommandType = CommandType.Text
oCommmand.CommandText = "select CompanyName, ContactID from [view_Merchants] order by CompanyName"


'oDr = oCommmand.ExecuteReader
oDr = oCommmand.ExecuteReader(CommandBehavior.CloseConnection)

' oDr.Read()

Return oDr

Catch exsql As SqlException
Throw New ApplicationException(exsql.Message, exsql)

Catch ex As Exception
Throw New ApplicationException(ex.Message, ex)

Finally
If Not oDr Is Nothing Then oDr.Close() : oDr = Nothing
oCommmand = Nothing
If oConnection.State = ConnectionState.Open Then oConnection.Close() : oConnection = Nothing
End Try

End Function

Thanks

 

[ca8msm]

oDr = oCommmand.ExecuteReader(CommandBehavior.CloseConnection)

You are telling the object to close the connection so I imagine it will be closed once the function exits.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[IT4EVR]

This is what I think is happening...

The CommandBehavior maybe closing the connection but it doesn't always do that.

Actually I think the problem is in the Finally statement of the PopulateSectors method as well. You should NOT close the connection in the method being called. Basically you are closing the connection before the DataReader object is passed to the caller. You are also setting the Connection to nothing if it is open, thereby wiping it out from memory.

What I do is create a separate public method for opening and closing connections within the data access class. That way I can reuse it in various places.