ALTERPROCEDURE[dbo].cs_guestbook_Post_Create ( @SectionIDint, @Subjectnvarchar(256), @UserIDint, @PostAuthornvarchar(64) =null, @Bodyntext, @FormattedBodyntext, @EmoticonIDint=0, @PostTypeint=0, @PostDatedatetime=null, @UserHostAddressnvarchar(32), @PropertyNamesntext=null, @PropertyValuesntext=null, @SettingsIDint, @PostIDint out ) AS SETTransactionIsolationLevelReadUNCOMMITTED /**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁, 也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更 改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有 语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/ BEGIN SET NOCOUNT ON /**//*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。 当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。*/ DECLARE@ThreadIDint -- set the PostDate -- 设置回帖日期 IF@PostDateISNULL SET@PostDate=GetDate() -- set the username -- 设置用户名即作者 IF@PostAuthorISNULL SELECT @PostAuthor= UserName FROM cs_vw_Users_FullUser WHERE cs_UserID =@UserID -- Do we care about duplicates? -- 我们是否考虑完全相同的 Select@ThreadID= ThreadID FROM cs_Threads Where SectionID =@SectionID SET NOCOUNT ON /**//*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。 当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。*/ BEGINTRAN-- 开始事务 IF@ThreadIDisnull BEGIN INSERT cs_Threads -- 插入数据 ( SectionID, PostDate, UserID, PostAuthor, ThreadDate, MostRecentPostAuthor, MostRecentPostAuthorID, MostRecentPostID, IsLocked, IsApproved, IsSticky, StickyDate, ThreadEmoticonID, SettingsID ) VALUES ( @SectionID, @PostDate, @UserID, @PostAuthor, @PostDate, @PostAuthor, @UserID, 0, -- MostRecentPostID, which we don't know until after post INSERT below. 0, 1, 0, @PostDate, -1, @SettingsID ) -- Get the new ThreadID -- 获取主题ID SELECT @ThreadID=@@IDENTITY FROM cs_Threads -- Now we add the new post -- 现在插入新的帖子 /**//*在主题表中插入数据的时候也要在帖子表中插入数据,因为主题只是帖子的信息统计 具体的内容还是帖子里的.*/ INSERT cs_Posts ( SectionID, ThreadID, ParentID, PostLevel, SortOrder, Subject, UserID, PostAuthor, IsApproved, IsLocked, Body, FormattedBody, PostType, PostDate, IPAddress, EmoticonID, PropertyNames, PropertyValues, SettingsID ) VALUES ( @SectionID, @ThreadID, 0, -- ParentID, which we don't know until after INSERT 1, -- PostLevel, 1 marks start/top/first post in thread. 1, -- SortOrder (not in use at this time) @Subject, @UserID, @PostAuthor, 1, 0, @Body, @FormattedBody, @PostType, @PostDate, @UserHostAddress, -1, @PropertyNames, @PropertyValues, @SettingsID ) -- Get the new PostID -- 获取帖子ID SELECT @PostID=@@IDENTITY -- Update the new Thread with the new PostID -- 更新主题表 UPDATE cs_Threads SET MostRecentPostID =@PostID WHERE ThreadID =@ThreadID END ELSEBEGIN-- @ParentID <> 0 means there is a reply to an existing post -- 如果@ParentID不为0则表示有回复 -- Insert the new post INSERT cs_Posts ( SectionID, ThreadID, ParentID, PostLevel, SortOrder, Subject, UserID, PostAuthor, IsApproved, IsLocked, Body, FormattedBody, PostType, PostDate, IPAddress, EmoticonID, PropertyNames, PropertyValues, SettingsID ) VALUES ( @SectionID, @ThreadID, 0, 1, 0, @Subject, @UserID, @PostAuthor, 1, 0, @Body, @FormattedBody, @PostType, @PostDate, @UserHostAddress, -1, @PropertyNames, @PropertyValues, @SettingsID ) -- Grab the new PostID and update the ThreadID's info -- 获取新的回帖ID并更新主题表 SELECT @PostID=@@IDENTITY UPDATE cs_Threads SET TotalReplies = (SELECTCOUNT(*) FROM cs_Posts WHERE ThreadID =@ThreadID) WHERE ThreadID =@ThreadID END END COMMITTRAN SET NOCOUNT OFF SELECT@PostID=@PostID
cs_guestbook_GetPosts ALTERPROCEDURE dbo.cs_guestbook_GetPosts ( @PageIndexint, @PageSizeint, @SortOrderbit, @SectionIDint, @SettingsIDint ) AS SETTransactionIsolationLevelReadUNCOMMITTED BEGIN DECLARE@PageLowerBoundint DECLARE@PageUpperBoundint DECLARE@ThreadIDint -- First set the rowcount -- 设置行数 DECLARE@RowsToReturnint SET@RowsToReturn=@PageSize* (@PageIndex+1) SETROWCOUNT@RowsToReturn -- Set the page bounds -- 设置页面绑定 SET@PageLowerBound=@PageSize*@PageIndex SET@PageUpperBound=@PageLowerBound+@PageSize+1 -- Create a temp table to store the select results -- 创建临时表 CREATETABLE #PageIndex ( IndexID intIDENTITY (1, 1) NOTNULL, PostID int ) -- Sort by Post Date -- 按回帖日期排序 IF@SortOrder=0 INSERTINTO #PageIndex (PostID) SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved =1AND SectionID =@SectionIDand SettingsID =@SettingsIDORDERBY PostID ELSE INSERTINTO #PageIndex (PostID) SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved =1AND SectionID =@SectionIDand SettingsID =@SettingsIDORDERBY PostID DESC -- Select the individual posts -- 查询个人的帖子 SELECT P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved, P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews, P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues, P.PostConfiguration, P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings, T.*, U.*, #PageIndex.*, T.IsLocked, T.IsSticky, Username = P.PostAuthor, EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID), AttachmentFilename ='', Replies =0, IsModerator =0, HasRead =0-- not used FROM cs_Posts P (nolock), cs_Threads T, cs_vw_Users_FullUser U, #PageIndex WHERE P.PostID = #PageIndex.PostID AND P.UserID = U.cs_UserID AND T.ThreadID = P.ThreadID AND #PageIndex.IndexID >@PageLowerBoundAND #PageIndex.IndexID <@PageUpperBoundand U.SettingsID =@SettingsID ORDERBY IndexID END SELECTCount(PostID) FROM cs_Posts (nolock) WHERE IsApproved =1AND SectionID =@SectionIDand SettingsID =@SettingsID DROPTable #PageIndex -- 删除临时表