CODEWITHSUNDEEP
asp.netoracle-databaseconnection-pooling
chris
chris

Reputation: 37490

OracleClient command - best practices around USING and disposing of command

According to the MSDN documentation for the OracleClient.OracleCommand:

Public Sub ReadMyData(ByVal connectionString As String)
    Dim queryString As String = "SELECT EmpNo, DeptNo FROM Scott.Emp"
    Using connection As New OracleConnection(connectionString)
        Dim command As New OracleCommand(queryString, connection)
        connection.Open()
        Dim reader As OracleDataReader = command.ExecuteReader()
        Try
            While reader.Read()
                Console.WriteLine(reader.GetInt32(0) & ", " _
                   & reader.GetInt32(1))
            End While
        Finally
            ' always call Close when done reading.
            reader.Close()
        End Try
    End Using
End Sub

The OracleCommand is not wrapped in a Using block.

Question: Should it be? OracleCommand inherits from DbCommand, which implements IDisposable.

I'm looking at code that does the following, and we're having problems with implicit cursors not getting closed:

     Dim cmd As OracleCommand = createCommand("some sql")
     ds = GetDataSet(cmd)
     cmd.Dispose()
     cmd = Nothing

GetDataSet looks like:

  Dim da As New OracleDataAdapter()
  Dim ds As New DataSet()

  Using conn As OracleConnection = GetOpenConnection()
     cmd.Connection = conn
     da.SelectCommand = cmd
     da.Fill(ds, 0)
     cmd.Connection = Nothing
  End Using
  da.Dispose()
  da = Nothing
  Return ds

Is this going to leak resources?

Upvotes: 0

Views: 943

Answers (2)

DancesWithBamboo
DancesWithBamboo

Reputation: 4156

How are you determining that the implicit cursors are open and that that is a problem? The reason I ask is that calling the "Close" method does not actually close the connection. It just lets the pool know that it is no longer being used and can be reallocated somewhere else. This is done for performance reasons because actually physically opening a connection is expensive. The same connection will be reused many times and may well remain open even after calling "Close" so if you are using some Oracle tools to peer into the connections it may be leading you astray.

I have never seen any reason to explicitly dispose of the command object. It is just a collection of parameters and other info.

If you use a using block as you have, there is absolutely no need to call "Close" or "Dispose" explicitly in code. That is the whole point of the using block. It protects you from forgetting to call Close since that is exactly what it does for you.

Upvotes: 1

Rad
Rad

Reputation: 8389

First off, I think you should explicitly close your connections in your code, because depending on the provider there is some additional work that gets done when you call the Close method

Upvotes: 1

Related Questions