I'm in the process of expanding the Wrox Blog apps to set a BlogEntry with multiple categories.

Cause I'm not very deep on OOP I'd love some suggestions on how to modify BlogEntry, BlogManager, BlogManagerDB classes and related stored procedures.

NO PAIN :) now I'm entering in more details.

I created a table on DB with a many-to-many relation like this:

TABLE NAME:PostToCategories
Id (PK,int,not Null)
be_ID (FK,int,not Null) this is the foreign key to BlogEntry.Id
cat_ID (FK,int,not Null) this is the foreign key to Category.Id

[u]Rigt now my specific questions are:</u>
1 - BlogEntry Class need to maintain CategoryId Property in this scenario? If yes, a Collection cold be a better approach?

Some one has a better idea on how to manage this?

2 - Is better to create a different store procedure to manage categories? And then run 2 store procedures for example in SaveBlogEntry():
 

• sprocBlogEntryInsertSingleItem
• sprocBlogEntryInCategories (to create)

Or is a better approach.

Hope some expert in this type of analisys can give me some design suggestion.

Alan

__________________
Alan

Hi Alan,

I think you should take a look at the BugBase application, with the search feature in particular. The SearchCriteria class is able to store multiple "categories" (features, priorities and so on) which are then sent to the database. Inside a stored procedure, a Split method is used to split each item and use it in a query.

The SearchCriteria class uses a comma separated string for the items, but you could also use a generics List(Of Integer) to store the categories. E.g.:

Public Class BlogEntry
  Private _Categories As List(Of Integer)

  Public ReadOnly Property Categories() As List(Of Integer)
    Get
      Return _Categories
    End Get
  End Property
End Class

This allows you to add categories like this:

myBlogEntry.Categories.Add (4)

to add a category with an ID of 4 to the list.

Inside the BlogEntry class you can use this list to update the database. You could use the Split method I mentioned earlier, or you can fire a store procedure to bind each BlogEntry to a category. If you take that route, here's what you should do:

1. Save the BlogEntry and return its ID

2. Fire a Delete procedure that deletes all junction records from the BlogEntryCategory table, based on the ID of the BlogEntry from 1)

3. For each item in your List(Of Integer) fire a stored procedure that receives the BlogEntryId and the CategoryId to insert the junction record.

In the front end, in the Save button's Click handler for the BlogEntry, you should do something like this:

1. Get a BlogEntry object using BlogManager.GetBlogEntry(id)

2. Clear the (new) list of Categories:

myBlogEntry.Categories.Clear()

3. For each selected category in a ListBox, add the selected ID to the Categories list.

myBlogEntry.Categories.Add(Convert.ToInt32(myListI tem.Value))


4. Save the BlogEntry.

Does this put you on the right track?

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.

Yes Imar, this puts me on the right direction.

To use the "List" method sounds more easy to my knowledge.
Next chapter will be for sure "12 BugBase" to go deep on the Split method.

Thanks very much for your help!


Alan

You're welcome.

Good luck with the implementation and let me know if you need more help.

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.