Question about best practices with SqlConnection, error handling and memory handling

  • Thread starter Thread starter Lars-Erik Aabech
  • Start date Start date
L

Lars-Erik Aabech

Hi!

I've been scratching my head for a while not getting there...
If you have a method that instanciates an SqlConnection, what is neccesary
to do in the errorhandling part to clean up connections and memory - if that
is neccesary to do at all :P
Consider the following, and you will see my dilemma:

public DataSet someMethod(object someParameter)
{
SqlConnection cn;

try
{
cn.Open("...");
//... some more code
}
catch(Exception ex)
{
// Will the framework do this by it self, or is this a good thing to
do?
if (cn.State != ConnectionState.Closed)
try { cn.Close(); } catch { }
// And this....
cn = null;
throw(ex);
}
}

If I didn't try/catch at all.. would the connection close if the error
occurs after Open(), and would the garbage collector clean up the resources
immediately?

(ok, I could read a book - feel free to reccommend, but please help me
understand this too ;) )

Lars-Erik
 
Hello Lars-Erik,
I've been scratching my head for a while not getting there...
If you have a method that instanciates an SqlConnection, what is
neccesary
to do in the errorhandling part to clean up connections and memory -
if that
is neccesary to do at all :P
Click to expand...

Yes, you will need to do it.
Consider the following, and you will see my dilemma:
public DataSet someMethod(object someParameter)
{
SqlConnection cn;
try
{
cn.Open("...");
//... some more code
}
catch(Exception ex)
{
// Will the framework do this by it self, or is this a good thing to
do?
if (cn.State != ConnectionState.Closed)
try { cn.Close(); } catch { }
// And this....
cn = null;
throw(ex);
}
}
Click to expand...

In this specific example, you are just rethrowing the error, so it may make more sense to eliminate the catch block in favor of a finally. Example:

SqlConnection cn = null;
try
{
cn.Open("...");
// ... some more code
}
finally
{
if (cn != null && cn.State != ConnectionState.Closed)
{
try { cn.Close(); } catch {}
}
}

By using the finally block your connection will get closed irregardless of success or failure.
If I didn't try/catch at all.. would the connection close if the error
occurs after Open(), and would the garbage collector clean up the
resources immediately?
Click to expand...

No.
 
This is nice :)

But how would I know if the "//.. some more code" part failed? Actually I
didn't write the example as I plan, I'd pass a dataset as a reference, fill
it, and return true/false depending on the result. How would I know I should
return false if something fails? Would this be good practice?:

bool bRet = false;
SqlConnection cn = null;
try
{
// open and fill
bRet = true;
}
finally
{
if (cn != null && cn.State != ConnectionState.Closed)
{
try { cn.Close(); } catch {}
}
}
return bRet;

And why didn't you include cn = null after catch {}? Not neccesary, or did
you forget? ;)
BTW - is cn = null better than using the garbagecollector, or the same?

L-E

Matt Berther said:
Hello Lars-Erik,


Yes, you will need to do it.


In this specific example, you are just rethrowing the error, so it may
Click to expand...
make more sense to eliminate the catch block in favor of a finally. Example:
 
Hello Lars-Erik,
This is nice :)
Thanks!

But how would I know if the "//.. some more code" part failed?
Actually I didn't write the example as I plan, I'd pass a dataset as a
reference, fill it, and return true/false depending on the result. How
would I know I should return false if something fails? Would this be
good practice?:

bool bRet = false;
SqlConnection cn = null;
try
{
// open and fill
bRet = true;
}
finally
{
if (cn != null && cn.State != ConnectionState.Closed)
{
try { cn.Close(); } catch {}
}
}
return bRet;
Click to expand...

This will not work the way you are anticipating, as the exception will bubble up without a catch block. In this case, you will want to add an catch block to set bRet to false. However, you will want to continue to leave the connection closing in the finally block so that it gets executed irregardless of success or failure.
And why didn't you include cn = null after catch {}? Not neccesary, or
did
you forget? ;)
BTW - is cn = null better than using the garbagecollector, or the
same?
Click to expand...

The GC should pick up the cn variable as soon as it goes out of scope, so the cn = null is really unnecessary.
 
