Best way to check OleDbConnection state

Question

Apparently (MSDN) sometimes OleDbConnection.ResetState() does nothing, so even if the connection fails OleDbConnection.State will still be set to Open. I am looking for best workaround for this, so that when I check connection state I can avoid raising exceptions (as long as connection hasn't failed between the last check and usage attempt).

Is there nothing better than sending a "useless" sql statement each time just to see if an exception is thrown? How do you make sure your connection is open before you actually use it?

Answer 1

In your case I would do the following :

1. Don't bother being sure if the connection is "really" open BEFORE using it : it will be most of the time anyways, and you'll spare a lot of useless roundtrips to the server
2. BUT check for any exception each time you use the connection (create helper methods to avoid copy/paste here)
3. If you have an exception, then send your "useless" statement to check the database connection "real" state. I would do that because the type of exceptions you can get when the connection to the server is lost can sometimes be quite surprising (depending on what is happening at the time the connection is broken)

Hope this helps.