[dba-VB] C# - rigorous programming

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