Building Strongly Typed Nested Collections Part 2
By Tim Price
Requirements
Microsoft® Visual Studio® .NET 2003
Microsoft® Visual Basic .NET
Introduction
Part 1 of this article, showed how to create a collection of Place objects contained within their own Places collection class. The collection class was strongly typed, which meant that it would only accept Place objects, and exposes its members as Place type objects.
I will now show you how to create the World class object that will contain a collection of Place objects, which if you recall from Part 1, can contain a collection of Places objects. If this all sounds strange to you, please see Building Strongly Typed Nested Collections Part 1.
The World object contains three Methods. One public function, called by a consumer of this class and two are private helper Sub’s. It also contains one Public Property that exposes a collection of Places, defined as Destinations.
The class will be connecting to a SQL Server database; therefore, we are going to need to import the SqlClient class. Note: If you want to try this out on your own machine and you will not be using SQL Server, you must not use the SqlClient, you should import the class specific to your data needs. For example if your database server is compliant with ODBC, you can import the System.Data.Odbc class.
Create the World
Create a new class file and add the following code until it looks like example 1 below.
Imports System.Data.SqlClient
Public Class World
Inherits System.Collections.CollectionBase
End Class
We now have everything in place and can start adding our functionality. We need to dimension a private variable for internal use, of type Places.
Imports System.Data.SqlClient
Public Class World
Inherits System.Collections.CollectionBase
#Region "Private Member Variables"
Private Shared m_oDestinations As Places
#End Region
End Class
Adding a Public Property
This variable will only be visible to the internal methods of this class; we need to add a Public Property to this class that will expose the internal variable. Add the following code to your class.
#Region "Public Properties"
Public Shared ReadOnly Property Destinations() As Places
Get
Return m_oDestinations
End Get
End Property
#End Region
We now have a way of getting at the collection of Places from outside the class. Just to recap our class file should now look like this.
Imports System.Data.SqlClient
Public Class World
Inherits System.Collections.CollectionBase
#Region "Private Member Variables"
Private Shared m_oDestinations As Places
#End Region
#Region "Public Properties"
Public Shared ReadOnly Property Destinations() As Places
Get
Return m_oDestinations
End Get
End Property
#End Region
End Class
Adding a Function
Let’s start to add the functions that we discussed earlier. The first one is called LoadDestinations, This function returns a Boolean value, which indicates success or failure.
#Region "Methods"
Public Shared Function LoadDestinations() As Boolean
Try
Dim oConnection As New SqlConnection
oConnection.ConnectionString = "YourConnectionString"
oConnection.Open()
Dim oCommand As New SqlCommand
oCommand.Connection = oConnection
With oCommand
.CommandText = "rsp_GetDestinations"
.CommandType = CommandType.StoredProcedure
End With
Dim oDR As SqlDataReader
oDR = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
If oDR.HasRows Then
m_oDestinations = New Places(oDR)
Dim oDestination As Place
For Each oDestination In m_oDestinations
LoadPlaces(oDestination)
Next
Return True
Else
Return False
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Function
#End Region
This function looks quite complicated, but once we break it down it is really quite simple. The first thing we do is create a new SQL Connection and open it. Next we create a SQL Command object, give it the name of a Stored Procedure to call, and tell it the type of Command Text it is to execute. Because the CommandText represents a Stored Procedure (SPROC), we set the CommandText property accordingly. Also, this particular SPROC does not require any parameters. We then create a SqlDataReader object to read the rows of data, returned by the SPROC. This is all pretty much standard .Net database stuff.
We then check to see if the DataReader actually contains data. If it does, we create a new Places collection object and assign it to our internal m_oDestinations variable, by calling the New method of the Places collection passing it the DataReader. You will recall that the Places collection class has two New methods one does nothing and the other accepts a DataReader object, which, it uses to populate itself with Place objects.
We then dimension a new Place object, called oDestination to be used in a For...Each Loop. This loop will loop through all the Destinations objects that exist in the m_oDestinations collection. For each Destination in the collection, we call a LoadPlaces function, passing the Destination object as a parameter.
Creating a Recursive Routine
The fun starts with the call to LoadPlaces, this routine accepts a Place object as a parameter.
Private Shared Sub LoadPlaces(ByVal oPlace As Place)
Try
Dim oConnection As New SqlConnection
oConnection.ConnectionString = "YourConnectionString"
oConnection.Open()
Dim oCommand As New SqlCommand
oCommand.Connection = oConnection
With oCommand
.CommandText = "rsp_GetPlaces"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Parent_ID", SqlDbType.Int, 4).Value = oPlace.Place_ID
.Parameters("@Parent_ID").Direction = ParameterDirection.Input
End With
Dim oDR As SqlDataReader
oDR = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
If oDR.HasRows Then
oPlace.Locations = New Places(oDR)
Dim oNewPlace As Place
For Each oNewPlace In oPlace.Locations
'Call Recursion
LoadPlaces(oNewPlace)
Next
Else
LoadResorts(oPlace)
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Again this function requires data from the backend database. Therefore, there is the usual database stuff. However, this time the SPROC we are calling rsp_GetPlaces does require a parameter and that is the ID of the place whose child places we require. Please refer to Figure 1 in Building Strongly Typed Nested Collection Part 1 for an idea of the relationship we are modelling.
We create a new parameter give it a name, datatype, size and value. We then execute the command passing the returned data to a DataReader object. We now need to check whether or not the DataReader has any data.
If the DataReader does contain data, we create the Places public property of the Place object that was passed to the function as a parameter, equal to a New collection of Places passing the newly created DataReader object as it’s parameter. Next a new Place object is created and assigned to a variable called oNewPlace. This object variable is then used in another For…Each loop. We start a loop, looping through all the Places in the oPlace.Places collection on each iteration we call LoadPlaces. Note: This is the recursive part of the function.
If the DataReader does not contain data, we have drilled down to the bottom of our nested data in the database. It is now, simply a case of getting the resorts for the Place by calling LoadResorts. The LoadResorts method accepts a parameter of type Place and loads all the Places in the database, described as Resorts.
Loading Resorts
This method simply queries the database for all the resorts that belong to a particular location.
Private Shared Sub LoadResorts(ByVal oPlace As Place)
Try
Dim oConnection As New SqlConnection
oConnection.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
oConnection.Open()
Dim oCommand As New SqlCommand
With oCommand
.CommandText = "rsp_GetResorts"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Parent_ID", SqlDbType.Int, 4).Value = oPlace.PlaceID
.Parameters("@Parent_ID").Direction = ParameterDirection.Input
End With
Dim oDR As SqlDataReader
oDR = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
If oDR.HasRows Then
oPlace.Resorts = New Places(oDR)
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
You should be familiar with the routine by now. Create a new connection and open it, create a command object and set it’s properties. Create and fill a DataReader with the results of the command objects SPROC.
That’s it we have successfully loaded Destination, and all of the child Locations recursively until the recursions reaches it natural end. The code then calls the LoadResorts method to get all the Resorts for the final Location.
This process starts all over again back in the LoadDestinations function. Look at the code snippet below; we need to do this for all the Destination type objects in the Destinations collection.
For Each oDestination In m_oDestinations
LoadPlaces(oDestination)
Next
Putting it all together
Your code should now look like the code below.
Imports System.Data.SqlClient
Public Class World
Inherits System.Collections.CollectionBase
#Region "Private Member Variables"
Private Shared m_oDestinations As Places
#End Region
#Region "Public Properties"
Public Shared ReadOnly Property Destinations() As Places
Get
Return m_oDestinations
End Get
End Property
#End Region
#Region "Methods"
Public Shared Function LoadDestinations() As Boolean
Try
Dim oConnection As New SqlConnection
oConnection.ConnectionString = "YourConnectionString"
oConnection.Open()
Dim oCommand As New SqlCommand
oCommand.Connection = oConnection
With oCommand
.CommandText = "rsp_GetDestinations"
.CommandType = CommandType.StoredProcedure
End With
Dim oDR As SqlDataReader
oDR = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
If oDR.HasRows Then
m_oDestinations = New Places(oDR)
Dim oDestination As Place
For Each oDestination In m_oDestinations
LoadPlaces(oDestination)
Next
Return True
Else
Return False
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Function
Private Shared Sub LoadPlaces(ByVal oPlace As Place)
Try
Dim oConnection As New SqlConnection
oConnection.ConnectionString = "YourConnectionString"
oConnection.Open()
Dim oCommand As New SqlCommand
oCommand.Connection = oConnection
With oCommand
.CommandText = "rsp_GetPlaces"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Parent_ID", SqlDbType.Int, 4).Value = oPlace.Place_ID
.Parameters("@Parent_ID").Direction = ParameterDirection.Input
End With
Dim oDR As SqlDataReader
oDR = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
If oDR.HasRows Then
oPlace.Locations = New Places(oDR)
Dim oNewPlace As Place
For Each oNewPlace In oPlace.Locations
'Call Recursion
LoadPlaces(oNewPlace)
Next
Else
LoadResorts(oPlace)
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Shared Sub LoadResorts(ByVal oPlace As Place)
Try
Dim oConnection As New SqlConnection
oConnection.ConnectionString = "YourConnectionString"
oConnection.Open()
Dim oCommand As New SqlCommand
With oCommand
.CommandText = "rsp_GetResorts"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Parent_ID", SqlDbType.Int, 4).Value = oPlace.Place_ID
.Parameters("@Parent_ID").Direction = ParameterDirection.Input
End With
Dim oDR As SqlDataReader
oDR = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
If oDR.HasRows Then
oPlace.Resorts = New Places(oDR)
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
#End Region
End Class
That’s it for Part 2. We now have a Class object that we can use to load all the data from our database and perform the necessary nesting of collections as and when nesting is required.
Additional Resources
Final Instalment
In the third and final instalment of this article, I will show you the database schema and the SPROCs used by the methods in this article. You will then have all the tools and know how to create a very versatile and robust collection of nested custom business objects. This hierarchy could be used in a number of scenarios, one obvious one being a Menu hierarchy.