SQL EFFICIENCY: using directive vs try/catch

[R Reyes]

Hi there!

Problem:
I am refactoring all my database code because I noticed every few days
sqlservr.exe would show as Mem Usage of 500,000k in the task manager and
crash our server.

Solution:
I wanted to be able to use the .Dispose() command and found the "using"
directive would do the trick. I have refactored a few try/catch/finally
blocks but found there is no way to CATCH the error and that is where I
usually write to an error log on the server describing the error.

For example here is some code:
using (SqlConnection sqlConn = new
SqlConnection(Constants.DATABASE_CONNECTION_STRING))
{
using (SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn))
{
sqlConn.Open();
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
// Some database code here
}
}
}
}
}

See, there is no try/catch block AT ALL!

Question:
If I code things this way, will I always miss out on catching errors? In
any program, the error could happen anywhere. A "normal" way would just be
to use the try/catch at the sqlReader.Read() part, but what if the error
happens at sqlConn.Open(), then we'd miss the error completely and the
program would crash right?

So, as far as .Dispose() goes this seems the way to go for resource/garbage
handling, but if you can't trap errors why would someone want to use this?
Again, If we received an error on sqlConn.Open() wouldn't the program crash?
There is see no catching the program crash unless we got rid of the "using"
directive right?

Please advise. Thanks in advance.

R Reyes

 

[Lasse Vågsæther Karlsen]

Hi there!

Problem:
I am refactoring all my database code because I noticed every few days
sqlservr.exe would show as Mem Usage of 500,000k in the task manager and
crash our server.

Solution:
I wanted to be able to use the .Dispose() command and found the "using"
directive would do the trick. I have refactored a few try/catch/finally
blocks but found there is no way to CATCH the error and that is where I
usually write to an error log on the server describing the error.

For example here is some code:
using (SqlConnection sqlConn = new
SqlConnection(Constants.DATABASE_CONNECTION_STRING))
{
using (SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn))
{
sqlConn.Open();
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
// Some database code here
}
}
}
}
}

See, there is no try/catch block AT ALL!

Question:
If I code things this way, will I always miss out on catching errors? In
any program, the error could happen anywhere. A "normal" way would just be
to use the try/catch at the sqlReader.Read() part, but what if the error
happens at sqlConn.Open(), then we'd miss the error completely and the
program would crash right?

So, as far as .Dispose() goes this seems the way to go for resource/garbage
handling, but if you can't trap errors why would someone want to use this?
Again, If we received an error on sqlConn.Open() wouldn't the program crash?
There is see no catching the program crash unless we got rid of the "using"
directive right?

Please advise. Thanks in advance.

R Reyes

using (a)
{
...
}

is equivalent to:

a
try
{
...
}
finally
{
a.Dispose();
}

To answer your questions specifically:

1. an exception in the block inside the using statement will be
propagated up to any containing try/catch blocks, as though you didn't
use using... ie. "using" does not change how exceptions are handled here
2. Why would anyone want to use this? Do you catch exceptions all over
the place or only where you know how to handle them?

There's nothing preventing you from just using a normal try/catch block
to catch and log your exceptions.

 

[Jon Skeet [C# MVP]]

Problem:
I am refactoring all my database code because I noticed every few days
sqlservr.exe would show as Mem Usage of 500,000k in the task manager and
crash our server.

Solution:
I wanted to be able to use the .Dispose() command and found the "using"
directive would do the trick. I have refactored a few try/catch/finally
blocks but found there is no way to CATCH the error and that is where I
usually write to an error log on the server describing the error.

Well, you can add a try/catch within the using statement (or outside
it, if you want).

Do you really want to catch the error at that point though? I tend to
catch errors much higher up the stack than database connectivity. Can
you really recover from this exception? If not, just let it bubble up
to somewhere that is capable of logging the exception and reporting it
to the user in some friendly fashion, then continuing in whatever way
is most appropriate.

 

[R Reyes]

Thanks for you response.

To answer your question:
Usually if an error occurs - say after an insert/update statement fails - I
use the CATCH to write the SQL statement that just failed to an error log so
that I can later examine the query and why it didn't work. Also, this is how
I let the user know that their "SAVE" command (or query for us programmers)
failed.

If I just used the "using" directive, I wouldn't have a way to tell the user
if the query was successful or not, since all the "using" directive would do
is execute then close the connection (try/finally) but not return 1 or 0
(fail/success).

 

[R Reyes]

Hi Jon, as I let Lasse know in my last post to him the only reason why I want
to catch the error is to write the SQL to an error log so that I can see what
went wrong.

catch (Exception ex)
{
Logs.errorlogAppend("\r\n" + DateTime.Now + ": Failed on
MyQueryFunction(SQL=" + SQL + ").\r\nUser: " + Windows.getUserIdentity() +
"\r\nError: " + ex.ToString());
}

// Note: Windows.getUserIdentity() is the function to retrieve windows user

Maybe I am just choosing the wrong place to write to an error log, but if
not, this is why I need CATCH blocks and I guess I could just use a wrapper
try/catch around all my database connection "using" blocks.

 

[Lasse Vågsæther Karlsen]

Thanks for you response.

To answer your question:
Usually if an error occurs - say after an insert/update statement fails - I
use the CATCH to write the SQL statement that just failed to an error log so
that I can later examine the query and why it didn't work. Also, this is how
I let the user know that their "SAVE" command (or query for us programmers)
failed.

If I just used the "using" directive, I wouldn't have a way to tell the user
if the query was successful or not, since all the "using" directive would do
is execute then close the connection (try/finally) but not return 1 or 0
(fail/success).

If you don't handle the exception, then yes, you will get a more generic
error message.

However, using has nothing to do with error handling, it has to do with
resource management.

If you need to handle exceptions, use a try/catch.

 

[Jon Skeet [C# MVP]]

Hi Jon, as I let Lasse know in my last post to him the only reason why I want
to catch the error is to write the SQL to an error log so that I can see what
went wrong.

catch (Exception ex)
{
Logs.errorlogAppend("\r\n" + DateTime.Now + ": Failed on
MyQueryFunction(SQL=" + SQL + ").\r\nUser: " + Windows.getUserIdentity() +
"\r\nError: " + ex.ToString());
}

// Note: Windows.getUserIdentity() is the function to retrieve windows user

Maybe I am just choosing the wrong place to write to an error log, but if
not, this is why I need CATCH blocks and I guess I could just use a wrapper
try/catch around all my database connection "using" blocks.

It would be better to have fewer catch blocks, but have them higher up
the tree.

You mentioned in your previous response that you were returning 1/0 to
signify success/failure - that's the "old school" error handling
mechanism. The .NET idiom is to not return anything if it would just be
success/failure, and let exceptions indicate failure.