Many-to-many in theBeerHouse

philthy · #1 (**permalink**) December 29th, 2008, 07:02 AM

I’m trying to fix together a blog, using theBeerHouse architecture, and to do this, I’m adding a tagging option to the articles.
This means I need a many-to-many relation in the database, since one article can have many tags, and a tag can refer to many articles.

So, I’m adding a tags table, and a tag_article junction table. I’m not a huge SQL freak, but I think I got the hang of most of it. Also, I’ve added a TagDetails class file.

I’m putting a manage tags section in the articles module, and the thought is that you would ad some tags to the tags table first,
and then when you add/edit an article, you’d get the option to choose one or more of these tags in a checkbox list.

And when you save the article, you’d update the tag_articles table with the ID of the article and the ID(s) of the tags you chose for the article, thus associating the article and the tags(s).

This means that you’d have to update two tables in one go, the articles and the tag_articles.
In theBeerHouse, when an article is saved, the InsertArticle method in Article BLL is called with the parameters from the page, and an ArticleDetails object is created. I would have to also get the ID’s of the chosen tags from the page and pass them along with the ArticleDetails object, or in some other way.

My first thought was to add a TagDetails list property to the Article BLL class. This would have to be done anyway, so that the Article BLL object can maintain a list of tags, like it maintains a list of Comment objects.

Then, in the InsertArticle BLL method, get the ID’s of the chosen tags from that list. I would need them as a parameter in the stored procedure later. I was thinking of using the InsertArticle stored procedure, to update both the Articles table and the tag_article table. (I guess I’d also have to put the TagID’s in there as parameters in order to update both tables, right?)

In the tag_articles junction table, I would put the ID of the new/updated article (SCOPE_IDENTITY), and the ID’s of the tags. I should be able to select the tagID’s from the tags table, based on the names of them, since they would be unique. Does this even make any sense?

I guess my question is, what would be a smart way of getting the tag ID(s) from the page to the DAL, and update two tables in one go? I have a feeling that my idea above would somehow break the architecture. I’m not experienced enough to see it 100%, but I’m getting there.

Have any of you guys added many-to-many functionality to this architecture before?
Maybe you know of some gotcha’s

Lee Dumond · #2 (**permalink**) December 30th, 2008, 02:42 AM

Sounds like you are on the right track for this so far. The only thing I would add is that you might consider either using a transaction in the BLL, or a rollback in the sproc, so that if the update to one of the tables fails you roll back the updates to the other.

Let us know how you make out with this.

philthy · #3 (**permalink**) December 31st, 2008, 10:09 AM

Great, then I'm not totally lost... Thanks for the tip on transactions. The idea is to first insert a new article, and then insert the appropriate row(s) in the junction table to associate the tags and articles. So I need to make sure the article is indeed inserted before updating the junction table, since I need the articleID. So it looks like transactions is just what I need...

But wouldn't the transaction code go into the SqlArticlesProvider in the DAL?

By the way, Happy new year

Lee Dumond · #4 (**permalink**) December 31st, 2008, 10:57 AM

Quote:

Originally Posted by philthy

But wouldn't the transaction code go into the SqlArticlesProvider in the DAL?

Yeah, that's what I meant.

Happy New Year to you too.

jimibt · #5 (**permalink**) January 2nd, 2009, 07:13 AM

Quote:

Originally Posted by Lee Dumond

Yeah, that's what I meant.

Happy New Year to you too.

happy new year all.

actually, the transaction can go in either the BLL or the DAL. my preference is to put it into the BLL as you can have a 'unit of work' method that inserts all the appropriate parent and child (many-many) records as a single atomic 'unit' via calls to the DAL methods.

hope this helps

philthy · #6 (**permalink**) January 3rd, 2009, 12:41 PM

Quote:

Originally Posted by jimibt

actually, the transaction can go in either the BLL or the DAL. my preference is to put it into the BLL as you can have a 'unit of work' method that inserts all the appropriate parent and child (many-many) records as a single atomic 'unit' via calls to the DAL methods.

I'm not too familiar with transactions yet, but won't this tie the transaction to sql server only? That's why I first thought of putting it in the SqlArticlesProvider...

Edit:
I looked a little more into it. In the Articles BLL class, I should be able to wrap the calls to two different insert methods in the SqlArticlesProvider class, into a TransactionScope Using statement, right?

My idea so far is to first call:
SiteProvider.Articles.InsertArticle(ArticleDetails Object); and then if that succedes, SiteProvider.Articles.InsertArticleTags(ListOfTagD etailsObjects); which is a new method that inserts a record in the junction table...

Lee Dumond · #7 (**permalink**) January 4th, 2009, 04:49 PM

Quote:

Originally Posted by philthy

My idea so far is to first call:
SiteProvider.Articles.InsertArticle(ArticleDetails Object); and then if that succedes, SiteProvider.Articles.InsertArticleTags(ListOfTagD etailsObjects); which is a new method that inserts a record in the junction table...

Pretty close, but not exactly.

You shouldn't need to test whether InsertArticle succeeds before calling InsertArticleTags. If you properly call both methods inside the scope of a single Transaction, it should roll back BOTH inserts if EITHER fails.

jimibt · #8 (**permalink**) January 4th, 2009, 05:02 PM

Quote:

Originally Posted by philthy

I'm not too familiar with transactions yet, but won't this tie the transaction to sql server only? That's why I first thought of putting it in the SqlArticlesProvider...

Edit:
I looked a little more into it. In the Articles BLL class, I should be able to wrap the calls to two different insert methods in the SqlArticlesProvider class, into a TransactionScope Using statement, right?

My idea so far is to first call:
SiteProvider.Articles.InsertArticle(ArticleDetails Object); and then if that succedes, SiteProvider.Articles.InsertArticleTags(ListOfTagD etailsObjects); which is a new method that inserts a record in the junction table...

philthy,

yes, looks like you figured it out exactly - TransactionScope a la:

Code:

            using (TransactionScope scope = new TransactionScope())
            {
            }

you can of course 'grab' the identityscope via the insert method in your SqlArticlesProvider methods something like this:

Code:

        /// <summary>
        /// Insert a Artist
        /// </summary>
        internal override int InsertArtist(ArtistDetails artist)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO Artist ([BandName]) 
                                VALUES (@BandName);SELECT SCOPE_IDENTITY()", cn);        
                cmd.Parameters.Add("@BandName", SqlDbType.NVarChar).Value = artist.BandName;
                
                cn.Open();
                
                int ret = Convert.ToInt32(this.ExecuteScalar(cmd));
                return ret;
            }
        }

your BLL method would then (if the insert succeded), pick up the ID of the newly inserted row. this could then be passed in as a parameter to the junction table(s).

hope this is clear

Need to download code?

Navigation