Defeasible asynchronous Oracle database operations

Introduction

This code helps perform long-term Microsoft Server or Oracle database query in the background.

Background

Let's imagine that you have an ASP.NET application and it is necessary to perform long-term Microsoft Server or Oracle database query in the background. Or you may have a WinForms application with a huge database update, but at the same time, you want to unlock the user interface. If you've got a long-term database operation, I've got the remedy for it: here is a threading as antibiotic and defeasible database operations as analgesic.

Here is an antibiotic: Backgroundworker: http://msdn.microsoft.com/en-us/library/ms171728(VS.90).aspx

Here is an analgesic: OracleCommand.Cancel(): http://docs.oracle.com/cd/B28359_01/win.111/b28375/OracleCommandClass.htm#i996876

Update the Data in a Separate Thread

Let's mix them together:

I will use the DatabaseHelper approach, but without static methods. The reason why - you'll better be careful with threads and account them. With the code below, you may have exactly one instance of wrapper to run exactly one SQL command in the background, and kill the thread when it is finished.

Public Class asyncDBUtils

    Private con As OracleConnection
    Private _TransactionObject As Oracle.DataAccess.Client.OracleTransaction
    Public Sub New()
        con = DBUtils.get_New_OracleConnection()
        _TransactionObject = con.BeginTransaction(IsolationLevel.ReadCommitted)
        Console.WriteLine("Start transaction")
    End Sub

#Region "execute_nonquery"
    Private cmd2 As OracleCommand
    Private WithEvents execute_nonquery_Worker2 _
    As BackgroundWorker 'worker for execute_nonquery
    Private execute_nonquery_callBackFunc2 As Func(Of Integer, Object)


    ''' <summary>
    ''' stores the callback function
    ''' triggers the BackgroundWorker in a separate thread to exec 
    ''' the SQL clause in the background
    ''' </summary>
    ''' <param name="sql">sql Update, Insert or Delete clause</param>
    ''' <param name="func2">callback function should accept one Integer - 
    ''' background operation status</param>
    ''' <remarks></remarks>
    Public Sub execute_nonquery_async(ByVal sql As String, _
        ByRef func2 As Func(Of Integer, Object))
        execute_nonquery_callBackFunc2 = func2
        execute_nonquery_Worker2 = New BackgroundWorker
        execute_nonquery_Worker2.WorkerReportsProgress = False
        execute_nonquery_Worker2.WorkerSupportsCancellation = True
        execute_nonquery_Worker2.RunWorkerAsync(sql)

        ' Continue in the main thread.
        'MsgBox("release the main thread.")
    End Sub

    ''' <summary>
    ''' cancel BackgroundWorker
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub execute_nonquery_async_cancel()
        execute_nonquery_Worker2_Cancel()
    End Sub

    ''' <summary>
    ''' this method does the work you want done in the background.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="evArgs"></param>
    ''' <remarks>triggered by Me.execute_nonquery_Worker2.RunWorkerAsync(sql)
    ''' the work is done in a separate thread, so DO NOT update interface here</remarks>
    Private Sub execute_nonquery_Worker2_start( _
        ByVal sender As Object, _
        ByVal evArgs As DoWorkEventArgs) _
        Handles execute_nonquery_Worker2.DoWork
        Dim worker As BackgroundWorker = CType(sender, BackgroundWorker)
        Dim sql As String = DirectCast(evArgs.Argument, System.String)
        cmd2 = New OracleCommand(sql, con)
        cmd2.CommandType = CommandType.Text
        Try
            Console.WriteLine("Execute: execute_nonquery")
            'Dim stopwatch As New System.Diagnostics.Stopwatch()
            'stopwatch.Start()
            Dim result As Integer = cmd2.ExecuteNonQuery()
            'stopwatch.[Stop]()
            Console.WriteLine("execute_nonquery Execute Done.")
            evArgs.Result = result
            evArgs.Cancel = False
            Console.WriteLine("Commit execute_nonquery results.")
            _TransactionObject.Commit()
        Catch e As Exception
            Console.WriteLine("The command execute_nonquery has been cancelled.", _
        e.Message)
            evArgs.Result = -1
            evArgs.Cancel = True
            Console.WriteLine("Rollback execute_nonquery results.")
            _TransactionObject.Rollback()
        End Try
    End Sub

    ''' <summary>
    ''' This method cancels the database operation and the backgroundworker thread
    ''' </summary>
    ''' <remarks>it is thread safe for execute_nonquery_Worker2 and
    ''' cmd.Cancel()</remarks>
    Private Sub execute_nonquery_Worker2_Cancel()
        If execute_nonquery_Worker2.WorkerSupportsCancellation = True Then
            ' Cancel the asynchronous operation.
            Try
                ' Stop the database operation
                execute_nonquery_Worker2.CancelAsync()
                cmd2.Cancel()
                Console.WriteLine("execute_nonquery Cancel done.")
            Catch e As Exception
                Console.WriteLine(e.ToString)
            End Try
        End If
    End Sub

    ''' <summary>
    ''' This method is called by BackgroundWorker on completion the background work,
    ''' calls the callback function and passes the result
    ''' on cancel or error - returns - 1,
    ''' on done - returns the status of the background sql operation
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e">e.Result is passed as evArgs.Result 
    ''' in the execute_nonquery_Worker2_start</param>
    ''' <remarks>The method is executed in the main thread, 
    ''' so it is save to call </remarks>
    Private Sub execute_nonquery_Worker2_RunWorkerCompleted_
    (ByVal sender As System.Object, _
    ByVal e As RunWorkerCompletedEventArgs) Handles _
    execute_nonquery_Worker2.RunWorkerCompleted
        If e.Cancelled = True Then
            ' Stop the database operation
            cmd2.Cancel()
            'update the interface  - message that the background worker has been canceled
            'like resultLabel.Text = "Canceled!"
            ' e.Result is not assigned on Cancel
            execute_nonquery_callBackFunc2.Invoke(-1)
        ElseIf e.Error IsNot Nothing Then
            ' Stop the database operation
            cmd2.Cancel()
            'update the interface  - message that the background worker 
            'thrown an exception
            'like resultLabel.Text = "Error: " & e.Error.Message
            ' e.Result is not assigned on Cancel
            execute_nonquery_callBackFunc2.Invoke(-1)
            Throw New Exception(e.Error.Message)
        Else
            'update the interface  - message that the background worker done the job
            'resultLabel.Text = "Done!"
            '!!!! update any data, any components, any interface here
            execute_nonquery_callBackFunc2.Invoke(DirectCast(e.Result, Integer))
        End If
    End Sub

