Avatar of brgdotnet
brgdotnet
Flag 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
Visual Basic.NET

Avatar of undefined
Last Comment
Jacques Bourgeois (James Burger)

8/22/2022 - Mon
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.
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
kaufmed

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
brgdotnet

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jacques Bourgeois (James Burger)

The application seems to hang because of the 15 seconds timeout. Set it to a lower value.
kaufmed

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.