c# - Execute a stored procedure from a windows form asynchronously and then disconnect?

Question

I am calling a stored procedure from my application that can take 30 minutes to execute.

I don't want to make my user leave the application open for that entire time period. So I would like to call the sproc, let it fly, and let them shut down the application and come back later.

How can I do this?

Answer 1

This is actually a quite common scenario. You cannot do anything client based because the client may go away and disconnect and you'll lose the work achieved so far. The solution is to use Service Broker Activation: you create a service in the database and attach an activated procedure. In your application (or ASP page) you send a message to the service and embed the necessary parameters for your procedure. After your application commits, the message activates the service procedure. the service procedure reads the parameters from the message and invokes your procedure. since activation happens on a server thread unrelated to your original connection, this is reliable. In fact the server can even shutdown and restart while your procedure is being executed and the work will be rolled back then resumed, since the activating message will trigger again the service procedure after the restart.

Update

I have published the details of how to do this including sample code on my blog: Asynchronous procedure execution.