开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