Wrox Programmer Forums
|
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
 
Old September 19th, 2007, 04:01 PM
Friend of Wrox
 
Join Date: Mar 2006
Posts: 310
Thanks: 0
Thanked 0 Times in 0 Posts
Default Forums Extensions II - New/Old Posts

In version 1 of this site (thePhile) I used this method to the effect:

Code:
CREATE TABLE [dbo].[Forums_MbrViews] (
    [MemberID] [int] NOT NULL ,
    [TopicID] [int] NULL ,
    [ForumID] [int] NULL ,
    [TopicLastVisit] [datetime] NULL ,
    [MbrLastVisit] [datetime] NULL ,
    [SessionID] [nvarchar] (24) NOT NULL 
) ON [PRIMARY]
GO
Every time that a user view a topic, i call a procedure that increment view count And insert on the "Forums_MbrViews" the respective values.
If sessionID don't match, first delete all values from this table with this memberID, and then insert the actual values.
With that I prevent the table getting bigger...

Code:
CREATE PROCEDURE sp_Forums_InsertMbrViews
@User        nvarchar(50),
@TopicID    int,
@LastVisit    datetime,
@SessionID    nvarchar(24)
AS

declare @MemberID    int
declare @oldSessionID nvarchar(24)
declare @Date        datetime
DECLARE @ForumID int

    SELECT @ForumID = ForumID FROM Forums_Topics Where TopicID = @TopicID
    IF @ForumID is NULL
        BEGIN
            SET @ForumID = 0
        END
    IF @TopicID = 0
        BEGIN
            SET @Date = '2000-01-01 00:00:00.000'
        END ELSE
        BEGIN
            SET @Date = GETDATE()
        END

    if @User is null or @User='' 
    begin
        set @MemberID = -1        
    end else
    begin
        select @MemberID = MemberID from Forums_Members where MemberName = @User
        if @MemberID is null or @MemberID =''
        begin
            set @MemberID = -1 
        end
    end

    if @MemberID > 0
    begin

        SELECT @oldSessionID = SessionID FROM Forums_MbrViews WHERE MemberID = @MemberID
        IF @oldSessionID NOT LIKE @SessionID OR @oldSessionID IS NULL
        BEGIN
        DELETE FROM Forums_MbrViews WHERE MemberID = @MemberID
        END
        IF (SELECT COUNT(*) FROM Forums_MbrViews Where TopicID = @TopicID AND MemberID = @MemberID) > 0
        BEGIN
            UPDATE Forums_MbrViews SET ForumID = @ForumID, TopicLastVisit = @Date, MbrLastVisit = @LastVisit WHERE MemberID = @MemberID AND TopicID = @TopicID 
        END ELSE
        BEGIN
            INSERT INTO Forums_MbrViews (MemberID, TopicID, ForumID, TopicLastVisit, MbrLastVisit, SessionID) VALUES (@MemberID, @TopicID, @ForumID, @Date, @LastVisit, @SessionID)
        END
    END
GO
When I call my "GetForums" i need always to send my memberid!
Then I inner Join this two tables and check if this each forum have new or old posts...
The same thing when I Call "GetThreads".

This method allow me to do all that under sql!
I just retrieve "0" if is old, or "1" if is new...

But my problem now is that the BLL and DAl changed with TheBeerHouse.
With my old method I don't cached anything!
Using this method, don't make sense cache topics or threads list because I need to call that in order to Join the table with MbrViews table and see if each topic or thread is new...

I didn't saw this method anywhere! So... forgive some newby errors in my sqlScript!

Just comment and tell if you have a better idea for showing New/Old Posts and Forums!

(forgive my bad english)
 
Old September 20th, 2007, 03:46 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Max,

i replied to this in another post and post it here again to keep it 'under one roof'. Looking thro' your code, the logic that i used is quite similar in places, tho' we vary slighly in a few ways.

anyway, here it is:

i did start on the new post / old post idea a while back and got a version 0.5 working failry well. however, more pressing issues (both here and on other things) took over and i never quite finished the idea. basically, what i did was:

1. create a table called UserViewPosts

