Many-to-many in theBeerHouse

philthy · 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 · 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 · 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 · 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 · 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 · 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 · 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 · 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