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 takes a longer time) 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

I would have done it in a separate thread anyway, so I would probably go for the BackgroundWorker approach.

Answer 2

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).

Answer 3

check
http://support.microsoft.com/kb/315582/en-us http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/66f45455-c2d1-436d-904c-f904958934a5