Easy way to run procedure in the background
I'm running stored procedure from asp.net front-end but it's quite long. What is the easy 开发者_StackOverflowway on running that thing in the background? I mean if I close the browser, I still want my stored procedure to complete not just die. Also, I would like to perform other actions on the front-end while my procedure is running. Any good solution for that?
Both SQL Agent and the Service Broker can do this, though it does take some work on your part.
Just launch it in another Thread as so:
'Starts execution of the proc
Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
Dim t As New Threading.Thread(AddressOf DoWork)
t.Start()
End Sub
Private Sub DoWork()
Using c As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
c.Open()
Dim command = New SqlCommand("LongTest", c)
command.CommandType=Data.CommandType.StoredProcedure
command.CommandTimeout = 0
command.ExecuteNonQuery()
End Using
End Sub
Here's the sp that I used for my test:
create PROCEDURE dbo.LongTest
AS
BEGIN
WaitFor Delay '00:00:30' --wait for 30 seconds before doing anything
insert into TableImageTest(image)
values(null)
END
Use "Asynchronous Processing=true" in your connection string and call the sproc asynchronously as in this link.
http://geekswithblogs.net/frankw/archive/2008/07/05/execute-transact-sql-statement-asynchronously.aspx
精彩评论