How to test if a SqlConnection object is open
How can I test if the SqlConnection object is open?
My code looks similar to this; except that an exception will be thrown on line 2
1 Using connectionObject As New SqlCommand( ........)
2 connectionObject.Open()
3 If (connectionObject.State == ConnectionState.Open)
4 Then
End If
My code looks similar to this; except that an exception will be thrown on line 2
1 Using connectionObject As New SqlCommand( ........)
2 connectionObject.Open()
3 If (connectionObject.State == ConnectionState.Open)
4 Then
End If
kaufmed
Why are you maintaining a reference to a connection object? If you're worried about the overhead of creating a connection, then you should have connection pooling enabled.
ASKER
I am not worried about maintaining a reference to a connection object. Let me explain this at a high level. If my application has a database connection, then data will be displayed in a GridView control. If it does not have a database connection, (The network is down for example) then I need to prevent my application from crashing.
You see if the network connection to my database is down then the statement :connectionObject.Open()
will fail. It will fail miserably by throwing an exception. I would like to test if the connection exists before I attempt to open it. I don't give a hoot about connection pooling.
You see if the network connection to my database is down then the statement :connectionObject.Open()
will fail. It will fail miserably by throwing an exception. I would like to test if the connection exists before I attempt to open it. I don't give a hoot about connection pooling.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have tried the technique of catching the exception, however the application seems to stall and lockup for a while, which is a very irritating end user experience. I need to test beforehand if the database connection exists. Possibly some other Expert might have some good recommendations.
The application seems to hang because of the 15 seconds timeout. Set it to a lower value.
You may want to consider writing async code then. It's a topic that too vast for a comment, but the gist of it is that you sleep threads that are waiting for I/O so that your UI does not lock up whilst the application is doing nothing more than waiting for a response from the network. Have a look at the Async/Await documentation as well as the Channel 9 videos on the topic.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.