Wednesday, March 21, 2012

Open SQLConnection when Database Stopped

I'm hoping someone can explain to me exactly what the SQLConnection.Open() method does, especially when the database is stopped. I'm trying to include some error processing in my program, specifically to ensure the database is up and running. But even with the database stopped, the Open() statement works fine. Later, when I try to read from the database, I get the error. I'd like to stop it before it gets any further. Why is the Open() statement "working" even with the database stopped?Hmm. Interesting about the Open. Are you sure you aren't swallowing the exception that might arise? I've gotten exceptions when the db wasn't running. Why don't you post your code so we can see if that might be the problem?

If you want to be sure that the connection is open--which is good practice since connections can be lost--you can use the SqlConnection object's State property. It returns a ConnectionState Enumeration value.

Don|||I'm following through the code with debug, so I can see the Open() work successfully, then see the error occur on the Read(). But I noticed something only works the first time after the database has been stopped, and I think it may be using a pool connection that has remained open. The next time an Open() is attempted, it does give me an error. So closing a database is not disposing of any open connections, it would appear. Does this sound correct? If so, is there any way around it?|||Ah. That could indeed be the problem. What happens when you check the connection's state before you use it, but after the database stops? Itshould give you something other than open.

OTOH, do you stop your db often?

Incidentally, you could capture the SqlException you're getting and do something in response, such as notify the user, wait a few seconds and try again, whatever makes sense for the application.

Don|||The connection's state shows Open after I've successfully opened it once, then closed the db. It continues to show Open until the next db operation fails (e.g., Read), then it changes to Closed. So testing for it won't work either. I think your second idea is best, just respond to the type of SQLException that is raised and don't try to do too much with the Open condition.

No, we don't purposely stop the db, but I want to be able to exit the application on our terms regardless of the problem.

Thanks for your help and comments!|||I want to be able to exit the application on our terms regardless of the problem.

Quite wise.

Take care,

No comments:

Post a Comment