sql-servervb.netusing-statementidbcommandidbconnection

How can I prevent this code from disposing objects multiple times?


When I run a Code Analysis against the following code:

Protected Function GetOrderEntry() As IList(Of OE)
    Dim results As IList(Of OE) = New List(Of OE)()
    Using connection As IDbConnection = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection()
        connection.ConnectionString = ConfigurationManager.AppSettings("OrderEnterConnection")
        Using command As IDbCommand = connection.CreateCommand()
            command.CommandTimeout = 120
            command.CommandText = "Exec up_ViewOrderDetail_2012_Order '" & MemberNo1.Value & "','" & CustNo.Value & "','DEFAULT','" & OrderNo.Value & "','" & DeliveryDate.SelectedDate & "','','','','1'"
            connection.Open()
            Try
                Dim reader As IDataReader = command.ExecuteReader()
                results = PopulateGrid(reader)
            Catch ex As SqlException
                results.Clear()
                connection.Close()
            End Try
        End Using
    End Using
    Return results
End Function

...I get,

"CA2202 Do not dispose objects multiple times Object 'connection' can be disposed more than once in method 'OrderConfirm.GetOrderEntry()'. To avoid generating a System.ObjectDisposedException you should not call Dispose more than one time on an object"

The cursor is on the final "End Using" line; howis this seen as a double-disposal of objects? Wouldn't both of the "Using" blocks need to be terminated in this way?


Solution

  • I tried removing connection.createcommand And connection.Close(). And it worked fine

    Protected Function GetOrderEntry() As IList(Of OE)
        Dim results As IList(Of OE) = New List(Of OE)()
        Using connection As IDbConnection = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection()
            connection.ConnectionString = ConfigurationManager.AppSettings("OrderEnterConnection")
            Using command As New IDbCommand 
                Command.connection = connection
                command.CommandTimeout = 120
                command.CommandText = "Exec up_ViewOrderDetail_2012_Order '" & MemberNo1.Value & "','" & CustNo.Value & "','DEFAULT','" & OrderNo.Value & "','" & DeliveryDate.SelectedDate & "','','','','1'"
                connection.Open()
                Try
                    Dim reader As IDataReader = command.ExecuteReader()
                    results = PopulateGrid(reader)
                   Reader.close()
                Catch ex As SqlException
                    results.Clear()
    
                End Try
            End Using
        End Using
        Return results
    End Function