CREATE TABLE [dbo].[UserViewPosts](
    [UserID] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ParentPostID] [int] NOT NULL,
CONSTRAINT [PK_UserViewPosts] PRIMARY KEY CLUSTERED
(
    [UserID] ASC,
    [ParentPostID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2. i then amended the tbh_Forums_IncrementViewCount procedure adding @ParentPostID and @UserID paramaters.

3. when the IncrementViewCount sql is run, it also adds a row in the UserViewPosts table.

4. in tbh_Forums_GetForums, add an outer join onto the UserViewPosts table (on ParentPostID) and add a virtual column (isNew) to indicate whether the thread has had any new entires since you last read it. (i.e. if the row doesn't exist and returns a null, then the virtual column would have a value of true. if the row brings back data, then the isNew vlaue would be set to false)

5. when a new post is added or an old one amended, then delete all rows from the UserViewPosts table based on the ParentPostID of the thread.

That was as far as i got. the caveats of this approach being that the cacheing would have to be re-thought to accomodate the user by user 'view' on the forum/thread list. this is why i say i'd got to version 0.5 as this is quite a big area in itself. however, i think a 'generic' discrete user session/cache 'addition' might be quite useful for other things that need to view global data but apply user additions over the top of it.

let me know what you think of the general approach that i had taken.

[edit] i'm 'thinking' about the user cache 'overlay' idea and it seems like a good idea. once i work out the detail (or if you have any ideas on this area) i'll drop it here.

jimi

http://www.originaltalent.com
 
Old September 20th, 2007, 07:21 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Max,

you might want to take a look at this article as well. this discusses cache dependencies and shows simple callback techniques for automatically refreshing the cache where required/desired.

http://articles.techrepublic.com.com...2-1052997.html
http://support.microsoft.com/kb/308147

also, one other 'nice' little article to read is this one:

http://www.asp.net/learn/data-access...ial-62-cs.aspx

which deals with the creation of 'dynamic' sitemaps, so if you have category hierarchies etc, then those should be available as breadcrumbs no matter where you navigate from.

btw - i think one of your lines of code in the header must have a stange character in it that is causing the page to become very wide.

jimi

http://www.originaltalent.com
 
Old September 20th, 2007, 10:35 AM
Friend of Wrox
 
Join Date: Mar 2006
Posts: 310
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Jimi!

Sql isn't my area... I only know the minimum for do the basics things!
I didn't understand very well your "CONSTRAINT [PK_UserViewPosts]" CONSTRAINT
I still think that you need a sessionID column in order to delete all values if the current sessionID is different than the sessionID recorded on this table for this member.
But assuming that your code and my code retrieve "isNew" value...
If we want cache values like TBH does, we should forget this method and start to think about do all that under session list!

I think that doesn't make sense program DB to making some routines and program ASP.NET session for make others...
I'm pointing to discuss and choice the best method!

Cache dependencies like nice but I'll waist some time learning how this works and implementing this feature... :(
I don't have this time. I need to upgrade my site from asp.net 1.1 version as soon as possible!

 
Old September 21st, 2007, 03:50 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Max,

CONSTRAINT is basically just telling us that it's a unique index (called PK_UserViewPosts) on the table.

i really need to get my head back into what needs to be done here before adding any further 'ideas' that may only confuse the issue. unfortunately at the moment, i'm not in this 'area' of code but will try to spend some 'quantity' time :) with it over the next few days and see what ideas 'pop-up'.

i'll use this thread to keep this one going.

jimi

http://www.originaltalent.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Forums Extensions III - PageLinks on BrowseThreads Maxxim BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 9 December 11th, 2007 07:08 PM
The Phile II - updates and mods englere BOOK: ASP.NET Website Programming Problem-Design-Solution 19 December 21st, 2005 02:37 PM
TabStrip Part II dkr72 Excel VBA 1 December 7th, 2004 05:44 PM
Connecting to Ingres II n_nikhar ADO.NET 0 June 29th, 2004 08:38 AM
Moderation of Original posts: Feedback forums jminatel Forum and Wrox.com Feedback 1 March 17th, 2004 04:36 PM





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