Why does my application freeze when I try to close the SqlConnection object?

Question

I am trying to get column information in C # from a SQL table on SQL Server. I follow the example in this link: http://support.microsoft.com/kb/310107 My program hangs strangely when I try to close the connection. If the connection is not closed, the program exits without any exceptions. Here is 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. 

Entering SqlConnection inside the used block also causes the application to freeze if CommandBehavior.KeyInfo not changed to CommandBehavior.SchemaOnly .

 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); } 

There are more than 3 million rows in the table in question, but since I only get information about the scheme, I think this will not be a problem. My question is: why does my application get stuck when trying to close the connection?

SOLUTION This may not be optimal, but it works; I inserted the instruction command.Cancel(); just before calling Close in the connection:

 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(); // <-- This is it. connection.Close(); // Alternatively If this line is commented out, the program runs fast. 

Answer 1

I saw something like this for a long time. For me, this 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 team seems to want to complete. That is, go through the whole set of results. And my result set had millions of records. It would end ... in the end.

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

Answer 2

This looks right to me as a whole, and I think you need a little optimization. In addition to the DataReader exception suggestion above, I recommend using a connection pool. You can get the information here:

http://www.techrepublic.com/article/take-advantage-of-adonet-connection-pooling/6107854

Answer 3

Could you try it?

 DataTable dt = new DataTable(); using(SqlConnection conn = new SqlConnection("yourConnectionString")) { SqlCommand cmd = new SqlCommand("SET FMTONLY ON; " + yourQueryString + "; SET FMTONLY OFF;",conn); conn.Open(); dt.Load(cmd.ExecuteReader()); } 

SET FMTONLY ON / OFF from MSDN seems to be the way

Answer 4

There is a special way to do this using SMO (SQL Server Management Objects)

You can get a collection of tables in the database, and then read the properties of the table you are interested in (columns, keys, and all imaginable properties)

This is what SSMS uses to get and set the properties of all database objects.

Take a look at these links:

• Database.Tables Property
• Table class

This is a complete example of how to get table properties:

• Retrieving SQL Server 2005 Database Information Using SMO: Database Information, Table Information

This will allow you to get all possible information from the database very simply. There are many samples in VB.NET and C #.

Answer 5

I would try something like this. This ensures that all items are cleared - and avoids using a DataReader. You do not need this if you do not have unusually large amounts of data that can cause memory problems.

  public void DoWork(string connectionstring) { DataTable dt = new DataTable("MyData"); using (var connection = new SqlConnection(connectionstring)) { connection.Open(); string commandtext = "SELECT * FROM MyTable"; using(var adapter = new SqlDataAdapter(commandtext, connection)) { adapter.Fill(dt); } connection.Close(); } Console.WriteLine(dt.Rows.Count); }