Closing database connections in 3 tier

[MrNorth]

Im a big fan of 3 tier architecture. As a result of that I perform all my db readings in the datalayer... And when a reading is finished or the reading resulted in error I always close my connection
Is this the best way to do it? Either before I return my data or in the finally statement:

If Not myConnectionOra Is Nothing AndAlso myConnectionOra.State = ConnectionState.Open Then
                myConnectionOra.Close()
            End If

kind regards
Henrik

[Edneeis]

I find it best to use a Try Catch block for data access and the close the connection in the Finally block. The Finally block executes at the end of normal execution or before exception moves on if there is an exception so it is a good fail safe way of making sure you clean up your connection.

Dim cnn As SQLConnection
Try
  cnn=New SQLConnection("myconnectionstring")
  cnn.Open()
  'my data operation
Catch ex As Exception
  'my exception handling
Finally
  If Not cnn Is Nothing AndAlso cnn.State=Open Then
     cnn.Close()
  End If
End Try

PS This is a good idea regardless of the Tiers you use or using the Application Blocks are good as well.