#End Region
End Class

...

Your code should send the address of the procedure to callback and SQL clause, trigger the BackgroundWorker, and release the main thread. The BackgroundWorker will execute the long-term database update and will trigger the callback to update the interface.

You may call this from your main thread in this way:

Dim async As async_nonquery

Sub run()
    If async IsNot Nothing Then
        MsgBox("Already running!")
        Exit Sub
    End If
    async = New async_nonquery()
    'this sample produces long server execution
    Dim s As String = "update test_table set MESSAGE_TEXT = 'message text message _
    text message text message text message text message text message text message _
    text message text message text' where MESSAGE_TEXT like 'message%' "
    async.execute(s, AddressOf update_Interface_in_MAIN_thread)
End Sub

'Public Delegate Function update_dataset_callback(ByVal ds As System.Data.DataSet) 
'As Object

Private Function update_Interface_in_MAIN_thread(ByVal status As Integer) As Object

    Dim frm As New Form()
    frm.Width = 302
    frm.Height = 60

    Dim lbl As New Label()
    lbl.Location = New System.Drawing.Point(1, 1)
    lbl.Name = "label1"
    lbl.Size = New System.Drawing.Size(300, 32)

    frm.Controls.Add(lbl)

    frm.Show()
    lbl.Text = "Execution status" + status.ToString()
    async = Nothing
    Return Nothing

End Function

Public Sub Cancel()
    If async IsNot Nothing Then
        async.cancel()
    End If
    async = Nothing
End Sub

Fetch the Data in a Separate Thread

What if you want to get the dataset asynchronously with ODP.NET? Let me show how it can be done.

