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'.
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("@MyID").Value = intMyID
drdSQL = cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As System.Exception
If Not drdSQL.IsClosed Then
If cnnSQL.State = ConnectionState.Open Then
' I throw an exception here to email admin, notifying of error details.
Private Sub PopulateDropDownList()
Dim myDropDownList As New DropDownList
Dim myDataReader As SqlClient.SqlDataReader
myDataReader = MyUtilityClass.GetDataReader(123)
myDropDownList.DataSource = myDataReader
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.