SQL Stored procedure

[Darin]

How can I execute a stored procedure and NOT wait for it to finish
before my program continues. The SP takes about 15 minutes to run, so I
want to run it but not have the program locked while it is running.

I am running it via:

strCommand = New SqlCommand(in_sp, xconn)
strCommand.CommandType = CommandType.StoredProcedure
strCommand.CommandTimeout = 3600 '60 minutes
strCommand.ExecuteNonQuery

Thanks

Darin

 

[Maligui]

A common practice in coding is to have long procedures run in the
background. I would run the procedure on another thread.

 

[Maligui]

Try this code.

Dim t As New System.Threading.Thread(AddressOf RunStoredProcedure)

Sub Something()
t.Start()
End Sub

Private Sub RunStoredProcedure()
strCommand = New SqlCommand(in_sp, xconn)
strCommand.CommandType = CommandType.StoredProcedure
strCommand.CommandTimeout = 3600 '60 minutes
strCommand.ExecuteNonQuery()
End Sub

 

[Darin]

FOllowing up on the threading code, how will the program know it is
finished?

Darin

 

[gs]

t.IsAlive?

so when your program absolutely need the result before executing some
functionality, you can check that first at that point.

have tried the sample code given by Maligui yet?

Thread.IsAlive Property
Gets a value indicating the execution status of the current thread.


reference sample
Public ReadOnly Property IsAlive As Boolean
Visual Basic (Usage)
Dim instance As Thread
Dim value As Boolean

value = instance.IsAlive

 

[Ryan S. Thiele]

The thread will exit automatically. Or you can terminate it by using:

t.cancel

To reastart the thread, you have to make a new instance.

t = new thread

Also you can name the thread so you can keep track of it.

t = new thread
t.Name = "A Name"

the console will say:

Thread (A Name) as exitied with code 0 (0 might be in hex [0x000])

You can email me for some further help, been programming vb since 1996
I'm sing VS2005 team at this point. I might go enterprise verison.

 

[Stephany Young]

It would appear that everybody has missed the point here.

You really need to be checking out the :
SqlCommand.BeginExecuteNonQuery
and
SqlCommand.EndExecuteNonQuery
methods, which are specifically designed for exactly the asynchronous
behavior you appear to be looking for.

 

[Robinson]

It would appear that everybody has missed the point here.

You really need to be checking out the :
SqlCommand.BeginExecuteNonQuery
and
SqlCommand.EndExecuteNonQuery
methods, which are specifically designed for exactly the asynchronous
behavior you appear to be looking for.

This is true yes. I'm using it in my client, however the OP should still
really be using threads to do this. Once you get the pattern right it's
fairly simple and much cleaner. The thread should "invoke" a method on the
form when it has completed (ie. just before it exists it's ThreadMain
function). the Begin/EndExecute should be used in a loop to check a flag to
see if the user has cancelled the operation. This means the main process
can continue on with it's work and won't have to sit in "DoEvents" loop and
you can potentially cancel the database operation if the user wants to.

 

[gs]

great idea for user cancel option provided the database operation has
transaction processing and rollback capability