|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Stored procedureHow 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 *** Sent via Developersdex http://www.developersdex.com *** A common practice in coding is to have long procedures run in the
background. I would run the procedure on another thread. -- Show quoteHide quoteThiele Enterprises - The Power Is In Your Hands Now! "Darin" <darin_nospam@nospamever> wrote in message news:uDi2ig88GHA.2268@TK2MSFTNGP05.phx.gbl... > 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 > > *** Sent via Developersdex http://www.developersdex.com *** 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 -- Show quoteHide quoteThiele Enterprises - The Power Is In Your Hands Now! "Darin" <darin_nospam@nospamever> wrote in message news:uDi2ig88GHA.2268@TK2MSFTNGP05.phx.gbl... > 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 > > *** Sent via Developersdex http://www.developersdex.com *** FOllowing up on the threading code, how will the program know it is
finished? Darin *** Sent via Developersdex http://www.developersdex.com *** 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 Show quoteHide quote "Darin" <darin_nospam@nospamever> wrote in message news:e9IKDv%238GHA.1256@TK2MSFTNGP04.phx.gbl... > FOllowing up on the threading code, how will the program know it is > finished? > > Darin > > *** Sent via Developersdex http://www.developersdex.com *** 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. -- Show quoteHide quoteThiele Enterprises - The Power Is In Your Hands Now! "gs" <gs@dontMail.telus> wrote in message news:Oea5g5%238GHA.4632@TK2MSFTNGP02.phx.gbl... > 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 > "Darin" <darin_nospam@nospamever> wrote in message > news:e9IKDv%238GHA.1256@TK2MSFTNGP04.phx.gbl... >> FOllowing up on the threading code, how will the program know it is >> finished? >> >> Darin >> >> *** Sent via Developersdex http://www.developersdex.com *** > > 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. Show quoteHide quote "Darin" <darin_nospam@nospamever> wrote in message news:uDi2ig88GHA.2268@TK2MSFTNGP05.phx.gbl... > 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 > > *** Sent via Developersdex http://www.developersdex.com ***
Show quote
Hide quote
"Stephany Young" <noone@localhost> wrote in message This is true yes. I'm using it in my client, however the OP should still news:%230JWRXA9GHA.5012@TK2MSFTNGP06.phx.gbl... > 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. > > > "Darin" <darin_nospam@nospamever> wrote in message > news:uDi2ig88GHA.2268@TK2MSFTNGP05.phx.gbl... >> 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 >> 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. great idea for user cancel option provided the database operation has
transaction processing and rollback capability Show quoteHide quote "Robinson" <toomuchspamhaspassed@myinboxtoomuchtoooften.com> wrote in message news:ehabub$7u1$1$8302bc10@news.demon.co.uk... > "Stephany Young" <noone@localhost> wrote in message > news:%230JWRXA9GHA.5012@TK2MSFTNGP06.phx.gbl... >> 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. >> >> >> "Darin" <darin_nospam@nospamever> wrote in message >> news:uDi2ig88GHA.2268@TK2MSFTNGP05.phx.gbl... >>> 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 >>> > > 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. > 
Other interesting topics
Config file for DLL??
Why isnt this CLS compliant? Streamreader problem Window Size in Runtime Classes and Objects System.BadImageFormatException Hidden form and button PerformClick My.Settings wont update?? audio, wav, mps, midi, player determining current winform application is active, has focus or being used |
|||||||||||||||||||||||
