Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 29th, 2008, 07:02 AM
Authorized User
 
Join Date: Mar 2007
Location: , , Denmark.
Posts: 52
Thanks: 6
Thanked 2 Times in 2 Posts
Default Many-to-many in theBeerHouse

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
  #2 (permalink)  
Old December 30th, 2008, 02:42 AM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

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.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #3 (permalink)  
Old December 31st, 2008, 10:09 AM
Authorized User
 
Join Date: Mar 2007
Location: , , Denmark.
Posts: 52
Thanks: 6
Thanked 2 Times in 2 Posts
Default

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
  #4 (permalink)  
Old December 31st, 2008, 10:57 AM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Quote:
Originally Posted by philthy View Post

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.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #5 (permalink)  
Old January 2nd, 2009, 07:13 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Quote:
Originally Posted by Lee Dumond View Post
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
__________________
jimi

http://www.originaltalent.com
  #6 (permalink)  
Old January 3rd, 2009, 12:41 PM
Authorized User
 
Join Date: Mar 2007
Location: , , Denmark.
Posts: 52
Thanks: 6
Thanked 2 Times in 2 Posts
Default

Quote:
Originally Posted by jimibt View Post

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...

Last edited by philthy; January 3rd, 2009 at 08:34 PM..
  #7 (permalink)  
Old January 4th, 2009, 04:49 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Quote:
Originally Posted by philthy View Post

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.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #8 (permalink)  
Old January 4th, 2009, 05:02 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Quote:
Originally Posted by philthy View Post
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
__________________
jimi

http://www.originaltalent.com
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINQ to SQL in TheBeerHouse zhoux BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 October 1st, 2008 07:37 AM
Setup and Deployment of the TheBeerHouse alex BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 July 17th, 2008 03:30 PM
Where is TheBeerHouse database? andrewrajcoomar BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 October 4th, 2007 08:33 PM
thebeerhouse cant install with sqlserver2000? lingyun2003 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 4 June 18th, 2007 10:20 PM
Using TheBeerHouse hasanali00 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 April 21st, 2007 09:40 AM



All times are GMT -4. The time now is 04:51 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.