CODEWITHSUNDEEP
c#sqlsql-serverado.net
Jesus is Lord
Jesus is Lord

Reputation: 15399

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

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

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?

SOLUTION: Maybe this isn't optimal, but it does work; I inserted a command.Cancel(); statement right before Close is called on 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.

Upvotes: 8

Views: 3784

Answers (5)

tsells
tsells

Reputation: 2771

I would try something like this. This ensures all items are cleaned up - and avoids using DataReader. You don't need this unless you have unusually large amounts of data that would cause memory issues. 

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

Upvotes: 0

Jim Mischel
Jim Mischel

Reputation: 134005

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.

Upvotes: 7

JotaBe
JotaBe

Reputation: 39025

There is an specific way to do this, using SMO (SQL Server management objects)

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

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

Look at this references:

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

This will allow you to get all the possible information from the database in a very easy way. there are plenty of samples in VB.NET and C#.

Upvotes: 0

Steve
Steve

Reputation: 216293

Could you try this? 

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 the way to go

Upvotes: 0

Beenish Khan
Beenish Khan

Reputation: 1583

It looks right to me overall and I think you need a little optimization. In addition to the above suggestion regarding avoiding DataReader, I will recommend to use connection pooling. You can get the details from here :

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

Upvotes: 0

Related Questions