Proper Error Handling of SQL Connection opening

  • Thread starter Thread starter chigger
  • Start date Start date
C

chigger

Hopefully I can explain this properly. I once heard that putting a
SQLConnection.Open() in a Try Catch block was a bad idea. It was said that
it caused the system to be taxed to much. If being so, what is a proper way
to catch any errors from the open command of the SQL client?

Also, if any of you could provide links to sound topics on building proper
error handling in your vb.net application it would be much appreciated!


Thanks in advanced!

Chig
 
Chig,

I'm not aware of any problems using try...catch in this scenario. There is
some penalty from exceptions when they are thrown so they shouldn't be used
for normal program flow control, but in this situation I don't see how your
app can do anything useful anyway if it needs access to the database and
can't get it.
 
Chigger:

The whole concept of exception handling entails dealing with things that you
now may happen so you can respond to them. If there is ever a candidate
for a Try Catch block it's opening database connections. I have never heard
of any excess overhead associated with wrapping it in a try catch block, and
assuming that no exception is thrown, I seriously doubt that there's a
performance hit for simply wrapping the connection.open statement. Like
Ginny mentions, exceptions are costly but I can't believe there's any
discernable difference in performance (assuming a connection opens
correctly) between connection.open();
and try{connection.Open():}catch(System.Exception ex){}

And if an exception is thrown, that's the costly part..I don't think the try
catch is going to make a big difference if any at all. But what's the
alternative? If you don't catch it, it's going to propogate up the call
stack so there's no way to 'respond' to the exception. I have a scenario
where I try to open a connection and if I can't, I let the user select Try
Again, Work Offline, Change DB and Quit. There are reasons behind each of
these but the point is that I am responding to the exception and even if
there was a performance hit, it would have to be huge to offset the benefits
of letting the app continue. If you don't catch it, you're dead b/c nothing
else can really happen in a data driven app if you have no db.

In a nutshell, go ahead and wrap connection.open...it's the best alternative
I know of.
 
William,

With that being said (and it was great stuff) my delima now pertains
to understanding proper error handling and catching. I know that there
are thousands of way to catch errors and to dispose of them properly but
how?! Is there anything that you have came across in your internet
travels that shows the proper techniques for writing good general error
handling? I may sound a bit anal about it all but I honestly want to
produce good clean good that performs to a tee when it comes to handling
errors. I know that part of writing can get you in the end if you do not
do it well and with the future of my application becoming widespread I
really don't want to get caught so to say with my pants down.


TIA

Chig
 
Hi Chigger:

Thanks for the compliment. As far as resources on the subject go, the best
I've read (and I'm a .NET book junkie) has to be Jeffrey Richter's Applied
..NET Framework Programming
http://www.amazon.com/exec/obidos/tg/detail/-/0735614229/qid=1079041773/sr=8
-1/ref=pd_ka_1/102-0030869-0684145?v=glance&s=books&n=507846

The whole book is pure gold and if you have been doing Windows programming
for any amount of time, Richter is one of the most respected names in the
field so I take his suggestions seriously. He goes on a tirade about not
trapping System.Exception for instance, and has a lot of guidelines for what
Exception handling is and how to employ it.

I really oversimplified version of his thesis is this:

Don't trap exceptions just to trap them. You should be as specific as
possible, and do something when you trap them, namely respond to them.
Don't trap things you aren't going to respond to. So, if you had a proc
that only had MessageBox.Show("Hello"); there's no need to trap an
IndexOutofRangeException for instance. However, there are times when you
know that something 'could' happen, like trying to reference a file on a
network - the other machine could be down, the network could be down, your
network cable could be unplugged. So you may want to trap FileNotFound
exception (or, to be defensive, check for the existence in advance). WIth a
DB Connection on the other hand, you can't tell if you can open it without
trying to open it, so you'll want to trap this. When opening a file, you
can't tell if you can open it or not until you try...someone else may be
using it so trap that specific exception to.

And you don't have to try to trap things in every code block. That can lead
to misleading return values and hide serious logic problems. So often
you'll want to let the caller handle the problem (particularly if you are
developing code libraries).

He also recommends using Finally blocks very liberally b/c they are
guaranteed to execute.

Anyway, there are entire chapters of books dedicated to the subject so I
can't really synthesize it in one post. I'd really recommend picking up his
book b/c even though it's not a CF book, it's discussion of the theorectical
and inner workings of the framework are pure gold and you'll definitely get
a deeper understanding of what's going on behind the scenes.

HTH,

Bill
 
Back
Top