Set oDbConn = Server.CreateObject("ADODB.Connection")

Set rs = Server.CreateObject("ADODB.RecordSet")



oDbConn.Open "Provider=msdatashape;data provider=sqloledb;data

source=FUSEWIRE_SERVER\PORTELIFE;Initial Catalog=PorteLife;UID=sa;PWD=;"



oDbConn.CursorLocation = adUseClient



Set cmd = New Command





'Zero가 들어오는 경우 DB에서 Null로 넣는다.

Function ZeroToNull(value)

If value = "0" Then

ZeroToNull = Null

Else

ZeroToNull = value

End If

End Function



'Empty가 들어오는 경우 DB에서 Null로 넣는다.

Function EmptyToNull(value)

If value = "" Then

EmptyToNull = Null

Else

EmptyToNull = value

End If

End Function



Class Command

Public Parameters

Public Procedure



Private iArraySize



Private Sub Class_Initialize()



Parameters   = Array()



iArraySize = 1



End Sub



Private Sub Class_Terminate()



End Sub



'Zero가 들어오는 경우 DB에서 Null로 넣는다.

Function ZeroToNull(value)

If value = "0" or Len(value) = 0 Then

ZeroToNull = Null

Else

ZeroToNull = value

End If

End Function



'Empty가 들어오는 경우 DB에서 Null로 넣는다.

Function EmptyToNull(value)

If value = "" or Len(value) = 0 Then

EmptyToNull = Null

Else

EmptyToNull = value

End If

End Function



Public Sub AddParameter(pkey, pvalue)



' Null인 파라메터는 DB에 던지지 말고, 프로그램 단에서 먹어버리자.

If IsNull(pvalue) = False Then

ReDim Preserve Parameters(iArraySize)



Parameters(iArraySize) = Array(pkey, pvalue)

iArraySize = iArraySize + 1

End If



End Sub



Public Sub ClearParameter()

Parameters   = Array()

iArraySize = 1

End Sub



Public Function GetQueryString()



GetQueryString = Procedure

GetQueryString = GetQueryString + " "



If iArraySize > 1 Then

GetQueryString = GetQueryString & Parameters(1)(0) & "=" & "'" & Parameters

(1)(1) & "'"

End If



For i = 2 to iArraySize - 1

GetQueryString = GetQueryString & ", " & Parameters(i)(0) & "=" & "'" &

Parameters(i)(1) & "'"

Next



DebugMode = DebugMode * 1

If DebugMode > 0 Then

Response.Write "GetQueryString : " & GetQueryString & "<br>"

End If





End Function





Public Function Execute()

oDbConn.Execute(cmd.GetQueryString())

ClearParameter()

End Function



Public Function ExecuteQuery(querystring)

oDbConn.Execute(querystring)

End Function



Public Function ExecuteQueryReturn(querystring)

Dim AffectRow

oDbConn.Execute querystring, AffectRow

ExecuteQueryReturn = AffectRow

End Function





Public Function GetScalar()

Set rs1 = oDbConn.Execute(cmd.GetQueryString())

cmd.ClearParameter()



GetScalar = rs1(0)

End Function





'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''





'입력합니다.

'gradeid GradeID(int)

'companyid CompanyID(int)

'gradecode GradeCode(nvarchar(20))

'gradename GradeName(nvarchar(50))

'teambosstype TeamBossType(bit)



Public Function eGrade_insert(gradeid, companyid, gradecode, gradename, teambosstype)



cmd.Procedure = "eGrade_insert"

cmd.AddParameter "@GradeID", gradeid

cmd.AddParameter "@CompanyID", companyid

cmd.AddParameter "@GradeCode", gradecode

cmd.AddParameter "@GradeName", gradename

cmd.AddParameter "@TeamBossType", teambosstype



eGrade_insert = GetScalar()



End Function





'삭제합니다.

'gradeid GradeID(int)



Public Function eGrade_delete(gradeid)



cmd.Procedure = "eGrade_delete"

cmd.AddParameter "@GradeID", gradeid



eGrade_delete = GetScalar()



End Function





'수정합니다.



'gradeid GradeID(int)

'companyid CompanyID(int)

'gradecode GradeCode(nvarchar(20))

'gradename GradeName(nvarchar(50))

'teambosstype TeamBossType(bit)



Public Function eGrade_update(gradeid, companyid, gradecode, gradename, teambosstype)



cmd.Procedure = "eGrade_update"

cmd.AddParameter "@GradeID", gradeid

cmd.AddParameter "@CompanyID", companyid

cmd.AddParameter "@GradeCode", gradecode

cmd.AddParameter "@GradeName", gradename

cmd.AddParameter "@TeamBossType", teambosstype



eGrade_update = GetScalar()



End Function

.

.위와 같이 Command 클래스에 기본적인 프로시져에 대한 메소드를 정의해서 처리되도록 했습니다.

.기본적인 프로시져는 대부분 제너레이션 한 것들이고, 몇몇은 사용되는 곳이 많은 프로시져들입니다.

.

.

.

