Is it necessary to close a local OleDbDataReader before exit function?

[Peter]

Hi, everybody,

The codes below run under VS2003 for a long time. I want to upgrade it to VS2005. VS2005 gives me some varning messages such as "Varibles shouldn't be used before being assigned".

My question is: Is it necessary to close sDa in my code?

'-------------------------------------------------------------------------------
Private Function MethodIDExist(ByVal iMethodID As Integer) As Boolean
Dim oCmd As New OleDbCommand

If ConnMethod.State = ConnectionState.Closed Then ConnMethod.Open()
With oCmd
.Connection = ConnMethod
.CommandType = CommandType.StoredProcedure
.CommandText = "StoredProcedureName"

.Parameters.Add("", OleDbType.Integer)
.Parameters(0).Value = iMethodID
End With

Dim sDa As OleDbDataReader

Try

sDa = oCmd.ExecuteReader()
If sDa.Read() Then
Dim Num As Integer = sDa(0)
If Num > 0 Then
sDa.Close()
Return True
End If
End If
If Not sDa.IsClosed Then sDa.Close()
Return False
Catch exc As Exception
MessageBox.Show(exc.ToString)
'*********************************************************************
If (Not sDa Is Nothing) AndAlso (Not sDa.IsClosed) Then sDa.Close()
'*********************************************************************
Return False
End Try
End Function
'--------------------------------------------------------------------------------

Thanks in advance,

Peter

 

[Stephany Young]

Regardless of whether or not you are exiting from a method, you should
ALWAYS close a DataReader as soon as possible after you are finished using
it.

In my opinion the method is making hard work of something that is very
simple.

If, as the logic shows, you are only interested in the value from the first
column of the first row returned by the strored procedure, then you have no
need to use a DataReader at all. The ExecuteScalar() method of the
OleDbCommand object will do the job, in conjunction with a type conversion
and a test resulting in a boolean result.

Return (CType(oCmd.ExecuteScalar(), Integer) > 0)

Doing away with the DataReader object will stop the warning message from
being generated

Note too that there is an overload of the constructor for the OleDbCommand
object that takes commandtext and connection parameters. This means that you
do not have to set all the properties of the OleDbCommand object seperately.
You do, however, need to set the CommandType property seperately.

In addition, you can add the parameter object and set it's value in a single
statemment.

I note that you are 'opening' the connection object if it is not already
open and leaving it open. If that is your intention then well and good. If
however, you intend to leave it in the state you found it, then you need to
keep track of it's state and close it if you opened it in this method.

The 'improved' method would be something like:

Private Function MethodIDExist(ByVal iMethodID As Integer) As Boolean

' Save the current state of the connection
Dim _constate as ConnectionState = ConnMethod.State

If _constate = ConnectionState.Closed Then ConnMethod.Open()

Dim oCmd As New OleDbCommand("StoredProcedureName", ConnMethod)

oCmd.CommandType = CommandType.StoredProcedure

oCmd.Parameters.Add("", OleDbType.Integer).Value = iMethodID

Try
Return (CType(oCmd.ExecuteScalar(), Integer) > 0)
Catch exc As Exception
MessageBox.Show(exc.ToString)
Return False
Finally
' If the connection was closed to start with then close it here
If _constate = ConnectionState.Closed Then ConnMethod.Close()
End Try

End Function


Hi, everybody,

The codes below run under VS2003 for a long time. I want to upgrade it to
VS2005. VS2005 gives me some varning messages such as "Varibles shouldn't be
used before being assigned".

My question is: Is it necessary to close sDa in my code?

'-------------------------------------------------------------------------------
Private Function MethodIDExist(ByVal iMethodID As Integer) As Boolean
Dim oCmd As New OleDbCommand

If ConnMethod.State = ConnectionState.Closed Then ConnMethod.Open()
With oCmd
.Connection = ConnMethod
.CommandType = CommandType.StoredProcedure
.CommandText = "StoredProcedureName"

.Parameters.Add("", OleDbType.Integer)
.Parameters(0).Value = iMethodID
End With

Dim sDa As OleDbDataReader

Try

sDa = oCmd.ExecuteReader()
If sDa.Read() Then
Dim Num As Integer = sDa(0)
If Num > 0 Then
sDa.Close()
Return True
End If
End If
If Not sDa.IsClosed Then sDa.Close()
Return False
Catch exc As Exception
MessageBox.Show(exc.ToString)
'*********************************************************************
If (Not sDa Is Nothing) AndAlso (Not sDa.IsClosed) Then
sDa.Close()
'*********************************************************************
Return False
End Try
End Function
'--------------------------------------------------------------------------------

Thanks in advance,

Peter

 

[Peter]

Thank you very much, Stephany. I really need to do much to improve my old
codes.

Peter