Link to home
Create AccountLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

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
Avatar of kaufmed
kaufmed
Flag of United States of America image

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.
Avatar of brgdotnet

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.
SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account