weird problem with datareader

  • Thread starter Thread starter Cc
  • Start date Start date
C

Cc

hi,
while I making my own class library using odbc.net I encounter strange
problem
in this class
Public Sub New()

' some code doing openning connection

end sub



Protected Overrides Sub Finalize()

'somecode doing closing connection

MyBase.Finalize()

End Sub





the idea in here is to save trouble from openning and closing connection .
in this class I have some function

Private Function executeSQL_Q(ByVal arg_SQL As String, Optional ByVal
arg_isStoredProc As Boolean = False) As OdbcDataReader

Try

Dim m_oCmd As New OdbcCommand

m_oCmd.Connection = loc_DBconn

If arg_isStoredProc Then

m_oCmd.CommandType = CommandType.StoredProcedure

Else

m_oCmd.CommandType = CommandType.Text ''default

End If

m_oCmd.CommandText = arg_SQL

Return m_oCmd.ExecuteReader

Catch eX As System.Exception

Throw

End Try

End Function

----------------------------------------------------------------------------
--------------------------------------------

Public Function version() As String

Dim retval As String

Try

Dim m_oReader As OdbcDataReader

m_oReader = executeSQL_Q("select VERSION();")

m_oReader.Read()

retval = m_oReader.Item("VERSION()")

m_oReader.Close()

Catch eX As Exception

Throw

End Try

Return retval

End Function

as you see this is only a very simple function. when calling this class
function once and exist program it work fine, the problem only occur
whenever I call this function more than once and exit the program that
reference this class will get unhandle error (I can't get the error even
though I use try and catch exception).

after a while I finaly found where this error occur. to solve this I must
manual close the connection rather than let the finalize procedure do it for
me

Is there a way to let it the close connection at finalize rather than I have
to manualy close without having this error ?
 
hi,
while I making my own class library using odbc.net I encounter strange
problem
in this class
Public Sub New()

' some code doing openning connection

end sub



Protected Overrides Sub Finalize()

'somecode doing closing connection

MyBase.Finalize()

End Sub





the idea in here is to save trouble from openning and closing connection .
in this class I have some function

Private Function executeSQL_Q(ByVal arg_SQL As String, Optional ByVal
arg_isStoredProc As Boolean = False) As OdbcDataReader

Try

Dim m_oCmd As New OdbcCommand

m_oCmd.Connection = loc_DBconn

If arg_isStoredProc Then

m_oCmd.CommandType = CommandType.StoredProcedure

Else

m_oCmd.CommandType = CommandType.Text ''default

End If

m_oCmd.CommandText = arg_SQL

Return m_oCmd.ExecuteReader

Catch eX As System.Exception

Throw

End Try

End Function

----------------------------------------------------------------------------
--------------------------------------------

Public Function version() As String

Dim retval As String

Try

Dim m_oReader As OdbcDataReader

m_oReader = executeSQL_Q("select VERSION();")

m_oReader.Read()

retval = m_oReader.Item("VERSION()")

m_oReader.Close()

Catch eX As Exception

Throw

End Try

Return retval

End Function

as you see this is only a very simple function. when calling this class
function once and exist program it work fine, the problem only occur
whenever I call this function more than once and exit the program that
reference this class will get unhandle error (I can't get the error even
though I use try and catch exception).

after a while I finaly found where this error occur. to solve this I must
manual close the connection rather than let the finalize procedure do it for
me

Is there a way to let it the close connection at finalize rather than I have
to manualy close without having this error ?
Click to expand...

If I'm not mistaken, you cannot be assured when code in the Finalize method
will be called. It may never be called at all. That's why your class
should implement IDisposable. When you are finished with the class, call
its dispose method. In that method you should close the connection.
 
Do *NOT* close/dispose a sqlclient object (connection, for example) in a
finalize method (see link).
Open and close the connection as needed. SQL connection pooling will ensure
you get good performance, so you don't need to keep the connection open the
whole time (the unmanaged connection in the pool will actually stay open
until it times out).

http://support.microsoft.com/default.aspx?scid=kb;en-us;827366

-Rob Teixeira [MVP]
 
Back
Top