Verifying connection to DB

[yablonka]

How can verify that my connection is still alive after the Connetion.Open method was already called succefully?

Thank you.

 

[archer_coal]

RE

Dim conn_state as string
conn_state = Myconnection.State
Label1.Text = conn_state

1 = open
0 = closed

 

[yablonka]

Thanks but, I tried it and as far as I could see Connection.State does not update, i.e. once the connection is established the Connection.State gets the value of "1" and then if the connection is lost (because of network problems or anything like that) Connection.State does not contain "0" but remains "1".

 

[dsgreen57]

I hear where you are coming from this one... and it is a right pain in the arse the only way I found to get around it is to write a connection class, which maintains your connection and have a method such as GetConnection()... within this attempt some operation, which accesses the database, wrap this in a try block and then re-open a connection if it fails.

If someone has a solution other than this then I would love to hear it as it has been driving me mad trying to find a better way.

 

[wyrd]

You dont have to write some custom class for this. Theres a ConnectionState enumerator which you can use.

Valid values of ConnectionState are Closed, Connecting, 
Open, Executing, Fetching, and Broken.

If (cn.State And ConnectionState.Open) <> 0 Then
    cn.Close
End If

On top of this, theres also the StateChange event you can use that triggers whenever the state of a connection changes.