Your code leaves the procedure to callback when the Dataset is ready, triggers the BackgroundWorker, and releases the main thread. The BackgroundWorker fetches the dataset and runs the callback to update the interface. Here is the code:

''' <summary>
''' The class to get dataset asynchronously
''' Does not support transactions from MAIN thread because it opens 
''' the new connection for every new command
''' Should be instantiated
''' Updates the interface trough the call-back function
''' </summary>
''' <remarks></remarks>
Public Class async_get_dataset

    Private con As OracleConnection
    Private _TransactionObject As Oracle.DataAccess.Client.OracleTransaction
    Public Sub New()
        con = DBUtils.get_New_OracleConnection()
        _TransactionObject = con.BeginTransaction(IsolationLevel.ReadCommitted)
        Console.WriteLine("Start transaction")
    End Sub

#Region "get_dataset"
    Private WithEvents get_dataset_Worker1 _
    As BackgroundWorker 'BackgroundWorker for get_dataset
    Private get_dataset_callBackFunc1 As Func_
    (Of System.Data.DataSet, Object) 'callback function
    Private cmd1 As OracleCommand

    ''' <summary>
    ''' stores the callback function
    ''' triggers the BackgroundWorker in a separate thread to 
    '''exec the SQL clause in the background
    ''' </summary>
    ''' <param name="sql">sql Select clause</param>
    ''' <param name="func1">callback function should accept 
    ''' one System.Data.DataSet - background operation result</param>
    ''' <remarks></remarks>
    Public Sub execute(ByVal sql As String, ByRef func1 As Func_
    (Of System.Data.DataSet, Object))
        get_dataset_callBackFunc1 = func1
        get_dataset_Worker1 = New BackgroundWorker
        get_dataset_Worker1.WorkerReportsProgress = True
        get_dataset_Worker1.WorkerSupportsCancellation = True
        Me.get_dataset_Worker1.RunWorkerAsync(sql)

        ' Continue in the main thread.
        'MsgBox("release the main thread.")
    End Sub

    ''' <summary>
    ''' cancel BackgroundWorker
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub cancel()
        get_dataset_Worker1_Cancel()
    End Sub

    ''' <summary>
    ''' this method does the work you want done in the background.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="evArgs"></param>
    ''' <remarks>triggered by get_dataset_Worker1.RunWorkerAsync(sql)
    ''' the work is done in a separate thread, so DO NOT update interface here</remarks>
    Private Sub get_dataset_Worker1_start( _
        ByVal sender As Object, _
        ByVal evArgs As DoWorkEventArgs) _
        Handles get_dataset_Worker1.DoWork
        Dim worker As BackgroundWorker = CType(sender, BackgroundWorker)
        Dim sql As String = DirectCast(evArgs.Argument, System.String)
        cmd1 = New OracleCommand(sql, con)

        Dim da As OracleDataAdapter
        Dim cb As OracleCommandBuilder
        Dim ds As DataSet
        cmd1.CommandType = CommandType.Text
        Try
            da = New OracleDataAdapter(cmd1)
            cb = New OracleCommandBuilder(da)
            ds = New DataSet()

            Console.WriteLine("Execute: get_dataset")
            'Dim stopwatch As New System.Diagnostics.Stopwatch()
            'stopwatch.Start()
            da.Fill(ds)
            'stopwatch.[Stop]()
            Console.WriteLine("Execute: get_dataset -Done.")
            evArgs.Result = ds
            evArgs.Cancel = False
            Console.WriteLine("Commit get_dataset results.")
            _TransactionObject.Commit()

        Catch e As Exception
            Console.WriteLine("The command get_dataset has been cancelled.", e.Message)
            ds = New System.Data.DataSet() ' empty dataset
            evArgs.Result = ds
            evArgs.Cancel = True
            Console.WriteLine("Rollback get_dataset results.")
            _TransactionObject.Rollback()
        End Try

        'worker.ReportProgress(i * 10)

        ' the preferred method is to do it from the Completed
        ' event handler which runs in the same thread as the one
        ' that created the control.
    End Sub

    ''' <summary>
    ''' This method cancels the database operation and the backgroundworker thread
    ''' </summary>
    ''' <remarks>it is thread safe for get_dataset_Worker1 and
    ''' cmd.Cancel()</remarks>
    Private Sub get_dataset_Worker1_Cancel()
        If get_dataset_Worker1.WorkerSupportsCancellation = True Then
            ' Cancel the asynchronous operation.
            Try
                ' Stop the database operation
                get_dataset_Worker1.CancelAsync()
                cmd1.Cancel()
                Console.WriteLine("Cancel: get_dataset done.")

            Catch e As Exception
                Console.WriteLine(e.ToString)
            End Try
        End If
    End Sub

    ' This event handler may update the progress.
    Private Sub get_dataset_Worker1_ProgressChanged(ByVal sender As System.Object, _
    ByVal e As ProgressChangedEventArgs) Handles get_dataset_Worker1.ProgressChanged
        'update user interface - message the user on the progress -
        'it is safe here in this system
        'resultLabel.Text = (e.ProgressPercentage.ToString() + "%")
    End Sub

    ''' <summary>
    ''' This method is called by BackgroundWorker on completion the background work,
    ''' it receives the result of execution in e.Result from get_dataset_Worker1_start
    ''' on cancel or error - returns empty System.Data.DataSet(),
    ''' on done - returns the status of the background sql SELECT operation
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e">e.Result is passed as evArgs.Result 
    ''' in the get_dataset_Worker1_start</param>
    ''' <remarks>The method is executed in the main thread,
    ''' so it is save to update the interface in the callback function </remarks>
    Private Sub get_dataset_Worker1_RunWorkerCompleted(ByVal sender As System.Object, _
    ByVal e As RunWorkerCompletedEventArgs) Handles get_dataset_Worker1.RunWorkerCompleted
        If e.Cancelled = True Then
            ' Stop the database operation
            'cmd.Cancel()
            'update the interface  - message that the background worker has been canceled
            'like resultLabel.Text = "Canceled!"
            ' e.Result is not assigned on Cancel
            get_dataset_callBackFunc1.Invoke(New System.Data.DataSet())

        ElseIf e.Error IsNot Nothing Then
            ' Stop the database operation
            cmd1.Cancel()
            'update the interface  - message that the background worker thrown an exception
            'like resultLabel.Text = "Error: " & e.Error.Message
            ' e.Result is not assigned on Cancel
            get_dataset_callBackFunc1.Invoke(New System.Data.DataSet())
            Throw New Exception(e.Error.Message)
        Else
            'update the interface  - message that the background worker done the job
            'resultLabel.Text = "Done!"
            '!!!! update any data, any components, any interface here
            get_dataset_callBackFunc1.Invoke(DirectCast(e.Result, System.Data.DataSet))

        End If
    End Sub