This will not work the way you are anticipating, as the exception will
bubble up without a catch block. In this case, you will want to add an catch
block to set bRet to false. However, you will want to continue to leave the
connection closing in the finally block so that it gets executed
irregardless of success or failure.

But if bRet allready is false, and something fails, bRet will never be set
to true. So the result will be false, won't it? What I won't know though is
wether the error occured before or after the dataset is filled. Which would
be the same with a catch block... no?
The GC should pick up the cn variable as soon as it goes out of scope, so
Click to expand...
the cn = null is really unnecessary.

Nice :) I've never felt quite sure of that..

Lars-Erik
 
Hello Lars-Erik,
But if bRet allready is false, and something fails, bRet will never be
set to true. So the result will be false, won't it? What I won't know
though is wether the error occured before or after the dataset is
filled. Which would be the same with a catch block... no?
Click to expand...

Yes, except that your 'return bRet' line will never be hit, since the exception will bubble itself up. You have a choice to handle the exception in this method, or allow the calling method to handle the exception.

Theoretically, you could even do something like this:

SqlConnection cn = null;
try
{
// open and fill
return true;
}
catch (SqlException)
{
return false;
}
finally
{
if (cn != null && cn.State != ConnectionState.Closed)
{
try { cn.Close(); } catch {}
}
}
 
Hi again :)
Yes, except that your 'return bRet' line will never be hit, since the
Click to expand...
exception will bubble itself up. You have a choice to handle the exception
in this method, or allow the calling method to handle the exception.

What do you mean "bubble itself up"? I thought the code after finally would
be executed, exception or no exception...
Theoretically, you could even do something like this:

SqlConnection cn = null;
try
{
// open and fill
return true;
}
catch (SqlException)
{
return false;
}
finally
{
if (cn != null && cn.State != ConnectionState.Closed)
{
try { cn.Close(); } catch {}
}
}
Click to expand...

I'm quite positive I've tried this (i have to do it again though) and
debugged, finding out that finally won't be called if I do return in try or
catch. And the code after finally would be executed as long as I don't throw
and error - I thought :|

Have to try this later or tomorrow, will you keep watching the thread in
case I see something in the same context that bugs me? :P
Tnx for the input so far, though!

L-E
 
Hello Lars-Erik,
What do you mean "bubble itself up"? I thought the code after finally
would be executed, exception or no exception...
Click to expand...

Right, and it will. However, if you dont handle the exception in your method, then the calling method will have to.
I'm quite positive I've tried this (i have to do it again though) and
debugged, finding out that finally won't be called if I do return in
try or catch. And the code after finally would be executed as long as
I don't throw and error - I thought :|
Click to expand...

According to the docs at: http://msdn.microsoft.com/library/d...s/csref/html/vclrfthetryfinallystatementx.asp, control is always passed to the finally block regardless of how the try block exits.
 
Right, and it will. However, if you dont handle the exception in your
method, then the calling method will have to.

OK, I think understand what you mean by bubbling. It's when a method _don't_
handle an exception and the error is thrown automatically, right? So the
calling method could do a try/catch and handle the error..?
According to the docs at:
Click to expand...
http://msdn.microsoft.com/library/d...s/csref/html/vclrfthetryfinallystatementx.asp,
control is always passed to the finally block regardless of how the try
block exits.

According to the docs you refer to, control is always passed to the finnaly
block regardless if an error is thrown or not, but not (it doesn't say) if
return is executed in either the try or catch block. Try stepping through
this code: (I just did..)

private bool doTryCatchTest()
{
bool bRet = false;
object o = (object)"";
try
{
o = ((int)o) + 1;
bRet = true;
}
catch
{
bRet = false; // Really no need to do this either ;)
}
finally
{
o = null;
}
return bRet;
}

Actually, the finally block isn't even neccessary here to achieve the
purpose..

But anyway, you'r general approach to my problem is greatly appreciated :)
Nice way to do it... Tnx! :)


Lars-Erik
 
Hello Lars-Erik,
OK, I think understand what you mean by bubbling. It's when a method
_don't_ handle an exception and the error is thrown automatically,
right? So the calling method could do a try/catch and handle the
error..?
Right...

But anyway, you'r general approach to my problem is greatly
appreciated :) Nice way to do it... Tnx! :)
Click to expand...

You're welcome...
 
Back
Top