[dba-VB] C# - rigorous programming

Michael,

 > I see you have made a lot of progress over the weekend.

I made awesome progress on two different fronts.  I got NLog working (to a file, not to SQL Server) 
and I got some stuff figured out on C# cleanup.  I believe in logging, both errors and progress.  I 
haven't had logging though, only specific application control stuff.

 > I think the reason is that the finalizer is indeterminate. So the 'close it when you no longer 
need it' rule still applies.

I understand "close when you no longer need it".  I did think that the the Dispose method forced the 
garbage collector to take care of that specific object right then and there.  I thought that in the 
case of SQL Server or other database objects, that immediate cleanup would be a good thing.

John W. Colby
www.ColbyConsulting.com


Michael Maddison wrote:
> Hi John,
> 
> I see you have made a lot of progress over the weekend.
> 
> Now that you have moved this method into its own class, make sure you
> don't ...
> ' call Close or Dispose on a Connection, a DataReader, or any other
> managed object 
> in the Finalize method of your class' which is what I think MS was
> saying in you previous message.
> I think the reason is that the finalizer is indeterminate. So the 'close
> it when you no longer need it' rule still applies.
> 
> Cheers
> 
> Michael M
> 
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Monday, 26 July 2010 6:08 AM
> To: VBA
> Subject: [dba-VB] C# - rigorous programming
> 
> I had a bunch of code to open connections, command objects and a reader,
> do something with the reader and then close it all.  it is not a trivial
> amount of code if you are going to try and wrap it in a try / catch and
> properly close the stuff.  So I wrote the following code:
> 
>          public static SqlDataReader GetDataReader(string strCnn, string
> strSQL)
>          {
>              SqlConnection mCnn = null;
>              SqlCommand myCommand = null;
>              SqlDataReader myDR;
>              try
>              {
>                  mCnn = new SqlConnection(strCnn);
>                  mCnn.Open();
>                  myCommand = new SqlCommand(strSQL, mCnn);
>                  myDR = myCommand.ExecuteReader();
>                  return myDR;
>              }
>              catch (SqlException)
>              {
>                  throw;
>              }
>              catch (Exception)
>              {
>                  throw;
>              }
>              finally
>              {
>                  if (myCommand != null)
>                  {
>                      myCommand.Dispose();
>                      myCommand = null;
>                  }
>                  if (mCnn != null)
>                  {
>                      mCnn.Close();
>                  }
>              }
>          }
> 
> 
> Which basically just took the three pieces and wrapped them.
> 
> Unfortunately it doesn't work because the reader closes if the
> connection closes.  It seems that the reader only holds one record at a
> time and when the next record is asked for goes and gets it on demand.
> 
> But look at all of the code required to get around the C# syntax police.
> The objects have to be dimensioned before the try, the try has to wrap
> the object opens in case they fail (the point of the
> try) and the finally has to close everything back down correctly, but
> now have to be checked against null because they might in fact be null
> which would trigger another error trying to close an object that isn't
> opened.
> 
> So what's a poor boy to do?
> 
> I decided to build a class to do this and then use a property to expose
> the reader.  The class correctly cleans up when it is destroyed.  That
> seems to work.
> 
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>