Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

datareader object class

hello,

  I was wondering if anyone can help me on this. I have a class that creates and returns a datareader object. If I close the db connection I can do while dbreader.read because it says the object is closed? How can I create a second method in the class to close it. It looks like this:

Public Class dbDataObj : Inherits dbConn

    Public Function GetDataReader(ByVal sqlText As String) As SqlDataReader
        Dim dr As SqlDataReader
        Dim obj As New dbConn()
        Dim sqlConn As SqlConnection = New SqlConnection(obj.getConnStr.ToString)
        Dim sqlCmd As SqlCommand = New SqlCommand(sqlText, sqlConn)
        Try
            sqlCmd.Connection.Open()
            dr = sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        Catch exception As SqlClient.SqlException
            Console.WriteLine("Unable to open database!")
            Console.WriteLine(exception)
        Finally
            'sqlCmd.Connection.Close()
            ' if this line is uncommented I get a runtime error
            ' please help
        End Try

        Return dr
    End Function

bryan murtha
Wednesday, November 05, 2003

The DataReader requires the Connection stay open. So don't close it! :-) Instead, when you call ExecuteReader() pass it a CommandBehavior.CloseConnection argument and call Close() on the DataReader instead - this will then will close the Connection.

Alternatively it sounds like you want to grab the data quickly and close the connection. In this case, have your method return a DataTable instead and use a SqlDataAdapter's Fill() method to fill a new DataTable. This data will be "disconnected".

Duncan Smart
Wednesday, November 05, 2003

*  Recent Topics

*  Fog Creek Home