• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Correct way to Close/De-reference an object called from another class?

I just need to know if the code below is going to successfully close/dereference/release the resources for my DataReader, Command & Connection objects.

I have 2 classes - MyUtilityClass which contains a function which will return a DataReader, and 'MyMainClass' which calls the 'GetDataReader' function in 'MyUtilityClass'.

-----------------------

(MyUtilityClass)...
 Public Function GetDataReader(ByVal intMyID As Integer) As SqlDataReader
      Dim cnnSQL As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
      Dim cmdSQL As New SqlCommand("myStoredProcedure", cnnSQL)
      Dim drdSQL As SqlDataReader

      cmdSQL.CommandType = CommandType.StoredProcedure
      cmdSQL.Parameters.Add("@MyID", SqlDbType.Int)
      cmdSQL.Parameters("@MyID").Value = intMyID

      Try
         cnnSQL.Open()
         drdSQL = cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)
      Catch ex As System.Exception
         If Not drdSQL.IsClosed Then
            drdSQL.Close()
         End If
         If cnnSQL.State = ConnectionState.Open Then
            cnnSQL.Close()
         End If

         ' I throw an exception here to email admin, notifying of error details.
      End Try

      Return drdSQL
   End Function

-----------------------

(MyMainClass)
   Private Sub PopulateDropDownList()
      Dim myDropDownList As New DropDownList
      Dim myDataReader As SqlClient.SqlDataReader

      myDataReader = MyUtilityClass.GetDataReader(123)

      myDropDownList.DataSource = myDataReader
      myDropDownList.DataBind()

      myDataReader.Close()
   End Sub

-----------------------

1) The first thing I am concerned about is, I am not calling the Dispose method for either the Command or Connection objects at any point, as if I do so in a 'Finally' block within 'MyUtilityClass', the DataReader I return is going to effectively be closed/non-useable. For my other functions that are not returning a DataReader object, I like to call Dispose before returning the function value (I think this is the right thing to do?)

2) I am setting the 'CommandBehaviour' to 'CloseConnection' in the 'GetDataReader' function - Is this instance of the DataReader actually being Closed OUTSIDE of this function (i.e. in the 'PopulateDropDownList' routine in 'MyMainClass') as it is being assigned to another instance of a datareader object which is THEN being closed, or are they being treated as 2 different datareaders, 1 of which is being left open and the 2nd one which is being closed within the 'PopulateDropDownList' routine?

I'm just trying to get my head around what I should and shouldn't be doing with regards to the closing/resource releasing of these objects.

Any help much appreciated.
0
XMLnoobie
Asked:
XMLnoobie
  • 2
1 Solution
 
PaulHewsCommented:
1) Once you've closed the object, you've released the necessary resources and the garbage collection will do the final work.  

2) There is only one datareader object in use and once you close it, the connection is closed with it.  That is one way to release the resources.  I use a standalone dataaccess class where the connection object is created in the constructor, and there's a Dispose method that releases all the resources.  That way, I can use the same dataaccess class for several calls, and then call dispose to mop up the rest.
0
 
XMLnoobieAuthor Commented:
Hi Paul, thanks for your comments - So there is nothing wrong with the way I am doing the above then?
I like the sound of how you use this standalone data access class. I don't suppose you know of any example sites that use this approach where I could reference some sample code do you?

Thanks again.
0
 
PaulHewsCommented:
No, I haven't seen any sample code for it--it's not a unique or new idea however.  If you go searching you might find one.  You can make your job easier by downloading and using the Microsoft Application Data Access Block, which contains generic functions for returning a dataset, datareader, executing dynamic queries, etc.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

Then you can build your class yourself, using what you need.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now