title: Why does my application hang while trying to close a SqlConnection object?
body: <p>I am trying to get column information in C# from a SQL table on SQL Server.  I am following the example in this link: <a href="http://support.microsoft.com/kb/310107" rel="nofollow">http://support.microsoft.com/kb/310107</a> 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:</p>
  
  <pre><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.
  </code></pre>
  
  <p>Putting the <code>SqlConnection</code> inside a using block also causes the application to hang unless <code>CommandBehavior.KeyInfo</code> is changed to <code>CommandBehavior.SchemaOnly</code>.</p>
  
  <pre><code>using (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 even here in the using
      DataTable table = reader.GetSchemaTable();
      Console.WriteLine(table.Rows.Count);
  }
  </code></pre>
  
  <p>The table in question has over 3 million rows, but since I am only obtaining the Schema information, I would think this wouldn't be an issue.  My question is: Why does my application get stuck while trying to close a connection?</p>
  
  <p><strong>SOLUTION</strong>: Maybe this isn't optimal, but it does work; I inserted a <code>command.Cancel();</code> statement right before <code>Close</code> is called on connection:</p>
  
  <pre><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);
  command.Cancel(); // &lt;-- This is it.
  connection.Close(); // Alternatively If this line is commented out, the program runs fast.
  </code></pre>