CMS: Expanding the database

retroviz · February 6th, 2007, 11:35 AM

Hi,

The CMS system is coming on well now. I have added in a login status control in place of the "Login" hyperlink which is quite cool (as it changes depending on your status). Also added "Last Modified: #####" on the content list. Finally I have converted the app to AJAX. Still concentrating on learning Asp.net but the Update Panels are so easy to implement, I couldn't resist. This was particularly effective on the AddEditContent page where the whole page [u]was posting back when changing the drop downs.

So the UI is looking quite good. However I need to make quite a big change to the navigation structure. As the site will be used an the Intranet site for the I.T support deapartment, they will have a large number of subjects that will not fit on the current (horizontal) nav bar.

For example, we would have a content type Hardware then a Main Category Printers, then a further category Product Manufacturer, then model group, then model.

I think a tree view will be the best solution as I can have multiple child tables for each of these groups.

Can you offer your thoughts on whether this is a good way to go, how this may integrate with the existing db and whether you are aware of any good tutorials on binding a treeview to a database (i have found a few).

Many thanks,
retro

Imar · February 6th, 2007, 05:03 PM

Hi there,

Yes, you could use a treeview. However, you may need to do some manual programming as the treeview expect hierarchical data, while the items are stored in the database as relational data.

The same applies to the <asp:menu /> control, although it may be better suited for the task. Combine that with the CSS Adapters (from www.asp.net) and you end up with a clean solution.

You can take a look here for some ideas: http://aspalliance.com/822

The database scheme is similar to the CMS design, although you may need to change it a little....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

retroviz · February 8th, 2007, 07:06 PM

Hi Imar,

Im getting there. If you could offer any pointers for the next step I would much appreciate it.

So far I have created a menu control on default.aspx (just for testing purposes).

Most of the examples I have found for displaying Database Data in a Menu control were in C# so it has taken me a while to get this one working (in VB.net).

My code behind is as follows:

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            PopulateMenu()
        End If
    End Sub

    Sub PopulateMenu()
        Dim dst As DataSet = GetMenuData()
        For Each masterRow As DataRow In dst.Tables("ContentType").Rows()
            Dim masterItem As New MenuItem(masterRow("Description").ToString())
            Menu1.Items.Add(masterItem)
            For Each childRow As DataRow In masterRow.GetChildRows("Children")
                Dim childItem As New MenuItem(childRow("Description").ToString())
                masterItem.ChildItems.Add(childItem)
            Next
        Next
    End Sub

    Function GetMenuData() As DataSet
        Dim myCon As New SqlConnection
        myCon = New SqlConnection()
        myCon.ConnectionString = ConfigurationManager.ConnectionStrings("CMS").Conn ectionString

        'Dim con As New SqlConnection(myCon)
        Dim dadContentTypes As New _
          SqlDataAdapter("SELECT * FROM ContentType", myCon)
        Dim dadCategories As New _
          SqlDataAdapter("SELECT * FROM Category", myCon)
        Dim dst As New DataSet()
        dadContentTypes.Fill(dst, "ContentType")
        dadCategories.Fill(dst, "Category")
        dst.Relations.Add("Children", _
            dst.Tables("ContentType").Columns("Id"), _
            dst.Tables("Category").Columns("ContentTypeId"))
        Return dst
    End Function
End Class

My next task is to get the menu items to link to the relevant content within the pages.
I believe I need to modify the above code to output the ID of the ContentType/Category tables so that this can be used in the hyperlink as a query parameter.

Do I need to do this in the MenuItemClick event using the MenuEventArgs to then navigate to a url like:
"~/ContentList.aspx?ContentTypeId=" & Request.QueryString.Get("ContentTypeId") & "&CategoryId=" & Eval("Id") (as per the current nav bar buttons)?

Any help would be appreciated.
Thanks
retro

Imar · February 9th, 2007, 03:45 PM

Hi again retro,

The easiest way to do this is to use one of the overloaded constructors of the MenuItem class. Something like this would work:

Dim myItem As MenuItem = New MenuItem("Menu Text", "SomeValue", String.Empty, "http://www.wrox.com")
masterItem.ChildItems.Add(myItem )

This adds a MenuItem with a text of Menu Text. The fourth parameter of the constructor is the destination URL. Instead of passing www.wrox.com, you'd pass the current page, with a QueryString for the ContentType and Category.

Does this help?

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Herzeleid by Rammstein (Track 9 from the album: Herzeleid) What's This?

retroviz · February 26th, 2007, 01:59 PM

