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

December 29th, 2008, 07:02 AM
|
Authorized User
|
|
Join Date: Mar 2007
Posts: 52
Thanks: 6
Thanked 2 Times in 2 Posts
|
|
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
|

December 30th, 2008, 02:42 AM
|
 |
Wrox Author
|
|
Join Date: Jan 2008
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
|
|
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.
|

December 31st, 2008, 10:09 AM
|
Authorized User
|
|
Join Date: Mar 2007
Posts: 52
Thanks: 6
Thanked 2 Times in 2 Posts
|
|
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 
|

December 31st, 2008, 10:57 AM
|
 |
Wrox Author
|
|
Join Date: Jan 2008
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
|
|
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.
|

January 2nd, 2009, 07:13 AM
|
Friend of Wrox
|
|
Join Date: Mar 2007
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
|
|
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
|

January 3rd, 2009, 12:41 PM
|
Authorized User
|
|
Join Date: Mar 2007
Posts: 52
Thanks: 6
Thanked 2 Times in 2 Posts
|
|
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...
Last edited by philthy; January 3rd, 2009 at 08:34 PM..
|

January 4th, 2009, 04:49 PM
|
 |
Wrox Author
|
|
Join Date: Jan 2008
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
|
|
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.
|

January 4th, 2009, 05:02 PM
|
Friend of Wrox
|
|
Join Date: Mar 2007
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
|
|
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 
|
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 |
|
 |