DataSource.asp

出自ProgWiki

用途

• 封裝ASP中的ADO操作

（尚未測試，待驗證）

程式碼

<!--#include file="adovbs.inc" -->
<%
'ADODB操作相關共用變數
Dim conn
Dim rs
Dim rs2
Dim sqlcmd1
Dim sqlcmd2
 
'開啟連線
Sub OpenDataSource
	If (Not IsEmpty(conn)) Then
		If (conn.State = 0) Then conn.Open Application("Conn")
	Else
		Set conn = Server.CreateObject("ADODB.Connection")
		conn.Open Application("Conn")
	End If
End Sub
 
'關閉相關物件
Sub CloseDataSource
	'Colse ADODB.Command
	If (Not IsEmpty(sqlcmd2)) Then
		If (sqlcmd2.State <> 0) Then sqlcmd2.Close
		Set sqlcmd2 = Nothing
	end if
	If (Not IsEmpty(sqlcmd1)) Then
		If (sqlcmd1.State <> 0) Then sqlcmd1.Close
		Set sqlcmd1 = Nothing
	End If
 
	'Colse ADODB.Recordset
	If (Not IsEmpty(rs2)) Then
		If (rs2.State <> 0) Then rs2.Close
		Set rs2 = Nothing
	end if
	If (Not IsEmpty(rs)) Then
		If (rs.State <> 0) Then rs.Close
		Set rs = Nothing
	End If
 
	'Colse ADODB.Connection
	If (Not IsEmpty(conn)) Then
		If (conn.State <> 0) Then conn.Close
		Set conn = Nothing
	End If
End Sub
 
'開啟查詢 (回傳資料筆數)
Function OpenQuery(id, strSql, IsOnlyRead)
	If (IsNumeric(id) = false) Then
		OpenQuery = 0
		Exit Function
	End If
 
	Call OpenDataSource
 
	If (id = 1) Then
		If (not IsEmpty(rs)) Then
			If (rs.State <> 0) Then rs.Close
		Else
			Set rs = Server.CreateObject("ADODB.Recordset")
		End If
		If (IsOnlyRead = true) Then
			rs.Open strSql, conn, adOpenKeyset, adLockReadOnly
		Else
			rs.Open strSql, conn, adOpenKeyset, adLockOptimistic
		End If
		If rs.EOF Then
		    OpenQuery = 0
		Else
		    OpenQuery = rs.RecordCount
		End If
	Elseif (id = 2) Then
		If (Not IsEmpty(rs2)) Then
			If (rs2.State <> 0) Then rs2.Close
		Else
			Set rs2 = Server.CreateObject("ADODB.Recordset")
		End If
		If (IsOnlyRead = true) Then
			rs2.Open strSql, conn, adOpenKeyset, adLockReadOnly
		Else
			rs2.Open strSql, conn, adOpenKeyset, adLockOptimistic
		End If
		If rs2.EOF Then
		    OpenQuery = 0
		Else
		    OpenQuery = rs2.RecordCount
		End If
	Else
		OpenQuery = 0
	End If
End Function
 
'執行SQL命令
Sub ExecSqlCmd(strSql)
	Call OpenDataSource
	conn.Execute(strSql)
End Sub
 
Function GetRealSqlType(strSqlType)
	Select Case strSqlType
		Case "Empty"
			GetRealSqlType = adEmpty
		Case "TinyInt","Int8"
			GetRealSqlType = adTinyInt
		Case "SmallInt","Int16"
			GetRealSqlType = adSmallInt
		Case "Integer","Int","Int32"
			GetRealSqlType = adInteger		
		Case "BigInt","Int64"
			GetRealSqlType = adBigInt	
		Case "UnsignedTinyInt","UInt8"
			GetRealSqlType = adUnsignedTinyInt
		Case "UnsignedSmallInt","UInt16"
			GetRealSqlType = adUnsignedSmallInt						
		Case "UnsignedInt","UInt","UInt32"
			GetRealSqlType = adUnsignedInt
		Case "UnsignedBigInt","UInt64"
			GetRealSqlType = adUnsignedBigInt
		Case "Single"
			GetRealSqlType = adSingle
		Case "Double"
			GetRealSqlType = adDouble
		Case "Currency"
			GetRealSqlType = adCurrency
		Case "Decimal"
			GetRealSqlType = adDecimal
		Case "Numeric"
			GetRealSqlType = adNumeric
		Case "Boolean","Bool"
			GetRealSqlType = adBoolean		
		Case "BSTR"
			GetRealSqlType = adBSTR
		Case "Char"
			GetRealSqlType = adChar
		Case "VarChar"
			GetRealSqlType = adVarChar
		Case "LongVarChar"
			GetRealSqlType = adLongVarChar
		Case "WChar","NChar"
			GetRealSqlType = adWChar
		Case "VarWChar","NVarChar"
			GetRealSqlType = adVarWChar
		Case "LongVarWChar"
			GetRealSqlType = adLongVarWChar
		Case "Date"
            GetRealSqlType = adDate
        Case "Variant"
            GetRealSqlType = adVariant
		Case Else	
			GetRealSqlType = adIUnknown 
	End Select
    'Const adError = 10
    'Const adUserDefined = 132
    'Const adIDispatch = 9
    'Const adGUID = 72
    'Const adDBDate = 133
    'Const adDBTime = 134
    'Const adDBTimeStamp = 135
    'Const adBinary = 128
    'Const adVarBinary = 204
    'Const adLongVarBinary = 205
    'Const adChapter = 136
    'Const adFileTime = 64
    'Const adPropVariant = 138
    'Const adVarNumeric = 139
    'Const adArray = &H2000
