-
cs_guestbook_GetPosts///cs_guestbook_Post_Create
ALTER PROCEDURE [dbo].cs_guestbook_Post_Create
(
@SectionID int,
@Subject nvarchar(256),
@UserID int,
@PostAuthor nvarchar(64) = null,
@Body ntext,
@FormattedBody ntext,
@EmoticonID int = 0,
@PostType int = 0,
@PostDate datetime = null,
@UserHostAddress nvarchar(32),
@PropertyNames ntext = null,
@PropertyValues ntext = null,
@SettingsID int,
@PostID int out
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
![](/Images/OutliningIndicators/None.gif)
BEGIN
SET NOCOUNT ON
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新
@@ROWCOUNT 函数。*/
![](/Images/OutliningIndicators/None.gif)
DECLARE @ThreadID int
![](/Images/OutliningIndicators/None.gif)
-- set the PostDate
-- 设置回帖日期
IF @PostDate IS NULL
SET @PostDate = GetDate()
![](/Images/OutliningIndicators/None.gif)
-- set the username
-- 设置用户名即作者
IF @PostAuthor IS NULL
SELECT
@PostAuthor = UserName
FROM
cs_vw_Users_FullUser
WHERE
cs_UserID = @UserID
![](/Images/OutliningIndicators/None.gif)
-- Do we care about duplicates?
-- 我们是否考虑完全相同的
![](/Images/OutliningIndicators/None.gif)
Select @ThreadID = ThreadID FROM cs_Threads Where SectionID = @SectionID
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新
@@ROWCOUNT 函数。*/
![](/Images/OutliningIndicators/None.gif)
BEGIN TRAN -- 开始事务
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
IF @ThreadID is null
BEGIN
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
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
)
![](/Images/OutliningIndicators/None.gif)
-- Get the new ThreadID
-- 获取主题ID
SELECT
@ThreadID = @@IDENTITY
FROM
cs_Threads
-- Now we add the new post
-- 现在插入新的帖子
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*在主题表中插入数据的时候也要在帖子表中插入数据,因为主题只是帖子的信息统计
具体的内容还是帖子里的.*/
INSERT cs_Posts
( SectionID,
ThreadID,
ParentID,
PostLevel,
SortOrder,
![](/Images/OutliningIndicators/None.gif)
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 )
![](/Images/OutliningIndicators/None.gif)
-- Get the new PostID
-- 获取帖子ID
SELECT
@PostID = @@IDENTITY
![](/Images/OutliningIndicators/None.gif)
-- Update the new Thread with the new PostID
-- 更新主题表
UPDATE
cs_Threads
SET
MostRecentPostID = @PostID
WHERE
ThreadID = @ThreadID
![](/Images/OutliningIndicators/None.gif)
END
ELSE BEGIN -- @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 )
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- Grab the new PostID and update the ThreadID's info
-- 获取新的回帖ID并更新主题表
SELECT
@PostID = @@IDENTITY
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
UPDATE
cs_Threads
SET
TotalReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID)
WHERE
ThreadID = @ThreadID
END
END
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
COMMIT TRAN
![](/Images/OutliningIndicators/None.gif)
SET NOCOUNT OFF
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT @PostID = @PostID
![](/Images/OutliningIndicators/ContractedBlock.gif)
cs_guestbook_GetPosts
ALTER PROCEDURE dbo.cs_guestbook_GetPosts
(
@PageIndex int,
@PageSize int,
@SortOrder bit,
@SectionID int,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
![](/Images/OutliningIndicators/None.gif)
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
![](/Images/OutliningIndicators/None.gif)
-- First set the rowcount
-- 设置行数
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
![](/Images/OutliningIndicators/None.gif)
-- Set the page bounds
-- 设置页面绑定
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- Create a temp table to store the select results
-- 创建临时表
CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
PostID int
)
![](/Images/OutliningIndicators/None.gif)
-- Sort by Post Date
-- 按回帖日期排序
IF @SortOrder = 0
INSERT INTO #PageIndex (PostID)
SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID
![](/Images/OutliningIndicators/None.gif)
ELSE
INSERT INTO #PageIndex (PostID)
SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID DESC
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- 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 > @PageLowerBound AND
#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
IndexID
END
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT Count(PostID) FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID
![](/Images/OutliningIndicators/None.gif)
DROP Table #PageIndex
-- 删除临时表
![](/Images/OutliningIndicators/None.gif)
-
相关阅读:
HTML CSS整理笔记
2020软件工程最后一次作业
form表单的基本用法
图片预加载和懒加载(2)——懒加载
ES6——promise基础
图片预加载和懒加载(2)——预加载
图片预加载和懒加载(1)
js时间——转换为我们需要的格式
原生js瀑布流
富文本——小程序中使用特殊符号及标签
-
原文地址:https://www.cnblogs.com/ruanbl/p/509744.html
Copyright © 2020-2023
润新知