How to use async methods to load Db Data and keep

Question

I made a somewhat large application that works well, except for its UI (winforms) freezes when using webclient to retrieve data from web (link is um... not the fastest), or data connection to retrieve queries from the database (which is stored in a far, slow server - can't avoid it).

So I thought of taking advantage of async methods, in order user can move, minimize and click the window without OS getting nervous and tagging it as "not responding".

I don't want my code to do nothing else in-between those lenghty operations, just keep UI responsive (I know I should disable controls to prevent user of asking something else, or the same afgain, before first operation completes).

But I have no experience whatsoeever with async methods, and my first test attempt was this:

Public Function GetDatatableUIblocked() As DataTable
    Dim retTable As New DataTable
    Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\lanserver\storage\DB.accdb;Persist Security Info=False;")
    Dim command = connection.CreateCommand()
    command.CommandText = "SELECT * FROM bdPROC;"
    connection.Open()
    Dim reader = command.ExecuteReader
    retTable.Load(reader)
    connection.Close()
    Return retTable
End Function

Public Function GetDatatableUIfree() As DataTable
    Dim retTable As New DataTable
    Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\lanserver\storage\DB.accdb;Persist Security Info=False;")
    Dim command = connection.CreateCommand()
    command.CommandText = "SELECT * FROM bdPROC;"
    connection.Open()
    Dim readerTask = command.ExecuteReaderAsync()

    readerTask.Start() '<=========================== EXCEPTION HAPPENS HERE

    Do
        Application.DoEvents()
    Loop Until readerTask.IsCompleted OrElse readerTask.IsFaulted
    Dim reader = readerTask.Result
    retTable.Load(reader)
    connection.Close()
    Return retTable
End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ProgressBar1.Visible = True
    Dim dt = GetDatatableUIblocked()
    ProgressBar1.Visible = False
    DataGridView1.DataSource = dt
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    ProgressBar1.Visible = True
    Dim dt = GetDatatableUIfree()
    ProgressBar1.Visible = False
    DataGridView1.DataSource = dt
End Sub

However, I get an exception when running readerTask.Start(), it says something like 'Start cannot be called in a task which is already complete' (I'm translating, my VS is not in English).

I checked some threads here in SO but honestly I wasn't able to grasp the concept and apply it to my problem, so I humbly ask for help. Thank you very much!

Answer 1

One way to leave the UI free is to use a Task. The proposed solution sort of thwarts the point of a BackGroundWorker with a do nothing loop to wait for it to complete. The code below makes a few other noteworthy changes:

' note the Async modifier
Private Async Sub btnDoIt_Click(...
    Dim sql = "SELECT * FROM RandomData"
    dtSample = Await Task(Of DataTable).Run(Function() LoadDataTable(sql))
    dgv2.DataSource = dtSample
End Sub

Private Function LoadDataTable(sql As String) As DataTable
    ' NO UI/Control references allowed
    Dim dt = New DataTable
    Using dbcon As New OleDbConnection(ACEConnStr)
        Using cmd As New OleDbCommand(sql, dbcon)
            dbcon.Open()
            dt.Load(cmd.ExecuteReader())
        End Using
    End Using
    Return dt
End Function

I dont have the exact conditions mentioned in the OP, but I do have an Access table with 500k rows. This taxes OleDB enough that it can take 6-10 seconds to load which is plenty long enough to tell if the UI remains responsive. It does.

1. Don't forget to use Asynch on whatever method will be awaiting the Task to complete.
2. The LoadTable method is set up to load any table from a valid query, so other things can use it. If/when queries change, just make changes to the things calling it.
3. DB Provider objects such as Connections and DBCommand objects ought to be disposed when you are done with the to release resources and prevent leaks. The Using blocks do this for us.
4. There is no need for a do-nothing/DoEvents loop, nor an event for a completed notice. The Await code will wait for the load method to complete so that any other things you need to do can be done afterwards.

A Task can often be simpler to use than a BackGroundWorker.

Resources

• Task Class
• Asynchronous Programming with Async and Await (Visual Basic)
• Using Statement

Answer 2

never mind. I've got what I wanted with a BackgroundWorker instead:

Public Function GetDatatableUIfree() As DataTable
    Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\lanserver\storage\DB.accdb;Persist Security Info=False;")
    Dim command = connection.CreateCommand()
    command.CommandText = "SELECT * FROM bdPROC;"
    connection.Open()
    Dim retTable As New DataTable, bgw As New BackgroundWorker, bgw_complete As Boolean
    AddHandler bgw.DoWork,
        Sub(sender As Object, e As DoWorkEventArgs) retTable.Load(command.ExecuteReader)
    AddHandler bgw.RunWorkerCompleted,
        Sub(sender As Object, e As RunWorkerCompletedEventArgs) bgw_complete = True
    bgw.RunWorkerAsync()
    Do
        Application.DoEvents()
    Loop Until bgw_complete
    connection.Close()
    Return retTable
End Function

Thank you very much anyway for the help.