Testing SQL Connection String - Runtime

sa Eish

I have a scenario where the program needs to have a variable connection string for the SQL server. The string is combined a collection of: • ServerName • SqlDatabase Name • User Name • Password • Connection Timeout Value Every time the connection string change it needs to validate the string and other operations needs to run. The problem comes in when the connection string has incorrect information. I have tried multi threads as I want the users to continue working on other functions while the system test the connection, but if the connection string generate an error it overpower all the threads and stall the system in any case… Is there a better why and faster why to test the connection, with out letting the user/system wait for response SQL connection timeout? PS: Reducing the timeout doesn’t help as the network has at certain times slow response times. This all happens in RunTime... **************************************************************************** I am not english so leave my grammer and spelling alone... :Mad: :Cool: ****************************************************************************

Kschuler

I'm not sure if it is faster, but you could test the SQL connection by using a Try/Catch block to see if the connection can be opened before you actually use the connection. It would look something like this:

   Try
            iconn.Open()
            boolReturn = True
        Catch ex As OleDb.OleDbException
            boolReturn = False
        Catch ex As SqlClient.SqlException
            boolReturn = False
        Catch ex As Odbc.OdbcException 
            boolReturn = False 
        Catch ex As Exception
            boolReturn = False
        Finally
            'Always close connection
            If iconn.State = ConnectionState.Open Then
                iconn.Close()
            End If
        End Try

Hope this helps.

sa Eish

I am testing the connection string by opening it and then closing the connection which has been assigned the function of testing. The problem comes in when u open the connection and the server is not there. It stalls the whole system for 30 seconds (Default Time out).

Try
	_conn.ConnectionString = ReconfigConnString()

I am looking for an idea where the system can test the connection AND do the other functions that needs to be complete before the form close. In multi threading the connection thread will over power the other threads and will cause one of two things. Deadlocks or either full system stalling until time out.:cool: System complete...bug free? I'll find a bug. :zzz: