ASP.NET 2.0 Website Programming Problem - Design - Solution

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