Asynchronous call of a SQL Server stored procedure in C#

Question

Is it possible to call a SQL Server stored procedure asynchronously via C#?

I have a stored procedure which writes a backup of a specific database (this can take a long time to complete) and I want to show the progress of the backup process in a windows forms (for this I use http://www.wisesoft.co.uk/articles/tsql_backup_restore_progress.aspx). Or should I use the Backgroundworker control and run the SP in a backgroundjob (own thread) ?

Answer 1

In your SqlCommand you can run commands asynchronously using BeginExecuteNonQuery and EndExecuteNonQuery. The latter will block until its done. However this won't report the progress from the server about how the backup is going - I'd use a marquee progress bar for it.

To avoid the EndExecuteNonQuery from blocking your UI (depending on how you handle it), you will need a background thread. If you use this then you may as well not use BeginXXX EndXXX methods and do it synchronously on a background thread - the BackgroundWorker is best for this.

To avoid using a background thread in the UI, instead of blocking on EndXXX you will need to register a callback and handle the resulting event (calling EndXXX in this event handler, but it will return immediately).

Update: as per a comment, for asynchronous calls into the SQL command/connection stuff, you need to specify as much in the connection string:

http://www.connectionstrings.com/sql-server-2008

Server=myServerAddress; Database=myDataBase; Integrated Security=True; Asynchronous Processing=True;

Or in code using the connection string builder:

builder.AsynchronousProcessing = true;