close db connection without breaking existing code

Question

I have a function that I need to add code to dispose the opened database connection. I am unsure where in the function to add :

   dbCommand.Dispose()
    db = Nothing

without breaking the code accidentally.

Could I get some help in adding these lines of code to the existent code below?

Thank you.

The code is:

Public Shared Function SendAdminEmail() As Boolean
    Dim ipAddress As String = ""
    Try
        ipAddress = HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")
    Catch ex As Exception
    End Try

    If (ipAddress > "") Then
        Dim db As Database = DatabaseFactory.CreateDatabase(Globals.AppSettings("webdb"))
        Dim dbCommand As DbCommand = db.GetStoredProcCommand("VerifySendAdminEmail")
        db.AddInParameter(dbCommand, "@IPAddress", DbType.String, ipAddress)
        Dim ds As DataSet = db.ExecuteDataSet(dbCommand)
        If (Not ds Is Nothing) Then
            If (ds.Tables.Count > 0) Then
                If (ds.Tables(0).Rows.Count > 0) Then
                    If (ds.Tables(0).Rows(0)(0).ToString = "0") Then
                        Return False
                    End If
                End If
            End If
        End If
    End If
    Return True
End Function

LarsTech · Accepted Answer · 2014-09-16 14:53:40Z

2

If an object uses the IDisposable interface, then you can simply wrap the objects in a using block, which will automatically call the Dispose method for you. For a database connection, they will close the connection for you, even when you short-circuit the function with an early return call like in your example.

Using db As Database = DatabaseFactory.CreateDatabase(Globals.AppSettings("webdb"))
  Using dbCommand As DbCommand = db.GetStoredProcCommand("VerifySendAdminEmail")
    db.AddInParameter(dbCommand, "@IPAddress", DbType.String, ipAddress)
    Using ds As DataSet = db.ExecuteDataSet(dbCommand)
      If (Not ds Is Nothing) Then
        If (ds.Tables.Count > 0) Then
          If (ds.Tables(0).Rows.Count > 0) Then
            If (ds.Tables(0).Rows(0)(0).ToString = "0") Then
              Return False
            End If
          End If
        End If
      End If
    End Using
  End Using
End Using

answered Sep 16, 2014 at 14:53

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

watermelon_heart Over a year ago

Thank you for that information. I did not know that. Question, can the db connections be disposed before the last end if shown in the code I pasted? Sort if like: End If '20140916/ ANG dbCommand.Dispose() db = Nothing End If Return True

LarsTech Over a year ago

@itortu If you are done using the object, then you can call dispose on that object. But I don't know why you would avoid using the using block: it's your friend. It's a good habit to get into.

watermelon_heart Over a year ago

I do not know how to get the Using statement to work. I get an error: using' operand of type 'microsoft.practices.enterpriselibrary.data.database' must implement 'system.idisposable'

LarsTech Over a year ago

@itortu Then you can't use the Using block on your Database object. It does not implement the interface. I'm not familiar with that library. It looks old.

Collectives™ on Stack Overflow

close db connection without breaking existing code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related