#End Region

End Class

You may call this from your main thread in this way:

Dim async As async_get_dataset

Sub run()
    If async IsNot Nothing Then
        MsgBox("Already running!")
        Exit Sub
    End If
    async = New async_get_dataset()
    Dim s As String = "select *   from   test_table _
    where MESSAGE_TEXT like 'message%' and  issue_date between to_date_
    ('<%txtDateFrom%>','MM/dd/yyyy') and to_date('<%txtDateTo%>','MM/dd/yyyy') "
        s = s.Replace("<%txtDateFrom%>", "01/01/2009")
        s = s.Replace("<%txtDateTo%>", "02/01/2010")
        async.execute(s, AddressOf update_Interface_in_MAIN_thread)
End Sub

'Public Delegate Function update_dataset_callback_
(ByVal ds As System.Data.DataSet) As Object

Private Function update_Interface_in_MAIN_thread_
(ByVal ds As System.Data.DataSet) As Object

    Dim frm As New Form()
    frm.Width = 300
    frm.Height = 300

    Dim grd As New DataGridView()
    grd.Location = New System.Drawing.Point(1, 1)
    grd.Name = "Grid1"
    grd.Size = New System.Drawing.Size(300, 300)

    frm.Controls.Add(grd)

    frm.Show()
    If ds.Tables.Count > 0 Then
        grd.DataSource = ds.Tables(0)
    End If
    async = Nothing
    Return Nothing

End Function

Public Sub Cancel()
    If async IsNot Nothing Then
        async.cancel()
    End If
        async = Nothing
End Sub