Gracefully terminate long running SQL Server query from ADO.NET

Question

I use an asynchronous process to retrieve data from a long running SQL query. The process calls a stored procedure through the SqlCommand.BeginExecuteReader() method.

At times, I would like to shut my process down and gracefully terminate any running queries.

After some testing, it seems as if calling SqlConnection.Close() is sufficient. For good measure, I also explicitly call SqlConnection.Dispose().

When running a trace through SQL Server Profiler, it appears as if the query is ended gracefully as I see a SQL:BatchStarting followed by a SQL:BatchCompleted after calling Close().

Can anyone please confirm whether closing the connection is all that is required?

Answer 1

Calling Close is sufficient, just a few remarks:

First of all make sure you write your Close methods inside finally block to ensure correct exception handling.

Also you do not need to call both SqlConnection.Close and SqlConnection.Dispose methods as they both do the same. Dispose method is added to implement IDisposable pattern and this method internally calls SqlConnection.Close method, which has more natural name, because we close connections, but not dispose them :)

If you are using DataReader then you have to close it as well each time after you want to use connection for other purpose.