Why does my application hang while trying to close a SqlConnection object?

Question

I am trying to get column information in C# from a SQL table on SQL Server. I am following the example in this link: http://support.microsoft.com/kb/310107 My program strangely gets hung up when it tries to close the connection. If the connection is not closed, the program exits without any Exceptions. Here's my code: ``` SqlConnection connection = new SqlConnection(@"MyConnectionString"); connection.Open(); SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection); SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); // If this is changed to CommandBehavior.SchemaOnly, the program runs fast. DataTable table = reader.GetSchemaTable(); Console.WriteLine(table.Rows.Count); connection.Close(); // Alternatively If this line is commented out, the program runs fast. ``` Putting the `SqlConnection` inside a using block also causes the application to hang unless `CommandBehavior.KeyInfo` is changed to `CommandBehavior.SchemaOnly`. ``` using (SqlConnection conn…

Accepted Answer

I saw something like this, long ago. For me, it was because I did something like:

```
SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection);
SqlDataReader reader = command.ExecuteReader();

// here, I started looping, reading one record at a time
// and after reading, say, 100 records, I'd break out of the loop

connection.Close();  // this would hang
```

The problem is that the command appears to want to complete. That is, go through the entire result set. And my result set had millions of records. It would finish ... eventually.

I solved the problem by adding a call to `command.Cancel()` before calling `connection.Close()`.

See http://www.informit.com/guides/content.aspx?g=dotnet&seqNum=610 for more information.

Why does my application hang while trying to close a SqlConnection object?

Solution

Problem