Execute Query taking priority over animation

Hi,

I've have a VB application that copies files amongst other things.

When the button is clicked, it triggers a stored procedure that copies these files. For some reason, an embedded animation in the form does not play until after the stored procedure is triggered, despite it being placed in the code before the cmd.ExecuteNonQuery() command:



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

pctAni.Visible = True '--> The 'copying files animation

'The 'copying files' animation does not kick in until after the Execute (), specifically cmd.ExecuteNonQuery(), has completed.

Execute()

End Sub

Private Sub Execute()

Dim cnn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand

My.Settings("cnnString") = "Data Source=vw04crmtest;Initial Catalog=master;User ID=sa"

cnn.ConnectionString = My.Settings("cnnString")
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_backup_processes"
.Connection = cnn
.CommandTimeout = 300

End With

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()


MsgBox("Processes Backed Up")

'pctAni.Visible = False - Commented out for testing


End Sub


Any ideas why the Execute command is taking priority over the animation, despite the animation be placed before it?

Thanks

BY default, ADO database activities are synchronous in nature. This means that when the ADO command is executed, processing in the current thread halts until control is returned. The synchronous model is easier to program, but, as you've experienced, it does have drawbacks.

Now, in classic VB, you would need to declare the objects "WithEvents" in order to use them asynchronously.

For dotNet, ref the following DevX article (http://www.devx.com/dotnet/Article/26747/1954?pf=true).

BY default, ADO database activities are synchronous in nature. This means that when the ADO command is executed, processing in the current thread halts until control is returned. The synchronous model is easier to program, but, as you've experienced, it does have drawbacks.

Now, in classic VB, you would need to declare the objects "WithEvents" in order to use them asynchronously.

For dotNet, ref the following DevX article (http://www.devx.com/dotnet/Article/26747/1954?pf=true).

Thank you, Lou. Now I know.

Back again.

The code has now been modified to be asynchronous. But I am still having the same problem of the animation not playing till after. This is the first time I'm using this method, the problem may not be difficult to spot.




Dim arCopy As IAsyncResult 'Using the asynchronous method

cnn.ConnectionString = My.Settings("cnnString")

My.Settings("cnnString") = "Data Source=vw04crmtest;Asynchronous Processing=true;Initial Catalog=master;User ID=sa"


With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_backup_processes"
.Connection = cnn
.CommandTimeout = 300
End With

cnn.Open()

arCopy = cmd.BeginExecuteNonQuery() 'Using begin and end

While Not arCopy.IsCompleted
pctAnimation.Visible = True 'THE ANIMATION
End While

cmd.EndExecuteNonQuery(arCopy)

cnn.Close()

MsgBox("Processes Backed Up")

pctAni.Visible = False



End Sub

Why don't you start the animation before you call the backup procedure?

Why don't you start the animation before you call the backup procedure?


I did originally, (see second line in code of first post). It doesn't matter where it is in the button click event, the backup procedure keeps taking priority.

OK, completely missed that. :o

How do you have your animation set up? Is it just an animated gif in a picture box, or do you have code somewhere that is supposed to run and update images?

OK, completely missed that. :o

How do you have your animation set up? Is it just an animated gif in a picture box, or do you have code somewhere that is supposed to run and update images?

It's a simple 'file copying' animated gif in a picture box. By default it's invisible. When the backup button is clicked...

animation.visable = true

Back everything up then

animation.visible = false.

I am copying by triggering a stored procedure on sql server that copies files using cmdshell (as well as other things to the database). I think the problem may have somehing to do with using this method, as the I am able to change the form colour while still waiting for a response from the database.

If you're able to change the color, then I'm wondering if the problem is somewhere else. I do mostly WebForms, so I'm a little out of place here.

Does the picture box have a property that you need to set to allowed animations? Is it displaying, but just not moving?

Is the gif set up with a finite number of loops? Not all .gifs are set up to repeat for ever. So it repeats 3 or 4 times while it's hidden. You start the process, and then it displays static...

Have you tried calling Me.Invalidate() after you set the .gif to visible? That will force the Form to redraw itself.

Just a couple ideas to try. Not sure what's going on.

I doubt it has anything to do with the way you're copying files. Although have you tried doing it using the System.IO namespace instead of cmdshell from SQL Server? It seems like you should be able to do this without SQL being involved at all.

Hi Wayne, I tried all your suggestions above but with no luck. I'm using SQL Server because (I'm a dba!) and after copying the files, the stored procedures triggers sql server jobs such as transactional log backups.

But...

I have almost got it working using the background worker componant...

When the button is clicked...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

animation.visible = true ----> This works! and animates while copying the directory

background = New BackgroundWorker

background.RunWorkerAsync()

End Sub

Private Sub background_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles background.DoWork

cnn.ConnectionString= "Data Source=(local);Asynchronous Processing=true;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=misc;User ID=sa"

With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_backup_processes"
.Connection = cnn
.CommandTimeout = 300
End With

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

animation.Visible = False

MsgBox("Processes Backed Up")


End Sub


Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted

animation.Visible = False

End Sub


With the RunWorkerCompleted sub, the animations visibility should be reset to false, but it isn't. Any ideas?

Thanks, again.

Finaly working.

The problem was calling 'Background' instead of backgroundworker1. Tired eyes.

The final code for a simple asynchronous thread is as follows:

Imports System.ComponentModel

Public Class frmMain



Dim cnn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Me.Cursor = Cursors.WaitCursor
Button1.Enabled = False
lblWait.Visible = True
animation.Visible = True



BackgroundWorker1.RunWorkerAsync()

End Sub

Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
Me.lblWait.Visible = False
Me.animation.Visible = False
Me.Button1.Enabled = True
Me.Cursor = Cursors.Default
End Sub


Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
My.Settings("cnnString") = "Data Source=(local);Asynchronous Processing=true;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=misc;User ID=sa"
cnn.ConnectionString = My.Settings("cnnString")

With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_backup_processes"
.Connection = cnn
.CommandTimeout = 300
End With

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

MsgBox("Processes Backed Up")
End Sub
End Class