Hi Imar that helped alot thanks.

Did actually post about another another issue with my code but turned out to be user error :o)

I do however need bit of help with my next problem.

I have added in one new table (for now) called "CategoryLevel2".

The dynamic menu I am creating will replace the existing sub menu. I have therefore utilised the existing stored procedure "sprocCategorySelectList" so that my menu is filtered by the content type. The code for the populate menu sub and getmenudata function is as follows:

Code:

    Sub PopulateMenu()
        Dim dst As DataSet = GetMenuData()

        For Each menuLevel1Row As DataRow In dst.Tables("Category").Rows()
            Dim level1Item As New MenuItem(menuLevel1Row("Description").ToString(), "SomeValue", String.Empty, "~/ContentList.aspx?ContentTypeId=" & Request.QueryString("ContentTypeId") & "&CategoryId=" & menuLevel1Row("Id"))
            Menu1.Items.Add(level1Item)

            For Each menuLevel2Row As DataRow In menuLevel1Row.GetChildRows("menuLevel_1_2")
                Dim level2Item As New MenuItem(menuLevel2Row("Description").ToString(), "SomeValue", String.Empty, "~/results.aspx?name=" & menuLevel2Row("ID"))
                level1Item.ChildItems.Add(level2Item)

            Next

        Next

    End Sub
    Function GetMenuData() As DataSet

        Dim myCon As New SqlConnection

        myCon = New SqlConnection()

        myCon.ConnectionString = ConfigurationManager.ConnectionStrings("CMS").ConnectionString

        Dim cmd As New SqlCommand("sprocCategorySelectList", myCon)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@contentTypeID", SqlDbType.Int).Value = Request.QueryString("ContentTypeID")

        Dim dadMenuLevel1 As New SqlDataAdapter(cmd)

        Dim dadMenuLevel2 As New SqlDataAdapter("SELECT * FROM CategoryLevel2", myCon)

        Dim dadMenuLevel3 As New SqlDataAdapter("SELECT * FROM MenuLevel3", myCon)

        Dim dst As New DataSet()

        dadMenuLevel1.Fill(dst, "Category")

        dadMenuLevel2.Fill(dst, "CategoryLevel2")

        dst.Relations.Add("menuLevel_1_2", dst.Tables("Category").Columns("Id"), dst.Tables("CategoryLevel2").Columns("CategoryID"))

        Return dst

    End Function

The code in bold is causing me problems. I get the following error at runtime:

"This constraint cannot be enabled as not all values have corresponding parent values".

If i remove the data from the CategoryLevel2 table I dont get the problem so there seems to be a problem with creating the relationship when not all Categories have data in CategoryLevel1(child).

Could you offer any words of wisdom (again).

Many thanks (again)

ret

Imar · February 26th, 2007, 02:53 PM

And is the error message correct? What happens when you execute the queries in the query analyzer?

Do you get records on a lower level without a parent level? Do you enforce the relationship at all the levels in the database?

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

retroviz · February 26th, 2007, 06:48 PM

Yes I see the problem now.

The first level of the menu control is populated using "sprocCategorySelectList". This will show all categories where contentTypeID = @contentTypeID

so clicking on News will populate the first level of the menu with all News categories.

I then try and populate the second level with "SELECT * FROM CategoryLevel2"

Of course this time I am trying to extract all records from the CategoryLevel2 then populate the menu based on the items in the first dataset

so I am trying to show a CategoryLevel2 item such as "Mono Laser" for the Category "Printers" which is in the ContentType "Hardware" even though I am looking at the News Categories.

So now i know what I am doing wrong :o)....

Based on the dataset created using "sprocCategorySelectList" is it possible to create my second dataset (or level 2 of the menu) based on the results of the first dataset

i.e. "SELECT * FROM CategoryLevel2 WHERE CategoryID = @categoryID"
in psuedo
Add Parameter @categoryID = dadMenuLevel1.Fill(dst, "Id")

