00.ASP.NET - 기본형 게시판(1) : DB설계

DB설계 [ DB 추가방법 ]  01.ASP.NET - 기본형 게시판(2) : List(리스트) 페이지
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