Taeyo.net의 DBHelper를 기초로 작성된 class입니다.
<%
Class clsDBHandler
Private strConnectID, strConnectPW, strConnectHost, strSelectDB
Private objConnect
Private objCmd, objRS
Private intRowsCount
'========================================================================
' 클래스 시작 메서드(DB 연결객체 생성)
'========================================================================
Private Sub Class_Initialize()
strConnectID = "DB ID"
strConnectPw = "DB password"
strConnectHost = "Server name or IP"
strSelectDB = "Use db"
If IsObject(objConnect) Then
If Not objConnect is Nothing Then
If objConnect.State = adStateOpen Then
objConnect.Close
End If
Set objConnect = Nothing
End If
End If
End Sub
'========================================================================
' DB 연결 정보 (속성) 설정
'========================================================================
Public Property Let ConnectID(strID)
strConnectID = strID
End Property
Public Property Let ConnectPw(strPw)
strConnectPw = strPw
End Property
Public Property Let ConnectHost(strHost)
strConnectHost = strHost
End Property
Public Property Let SelectDB(strDB)
strSelectDB = strDB
End Property
'========================================================================
' DB 연결 정보 (속성) 반환
'========================================================================
Public Property Get ConnectID()
ConnectID = strConnectID
End Property
Public Property Get ConnectPw()
ConnectPw = strConnectPw
End Property
Public Property Get ConnectHost()
ConnectHost = strConnectHost
End Property
Public Property Get SelectDB()
SelectDB = strSelectDB
End Property
'========================================================================
' GetRows실행 후 Rows Count 반환 : 실행이 없을 경우 0 반환
'========================================================================
Public Property Get getRowsCount()
getRowsCount = intRowsCount
End Property
'========================================================================
' DB 연결
'========================================================================
Public Sub DBConnection()
Dim strConnectString
If IsObject(objConnect) Then
objConnect.Close
Set objConnect = Nothing
End If
strConnectString = "Provider=SQLOLEDB;Data Source="&
strConnectString &";Initial Catalog="& strSelectDB &_
";User Id="& strConnectID &";Password="& strConnectPW &";"
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open strConnectString
End Sub
'========================================================================
' DB 연결 객체를 반환
'========================================================================
Public Function DBConnectReturnConn(Host, DB, ID, PW)
Dim strConnectString, Conn
If IsNull(Host) Or Host = "" Then Host = strConnetHost
If IsNull(DB) Or DB = "" Then DB = strSelectDB
If IsNull(ID) Or ID = "" Then ID = strConnectID
If IsNull(PW) Or PW = "" Then PW = strConnectPW
strConnectString = "Provider=SQLOLEDB;Data Source="& Host &";Initial Catalog="& DB &_
";User Id="& ID &";Password="& PW &";"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnectString
Set DBConnectReturnConn = Conn
Conn.Close
Set Conn = Nothing
End Function
'========================================================================
' 지정된 필드의 COUNT를 반환
'========================================================================
Public Function getFieldCount(strTableName, strField, strWhere)
Dim strSQL, Rows
strSQL = "SELECT COUNT("& strField &") FROM "& strTableName
If strWhere <> "" And Not IsNull(strWhere) Then
strSQL = strSQL &" WHERE "& strWhere
End If
Rows = SQLExecReturnGetRows(strSQL, Nothing)
getFieldCount = Rows(0, 0)
End Function
'========================================================================
' query를 실행하고 RecordSet을 반환
'========================================================================
Public Function SQLExecReturnRS(strSQL)
Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objConnect, adOpenStatic, adLockReadOnly
Set objRS.ActiveConnection = Nothing
Set SQLExecReturnRS = objRS
Set objRS = Nothing
End Function
'========================================================================
' Query 실행 후 반환 값을 GetRows로 리턴
'========================================================================
Public Function SQLExecReturnGetRows(strSQL, arrField)
Dim Rows
getRowsCount = 0 '프로퍼티 초기화
Set objRS = SQLExecReturnRS(strSQL)
If Not objRS.EOF And Not objRS.BOF Then
If IsArray(arrField) Then
Rows = objRS.GetRows( , , arrField)
Else
Rows = objRS.GetRows()
End If
getRowsCount = UBound(Rows, 2) + 1 '배열의 최대 수치임으로 +1 하여 반환
SQLExecReturnGetRows = Rows
objRS.Close
Set objRS = Nothing
End If
End Function
'========================================================================
' 단순 query 실행
'========================================================================
Public Sub SQLExec(strSQL)
objConnect.Execute(strSQL)
End Sub
'========================================================================
' Stored Proc 실행 (ReordSet 반환 없음)
'========================================================================
Public Sub ExecSP(strSPName, arrParam)
Dim Param, i, strValue, LB, UB, Params
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objConnect
objCmd.CommandText = strSPName '실행할 프로시져 할당
objCmd.CommandType = adCmdStoredProc '프로시져 실행 명시
'arrParam을 이용하여 CreateParamter 생성 해줌
CmdAppendParameter arrParam
objCmd.Execute , , adExecuteNoRecords
End Sub
'========================================================================
' Stored Proc 실행 (ReordSet 반환)
'========================================================================
Public Function ExecSPReturnRS(strSPName, arrParam)
Set objCmd = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.RecordSet")
objCmd.ActiveConnection = objConnect
objCmd.CommandText = strSPName '실행할 프로시져 할당
objCmd.CommandType = adCmdStoredProc '프로시져 실행 명시
'arrParam을 이용하여 CreateParamter 생성 해줌
CmdAppendParameter arrParam
objRS.CursorLocation = adUseClient
objRS.Open objCmd, , adOpenStatic, adLockReadOnly
Set objRS.ActiveConnection = Nothing
Set ExecSPReturnRS = objRS
objRS.Close
Set objRS = Nothing
End Function
'========================================================================
' Stored Proc 실행 (GetRows 반환)
'========================================================================
Public Function ExecSPReturnGetRows(strSPName, arrParam, arrField)
Dim Rows
Set objRS = ExecSPReturnRS(strSPName, arrParam)
getRowsCount = 0
If Not objRS.EOF And Not objRS.BOF Then
If IsArray(arrField) Then
Rows = objRS.GetRows( , , arrField)
Else
Rows = objRS.GetRows()
End If
If IsArray(Rows) Then
getRowsCount = UBound(Rows, 2) + 1
End If
End If
objRS.Close
Set objRS = Nothing
ExecSPReturnGetRows = Rows
End Function
'========================================================================
' Command 객체에 Parameter를 추가 해준다
'========================================================================
Private Sub CmdAppendParameter(arrParam)
Dim Param, i, strValue, LB, UB, Params
If VarType(arrParam) = 8192 or VarType(arrParam) = 8204 or VarType(arrParam) = 8209 then
Params = arrParam
For i = LBound(Params) To UBound(Params)
LB = LBound(Params(i))
UB = UBound(Params(i))
If UB - LB = 4 Then
If VarType(Trim(Params(i)(4))) = vbString Then
If Params(i)(4) = "" Or IsEmpty(Params(i)(4)) Or IsNull(Params(i)(4)) Then
strValue = Null
Else
strValue = Params(i)(4)
End If
Else
strValue = Params(i)(4)
End If
objCmd.Parameters.Append objCmd.CreateParameter(Params(i)(0),
Params(i)(1), Params(i)(2), Params(i)(3), strValue)
End If
Next
End If
End Sub
'========================================================================
' Stored Proc 실행 후 반환 값 리턴(RETURN_VALUE 포함)
'========================================================================
Public Function CmdReturnOutput(strValName)
CmdReturnOutput = objCmd.Parameters(strValName).value
End Function
'========================================================================
' Command 객체 소멸
'========================================================================
Public Sub CmdDispose()
Set objCmd.ActiveConnection = Nothing
Set objCmd = Nothing
End Sub
'========================================================================
' 트랜젝션을 시작한다
'========================================================================
Public Sub BeginTrans()
If Not IsObject(objConnect) Or objConnect Is Nothing Then
DBConnection()
End If
objConnect.BeginTrans
End Sub
'========================================================================
' 활성화된 트랜젝션을 커밋 한다
'========================================================================
Public Sub Commit()
If IsObject(objConnect) Or (Not objConnect Is Nothing) Then
objConnect.CommitTrans
End If
End Sub
'========================================================================
' 활성화된 트랜젝션을 롤백 한다
'========================================================================
Public Sub RollBack()
If IsObject(objConnect) Or (Not objConnect Is Nothing) Then
objConnect.RollBackTrans
End If
End Sub
'========================================================================
' 클래스 소멸자
'========================================================================
Private Sub Class_Terminate()
If IsObject(objRS) Then
If Not objRS Is Nothing Then
Set objRS = Nothing
End If
End If
If IsObject(objCmd) Then
If Not objCmd Is Nothing Then
objCmd.ActiveConnection = Nothing
If Not objCmd Is Nothing Then
Set objCmd = Nothing
End If
End If
End If
If IsObject(objConnect) Then
If Not objConnect Is Nothing Then
If objConnect.State = adStateOpen Then
objConnect.Close
End If
Set objConnect = Nothing
End If
End If
End Sub
End Class
%>
class의 사용 방법
<%
Dim DBH
Set DBH = new clsDBHandler '인스턴트 생성
'strConnectID = "DB ID"
'strConnectPw = "DB password"
'strConnectHost = "Server name or IP"
'strSelectDB = "Use db"
'위의 변수 값을 수정 하여 주면 인스턴트 생성시 기본 연결 정보를 바꿀수 있음
'다른 DB와의 연결
With DBH
.strConnectID = "DB"
.strConnectPw = "Password"
.strConnectHost = "Server Address"
.strSelectDB = "Use DB"
.DBConnection()
End DBH
'기존의 접속 정보는 사라지고 새로운 DB로 연결하게 됨.
'한 페이지에서 다수의 DB를 연결한다면 여러 변수를 선언하여 각각 다른 연결정보로
'인스턴트를 생성하면 된다.
'getFieldCount메서드의 사용
'strTableName : 카운트를 해야 되는 Table의 이름
'strField : 카운트될 필드의 명
'strWhere : 조건
Dim cnt
cnt = DBH.getFieldCount("TB_TEST", "*", "UserID LIKE '%test%'")
'SQLExecReturnRS
'쿼리를 실행하고 결과를 RecordSet을 반환
Dim SQL, RS
SQL = "SELECT * FROM TB_TEST"
Set RS = DBH.SQLExecReturnRS(SQL)
'SQLExecReturnGetRows
'쿼리를 실행하고 결과를 GetRows로 반환
'strSQL : 쿼리
'arrField : 반환 받을 필드를 배열로 작성하여 입력 (쿼리 자체에서 지정하였을 경우나 설정이 필요
'없는 경우 Nothing으로 처리 가능
Dim Rows, Fields
SQL = "SELECT * FROM TB_TEST"
Fields = Array("Test1", "Test2", "Test3")
Rows = DBH.SQLExecReturnGetRows(SQL, Fields)
'ExecSP : 단순 StoredProc실행 RecordSet 반환 없음
'strSPName : 프로시져의 명
'arrParam : 파라메터 설정 (배열로 작성되어야 한다)
Dim Prm(2)
Prm(0) = Array("@Test1", adInteger, adParamInput, , 1)
Prm(1) = Array("@Test2", adVarchar, adParamInput, 30, "test")
Prm(2) = Array("@Test3", adInteger, adParamOutput, , "")
DBH.ExecSP("SP_TEST", Prm)
%>
'프로그래밍 > ASP' 카테고리의 다른 글
URL 자동링크 함수 (0) | 2010.04.23 |
---|---|
ASP강좌 5강 DB,SQL & ADO객체 (0) | 2009.06.13 |
이미지 저장시 리사이즈 (0) | 2009.06.04 |
ajaxed - free classic ASP Ajax Library (0) | 2009.06.04 |
이미지 파일 리사이즈 (0) | 2009.06.04 |