not sure if this would work ??? :(

thanks
ret

Imar · February 27th, 2007, 01:09 PM

Hard for me to tell, without seeing all your explicit data structures and records.

But what if you fire all three stored procedures in a query analyzer? What records do you get?

Imar

retroviz · February 27th, 2007, 04:52 PM

Hi Imar,

The stored procedures run fine in query analyser (because they are completely independent of eachother at this point).
Would it be possible to email you my MenuLevel application as it may make it easier (consists of 2 .aspx pages and a 3 table db)?

Will try and explain below anyway. Forget the stored procedures for the time being. If you imagine I have a three table database, a table for each level in the database (TableMenu1, TableMenu2, TableMenu3). All I am concerned with at the moment is populating the database:

First the GetMenuData Function creates my datasets (cut down version)

Function GetMenuData() As DataSet

Code:

<center>.............................................</center>

        Dim dadMenuLevel1 As New SqlDataAdapter("SELECT * FROM TableMenu1", myCon)

        Dim dadMenuLevel2 As New SqlDataAdapter("SELECT * FROM TableMenu2", myCon)

        Dim dadMenuLevel3 As New SqlDataAdapter("SELECT * FROM TableMenu3", myCon)

        Dim dst As New DataSet() 'create dataset based on the above queries

        dadMenuLevel1.Fill(dst, "TableMenu1")
        dadMenuLevel2.Fill(dst, "TableMenu2")
        dadMenuLevel2.Fill(dst, "TableMenu3")

        dst.Relations.Add("menuLevel_1_2", dst.Tables("TableMenu1").Columns("Id"), dst.Tables("TableMenu2").Columns("TableMenu1ID")) 'relationship between TableMenu1 and 2
        dst.Relations.Add("menuLevel_2_3", dst.Tables("TableMenu2").Columns("Id"), dst.Tables("TableMenu3").Columns("TableMenu2ID")) 'relationship between TableMenu2 and 3

        Return dst

    End Function

I then populate the menu using the following code:

Code:

    Sub PopulateMenu()
        Dim dst As DataSet = GetMenuData()
        For Each menuLevel1Row As DataRow In dst.Tables("TableMenu1").Rows()
            Dim level1Item As New MenuItem(menuLevel1Row("Description").ToString)
            Menu1.Items.Add(level1Item)

 For Each menuLevel2Row As DataRow In menuLevel1Row.GetChildRows("menuLevel_1_2")
                Dim level2Item As New MenuItem(menuLevel2Row("Description").ToString)
                level1Item.ChildItems.Add(level2Item)

 For Each menuLevel3Row As DataRow In menuLevel2Row.GetChildRows("menuLevel_2_3")
                Dim level3Item As New MenuItem(menuLevel2Row("Description").ToString)
                level2Item.ChildItems.Add(level3Item)

            Next
        Next
    Next
End Sub

This works no problem. All you need is a three table relational database.
[u]TableMenu1
Id
Description
[u]TableMenu2
Id
TableMenu1ID
Description
[u]TableMenu3
Id
TableMenu2ID
Description

So the whole thing is fairly straightforward. If you then drop an asp:menu control onto the page it will be populated with 3 levels based on the 3 related tables.

[u]My Problem
Change the SQL for the first SqlDataAdapter to

Code:

("SELECT * FROM TableMenu1 WHERE Id = 1")

What I would hope to see is one item in my menu (for record where ID=1) and when I hover over this, its child items.

Instead I get the error as I listed before. I now know I get this because although I have filtered the first table in the dataset (for ID=1). I am then trying to create a relationship with the second table in the dataset (where I have selected all the records).

So I end up with child items without a parent item.

All I stuggling to work out is how I can filter the second dataset table by the values in its parent, then the values in the third dataset by the values in the second (so if first level is Hardware, Second Level only shows Hardware Items and Third level shows Hardware Specific Items)

Hope ive explained it enough. As always I will contine to battle through myself

Thanks
ret

Imar · February 27th, 2007, 04:57 PM

Yes, please send it to me. Much easier. When you send me a message through my Profile, I'll reply. Then you have my address and you can send the files. Might take a while though....

I know the stored procedures are unrelated. But, what I mean is this: Let's say procedure returns these items:

ID Name
1 Item 1
2 Item 2
4 Item 4

Column 1 is the PK for the table.

Then procedure 2 returns this:

ID Name Parent
1 Child 1 1
2 Child 2 2
3 Child 3 3
4 Child 4 4

When you try to link these two results sets, things will break as the Parent column of the child table contains references to IDs that no longer exist in the Parent table (item 3).
At least I think that's what the error message is saying....

(Sorry just reread your post a little closer. Looked like you figure this out. I think the fix is to change SQL 2 and 3 to match the ID=1 as well. So, for SQL2, you can do this:

..... WHERE ParentId = 1

and for SQL 3 you can do something like:

WHERE ParentId IN (SELECT Id FROM Table WHERE ParentId = 1)

For the second set, that would give you all child records of 1. Foe the third query, you'd get all records that have a parent that in turn has a parent with an ID of 1.)

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.