Progressbar monitoring stored procedures progress

[brazen]

Hi friends,
I have a stored procedure which takes around 30 secs to execute at the backend.I would like to implement a progressbar on the form which would show the progress of the stored procedure's execution.
How would I enable this.

Thanx in advance.

 

[NssB]

The progressbar control has a Min/Max and Value property.
You need to identify a parameter of the stored procedure you can define a min and max value from start to completion. Sometimes this is not possible and Progress Bars are 'guestimated'.

You say the procedure runs for approx 30 secs?
Min Value = 0
Max Value = 30

You then need to increment the Value property each second, perhaps in a timed loop (see below)

Do While pBar.Value < 30
    'Pause the Loop for 1 sec on each iteration
    Dim timeOut As DateTime = Now.AddMilliseconds(1000)
    Do
        Application.DoEvents()
    Loop Until Now > timeOut
    'Increment the value of the progress bar by 1
    pBar.Value += 1
Loop

 

[brazen]

Thanx friend,
I m a lil confused as to how to use ur code.i m not exactly sure wat exactly Application.Doevents does.
I have my code as such:

Dim strconn AsString = My.Settings.MySQLConnectionString
sqlConn.ConnectionString = strconn
Try
sqlConn.Open()
cmd.Connection = sqlConn
' tra = sqlConn.BeginTransaction
'cmd.Transaction = tra
'ProgressBar1.PerformStep()
cmd.CommandText = CommandType.StoredProcedure
cmd.CommandText = "prc_Update"
cmd.CommandTimeout = 180
cmd.ExecuteNonQuery()
MessageBox.Show("Success")
sqlConn.Close()

Catch ex As SqlException
MessageBox.Show(ex.Message)
EndTry

This code is executed on a button click event.
I dont understand how I would implement the progress bar as u mentioned and where exactly to use Applcation.Doevents
Really appreciate ur help
Thanx..

 

[NssB]

Hi,

OK, under the assumption your transaction last approx 30 secs, you could do the following, which is somewhat simpler and probably better practice than the looping timer I suggested.

Add a Timer Control to your form and set the milli-seconds property to 1000
On your ProgressBar control, set Min Value to 1 and Max value to 30

Then double click the Timer Control (bottom of the design view)
Add the tick event code as below.
Modify your Button Click event as below.

Private Sub timerControl1_tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles timerControl1.tick
    'Increment progressBar value by 1 to a max of 30
    progressBar1.Value += 1
End Sub    

Private Sub button1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
    Dim strconn AsString = My.Settings.MySQLConnectionString
    sqlConn.ConnectionString = strconn
    timerControl1.Enabled = True  'Start timer control
    Try
        sqlConn.Open()
        cmd.Connection = sqlConn
        ' tra = sqlConn.BeginTransaction
        'cmd.Transaction = tra
        'ProgressBar1.PerformStep() 
        cmd.CommandText = CommandType.StoredProcedure
        cmd.CommandText = "prc_Update"
        cmd.CommandTimeout = 180
        cmd.ExecuteNonQuery()
        MessageBox.Show("Success")
        sqlConn.Close()
    Catch ex As SqlException
        MessageBox.Show(ex.Message)
    Finally
        progressBar1.Value = 30   ' This completes the bar if above took less than 30 secs
        timerControl1.Enabled = False    'Stop timer control (30 secs later)
    EndTry
End Sub

Note: The above is the 'guestimated' timer method. If you can determine stop/start values from your stored procedure, you can set min/max values and increment the counter a program 'calculated' number of times, rather than pre-defined.


Hope this helps

NssB