SQL 如何在执行大型 SQLCommand VB.Net 时显示进度条
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16688990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to display progress bar while executing big SQLCommand VB.Net
提问by Dean Hart
I have this big SQL command that usually returns 20 000 - 100 000 rows of data. But as soon as i call the executeMyQuery function, the program hangs for a few seconds depending on how large the return is.
我有这个大 SQL 命令，它通常返回 20 000 - 100 000 行数据。但是一旦我调用 executeMyQuery 函数，程序就会挂起几秒钟，具体取决于返回的大小。
I only return one column.
我只返回一列。
How can I display a progress bar while this command is running?
如何在此命令运行时显示进度条？
Maybe in a Thread or something(I have NO experience with threads)
也许在线程或其他东西中（我没有线程经验）
Here is my code(The arguments are sent from 3 different combobox.selectedItem) :
这是我的代码（参数从 3 个不同的组合框.selectedItem 发送）：
Public Function executeMyQuery(dbname As String, colname As String, tblname As String)
Try
ListBox1.Items.Clear()
If Not String.IsNullOrWhiteSpace(connString) Then
Using cn As SqlConnection = New SqlConnection(connString)
cn.Open()
Using cmd As SqlCommand = New SqlCommand()
cmd.Connection = cn
Dim qry As String
qry = String.Format("select distinct [{0}] from {1}.dbo.{2} where [{0}] is not null", colname, dbname, tblname)
cmd.CommandText = qry
cmd.CommandTimeout = 0
Dim count As Integer
Using myReader As SqlDataReader = cmd.ExecuteReader()
While (myReader.Read())
count += 1
ListBox1.Items.Add(count.ToString & ". " & myReader.GetString(0))
End While
End Using
End Using
End Using
End If
cn.Close()
Catch ex As Exception
MsgBox("Error Occured : " & ex.Message)
cn.Close()
End
End Function
回答by Jodrell
Here is a cut down example of how to do Asychrounous Work with VB.Net 4.0.
这是一个关于如何使用 VB.Net 4.0 进行异步工作的简化示例。
Lets imagine you have a form that has the following imports,
假设您有一个具有以下导入的表单，
Imports System.Windows.Forms
Imports System.Threading
Imports System.Threading.Tasks
That form has two controls
该表单有两个控件
Private WithEvents DoSomthing As Button
Private WithEvents Progress As ProgressBar
Somewhere in your application we have a Functioncalled ExecuteSlowStuff, this function is the equivalent of your executeMyQuery. The important part is the Actionparameter which the function uses to show it is making progress.
在您的应用程序的某个地方，我们有一个Function被调用的ExecuteSlowStuff函数，这个函数相当于您的executeMyQuery. 重要的部分是Action函数用来表示它正在进步的参数。
Private Shared Function ExecuteSlowStuff(ByVal progress As Action) As Integer
Dim result = 0
For i = 0 To 10000
result += i
Thread.Sleep(500)
progress()
Next
Return result
End Function
Lets say this work is started by the click of the DoSomethingButton.
假设这项工作是通过单击DoSomethingButton.
Private Sub Start() Handled DoSomething.Click
Dim slowStuff = Task(Of Integer).Factory.StartNew(
Function() ExceuteSlowStuff(AddressOf Me.ShowProgress))
End Sub
You're probably wondering where ShowProgresscomes from, that is the messier bit.
你可能想知道ShowProgress从哪里来，那是更混乱的一点。
Private Sub ShowProgress()
If Me.Progress.InvokeRequired Then
Dim cross As new Action(AddressOf Me.ShowProgress)
Me.Invoke(cross)
Else
If Me.Progress.Value = Me.Progress.Maximum Then
Me.Progress.Value = Me.Progress.Minimum
Else
Me.Progress.Increment(1)
End If
Me.Progress.Refresh()
End if
End Sub
Note that because ShowProgresscan be invoked from another thread, it checks for cross thread calls. In that case it invokes itself on the main thread.
请注意，因为ShowProgress可以从另一个线程调用，所以它会检查跨线程调用。在这种情况下，它会在主线程上调用自己。
回答by Marius
During the query execution you cannot show a real progress bar. MySQL do not deliver any estimation how long the query will take to be finsihed. You can estimate the time by measuring your old runs and "fake" the progress bar with this informations. But this is kind of overkill. In most cases it is enough to show the user "something". Like a wheel spinning or a progress bar filling up every 2-3 seconds.
在查询执行期间，您无法显示真正的进度条。MySQL 不提供任何估计完成查询所需的时间。您可以通过测量您的旧运行并使用此信息“伪造”进度条来估计时间。但这有点矫枉过正。在大多数情况下，向用户显示“某物”就足够了。就像轮子旋转或进度条每 2-3 秒填充一次。
If you want a progress bar while filling the items, this is possible without changing much. Just add a progress bar control and increment it inside your "While(myReader.Reader())" loop. I even suspect this takes the longer time then the query. If you query takes long, check if you have an index on column!
如果您在填充项目时想要进度条，则无需进行太多更改即可。只需添加一个进度条控件并在“While(myReader.Reader())”循环中增加它。我什至怀疑这需要更长的时间然后查询。如果查询需要很长时间，请检查列上是否有索引！
If you want to show the user that something is happening you can use a thread. .NET has a nice BackgroundWorker().
如果您想向用户显示正在发生的事情，您可以使用线程。.NET 有一个很好的 BackgroundWorker()。
It is faily easy to start a BackgroundWorker
启动 BackgroundWorker 很容易失败
Dim bgw As New BackgroundWorker
bgw.WorkerReportsProgress = true
bgw.RunWorkerAsync()
Now you have to do the two events of the backgroundworker:
现在你要做backgroundworker的两个事件：
Dim WithEvents bgw As New BackgroundWorker
Dim progressBar As New progressbar
Sub start()
bgw.WorkerReportsProgress = true
bgw.RunWorkerAsync()
End Sub
Sub bgw_DoWork(sender As Object, e As DoWorkEventArgs) Handles bgw.DoWork
' put your sql code here
For i As Integer = 0 To 10000
If i Mod 1000 Then
bgw.ReportProgress(i / 100)
End If
Next
End Sub
Sub bgw_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles bgw.ProgressChanged
' put your progress changed events here
myProgressBar.Value = e.ProgressPercentage
End Sub
Remeber, inside the DoWork function you cannot access any GUI stuff. Do NOT put message boxes here, do NOT directly change the progressBar. ALWAYS use the bgw.progressChanged event. If you want to give messages from the bgw.doWork to the GUI you can use the reportProgress custom object to do that. Plz read further documentation for this. Do not raise the progressChanged event too often. It is quiet heavy and if you change something in the GUI every time your application might even get VERY slow. I try to call it not more then 10 times per second, if it does not do GUI stuff. And at most 2 time per second, if it does GUI stuff. (Updating a progress bar every second is fine for the user.)
请记住，在 DoWork 函数中，您无法访问任何 GUI 内容。不要把消息框放在这里，不要直接改变进度条。始终使用 bgw.progressChanged 事件。如果您想从 bgw.doWork 向 GUI 提供消息，您可以使用 reportProgress 自定义对象来执行此操作。请为此阅读更多文档。不要太频繁地引发 progressChanged 事件。它很安静，如果您每次在 GUI 中更改某些内容，您的应用程序甚至可能变得非常慢。如果它不执行 GUI 操作，我尝试每秒调用它不超过 10 次。如果它执行 GUI 操作，每秒最多 2 次。（每秒更新一个进度条对用户来说很好。）
