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

Question

Hi All, using a C# 3.5 Windows form, I am calling a stored procedure that can take 30 minutes to execute (please no knocks or debate on that...it’s doing a lot to a lot of data and is fully optimized). I don't want to make my user leave the app open for that entire time period, so I would like to call the sproc, let it fly and let them shut down the app and come back later. I am using out-of-the-box System.Data.SqlClient objects: SqlCommand, SqlConnection, etc.

Does anyone know if I can do this?

-Thx in advance!

Oh, against SQL Server 2005 if that makes any difference...

Answer 1

let them shut down the app and come back later

If you're going to allow them to completely close the app, you'll have to start up a seperate .exe or something in a different ThreadPool that executes your code calling the stored procedure. Otherwise your thread will die when you close the app.

Answer 2

You can use the BeginExecuteXXX/EndExecuteXXX methods (depending whether it returns a result or not) of the SqlCommand, passing a callback delegate.

Answer 3

I suggest a re-architecture. Create a "work queue" table where you log requests to run the stored procedure. Then either have a Windows Service or a SQL Server job check that work queue from time to time (or be really ingenious and use a trigger) to kick off the stored procedure. Have the stored procedure update the progress from time to time in the work queue table, and your front-end can look at that an tell the user the progress, and then display the results when they're done.

Answer 4

Another method that you could do would be to allow your application to run in the background (possibly in the notification area) and then exit or notify when the job completes. You could use this by using the BeginExecuteNonQuery and EndExecuteNonQuery methods to allow it to run in a separate thread.

Answer 5

If you really want to close down your application completely, I suggest you define a job in SQL Server Agent, and just execute a T-SQL statement to start that job manually. The syntax is:

sp_start_job 
     {   [@job_name =] 'job_name'
       | [@job_id =] job_id }
     [ , [@error_flag =] error_flag]
     [ , [@server_name =] 'server_name']
     [ , [@step_name =] 'step_name']
     [ , [@output_flag =] output_flag]

The job would execute your stored procedure. You will have to be a little creative to pass in any arguments. For example, insert the parameters into a "queue" table and have the job process all the rows in the queue.

Instead of a job, an insert trigger on your queue should work as well.

Answer 6

Your application's main window does not need to be open. If you launched it as a secondary thread, it will continue to run so long as IsBackground == false. I usually prefer to do this stuff through SQL Server Agent or as a client-server application (nothing prevents a client-server app from both running on the same machine, or even being the same binary).

It's been a while...

using System.Threading;

.....

Thread _t = null; void StartProcedure() { _t = new Thread(new ThreadStart(this.StartProc)); _t.IsBackground = false;//If I remember correctly, this is the default value. _t.Start(); }

bool ProcedureIsRunning { get { return _t.IsRunning; } //Maybe it's IsActive. Can't remember. }

void StartProc(object param) { //your logic here.. could also do this as an anonymous method. Broke it out to keep it simple. }

Answer 7

I prefer to use a background service for offline processing, where your user app tells the service what to do and then disconnects. The service can log elapsed times and errors/status, and restart if necessary. WCF is designed for this and supports queues to communicate with.

Answer 8

This is actually a quite common scenario. You cannot do anything client based because the client may go away and disconnect and you'll loose 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.