O'Reilly logo

ASP.NET 2.0 Website Programming Problem - Design - Solution by Marco Bellinaso

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.3. Solution

In this section we'll cover the implementation of key parts of this module, as described in the "Design" section. But you won't find complete source code printed here, as many similar classes were discussed in other chapters. See the code download to get the complete source code.

8.3.1. Implementing the Database

The most interesting stored procedure is tbh_Forums_InsertPost. This inserts a new record into the tbh_Posts table, and if the new post being inserted is approved it must also update the ReplyCount, LastPostBy, and LastPostDate fields of this post's parent post. Because there are multiple statements in this stored procedure, a transaction is used to ensure that they are both either committed successfully or rolled back:

ALTER PROCEDURE dbo.tbh_Forums_InsertPost ( @AddedDate datetime, @AddedBy nvarchar(256), @AddedByIP nchar(15), @ForumID int, @ParentPostID int, @Title nvarchar(256), @Body ntext, @Approved bit, @Closed bit, @PostID int OUTPUT ) AS SET NOCOUNT ON BEGIN TRANSACTION InsertPost INSERT INTO tbh_Posts (AddedDate, AddedBy, AddedByIP, ForumID, ParentPostID, Title, Body, Approved, Closed, LastPostDate, LastPostBy) VALUES (@AddedDate, @AddedBy, @AddedByIP, @ForumID, @ParentPostID, @Title, @Body, @Approved, @Closed, @AddedDate, @AddedBy) SET @PostID = scope_identity() -- if the post is approved, update the parent post's -- ReplyCount and LastReplyDate fields IF @Approved = 1 AND @ParentPostID > 0 BEGIN UPDATE tbh_Posts SET ReplyCount = ReplyCount + ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required