00.ASP.NET - 기본형 게시판(1) : DB설계
Database : Basic , 사용자 : Basic , 사용자 비밀번호 : 12345
--[0]
기본형게시판(Basic)용테이블설계
--[!]
Drop Table dbo.Basic
Create Table Basic
(
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(), --작성일
PostIP VarChar(15) Not Null, --작성IP
Content Text not Null, --내용
Password VarChar(20) Not Null, --비밀번호
ReadCount
Int Default 0, --조회수
Encoding VarChar(10) Not Null, --인코딩(HTML/Text/Mixed)
Homepage VarChar(100) Null, --홈페이지
ModifyDate
SmallDateTime Null, --수정일
ModifyIP VarChar(15) Null --수정IP
)
Go
--[!]
6개예시문
--[1]
입력: Write.aspx
Insert Into dbo.Basic Values('민', 'min@min.com', '안녕',
GETDATE(), '121.0.0.1', '안녕하세요', '1234', 0, 'HTML',
'minhompage.com', GETDATE(),'121.0.0.1')
Insert Into dbo.Basic Values('호', 'ho@min.com', '방가',
GETDATE(), '121.0.0.1', '반갑습니다', '1234', 0, 'HTML',
'minhompage.com', GETDATE(),'121.0.0.1')
Insert Into dbo.Basic Values('김', 'kin@min.com', 'ㅇㅇㅇ',
GETDATE(), '121.0.0.1', '안녕ㅇㅇㅇ', '1234', 0, 'HTML',
'minhompage.com', GETDATE(),'121.0.0.1')
Go
--[2]
출력: List.aspx
Select Num, Name, Email, Title, PostDate, ReadCount
From Basic Order By Num Desc
Go
--[3]
상세: View.aspx
Select * From Basic Where Num = 1
Go
--[4]
수정: Modify.aspx
Begin Tran
Update Basic
Set
Name
= '김민',
Email
= 'kimmin@mail.com',
Homepage
= 'kimminhomepage.com',
Title
= '등업',
Content
= '등업해주세요',
Encoding
= 'Text',
ModifyDate
= GETDATE(),
ModifyIP
= '127.0.0.1'
Where
Num
= 1
--RollBack
Tran
Commit Tran
Go
--[5]
삭제: Delete.aspx
Begin Transaction
Delete From Basic
Where Num = 1
--RollBack
Transaction
Commit Transaction
Go
--[6]
검색: Search.aspx
Select * From Basic Where Name Like '%김%'
Or Title Like '안%' Or Content Like '%3'
Go
--[!]
6개저장프로시저
--[7]
기본형게시판(Basic)에글을작성하는
--저장프로시저: WriteBasic
Create Proc dbo.WriteBasic
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password
VarChar(20),
@Encoding
VarChar(10),
@Homepage
VarChar(100)
--with
Encryption
As
Insert Into Basic
(
Name, Email, Title, PostIP, Content,
Password, Encoding, Homepage
)
Values
(
@Name, @Email, @Title, @PostIP, @Content,
@Password, @Encoding, @Homepage
)
Go
--[8]
기본형게시판(Basic)에서데이터를
--읽어오는저장프로시저: ListBasic
Create Proc dbo.ListBasic
As
Select * From Basic Order By Num Desc
Go
--[9]
해당글을세부적으로읽어오는
--저장프로시저: ViewBasic
Create Proc dbo.ViewBasic
@Num Int
As
Update Basic
Set ReadCount = ReadCount + 1
Where Num = @Num
Select * From Basic
Where Num = @Num
Go
--[10]
해당글에대한비밀번호읽어오는
--저장프로시저: ReadPassword
Create Proc dbo.ReadPasswordBasic
@Num Int
As
Select Password
From Basic
Where Num = @Num
Go
--[11]
해당글지우는저장프로시저: DeleteBasic
Create Proc dbo.DeleteBasic
@Num Int
As
Delete Basic Where Num = @Num
Go
--[12]
해당글을수정하는저장프로시저: ModifyBasic
Create Proc dbo.ModifyBasic
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@ModifyIP
VarChar(15),
@Content Text,
@Encoding
VarChar(10),
@Homepage
VarChar(100),
@Password
VarChar(20),
@Num Int
As
Declare @intCount Int
Select @intCount = COUNT(*) From Basic
Where Password = @Password And Num = @Num
If @intCount > 0
Update
Basic
Set
Name
= @Name,
Email
= @Email,
Title
= @Title,
ModifyIP
= @ModifyIP,
ModifyDate
= GETDATE(),
Content
= @Content,
Encoding
= @Encoding,
Homepage
= @Homepage
Where
Num = @Num
Else
Return
-1 --@@RowCount
Go
--[13]
검색저장프로시저:
--동적SQL문-> 아래정적쿼리문으로
Create Proc dbo.SearchBasic
@SearchField
VarChar(25),
@SearchQuery
VarChar(25)
As
Declare @strSql VarChar(150) --변수선언
Set @strSql = '
Select * From Basic
Where '
+
@SearchField + ' Like ''%'
+
@SearchQuery + '%'' Order By Num Desc'
--Print
@strSql
Exec(@strSql)
Go
--SQL
인젝션해킹
--SearchBasic
' 1=1; Drop Table Basic --', '메롱~'
Go
--[14]
검색저장프로시저: 정적쿼리문
Alter Proc dbo.SearchBasic
@SearchField
VarChar(25),
@SearchQuery
VarChar(25)
As
Set @SearchQuery = '%' + @SearchQuery + '%'
Select * From Basic
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
dbo.SearchBasic 'Name', '홍길동'
Go
Basic.sql