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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #81 (permalink)  
Old September 26th, 2007, 02:35 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Copenhagen, , Denmark.
Posts: 143
Thanks: 0
Thanked 1 Time in 1 Post
Default

jimi,

A few quick questions / remarks. I have pretty much used my own formatting in the TBH-code. First of all I replaced everything tbh_ with my own namespaces etc. Secondly, I used line breaks etc to make the code a tad more readable.

Of course, all of that bit me in the ass when I tried comparing your code with my code (using ExamDiff. I know, I'm just one of those people who wants to know what changed ;)), so if anyone new reads this, it'll save you time sticking to Marco's formatting.

In your readme, for the posts.cs you state that the only change you made was an addition to the cache purging. There's numerous other changes as well, though.

Also, here's a little something to compliment your code. It's from adding the Priority column to the Posts table. I chose just to give it a default value of 0. The script can be used if there is data in the table (I think. I think mine is empty, but it ought to work anyway):

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.EO_Posts
    DROP CONSTRAINT DF_EO_Posts_AddedDate
GO
ALTER TABLE dbo.EO_Posts
    DROP CONSTRAINT DF_EO_Posts_ParentPostID
GO
ALTER TABLE dbo.EO_Posts
    DROP CONSTRAINT DF_Table2_Approved
GO
ALTER TABLE dbo.EO_Posts
    DROP CONSTRAINT DF_EO_Posts_Closed
GO
ALTER TABLE dbo.EO_Posts
    DROP CONSTRAINT DF_EO_Posts_ViewCount
GO
ALTER TABLE dbo.EO_Posts
    DROP CONSTRAINT DF_EO_Posts_ReadCount
GO
CREATE TABLE dbo.Tmp_EO_Posts
    (
    PostID int NOT NULL IDENTITY (1, 1),
    AddedDate datetime NOT NULL,
    AddedBy nvarchar(256) NOT NULL,
    AddedByIP nchar(15) NOT NULL,
    ForumID int NOT NULL,
    ParentPostID int NOT NULL,
    Title nvarchar(256) NOT NULL,
    Body ntext NOT NULL,
    Approved bit NOT NULL,
    Closed bit NOT NULL,
    ViewCount int NOT NULL,
    ReplyCount int NOT NULL,
    Priority int NOT NULL,
    LastPostBy nvarchar(256) NOT NULL,
    LastPostDate datetime NOT NULL
    ) ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_EO_Posts_AddedDate DEFAULT (getdate()) FOR AddedDate
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_EO_Posts_ParentPostID DEFAULT ((0)) FOR ParentPostID
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_Table2_Approved DEFAULT ((1)) FOR Approved
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_EO_Posts_Closed DEFAULT ((0)) FOR Closed
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_EO_Posts_ViewCount DEFAULT ((0)) FOR ViewCount
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_EO_Posts_ReadCount DEFAULT ((0)) FOR ReplyCount
GO
ALTER TABLE dbo.Tmp_EO_Posts ADD CONSTRAINT
    DF_EO_Posts_Priority DEFAULT 0 FOR Priority
GO
SET IDENTITY_INSERT dbo.Tmp_EO_Posts ON
GO
IF EXISTS(SELECT * FROM dbo.EO_Posts)
     EXEC('INSERT INTO dbo.Tmp_EO_Posts (PostID, AddedDate, AddedBy, AddedByIP, ForumID, ParentPostID, Title, Body, Approved, Closed, ViewCount, ReplyCount, LastPostBy, LastPostDate)
        SELECT PostID, AddedDate, AddedBy, AddedByIP, ForumID, ParentPostID, Title, Body, Approved, Closed, ViewCount, ReplyCount, LastPostBy, LastPostDate FROM dbo.EO_Posts WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_EO_Posts OFF
GO
DROP TABLE dbo.EO_Posts
GO
EXECUTE sp_rename N'dbo.Tmp_EO_Posts', N'EO_Posts', 'OBJECT'
GO
ALTER TABLE dbo.EO_Posts ADD CONSTRAINT
    PK_EO_Posts PRIMARY KEY CLUSTERED
    (
    PostID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT


Overall, you did some awesome work that saved me (and undoubtedly others as well) numerous hours of work. Thank you so much :)

I''ll see if at any time soon I'll get a chance to check out the code with the temporary tables to see if it can be optimised. One possible quick win would be to use table variables instead of temp tables, but it depends on exactly what happens in the code.

For now, I would love to check it out, but I'm hoping to have a site up before the end of the weekend, and I still want to try and have a go at implementing private messages, and the Webparts (if I understand correctly, they're like widgets. However, I haven't reached that section of the book yet) before then as well, so you'll have to forgive me :)

Maxxim - I'll also see some time after launch if I can somehow shift the user's postcount to a column of its own, and I'll keep you in the loop :)

Cheers,

Peter

http://entropia-online.blogspot.com/
  #82 (permalink)  
Old September 26th, 2007, 03:00 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Peter,

good luck with the launch at the weekend, i'll be sure to check out the final (is it ever final :) result!!). As for optimisation, looking at your SQL skills, i have no doubt that you'll find a very elegant way to optimise the sql with wins all round.

keep it up!!

[edit] i may have actually changed my sql code since 'those days' when i put up that 1st cut at the forums, in which case, i'll drop off my latest SQL on that stuff at some point and leave a pointer to where to pick it up from.

jimi

http://www.originaltalent.com
  #83 (permalink)  
Old September 26th, 2007, 03:43 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Copenhagen, , Denmark.
Posts: 143
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jimi,

Cheers :)

