Does SqlCommand.ExecuteNonQuery wait until completion of a stored procedure?

Question

I am working on a Windows desktop application built in VB.NET that interfaces with a web-based database (Microsoft SQL Server 2012). It connects to database when required and performs some calculations locally.

Now, there is a new requirement to execute a stored procedure from this Windows application. I need to know that when I call SqlCommand.ExecuteNonQuery, is the SqlCommand freed up immediately or it waits till the sproc has finished all work?

I cannot test right now because the stored procedure is yet to be designed and it is going to take minimum 2 min to complete.

I am running the SqlCommand.ExecuteNonQuery on a separate thread than the UI.

Concerns if SqlCommand is not freed up immediately:

1. If user exits the application before completion, will the stored procedure finish its tasks?
2. If a timeout occurs, will the stored procedure finish its tasks?

Answer 1

Yes, ExecuteNonQuery will wait until the SP is complete or errors.

If you are running the ExecuteNonQuery from another thread, only that thread is waiting. Other threads are not so the user can close the application. If you cleanup the thread before closing, as you should, it will send a cancel to the SP. (This does not mean it will actually cancel. If it is almost done, it may finish anyway.) What happens then is up to your SQL developer. If they run the SP inside a transaction, they should rollback the changes and return the error. If not, get a new SQL developer.