Closing DbConnection Created by Function

Question

Perhaps this is the complete wrong way of doing things, and if so could you perhaps point me in the correct (elegant) way. :)

I have a module in my vb.net project. The module deals with db connections. The idea is for other modules to make use of this module when connections need to be created.

For each database type I have function that opens the db connection. As an example I have this function that opens an oracle connection.

Friend Function OracleConnection(ByVal HostAddress As String, ByVal PortNumber As String, ByVal DBName As String, ByVal UserId As String, ByVal Password As String) As OracleConnection
    Try
        OracleConnection = New OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" & HostAddress & ")(PORT=" & PortNumber & "))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" & DBName & ")(FAILOVER_MODE=(TYPE=select)(METHOD=BASIC)(RETRIES=180)(DELAY=5))));User Id=" & UserId & ";Password=" & Password & ";")
        OracleConnection.Open()
        OracleConnection = OracleConnection
    Catch ex As OracleException
        MsgBox(ex.Message, MsgBoxStyle.Critical)
        OracleConnection = Nothing
    End Try
    Return OracleConnection
End Function

Creating the connetion seems to work fine. The problem I have is that I am now not sure how to close the connection that got created by this function.

score 2 · Answer 1 · edited May 23 '17 at 12:05

Option 1

You should always close OracleConnection objects by calling Close or Dispose, or by using the OracleConnection object within a Using statement.

Otherwise, the garbage collection might not free them immediately. Such delays can cause errors if the maximum number of connections is reached while a number of connections are waiting to be deleted by the garbage collector.

By contrast, closing the connections by calling Close uses native resources more efficiently, enhancing scalability and improving overall application performance. To ensure that connections are always closed, open the connection inside of a Using block.

Public Sub InsertRow(ByVal connectionString As String)
    Dim queryString As String = "INSERT INTO Dept (DeptNo, Dname, Loc) values (50, 'TECHNOLOGY', 'DENVER')" 

    Using connection As New OracleConnection(connectionString)
        Dim command As New OracleCommand(queryString)
        command.Connection = connection
        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try 
    End Using 
End Sub

For more information, visit MSDN.

Option 2

Take a look at Oracle's recommended best practices:

http://www.oracle.com/technetwork/topics/dotnet/ow2011-bp-performance-deploy-dotnet-518050.pdf

You automatically get a connection pool when you create an OracleConnection. For most middle tier applications you will want to take advantage of that. You will also want to tune your pool for a realistic workload by turning on Performance Counters in the registry.

Please see the ODP.NET online help for details on connection pooling. Pool settings are added to the connection string.

Another issue people run into a lot with OracleConnections is that the garbage collector does not realize how truly resource intensive they are and does not clean them up promptly. This is compounded by the fact that ODP.NET is not fully managed and so some resources are hidden from the garbage collector.

Hence the best practice is to Close() AND Dispose() all Oracle ODP.NET objects (including OracleConnection) to force them to be cleaned up.

Credits go to Christian Shay this answer.

Closing DbConnection Created by Function

1 Answers1

Option 1

Option 2