I used version 1.3 which was the latest I could find :)

And I'll keep you updated on the site :) We (the missus and me) handpicked a bunch of people who'll test things for us. Hopefully soon after, we can officially launch, and then the task of expansion will start. I bet I'll be using your autogenerator and my CRRUD generator a lot ;)

Peter

http://entropia-online.blogspot.com/
  #84 (permalink)  
Old October 1st, 2007, 12:28 PM
Friend of Wrox
 
Join Date: Mar 2006
Location: , , Portugal.
Posts: 310
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello!

Jimi, when you have some time, open your "getForums" sql procedure, and compare yours with mine:

Code:
PROCEDURE dbo.Forums_GetForums
AS
SET NOCOUNT ON

SELECT ForumID, AddedDate, AddedBy, ParentID, 
        (SELECT Title From dbo.Forums_Forums WHERE ForumID = a.ParentID) as ParentTitle, 
        CategoryID, 
        (SELECT Title From dbo.Forums_Categories WHERE CategoryID = a.CategoryID) as CategoryTitle, 
        0 as IsNew,
        Title, Prefix, Importance, Description, ImageUrl,
        (SELECT Count(PostID) From dbo.Forums_Posts WHERE ParentPostID = 0 AND ForumID = a.ForumID) as Topics, 
        (SELECT Count(PostID) From dbo.Forums_Posts WHERE ParentPostID > 0 AND ForumID = a.ForumID) as Replies, 
        (Select LastPostDate FROM (SELECT Max(LastPostDate) as LastPostDate From dbo.Forums_Posts WHERE ForumID = a.ForumID) AS LastDate) as LastPostDate,
        (SELECT TOP 1 LastPostBy FROM dbo.Forums_Posts WHERE ForumID = a.ForumID AND ParentPostID = 0 ORDER BY LastPostDate DESC) as LastPostBy

   FROM dbo.Forums_Forums a
   ORDER BY Importance ASC, Title ASC
I added some columns but forget them.
In your procedure you created a couple of auxiliar tables and I don't understand if they are really necessary!
I'm kind of a newbie on sql matter's so...

I think my procedure is cleaner than yours but probably yours is more efficient!
Give me your opinion...

Thanks!

  #85 (permalink)  
Old October 1st, 2007, 02:02 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Max,

your's looks FAR better than mine, i really like it. i'll tweak it at this end to fit with how my posts table is now structured!!

thanks for that - nice 'n' clean and simple

cheers

jimi

http://www.originaltalent.com
  #86 (permalink)  
Old October 1st, 2007, 03:05 PM
Friend of Wrox
 
Join Date: Mar 2006
Location: , , Portugal.
Posts: 310
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jimi, I'm glad you liked...

I made this thinking sql 2000. I don't no yet how to take some advantage from the "GROUP BY" that sql 2005 has...

That was the reason because i had some doubts about the efficiency of this procedure...

Thanks for the feedback!








  #87 (permalink)  
Old October 1st, 2007, 03:13 PM
Friend of Wrox
 
Join Date: Mar 2006
Location: , , Portugal.
Posts: 310
Thanks: 0
Thanked 0 Times in 0 Posts
Default

