[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