[dba-VB] c# so FINALLY ?

Shamil,

Thanks for your reply.  C#, .Net and Visual Studio are very complex and I have so much to learn. 
And I have to make a living while I am learning, and I am trying to teach a young fellow that works 
for me what little I know as well.

I need more information from you.

I have two specific things I am trying to implement and make sure that I "cleanup" correctly.  The 
first is that I have a ton of places in my code where I am using SQL Server records for table driven 
processes.  As such I need to:

1) Work with data readers, looping through records.
2) Work with command objects doing .ExecuteNonQuery() to perform updates on flags.

In both these scenarios I want to make sure that I open the connection, open the command object, do 
whatever the command object is going to do, close the connection, and cleanup the command object. 
And I want (am trying) to do this in a single place so that I get consistent object handling / 
cleanup. So, for example I use a static method of a class:

         public static bool ExecNonQuery(string strCnn, string strSQL)
         {
             SqlConnection mCnn = null;
             SqlCommand myCommand = null;
             try
             {
                 mCnn = new SqlConnection(strCnn);
                 mCnn.Open();
                 myCommand = new SqlCommand(strSQL, mCnn);
                 myCommand.ExecuteNonQuery();
             }
             catch (SqlException)
             {
                 throw;
             }
             catch (Exception)
             {
                 throw;
             }
             finally
             {
                 if (myCommand != null)
                 {
                     myCommand.Dispose();
                     myCommand = null;
                 }
                 if (mCnn != null)
                 {
                     mCnn.Close();
                 }
             }
             return true;
         }

 > Dispose does all the clean-up for SqlCommand object instance...

Is my code above correct then?

 > It doesn't make sense to have any SUCCESS/FAILURE return values from this method as it has throw 
calls inside of catch blocks

OK.  So if the .ExecuteNonQuery fails to work, the throw happens but...

 > Again follow KISS-principle: - log exceptions as close as possible to the point where they appeared;

If I log in the catch block do I really want to then throw the error?  What is the calling procedure 
going to do with that?

It seems logical to me to do the logging in the catch block and then NOT throw the error, but rather 
simply return a boolean false saying that the process failed.  The calling procedure can then use 
the boolean to decide what to do.  My understanding is that if I throw the error up to the caller 
then it ends up in its catch block, which may throw the error etc.

I am good with handling the error close to the source but it just seems that implies NOT throwing 
the error on up.

And of course I need a reliable logger so that I can go see any errors.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi John --
> 
> Dispose does all the clean-up for SqlCommand object instance...
> 
> It doesn't make sense to have any SUCCESS/FAILURE return values from this
> method as it has 
> 
> throw
> 
> calls inside catch blocks...
> 
> Such methods do usually return "RecordAffected" value if that needed by the
> callers, some other execution stats, or nothing...
> 
> <<<
> Or should I just continuously throw 
> it all the way up to the top 
> and log everything at the very top?
> Again follow KISS-principle: 
> - log exceptions as close as possible to the point where they appeared;
> - return to the highest position in the call stack where you can gracefully
> continue your application execution, or quit your app with a user-friendly
> error message...
> 
> Logging all the way up to the top of the call stack would be similar to 
> 
> On Error GoTo ...
> 
> VB6/VBA approach - that one proved itself to be a professional approach for
> VB6/VBA but for C#/VB.NET it's usually considered as a timid, pavid,
> apprehensive and too expensive (in many areas) to be true approach, e.g. do
> you take into account that try/catch/finally do influence your code
> execution speed so putting them everywhere could degrade your code
> performance?...
> 
> Code instrumentation/logging/profiling is usually done while going down to
> the call stack, and .NET has special instrumentation classes, which can be
> used to organize as detailed as needed logging depending on current
> application settings...
> 
> After all - your application usually work or crash when deployed? - if the
> latter then whatever advanced logging/code instrumentation approach you'll
> use you'll anyway loose your customers (pun not intended)...
> 
> Be courageous! Forget "VB6/VBA timidity", recall your best times when you've
> been a "code cowboy" but do use Unit Testing (at least) - then you'll make
> your coding style light, your code running lightning fast and still being
> "bullet-proof"...
> 
> Thank you. :)
> 
> -- Shamil