OracleCommand ExecuteNonQuery with Async

Question

Public Function ExecuteNonQuery(ByVal cmd As OracleCommand) As Integer
    ' no of affected rows ...
    Dim affectedRows As Integer = -1
    ' execute the command ...
    Try
        ' open connection ...
        cmd.Connection.Open()
    Catch ex As Exception
        MsgBox("Unable to establish a connection to database." & ex.ToString, MsgBoxStyle.Critical)
        Exit Function
    End Try
    Try
        ' execute command ...
        'affectedRows = cmd.ExecuteNonQuery()
        affectedRows = cmd.ExecuteNonQuery()

    Catch ex As OracleClient.OracleException
        If CInt(ex.Code) = CInt(1) Then
            Return -2
        End If

    Catch ex As Exception
        ' rethrow error ...
        'Dim cfrErr As New CFR_Errors("Data_Access_Class","ExecuteNonQuery", ex)
        Throw New Exception(ex.InnerException.ToString, ex)
    Finally
        cmd.Connection.Close()
    End Try
    Return affectedRows
End Function

How can I use Oracle.ManagedDataAccess to execute my query async? We are changing our database to AWS and it may introduce latency,

Nkosi · Accepted Answer · 2020-02-12 16:57:17Z

Simple enough to refactor to using Async and Await since DbConnection and DbCommand should have the asynchronous API available.

Public Async Function ExecuteNonQueryAsync(ByVal cmd As DbCommand) As Task(Of Integer)
    ' no of affected rows ...
    Dim affectedRows As Integer = -1
    ' execute the command ...
    Try
        ' open connection ...
        Await cmd.Connection.OpenAsync()
    Catch ex As Exception
        MsgBox("Unable to establish a connection to CFR database." & ex.ToString, MsgBoxStyle.Critical)
        Exit Function
    End Try
    Try
        ' execute command ...
        affectedRows = Await cmd.ExecuteNonQueryAsync()

    Catch ex As OracleClient.OracleException
        If CInt(ex.Code) = CInt(1) Then
            Return -2
        End If

    Catch ex As Exception
        ' rethrow error ...
        Throw New Exception(ex.InnerException.ToString, ex)
    Finally
        cmd.Connection.Close()
    End Try
    Return affectedRows
End Function

Reference Asynchronous programming with Async and Await (Visual Basic)

Collectives™ on Stack Overflow

OracleCommand ExecuteNonQuery with Async

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related