.

.





'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''







End Class







Class DataSet



Public oRecordset

Public Tables



Public TableCount



Private iArraySize





Public Sub GetDataSet(cmd)



Set pRecordSet   = oDbConn.Execute(cmd.GetQueryString())

cmd.ClearParameter()



Do until pRecordSet Is Nothing





ReDim Preserve Tables(iArraySize)



Set oRecordset = New Recordset



oRecordset.setRecordSet(pRecordSet)



Set Tables(iArraySize - 1)  = oRecordset



Set oRecordset   = Nothing



Set pRecordSet   = pRecordSet.NextRecordset



iArraySize   = iArraySize + 1



Loop



TableCount  = iArraySize - 1



End Sub



Public Sub GetDataSetQuery(query)



Set pRecordSet   = oDbConn.Execute(query)



Do until pRecordSet Is Nothing



ReDim Preserve Tables(iArraySize)



Set oRecordset = New Recordset

oRecordset.setRecordSet(pRecordSet)



Set Tables(iArraySize - 1)  = oRecordset



Set oRecordset   = Nothing



Set pRecordSet   = pRecordSet.NextRecordset



iArraySize   = iArraySize + 1



Loop



TableCount  = iArraySize - 1



End Sub



'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Params 10

Public Sub GetBoardList(boardid, currentpage, pagesize, parentscontentid, orderby, ordertype,

searchfield, searchtext, depth, adminmode)



cmd.Procedure = "cGetContents"

cmd.AddParameter "@BoardID", boardid

cmd.AddParameter "@CurrentPage", currentpage

cmd.AddParameter "@PageSize", pagesize

cmd.AddParameter "@ParentsContentID", parentscontentid

cmd.AddParameter "@OrderBy", orderby

cmd.AddParameter "@OrderType", ordertype

cmd.AddParameter "@SearchField", searchfield

cmd.AddParameter "@SearchText", searchtext

cmd.AddParameter "@Depth", depth

cmd.AddParameter "@AdminMode", adminmode



GetDataSet(cmd)



End Sub





Public Sub GetView(viewname)



cmd.Procedure = "cGetView"

cmd.AddParameter "@ViewName", viewname



GetDataSet(cmd)



End Sub



.

.히딩크의 SQLHelper를 수정했습니다. 아래의 클래스이도 같습니다. (원작 히딩크)

.또한 Command 클래스에 있던 것처럼 기본적인 프로시져를 함수화하여 작성했습니다.

.해당 메소드를 호출하는 페이지에서는 단지 한줄짜리 함수호출만 하면 처리가 되도록 했습니다.

.

.

.



Private Sub Class_Initialize()



Tables   = Array()



iArraySize = 1



End Sub



Private sub Class_Terminate()



Tables   = Null



End sub  



End Class







Class Recordset



Private oRecordSet

Public arrRecordSet



Private oFields



Private intLoop



Public RowCount



Private Sub Class_Initialize()



Set oFields = Createobject("Scripting.Dictionary")



End Sub



Private sub Class_Terminate()



Set oFields = Nothing



End sub





Public Sub setRecordSet(pRecordSet)



If pRecordSet.State > 0 Then

If pRecordSet.Bof or pRecordSet.Eof Then



RowCount = -1



Else



Set oRecordSet = pRecordSet

Call setRecordArray

Call setFields

RowCount = UBOUND(arrRecordSet, 2)



End If

Else

RowCount = -1

End If



End Sub



Private Sub setRecordArray



arrRecordSet = oRecordSet.getRows



End Sub





Private Sub setFields



For intLoop = 0 To  oRecordSet.Fields.Count - 1



DebugMode = DebugMode * 1



If oFields.Exists(LCase(oRecordSet.Fields(intLoop).Name)) = True Then

oFields.Add LCase(oRecordSet.Fields(intLoop).Name) & intLoop,

intLoop

If DebugMode > 0 Then

Response.Write oRecordSet.Fields(intLoop).Name &

intLoop & "<br>"

End If

Else

oFields.Add LCase(oRecordSet.Fields(intLoop).Name), intLoop

If DebugMode > 0 Then

Response.Write oRecordSet.Fields(intLoop).Name

& "<br>"

End If

End If



Next



End Sub



Public Function Rows(pRowIndex, pFieldName)



Rows = arrRecordSet(oFields.Item(LCase(pFieldName)), pRowIndex)



End Function



End Class





예:게시판 생성

cmd.eBoardInfo_insert Null, AuthorityLevel, ContentTypeID, DefaultPoint, DayLimitPoint, ConvertQuot

(BoardName), BoardCategoryID



예:마일리지 조회

Set ds = New DataSet

ds.cGetMileage gubun, LoginMemberID, MileageType, startdate, enddate, RemoveNull(DepartmentID,-1),

MemberName



예:마일리지 참조

MileageTypeID = ds.Tables(0).Rows(i, "MileageTypeID")
  1. best rated vacuums 2008.05.23 05:15 신고

    중대한 축하!경이롭 위치 위치!

+ Recent posts