How to limit the wait time for SqlConnection.Open()?

Question

I want to test if the connection string is correct, so I created a new SqlConnection, called its Open() method. But I have to wait a long time before it returns when the server/data_source part of connection string is wrong.

I tried adding connection timeout to the connection string, it didn't work; I tried open the connection in another thread, then I call Thread.Abort() after several seconds. None of them worked.

So what's the correct way to do this? Thanks.

Answer 1

Set the ConnectionTimeOut property before calling Open.

Answer 2

Duplicate. This has been answered here before: How to check if connection string is valid?

Answer 3

after researching, I found the solution ( http://www.improve.dk/blog/2008/03/10/controlling-sqlconnection-timeouts ), I modified it a little bit. If there's not exception thrown, then the connection string is valid.

        var alive = true;
        string error = null;
        var success = false;

        // ReSharper disable AccessToModifiedClosure
        // ReSharper disable UseObjectOrCollectionInitializer
        var thread = new Thread(() =>
                                    {
                                        try
                                        {
                                            var connection = new SqlConnection(connectionString);
                                            connection.Open();
                                            connection.Close();

                                            if (alive)
                                                success = true;
                                        }
                                        catch (SqlException ex)
                                        {
                                            if (alive)
                                                error = ex.Message;
                                        }
                                        catch (ThreadAbortException)
                                        {
                                        }
                                        finally
                                        {
                                            if (connection.State == ConnectionState.Open)
                                                connection.Close();
                                        }
                                    });
        // ReSharper restore AccessToModifiedClosure
        // ReSharper restore UseObjectOrCollectionInitializer
        thread.IsBackground = true;
        var sw = Stopwatch.StartNew();
        thread.Start();

        var timeout = TimeSpan.FromSeconds(3);
        while (sw.Elapsed < timeout)
            thread.Join(TimeSpan.FromMilliseconds(200));
        sw.Stop();

        if (!success)
        {
            alive = false;
            throw new Exception(error ?? "Connection timeout, please check the connection string.");
        }