ADO.NET infomessage event not firing when connection is broken

  • Thread starter Thread starter itp
  • Start date Start date
I

itp

Hello,

I have looked at several ADO.NET resources and they all
say to use the infomessage event handler. I have added the
code to my form but when I disconnect the network cable,
it does not fire.

I have also read that infomessage is for server error
messages below 10. I have tested this by creating a stored
proc that raises a custom error of severity 8. When I
execute this, it does fire the infomessage event handler,
so I know that my code works.

But it does not catch broken connections! Inside VB.NET,
the Intellisense mouseover comment says "This value is
reserved for future versions of the product."

Is the "broken" state not supported in ADO.NET?

Anyone else have this issue or any info on it?

Regards
 
If you have a broken connection, the server will not be raising an error or Informational message to the client. It is the responsibility of the client to know if the connection is
bad or not. So the event will not fire if your connection is bad because of a disconnected cable or a database server is down. If this happens then all the connections in the
pool will be bad even after reconnecting the cable or the server comes back on line.

Connection pooling is On for all .NET Data Providers. If your App is not a Web App and only one user is using it then you don't need Connection pooling. You can turn it Off
by adding a parameter (Pooling = No) to your connection string.

If you need the Connection Polling then the only possible thing is to add code to your App so that you trap for errors when you call cn.Open and then decide what to do? For
every exception you get, the pool would know that the connection is bad and then remove it from the pool. Eventually, all bad connections will be removed and then
replaced with good ones. If the exception keeps coming indefinitely, then your issue is not with the Connection pooling.

I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
 
Hi Hussein,

Thank you for your reply. You're absolutely right, the
server cannot send a message to the client since the
connection is broken. I do realize however that I didn't
quite lay out the root of my problem...here it is

I have error handling code that checks if a connection is
open. If so, it executes a command. If not, it tries to
reopen it. The problem is that when the network goes down
or the server goes offline, the connection status still
returns "open". As such, my code goes on to execute the
command, and it errors out. Checking of the connection is
broken doesn't do much good either, as it also returns
false (0) when it should say that it is broken (true:1).
There should be some kind of constant polling or
percolation of a broken connection event to the provider,
and in such case, the "broken" property should be reset to
true (1).

Right now, the status returned is misleading. If it can't
say "broken", it should at least say that it is "closed".

Can the provider be fixed so that it will return the right
value?

Regards


-----Original Message-----

If you have a broken connection, the server will not be
Click to expand...
raising an error or Informational message to the client.
It is the responsibility of the client to know if the
connection is
bad or not. So the event will not fire if your
Click to expand...
connection is bad because of a disconnected cable or a
database server is down. If this happens then all the
connections in the
pool will be bad even after reconnecting the cable or the server comes back on line.

Connection pooling is On for all .NET Data Providers. If
Click to expand...
your App is not a Web App and only one user is using it
then you don't need Connection pooling. You can turn it
Off
by adding a parameter (Pooling = No) to your connection string.

If you need the Connection Polling then the only possible
Click to expand...
thing is to add code to your App so that you trap for
errors when you call cn.Open and then decide what to do?
For
every exception you get, the pool would know that the
Click to expand...
connection is bad and then remove it from the pool.
Eventually, all bad connections will be removed and then
replaced with good ones. If the exception keeps coming
Click to expand...
indefinitely, then your issue is not with the Connection
pooling.
I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft
Click to expand...
Strategic Technology Protection Program and to order your
FREE Security Tool Kit, please visit
 
I think this is addressed in Whidbey.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Yes, Bill is correct... several enhancements to the connection pooling are added in Whidbey.

What I said to workaround this is to have a loop and if you trap for errors on cn.Open then you keep calling cn.Open without the user knowing. This will help the pooling
mechanism to clean the dead connections because for every exception on cn.Open, it would know that the connection is bad and will destroy it. If you don't change the
default Max Pool Size then it is going to be = 100 so the pool will not have more than 100 connections at any time. So setting the loop to 100 at most will help clean 100 dead
connections. Checking the State of the connection is not what you want to check because it is set when the pool establishes and I don't think it changes when they become
bad.



Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
 
Back
Top