BTW, if you want to eliminate all the sql connections using direct queryStrings, you can use something like this:

Code:
PROCEDURE dbo.Forums_GetThreadsByForum
(
   @ForumID     int,
   @LowerBound  int,
   @UpperBound  int,
   @SortExpression nvarchar(256)
)
AS
SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
DECLARE @INITSQL nvarchar(2500)
DECLARE @ENDSQL nvarchar(1000)

SELECT @INITSQL = 'SELECT * FROM 
   (SELECT    Forums_Posts.PostID, 
            Forums_Posts.PollID,
            Forums_Posts.Priority,
            Forums_Posts.AddedDate, 
            Forums_Posts.AddedBy, 
            Forums_Posts.AddedByIP,
            Forums_Posts.ForumID, 
            Forums_Posts.ParentPostID, 
            Forums_Posts.Title,
            Forums_Posts.Closed, 
            Forums_Posts.ViewCount, 
            Forums_Posts.ReplyCount, 
            Forums_Posts.LastPostDate,
            Forums_Posts.LastPostBy, 
            Forums_Forums.Title AS ForumTitle,
            --0 AS isRead,
            ROW_NUMBER() OVER (ORDER BY '

 SELECT @ENDSQL = ' ) AS RowNum
            FROM Forums_Posts INNER JOIN
            Forums_Forums ON Forums_Posts.ForumID = Forums_Forums.ForumID
            WHERE Forums_Posts.ForumID = @ForumID AND ParentPostID = 0) ForumThreads
    WHERE ForumThreads.RowNum BETWEEN @LowerBound AND @UpperBound
    ORDER BY RowNum ASC'

SELECT @SQL = @INITSQL + @SortExpression + @ENDSQL

EXEC sp_executesql @SQL,
     N'@ForumID int, @LowerBound int, @UpperBound int',
     @ForumID, @LowerBound, @UpperBound
With this method you can erase all "bad" connections that TBH has...
I don't understand why Marco doesn't used something like this...

I hadn't see, yet, your search engine thread, but this is usefull for that too!
I made that for version 1!


  #88 (permalink)  
Old October 2nd, 2007, 09:42 AM
Registered User
 
Join Date: Oct 2007
Location: Bristol, Avon, United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jimibt
 OK,

Finally, the latest version of this forums/posts thing. this release includes both the new layout for the Forum listing as per previous messages, toghether with the much vaunted Priority flag for top level posts (can only be applied by admin/editors of course!!).

ok, grab yourself the code here:

http://www.worldindependentnews.com/...UpdateV1.3.zip

Good luck, jim

see it all in action at:

http://www.jamestollan.com/Default.aspx

Hi Jim,

I have been reading this thread over the last two days. TheBeerHouse is such a great starter kit and you have done some excellent stuff with the Forums section. (I wonder why the codeplex site isn't more user friendly to encourage this sort of chat and development??).

Anyway, in the above zip file, you are still missing the storedproc 'tbh_Forums_UnCloseThread'. Anychance you can paste it into the forum?

Jon

  #89 (permalink)  
Old October 2nd, 2007, 11:46 AM
Friend of Wrox
 
Join Date: Mar 2006
Location: , , Portugal.
Posts: 310
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code:
CREATE PROCEDURE dbo.tbh_Forums_UnCloseThread
(
    @ThreadPostID  int
)
AS

UPDATE tbh_Posts
   SET Closed = 0
   WHERE PostID = @ThreadPostID
  #90 (permalink)  
Old October 2nd, 2007, 02:47 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

max - thanks for that.

jon s - stick around and hopefully, you'll sow what you reap :). there's plenty to be done in terms of enhancements and efficiency 'tweaks'...

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
AJAX Extensions ilegend .NET Framework 3.5 1 September 19th, 2008 03:32 AM
Some Extensions plb BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 July 29th, 2008 11:57 AM
PHP Extensions Dnigma Pro PHP 0 June 22nd, 2006 12:41 AM
File Extensions nbnelson C# 2 August 25th, 2003 12:09 PM
converting Forum.aspx to Forum.ascx (help) drfunkie BOOK: ASP.NET Website Programming Problem-Design-Solution 1 July 11th, 2003 12:27 PM



All times are GMT -4. The time now is 03:08 PM.


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