如何使用异步方法加载Db数据并保持UI响应 [英] How to use async methods to load Db Data and keep UI responsive

问题描述

我制作了一个运行良好的大型应用程序，除了使用Webclient从Web检索数据(链接是um ...不是最快的)或从数据库检索查询的数据连接时，其UI(winforms)冻结之外(存储在较慢的远程服务器中-无法避免).

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".

我不希望我的代码在这些冗长的操作之间做任何其他事情，只是保持UI响应(我知道我应该禁用控件以防止用户在第一个操作完成之前询问其他问题或同样的问题)

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

但是，我在运行readerTask.Start()时遇到异常，上面写着无法在已经完成的任务中调用开始"(我正在翻译，我的VS不是英语).

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).

我在SO中检查了一些线程，但老实说，我无法理解这个概念并将其应用于我的问题，所以我谦虚地寻求帮助.非常感谢你！

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!

推荐答案

使UI保持空闲的一种方法是使用Task.所提出的解决方案以无用循环来阻止BackGroundWorker的点，以等待其完成.下面的代码进行了其他一些值得注意的更改:

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

我没有OP中提到的确切条件，但是我确实有一个包含500k行的Access表.这给OleDB造成了沉重的负担，以至于可能需要6到10秒的时间来加载它，足够长的时间来判断UI是否仍然响应.是的.

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. 别忘了在等待Task完成的任何方法上使用Asynch.
2. LoadTable方法被设置为从有效查询中加载任何表，以便其他事物可以使用它.如果/当查询发生更改时，只需对调用它的事物进行更改.
完成后，应该处置
3. DB Provider对象，例如Connections和DBCommand对象，以释放资源并防止泄漏. Using块为我们做到了.
4. 无需执行no/thing/DoEvents循环，也无需完成通知的事件. Await代码将等待load方法完成，以便以后可以执行任何其他操作.

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.

与BackGroundWorker相比，Task的使用通常更简单.

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

资源

• 任务类
• 使用异步和等待的异步编程(Visual Basic)
• 使用声明

这篇关于如何使用异步方法加载Db数据并保持UI响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！