1 --[1] 완성형 게시판(DotNetNote)용 테이블 설계
2 Create Table dbo.DotNetNote
3 (
4 Num Int Identity(1, 1) Not Null Primary Key, --번호
5 Name VarChar(25) Not Null, --이름
6 Email VarChar(100) Null, --이메일
7 Title VarChar(150) Not Null, --제목
8 PostDate DateTime Default GetDate() Not Null, --작성일
9 PostIP VarChar(15) Not Null, --작성IP
10 Content Text Not Null, --내용
11 Password VarChar(20) Null, --비밀번호
12 ReadCount Int Default 0, --조회수
13 Encoding VarChar(10) Not Null, --인코딩(HTML/Text)
14 Homepage VarChar(100) Null, --홈페이지
15 ModifyDate DateTime Null, --수정일
16 ModifyIP VarChar(15) Null, --수정IP
17 FileName VarChar(255) Null, --파일명
18 FileSize Int Default 0, --파일크기
19 DownCount Int Default 0, --다운수
20 Ref Int Not Null, --참조(부모글)
21 Step Int Default 0, --답변깊이(레벨)
22 RefOrder Int Default 0, --답변순서
23 AnswerNum Int Default 0, --답변수
24 ParentNum Int Default 0, --부모글번호
25 CommentCount Int Default 0, --코멘트수(확장을 위해서...)
26 Category VarChar(10) Null --카테고리(확장을 위해서...)
27 )
28 Go
29
30 -- BoardWrite.aspx
31 --[2] 완성형 게시판(DotNetNote)에 글을 작성 : WriteDotNetNote
32 Create Proc dbo.WriteDotNetNote
33 @Name VarChar(25),
34 @Email VarChar(100),
35 @Title VarChar(150),
36 @PostIP VarChar(15),
37 @Content Text,
38 @Password VarChar(20),
39 @Encoding VarChar(10),
40 @Homepage VarChar(100),
41 @Ref Int,
42 @FileName VarChar(255),
43 @FileSize Int
44 As
45 Insert DotNetNote
46 (Name, Email, Title, PostIP, Content, Password,
47 Encoding, Homepage, Ref, FileName, FileSize)
48 Values
49 (@Name, @Email, @Title, @PostIP, @Content, @Password,
50 @Encoding, @Homepage, @Ref, @FileName, @FileSize)
51 Go
52
53 -- BoardList.aspx
54 --[3] 완성형 게시판(DotNetNote)에서 데이터 출력 : ListDotNetNote
55 Create Procedure dbo.ListDotNetNote
56 As
57 Select * From DotNetNote Order By Ref Desc, RefOrder Asc
58 Go
59
60 --[4] 해당 글을 세부적으로 읽어오는 저장 프로시저 : ViewDotNetNote
61 Create Procedure dbo.ViewDotNetNote
62 @Num Int
63 As
64 Update DotNetNote Set ReadCount = ReadCount + 1 Where Num = @Num
65
66 Select * From DotNetNote Where Num = @Num
67 Go
68
69 --BoardReply.aspx
70 --[5] 완성형 게시판(DotNetNote)에 글을 답변 : ReplyDotNetNote
71 Create Proc dbo.ReplyDotNetNote
72 @Name VarChar(25),
73 @Email VarChar(100),
74 @Title VarChar(150),
75 @PostIP VarChar(15),
76 @Content Text,
77 @Password VarChar(20),
78 @Encoding VarChar(10),
79 @Homepage VarChar(100),
80 @Ref Int,
81 @Step Int,
82 @RefOrder Int,
83 @ParentNum Int,
84 @FileName VarChar(255),
85 @FileSize Int
86 As
87 Insert DotNetNote
88 (Name, Email, Title, PostIP, Content,
89 Password, Encoding, Homepage, Ref, Step, RefOrder,
90 ParentNum, FileName, FileSize)
91 Values
92 (@Name, @Email, @Title, @PostIP, @Content,
93 @Password, @Encoding, @Homepage, @Ref, @Step, @RefOrder,
94 @ParentNum, @FileName, @FileSize)
95 Go
96
97 --[6] DotNetNote 테이블에 있는 레코드의 개수를 구하는 저장 프로시저
98 Create Proc dbo.GetCountDotNetNote
99 As
100 Select Count(*) From DotNetNote
101 Go
102
103 --[7] BoardModify.aspx, BoardDelete.aspx
104 -- 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 : ReadPasswordDotNetNote
105 Create Proc dbo.ReadPasswordDotNetNote
106 @Num Int
107 As
108 Select Password From DotNetNote Where Num = @Num
109 Go
110
111 --[8] 해당 글을 지우는 저장 프로시저
112 -- 답변글이 있으면 업데이트하고 없으면 지운다.
113 Create Proc dbo.DeleteDotNetNote
114 @Num Int
115 As
116
117 Declare @AnswerNum Int
118 Declare @RefOrder Int
119 Declare @Ref Int
120 Declare @ParentNum Int
121
122 Select
123 @AnswerNum = AnswerNum,
124 @RefOrder = RefOrder,
125 @Ref = Ref,
126 @ParentNum = ParentNum
127 From
128 DotNetNote
129 Where Num = @Num
130
131 If @AnswerNum = 0
132 Begin
133 If @RefOrder > 0
134 Begin
135 UPDATE DotNetNote SET RefOrder = RefOrder - 1
136 WHERE Ref = @Ref AND RefOrder > @RefOrder
137 UPDATE DotNetNote SET AnswerNum = AnswerNum - 1
138 WHERE Num = @ParentNum
139 End
140 Delete DotNetNote Where Num = @Num
141 Delete DotNetNote WHERE Num = @ParentNum AND
142 ModifyIP = '((DELETED))' AND AnswerNum = 0
143 End
144 Else
145 Begin
146 Update DotNetNote
147 Set Name = '(Unknown)', Email = NULL, Password = NULL,
148 Title = '(삭제된 글입니다.)',
149 Content = '(삭제된 글입니다. '
150 + '현재 답변이 포함되어 있기 때문에 내용만 삭제되었습니다.)',
151 ModifyIP = '((DELETED))', FileName = NULL,
152 FileSize = NULL, CommentCount = 0
153 Where Num = @Num
154 End
155 Go
156
157 -- BoardModify.aspx
158 --[9] 해당 글을 수정하는 저장 프로시저 : ModifyDotNetNote
159 Create Proc dbo.ModifyDotNetNote
160 @Name VarChar(25),
161 @Email VarChar(100),
162 @Title VarChar(150),
163 @ModifyIP VarChar(15),
164 @ModifyDate DateTime,
165 @Content Text,
166 @Encoding VarChar(10),
167 @Homepage VarChar(100),
168 @Num Int
169 As
170 Update DotNetNote
171 Set
172 Name = @Name,
173 Email = @Email,
174 Title = @Title,
175 ModifyIP = @ModifyIP,
176 ModifyDate = @ModifyDate,
177 Content = @Content,
178 Encoding = @Encoding,
179 Homepage = @Homepage
180 Where Num = @Num
181 Go
182
183 --BoardSearch.aspx
184 --[10] 필드명에서 레코드를 검색하는 저장프로시저
185 Create Proc dbo.SearchDotNetNote
186 @SearchField VarChar(25),
187 @SearchQuery VarChar(25)
188 As
189 Set @SearchQuery = '%' + @SearchQuery + '%'
190 Select *
191 From DotNetNote
192 Where
193 (
194 Case @SearchField
195 When 'Name' Then [Name]
196 When 'Email' Then Email
197 When 'Title' Then Title
198 Else @SearchQuery
199 End
200 )
201 Like
202 @SearchQuery
203 Order By Num Desc
204 Go
205