--[1] 완성형 게시판(DotNetNote)용 테이블 설계
Create Table dbo.DotNetNote
(
Num Int Identity(1, 1) Not Null Primary Key, --번호
Name VarChar(25) Not Null, --이름
Email VarChar(100) Null, --이메일
Title VarChar(150) Not Null, --제목
PostDate DateTime Default GetDate() Not Null, --작성일
PostIP VarChar(15) Not Null, --작성IP
Content Text Not Null, --내용
Password VarChar(20) Null, --비밀번호
ReadCount Int Default 0, --조회수
Encoding VarChar(10) Not Null, --인코딩(HTML/Text)
Homepage VarChar(100) Null, --홈페이지
ModifyDate DateTime Null, --수정일
ModifyIP VarChar(15) Null, --수정IP
FileName VarChar(255) Null, --파일명
FileSize Int Default 0, --파일크기
DownCount Int Default 0, --다운수
Ref Int Not Null, --참조(부모글)
Step Int Default 0, --답변깊이(레벨)
RefOrder Int Default 0, --답변순서
AnswerNum Int Default 0, --답변수
ParentNum Int Default 0, --부모글번호
CommentCount Int Default 0, --코멘트수(확장을 위해서...)
Category VarChar(10) Null --카테고리(확장을 위해서...)
)
Go
-- BoardWrite.aspx
--[2] 완성형 게시판(DotNetNote)에 글을 작성 : WriteDotNetNote
Create Proc dbo.WriteDotNetNote
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@Homepage VarChar(100),
-- @Ref Int,
@FileName VarChar(255),
@FileSize Int
As
Declare @MaxRef Int
Select @MaxRef = Max(Ref) From DotNetNote
If @MaxRef is Null
Set @MaxRef = 1 -- 테이블 생성 후 처음만 비교
Else
Set @MaxRef = @MaxRef + 1
Insert DotNetNote(Name, Email, Title, PostIP, Content, Password, Encoding, Homepage, Ref, FileName, FileSize)
Values(@Name, @Email, @Title, @PostIP, @Content, @Password, @Encoding, @Homepage, @MaxRef, @FileName, @FileSize)
Go
-- BoardList.aspx
--[3][1] 완성형 게시판(DotNetNote)에서 데이터 출력 : ListDotNetNote
Create Procedure dbo.ListDotNetNote
As
Select * From DotNetNote Order By Ref Desc, RefOrder Asc
Go
--[3][2] 고급 페이징
Alter Procedure dbo.ListDotNetNote
@Page Int
As
Select Top 10 *
From DotNetNote
Where Num Not In (Select Top (10 * @Page) Num From DotNetNote Order By Ref Desc, RefOrder Asc)
Order By Ref Desc, RefOrder Asc
Go
--[3][3] 고급 페이징(SQL Server 2000/2005 공용 쿼리문)
Alter Procedure dbo.ListDotNetNote
@Page Int
As
Declare @strSql VarChar(500)
Set @strSql = '
Select Top 10 *
From DotNetNote
Where Num Not In (Select Top (10 * ' + Convert(VarChar,@Page) + ') Num
From DotNetNote Order By Ref Desc, RefOrder Asc)
Order By Ref Desc, RefOrder Asc
'
Exec(@strSql)
Go
Exec ListDotNetNote 0
Go
--[4] 해당 글을 세부적으로 읽어오는 저장 프로시저 : ViewDotNetNote
Create Procedure dbo.ViewDotNetNote
@Num Int
As
Update DotNetNote Set ReadCount = ReadCount + 1 Where Num = @Num
Select * From DotNetNote Where Num = @Num
Go
--BoardReply.aspx
--[5] 완성형 게시판(DotNetNote)에 글을 답변 : ReplyDotNetNote
Create Proc dbo.ReplyDotNetNote
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@Homepage VarChar(100),
@Ref Int,
@Step Int,
@RefOrder Int,
@ParentNum Int,
@FileName VarChar(255),
@FileSize Int
As
Insert DotNetNote
(Name, Email, Title, PostIP, Content,
Password, Encoding, Homepage, Ref, Step, RefOrder,
ParentNum, FileName, FileSize)
Values
(@Name, @Email, @Title, @PostIP, @Content,
@Password, @Encoding, @Homepage, @Ref, @Step, @RefOrder,
@ParentNum, @FileName, @FileSize)
Go
--[5] 답변하기 : SqlHelper 방식
Alter Proc dbo.ReplyDotNetNote
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@Homepage VarChar(100),
@ParentNum Int, -- 부모글의 고유번호(Num)
@FileName VarChar(255),
@FileSize Int
As
--[0] 변수 선언
Declare @MaxRefOrder Int
Declare @MaxRefAnswerNum Int
Declare @ParentRef Int
Declare @ParentStep Int
Declare @ParentRefOrder Int
--[1] 부모글의 답변수(AnswerNum)를 1증가
Update DotNetNote Set AnswerNum = AnswerNum + 1 Where Num = @ParentNum
--[2] 같은 글에 대해서 답변을 두 번 이상하면 먼저 답변한 게 위에 나타나게 한다.
Select @MaxRefOrder = RefOrder, @MaxRefAnswerNum = AnswerNum From DotNetNote
Where ParentNum = @ParentNum And RefOrder = (Select Max(RefOrder) From DotNetNote Where ParentNum = @ParentNum)
If @MaxRefOrder Is Null
Begin
Select @MaxRefOrder = RefOrder From DotNetNote Where Num = @ParentNum
Set @MaxRefAnswerNum = 0
End
--[3] 중간에 답변달 때(비집고 들어갈 자리 마련)
Select @ParentRef = Ref, @ParentStep = Step From DotNetNote Where Num = @ParentNum
Update DotNetNote Set RefOrder = RefOrder + 1 Where Ref = @ParentRef And RefOrder > (@MaxRefOrder + @MaxRefAnswerNum)
--[4] 최종저장
Insert DotNetNote(Name, Email, Title, PostIP, Content, Password, Encoding, Homepage, Ref, Step, RefOrder, ParentNum, FileName, FileSize)
Values(@Name, @Email, @Title, @PostIP, @Content, @Password, @Encoding, @Homepage, @ParentRef, @ParentStep + 1, @MaxRefOrder + @MaxRefAnswerNum + 1, @ParentNum, @FileName, @FileSize)
Go
--[6] DotNetNote 테이블에 있는 레코드의 개수를 구하는 저장 프로시저
Create Proc dbo.GetCountDotNetNote
As
Select Count(*) From DotNetNote
Go
--[8] 해당 글을 지우는 저장 프로시저
-- 답변글이 있으면 업데이트하고 없으면 지운다.
--Drop Proc dbo.DeleteDotNetNote
Create Proc dbo.DeleteDotNetNote
@Num Int,
@Password VarChar(30) -- 암호 매개변수 추가
As
Declare @cnt Int
Select @cnt = Count(*) From DotNetNote
Where Num = @Num And Password = @Password
If @cnt = 0
Begin
Return 0 -- 번호와 암호가 맞는게 없으면 0을 반환
End
Declare @AnswerNum Int
Declare @RefOrder Int
Declare @Ref Int
Declare @ParentNum Int
Select
@AnswerNum = AnswerNum,
@RefOrder = RefOrder,
@Ref = Ref,
@ParentNum = ParentNum
From
DotNetNote
Where Num = @Num
If @AnswerNum = 0
Begin
If @RefOrder > 0
Begin
UPDATE DotNetNote SET RefOrder = RefOrder - 1
WHERE Ref = @Ref AND RefOrder > @RefOrder
UPDATE DotNetNote SET AnswerNum = AnswerNum - 1
WHERE Num = @ParentNum
End
Delete DotNetNote Where Num = @Num
Delete DotNetNote WHERE Num = @ParentNum AND
ModifyIP = '((DELETED))' AND AnswerNum = 0
End
Else
Begin
Update DotNetNote
Set Name = '(Unknown)', Email = NULL, Password = NULL,
Title = '(삭제된 글입니다.)',
Content = '(삭제된 글입니다. '
+ '현재 답변이 포함되어 있기 때문에 내용만 삭제되었습니다.)',
ModifyIP = '((DELETED))', FileName = NULL,
FileSize = NULL, CommentCount = 0
Where Num = @Num
End
Go
-- BoardModify.aspx
--[9] 해당 글을 수정하는 저장 프로시저 : ModifyDotNetNote
Create Proc dbo.ModifyDotNetNote
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@ModifyIP VarChar(15),
@Content VarChar(8000),
@Password VarChar(30), -- 추가
@Encoding VarChar(10),
@Homepage VarChar(100),
@Num Int
As
Declare @cnt Int
Select @cnt = Count(*) From DotNetNote
Where Num = @Num And Password = @Password
If @cnt > 0 -- 번호와 암호가 맞는게 있다면...
Begin
Update DotNetNote
Set
Name = @Name, Email = @Email, Title = @Title,
ModifyIP = @ModifyIP, ModifyDate = GetDate(),
Content = @Content, Encoding = @Encoding,
Homepage = @Homepage
Where Num = @Num
Select '1'
End
Else
Select '0'
Go
--BoardSearch.aspx
--[10] 필드명에서 레코드를 검색하는 저장프로시저
Create Proc dbo.SearchDotNetNote
@SearchField VarChar(25),
@SearchQuery VarChar(25)
As
Set @SearchQuery = '%' + @SearchQuery + '%'
Select *
From DotNetNote
Where
(
Case @SearchField
When 'Name' Then [Name]
When 'Email' Then Email
When 'Title' Then Title
Else @SearchQuery
End
)
Like
@SearchQuery
Order By Num Desc
Go
-- 동적 쿼리문으로 변경
Alter Proc dbo.SearchDotNetNote
@SearchField VarChar(25),
@SearchQuery VarChar(25),
@Page Int
As
-- 순수문자열 : Name -> ' + 해당변수 + '
Declare @strSql VarChar(500)
Set @strSql = '
Select Top 10 * From DotNetNote
Where ' + @SearchField + ' Like ''%' + @SearchQuery + '%'' And Num Not In
(
Select Top (10 * ' + Convert(VarChar, @Page) + ') Num From DotNetNote
Where ' + @SearchField + ' Like ''%' + @SearchQuery + '%''
Order By Ref Desc, RefOrder Asc
)
Order By Ref Desc, RefOrder Asc
'
--Print @strSql
Exec(@strSql)
Go
-- 코멘트 테이블 생성
Create Table dbo.DotNetNoteComments
(
Num Int Identity(1, 1) Not Null Primary Key, -- 일련번호
BoardName VarChar(50) Null, -- 게시판이름(확장) : Notice, Free, Qna
BoardNum Int Not Null, -- 해당 게시판의 게시물 번호
Name VarChar(25) Not Null, -- 작성자
Opinion VarChar(8000) Not Null, -- 코멘트 내용
PostDate SmallDateTime Default(GetDate()), -- 작성일
Password VarChar(20) Not Null -- 코멘트 삭제용 암호
)
Go