SqlConnection Broken Notification

Is it possible to get a notification of some sort from the SqlConnection class if the connection is broken in any way?  This includes being broken from a server restart or say a manual kill of the connection process.
LVL 1
HandelITAsked:
Who is Participating?
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.

JimBrandleyCommented:
If you are using a connection pool, connections that become invalid are purged from the pool, without any notification to the pool consumer. If you subsequently attempt to connect, and the server is still down, you will get an exception with a message indicating that the connection could not be established.

Jim
0
HandelITAuthor Commented:
We are not using SQL Pooling, we are actually doing our own.  And if something occurs in between statement execution that closes the connection we wanted to find a way to know that.  But as far as you know there is no other way except to send a statement and let if fail?
0
JimBrandleyCommented:
I am working in a web environment, so have not worked without pooling. I will do some searching, but I have never seen anything to get state information from a connection other than Open or Closed.

Jim
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JimBrandleyCommented:
It looks like MS is thinking about your need. Thake a look at:

http://msdn2.microsoft.com/en-us/library/system.data.connectionstate(VS.71).aspx

Note the BROKEN state. Also, note that (in VS2005) it is marked "Reserved for a future version of the product".

Jim
0
JimBrandleyCommented:
I just dug through some of my books, and one suggests that BROKEN is a valid state in .Net 2.0. The comment in the link above could be outdated. It's worth a try. If you try it, be aware that the various state enum members can be ORed together in the result returned by the connection.State property.

Jim
0
HandelITAuthor Commented:
We are going to try it and let you know.
0
JimBrandleyCommented:
I'll keep my fingers crossed.

Jim
0
HandelITAuthor Commented:
Looks like it doesn't work.  What happens is the next time you try to use it the State gets changed to Closed and the StateChange event is fired, however it's too late to try and restore the connection because it doesn't get closed until it fails trying to run the DataAdapter.
0
JimBrandleyCommented:
Sorry - That was my only hope.

Jim
0
monarch_ilhanCommented:
I agree with Jim, It is not a better solution to discard SqlConnection Pool. But specially in web environment. If it is a desktop application you can prefer a static variable for connection. But even in this case you might use connection pool. Because in connection pool you can define your min and max open connection to the server. Just by setting in connection string.
And to be aware of broken connections, you can handle it by using a timer in a static class. Then you can query a sysdate from db which wont bring overload to the db. If exception occurs, you can raise an event.
0

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
HandelITAuthor Commented:
monarch_ilhan,

Do you mean SELECT GETDATE()?  Also we are using our own connection pooling inside of the application already because some IT administrator's won't allow pooling to run on the SQL Server.  Therefore we already only keep 3 connection open to the server at one time.
0
monarch_ilhanCommented:
Yes. It was oracle version of select getdate() .
If you use this dummy query for checking availability of connection to db, by getting a return value from db, you will assure that you  have a live connection to db. Even you dont have to read the returned value. Execute this query in a try catch block, and raise an event in catch block. otherwise sleep until next tick. And run this code as another thread than worker thread to avoid freezing the application (at least the value of timeout parameter of connection)
0
HandelITAuthor Commented:
That's kind of similar to what we ended up doing, our application is quite complicated so it took a bit more than just a background thread, but thank you all very much for your assistance in this matter.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.