SqlConnection.open is hanging

Question

I am developing a C# Console Application targeted to .NET 4.0 (soon to be 4.5) using Visual Studio Pro 2013, connecting to a remote SQL Server. SQL Server connection string is defined in the constructor.

I have using the following code which used to work flawlessly but in the last week, as started to act in the following way:

If I recompile my code, the .Open() call hangs. If I stop the application and restart it, it works ! Every time!

How can I debug what is hanging? If I stop it and re-run it, I can run 20 times without hanging. But if I recompile and run, it hangs. Then stop and re-run... works like a charm. I'm so confused :)

public static DataTable GetDataTableSql(string SqlCommand)
{
        DataSet _data = new DataSet();

        try
        {
            using (SqlConnection _connection = new SqlConnection(s_connectionString))
            {
                if (_connection.State != ConnectionState.Open)
                {
                    _connection.Open();
                }  
        }
    }
}

Any pointers would be greatly appreciated!

-Ed

UPDATE: I was able to recover an error message from the .Open() call:

GetDataTableSQL failed: Select * from tblSettings, A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Note that this ALWAYS HAPPENS IMMEDIATELY AFTER A RECOMPILE AND HAS NO ERROR IF I STOP AND RESTART IT ?!?!?!?

UPDATE: Connection string:

Data Source=X.X.X.X ;Initial Catalog=Catalog1;Persist Security Info=True;Connect Timeout=300;User ID=UserID;Password=Password1;

Sans host, dbname, username and password .. can you show us the connection string? — Lynn Crumbling, Nov 04 '15 at 17:17
Do other client apps connecting to the same data source experience the same delay? Is it possible that your issue is with the server, or possibly an issue with DNS resolution of the hostname? — Lynn Crumbling, Nov 04 '15 at 17:19
Side note: with a new connection, you don't need to check the State, just open it. — LarsTech, Nov 04 '15 at 17:19
what do you see in exception? Are you closing connection when you done with database operation? — Jigneshk, Nov 04 '15 at 17:22
I suspect a connectivity issue with the Data Source. What time of response times are you getting from that IP? — Lynn Crumbling, Nov 04 '15 at 17:23
Why is your connection string in the constructor? Shouldn't it be in the config file??? — Sean Lange, Nov 04 '15 at 17:32
Possible duplicate of [Connection.open for hangs indefinitely, no exception is thrown](http://stackoverflow.com/questions/16448857/connection-open-for-hangs-indefinitely-no-exception-is-thrown) — , Nov 10 '15 at 16:20

score 0 · Answer 1 · answered Nov 11 '15 at 17:27

0

I solved my problem by closing down Microsoft SQL Server Management Studios. Console Application worked like a charm.

Argh!

-Ed

answered Nov 11 '15 at 17:27

Ed Landau

966
2
11
24

OK, it might work now but you have not found the true reason yet. The problem might bug you again. It looks fixed but from experience I can tell that this should not have fixed it. – usr Nov 11 '15 at 18:51

score 0 · Answer 2 · edited Mar 14 '18 at 21:15

0

Add _Connection.Close() in finally. The whole process should be try:

catch

finally 
_Connection.Close();

edited Mar 14 '18 at 21:15

IKavanagh

6,089
11
42
47

answered Mar 14 '18 at 20:51

Alexis

11
2

SqlConnection.open is hanging

2 Answers2