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
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
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.
brgdotnetcontractorAuthor Commented:
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.
käµfm³d 👽Commented:
Wrap it in a try/catch. To my knowledge, that is standard practice for database connections. My usual pattern:

try
{
    using (SqlConnection con = new SqlConnection("connection string"))
    {
        con.Open();

        using (SqlCommand cmd = new SqlCommand("query", con))
        {
            SqlDataReader reader = cmd.ExecuteReader();  // For example

            // get data out of reader and put in domain object
        }
    }
}
catch (SqlException)
{
    // Do something (maybe logging)
}

Open in new window

Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Learn More!
Jacques Bourgeois (James Burger)PresidentCommented:
With the proper right, you could check if the service is running through a ServiceController object and check its Status property. But in most environmnents, the user won't have the needed rights.

And it will tell you nothing about the availability of the connection. There are to many things to consider.

- The server might be down
- The network connection might not work
- There might be too many connections running on the server to enable a new one
- The user might not have the rights for the parameters that you are requesting

So, trapping the Exception is the best way to go as far as I am concerned.

If it's the time it takes to get the error that bothers you, most of that time is not consumed by preparing the SqlException object itself, but by the connection TimeOut that that is set to 15 seconds by default. Unless you are working in a situation where the server is very slow to answer, you can usually lower down that value in the Connect Timeout property of the ConnectionString.
brgdotnetcontractorAuthor Commented:
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.
Jacques Bourgeois (James Burger)PresidentCommented:
The application seems to hang because of the 15 seconds timeout. Set it to a lower value.
käµfm³d 👽Commented:
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.
Athar SyedCommented:
You could make the SqlConnection a private member of the class (or ASP page), then make a function to open the connection and return a Boolean to indicate status.

private class ClassName
{
  private SqlConnection m_oConn = null;

  private bool OpenDB(string connstr)
  {
    if(string.IsNullOrEmpty(connstr) return false;

    if(m_oConn == null) {
      m_oConn = new SqlConnection(connstr);
      m_oConn.Timeout = 10;  // for connection timeout
    }

       if(m_oConn.state == ConnectionState.Open)
         return true;

   try {
      m_oConn.Open();
      return true;
   } catch(Exception ex) {
     // log error here
      return false;
   }
  }

  private void DoSomething() {
    if(!OpenDB()) {
        // DB Connection failed to open. Handle the scenario
    }
    // all AOK. continue with the connection
  }

}

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
If you look at any big commercial software that works with a database in the background (ACCPAC and Sage for instance), or have ever seen work done at any big company, they all rely on the exception or error thrown after a preset timeout in order to detect if a connection is available. They would do otherwise if they could.

In a proper environment, unavailable connections are almost always detected that way, because servers typically run 24/7. So connections problems are usually not related to the unavailability of the server, but to other problems that can be detected only by trying to open a connection.

If your problem is an intermittend network connection to the server, then look at what happens if you try to open a file on the network from Windows Explorer. Do you think that you can to better than the OS? In such a situation, the problem is solved at the server side, not in the application.

Same thing if you have problems connecting because you have used up all the connections permitted by your licence. Either pay to get more licences, or check at the server level to see if connections are used properly. Some applications can keep connections opened when they no longer use them. In .NET, I still see a lot of programmers who open a connection when they start an application and close it only when the application closes. This is not the way to go. This can lead to connection problems for other applications.

Solve the problem at its root, and a missed connection trapped in a Try...Catch will be so rare that it won't be a problem anymore.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.