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")
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")
'asp' 카테고리의 다른 글
| request 객체 OpenTextFile 메쏘드 (0) | 2008.09.22 |
|---|---|
| 비주얼 베이직으로 간단하게 만드는 ASP용 COM+ (0) | 2008.04.08 |
| ASP/테이블 너비 고정시키기 (0) | 2007.05.03 |
| 순수 ASP만으로 XML을 만들어 테이블에 바인딩 예제 (2) | 2007.05.03 |
| ASP 자동완성 (1) | 2007.05.03 |