tags:

views:

41

answers:

1

I have this simple code to test that a DB is ready:

Function testlocalcon() As Boolean
    Dim constr As String = _clconstr

    Try
        Using t As New SqlConnection()
            constr = constr & " ; Connect Timeout=1"

            If Not t.State = Data.ConnectionState.Open Then
                t.ConnectionString = constr

                t.Open()
                If t.State = Data.ConnectionState.Open Then
                    Return True
                Else
                    Return False
                End If
            Else
                Return True
            End If
        End Using
    Catch ex As Exception
        Return False
    End Try
End Function

I do not want to execute a query, just to check the connection, but no matter what the time out parameter is ignored. I search here (Stackoverflow) and internet and found nothing in how to fix this.

Any one else have this problem? Or, are there any other ideas on how to let the application know that the DB is ready?

A: 

I appologize for the code differenc but this is C# and I have used it in the past. Its pretty simple and should be readable.

private SQLServerConnection
private SqlConnection _SQLServerConnection;
public SqlConnection SQLServerConnection
    {
        get
        {
            return _SQLServerConnection;
        }
        set
        {
            _SQLServerConnection = value;
        }
    }

private void SetSQLServerConnectionString (string sqlServerName, string databaseName, string saPassword, int connectTimeout)
{
    SQLServerConnection = new SqlConnection("Password=" + saPassword + ";Persist Security Info=True;User ID=sa;Initial Catalog=" + databaseName + ";Data Source=" + sqlServerName + ";connection timeout=" + connectTimeout.ToString(CultureInfo.InvariantCulture));
}

internal bool TestSQLServerConnection(string sqlServerName, string databaseName, string saPassword, int connectTimeout)
{
    try
    {
        SetSQLServerConnectionString(sqlServerName, databaseName, saPassword, connectTimeout);
        SQLServerConnection.Open();
        return true;
    }
    catch (SqlException e)
    {

        return false;
    }
    finally
    {
        SQLServerConnection.Close();
    }
}
John Hartsock
Hey John Thanks, but your code is exactly the same as mine, it does not works, it just ignores the timeout value ... I copy your code too and use it , and yet the timeout parameter is ignored.. Any other thougts?
carlos
I beg to differ I have been using this code for installers to verify that the server exists and it works quite well.
John Hartsock
just to ask...why are you even worried about the timeout if you just checking if the server is available?
John Hartsock
also of note if you set connection timeout to 1 then it only tries for 1 second to connect to the server. "Connect Timeout/Connection Timeout: The length of time (in seconds) to wait for a connection to the server before terminating. The default value is 15." quoted from http://articles.techrepublic.com.com/5100-10878_11-6084879.html
John Hartsock
Hi John !!! Thanks for keeping with me on this .. I am worried because I would like to check fast, if the DB is down I do not want the user to wait 15 to 60 sec to know (the applicacion displays a message saying "Wait .. checkin DB " ) .. is just a matter of user friendly ..In the other hand, I already tryied everything, and not matter what I puton the Timeout, it always takes from 15 to 60 sec to abort the connection operation ... :( I copied your code and use it , and it did not work :( ... some other thougts?
carlos
Carlos where is your db located, is it same box as this application? if you want to see if you can log on to a SQL box you must account for latency on your network.
John Hartsock
Hi Jhon the db is in the same pc the program is running, and i can log perfectly .. :( ... I already try moving the sql to another server and it keeps working the same way ... well maybe is a bug :( ...Thanks a lot !
carlos
I am seeing the same problem. I am on a fast local network so I don't want to wait 30 seconds before the attempted connection fails. Why doesn't connection timeout cause it to fail faster?
BlueMonkMN