Building Strongly Typed Nested Collections Part 3
By Tim Price
Requirements
Microsoft® Visual Studio® .NET 2003
Microsoft® Visual Basic .NET
Microsoft® SQL Server
Introduction
This is the final instalment of this three part series, which showed how to create nested collection (part 1), load and nest the collections (part 2) and finally create the database Schema and Stored Procedures.
Let’s start of by looking at the database schema.
Database Schema
Figure 1
The funny looking square object at the top left hand corner with the link on one end and a key at the other, indicates a relationship. The key signifies which table has the Primary Key field and the link indicates that the joining table has the Foreign Key field. This type of relationship is known as a One-To-Many. For example, the table with the key can have at least one record in the adjoining table or many records in the adjoining table.
This table is in fact in a relationship with itself which is known as a self Self-Join. To make a self join work we need a field in the table that will hold the Foreign Key which is in fact the Primary Key from the same table and Allow Nulls. The Place_ID is the Primary Key and the Parent_ID is the Foreign Key. Have a look at figure 2, to see the datatypes for the table. It is also, in relationship with PlaceDescription. The Foreign Key field in Place table is Description_ID and the Primary Key field in PlaceDescription id PlaceDescription_ID.
Figure 2
As you can see from Figure 2 the only field that allows null values is the Parent_ID field and this is because any record that has a Parent_ID of null must be a top level Place (Destination).
Figure 3
The above figure shows the fields that make up the PlaceDescription table. This table is designed to hold the following values.
· Destination
· Location
· Resort
These two tables are all that are required to provide that backend data schema. We will now look at the three Stored Procedures (SPROCs) that will deal with getting the relevant data out of these tables.
Creating SPROCS
The first SPROC we will create is called rsp_GetDestinations. This SPROC is concerned with getting just the Destinations out of the Place table. A quick note on sproc naming, I like to prefix my SPROCs with one of the following:
· isp_Insertitemname – Insert Stored Procedure
· rsp_Getitemame - Read Stored Procedure
· usp_Updateitemname – Update Stored Procedure
· dsp_Deleteitemname – Delete Stored Procedure
You can obviously name your SPROCs in whatever manner you want; I just thought I would show you my naming convention.
rsp_GetDestinations
CREATE PROCEDURE [dbo].[rsp_GetDestinations]
/*******************************************************************************
*
*
* rsp_GetDestinations
*
* SUMMARY
* -------
* Returns all the Destinations
*
*
* MODIFICATION HISTORY
* --------------------
* Who Modified When Modified Modification
* --------------------------------------------------------------------
* Tim Price 25 Mar 2004 Created.
*
*
*
*******************************************************************************/
AS
SELECT
[Place].[Place_ID],
[Place].[Parent_ID],
[Place].[Name],
FROM
[Place]
WHERE
[Parent_ID] IS NULL
RETURN @@Error
There is nothing complicated going on here, all we are doing is getting all the records that have a Parent_ID of null.
rsp_GetPlaces
CREATE PROCEDURE [dbo].[rsp_GetPlaces]
/*******************************************************************************
*
*
* rsp_GetPlaces
*
* SUMMARY
* -------
* Returns all the Places
*
*
* MODIFICATION HISTORY
* --------------------
* Who Modified When Modified Modification
* ------------------------------------------------------------------------
* Tim Price 25 Mar 2004 Created.
* Tim Price 26 Mar 2004 Added the Inner Join.
*
*******************************************************************************/
(
@Parent_ID INT
)
AS
DECLARE @PlaceDescription_ID INT
SELECT @PlaceDescription_ID = (SELECT [PlaceDescription_ID] FROM PlaceDescription WHERE [Name] = 'resort')
SELECT
[Place].[Place_ID],
[Place].[Parent_ID],
[Place].[Name],
[Place].[Content],
[PlaceDescription].[Name] AS [PlaceType]
FROM
[Place]
INNER JOIN
[PlaceDescription]
ON
[Place].[Description_ID] = [PlaceDescription].[PlaceDescription_ID]
WHERE
(([Parent_ID] = @Parent_ID)
AND
([Description_ID] <> @PlaceDescription_ID))
RETURN @@Error
This SPROC is a little more complicated due to the fact the it does not want to return records that are described as Resorts and the only way it can do that is to get the PlaceDescription_ID from the PlaceDescription table. It achieves this with the following lines:
DECLARE @PlaceDescription_ID INT
SELECT @PlaceDescription_ID = (SELECT [PlaceDescription_ID] FROM PlaceDescription WHERE [Name] = 'resort')
The first line declares a variable name and data type (DECLARE in T-SQL is the equivalent to the Dim keyword in VB.Net). The second line the assigns the return value of the SELECT statement to this variable.
The SPROC has a parameter of data type INT and is the Parent_ID for the child records we want to retrieve. This parameter is used in conjunction with the @PlaceDescription_ID in the WHERE clause, to ensure we don’t get back Places that are described as Resorts.
rsp_GetResorts
CREATE PROCEDURE [dbo].[rsp_GetResorts]
/*******************************************************************************
*
*
* rsp_GetResorts
*
* SUMMARY
* -------
* Returns all the Resorts
*
*
* MODIFICATION HISTORY
* --------------------
* Who Modified When Modified Modification
* ------------------------------------------------------------------------
* Tim Price 25 Mar 2004 Created.
* Tim Price 26 Mar 2004 Added the Inner Join.
*
*******************************************************************************/
(
@Parent_ID INT
)
AS
DECLARE @PlaceDescription_ID INT
SELECT @PlaceDescription_ID = (SELECT [PlaceDescription_ID] FROM PlaceDescription WHERE [Name] = 'resort')
SELECT
[Place].[Place_ID],
[Place].[Parent_ID],
[Place].[Name],
[Place].[Content],
[PlaceDescription].[Name] AS [PlaceType]
FROM
[Place]
INNER JOIN
[PlaceDescription]
ON
[Place].[Description_ID] = [PlaceDescription].[PlaceDescription_ID]
WHERE
(([Parent_ID] = @Parent_ID)
AND
([Description_ID] = @PlaceDescription_ID))
RETURN @@Error
The only difference between this SPROC and the previous one is in the comparison operator in the final WHERE clause, naturally this SPROC is only interested in Places that are described as Resorts. To this end we require all the records where the Parent_ID is equal to the passed in parameter value and the Description_ID is equal to the @PlaceDescription_ID value.
Conclusion
There you have it. From Part 1 to Part 3 everything you need to know on how to create Strongly Typed Nested Collections. Get to know them, use them they are your friend.