End Function
 
'開啟參數化查詢 (回傳資料筆數)
'Dim SqlParam(3,10)  
'SqlParam(1,1)= "ParameterName1"    '參數名稱  
'SqlParam(2,1) = "Value1"           '值  
'SqlParam(3,1) = "String"           '型態  
Function OpenParameterizedQuery(id, strSql, CmdType, IsOnlyRead, SqlParam)
	If (IsNumeric(id) = false) Then
		OpenParameterizedQuery = 0
		Exit Function
	End If
 
	Call OpenDataSource
 
	If (id = 1) Then
		If (not IsEmpty(rs)) Then
			If (rs.State <> 0) Then rs.Close
		Else
			Set rs = Server.CreateObject("ADODB.Recordset")
		End If
 
		If (not IsEmpty(sqlcmd1)) Then
			If (sqlcmd1.State <> 0) Then sqlcmd1.Close
		Else
			Set sqlcmd1 = Server.CreateObject("ADODB.Command")
		End If
 
		If IsArray(SqlParam) Then 
			PCnt=UBound(SqlParam,2)  
			For y = 1 To PCnt  
				strParamName = "@" & SqlParam(1,y)  
				tValue = SqlParam(2,y)  
				RealSqlType = GetRealSqlType(SqlParam(3,y))   '轉換到Sql的資料型態  
				Set objParam = sqlcmd1.CreateParameter(strParamName , RealSqlType, adParamInput, 0, 0)  
				sqlcmd1.Parameters.Append objParam  
				sqlcmd1.Parameters(strParamName) = tValue    
			Next 
		End If 
 
		If (IsOnlyRead = true) Then
			rs.CursorType = adOpenKeyset
			rs.LockType = adLockReadOnly
		Else
			rs.CursorType = adOpenKeyset
			rs.LockType = adLockOptimistic
		end if
		rs = sqlcmd1.Execute
 
		If rs.EOF Then
		    OpenParameterizedQuery = 0
		Else
		    OpenParameterizedQuery = rs.RecordCount
		End If
 
	Elseif (id = 2) Then
		If (not IsEmpty(rs2)) Then
			If (rs2.State <> 0) Then rs2.Close
		Else
			Set rs2 = Server.CreateObject("ADODB.Recordset")
		End If
 
		If (Not IsEmpty(sqlcmd2)) Then
			If (sqlcmd2.State <> 0) Then sqlcmd2.Close
		Else
			Set sqlcmd2 = Server.CreateObject("ADODB.Command")
		End If
		sqlcmd2.CommandText  = strSql
		sqlcmd2.CommandType = CmdType
		sqlcmd2.ActiveConnection = conn
 
		If IsArray(SqlParam) Then 
			PCnt=UBound(SqlParam,2)  
			For y = 1 To PCnt  
				strParamName = "@" & SqlParam(1,y)  
				tValue = SqlParam(2,y)  
				RealSqlType = GetRealSqlType(SqlParam(3,y))   '轉換到Sql的資料型態  
				Set objParam = sqlcmd2.CreateParameter(strParamName , RealSqlType, adParamInput, 0, 0)  
				sqlcmd2.Parameters.Append objParam  
				sqlcmd2.Parameters(strParamName) = tValue    
			Next 
		End If 
 
		If (IsOnlyRead = true) Then
			rs2.CursorType = adOpenKeyset
			rs2.LockType = adLockReadOnly
		Else
			rs2.CursorType = adOpenKeyset
			rs2.LockType = adLockOptimistic
		End If
		rs2 = sqlcmd2.Execute
 
		If rs.EOF Then
		    OpenParameterizedQuery = 0
		Else
		    OpenParameterizedQuery = rs2.RecordCount
		End If
	Else
		OpenParameterizedQuery = 0
	End If
End Function
%>