SQL和ACCESS数据库连接管理类

时间：2022-09-24 11:58:49

<%
'==============================================================
'类名:DBaseClass
'用途:可以根据用户参数返回SQL或ACCESS的Connection对象
'用法:
'(Access数据库)
' set dbclass=new DBaseClass 
' dbclass.dbtype="Access"     设置为Access数据库类型 (默认为Access数据库类型)
' dbclass.dbpath="data/data.mdb"  设置Access数据库文件地址
' set rs=dbclass.getrs("select * from [user]")  返回执行SQL语句后的rs对象
' set conn=dbclass.getconn()       返回conn对象
' set rs=dbclass.getbasers()       返回空的Recordset对象
'(SQL数据库)
' dbclass.dbtype="sql"        设置为SQL数据库  (默认为Access数据库类型)
' dbclass.sqlusername="test"    设置登录数据库的用户名(默认是sa用户)
' dbclass.sqlpassword="1010"    设置登录数据库的用户密码(默认为空)
' dbclass.sqldatabasename="user"设置SQL数据库的库名
' dbclass.sqllocalname="192.168.0.1" 设置SQL数据库的服务器地址(默认为本机地址)
' set rs=dbclass.getrs("select * from [user]")  返回执行SQL语句后的rs对象
' set conn=dbclass.getconn()       返回conn对象
' set rs=dbclass.getbasers()       返回空的Recordset对象
' set dbclass=nothing              关闭对象
'================================================================
Class DBaseClass
'数据库驱动程序连接字符串变量
Dim D_ConnStr
'Access数据库文件的路径
Dim D_dbPath
'SQL数据库的设置变量
Dim D_SqlDatabaseName,D_SqlPassword,D_SqlUsername,D_SqlLocalName
'Conn对象
Dim D_Conn,D_Rs
'数据库驱动类型
'值:"1","A" "a" "Access" "access" 则为Access数据库驱动连接对象
'值:"2","S" "s" "SQL" "sql" 则为SQL数据库驱动连接对象
Dim D_DbType
'======================================
'函数名：被始化类
'======================================
Private Sub Class_Initialize
  '默认是Access数据库
  D_DbType="Access"
  D_SqlUsername="sa"
  D_SqlPassword=""
  D_SqlLocalName="(local)"
End Sub

'======================================
'函数名：注销类
'======================================
Private Sub Class_Terminate
on error resume next
 if isObject(D_Rs) then
   D_Rs.close
   set D_Rs=nothing
 end if
 if isObject(D_Conn) then
   D_Conn.close
   set D_Conn=nothing
 end if
End Sub

Public Property Let dbType(bNewValue)
 Select case lcase(cstr(bNewValue))
   case "1","a","access"
      D_DbType = "Access"
   case "2","s","sql"
      D_DbType="SQL"
   case else
      D_DbType="Access"
 End select
End Property
Public Property Get dbType
 dbType =D_DbType
End Property

'ACCESS数据连接的几个属性变量的赋值操作
Public Property Let dbPath(bNewValue)
 D_dbPath = bNewValue
End Property
Public Property Get dbPath
 dbPath =D_dbPath
End Property

'SQL数据连接的几个属性变量的赋值操作
Public Property Let SqlDatabaseName(bNewValue)
 D_SqlDatabaseName = bNewValue
End Property
Public Property Get SqlDatabaseName
 SqlDatabaseName =D_SqlDatabaseName
End Property

Public Property Let SqlPassword(bNewValue)
 D_SqlPassword = bNewValue
End Property
Public Property Get SqlPassword
 SqlPassword =D_SqlPassword
End Property

Public Property Let SqlUsername(bNewValue)
 D_SqlUsername = bNewValue
End Property
Public Property Get SqlUsername
 SqlUsername =D_SqlUsername
End Property

Public Property Let SqlLocalName(bNewValue)
 D_SqlLocalName = bNewValue
End Property
Public Property Get SqlLocalName
 SqlLocalName =D_SqlLocalName
End Property

'==============================================
'函数功能:获取数据库Connection对象
'说明:
'返回值:数据库Connection对象
'==============================================
Public Function getConn()
on error resume next
 if isObject(D_Conn) and not isNull(D_Conn) then
    getConn=D_Conn
 exit function
 end if
 Set D_Conn= Server.CreateObject("ADODB.Connection")
 D_Conn.Open getConnStr()
 if err.number>0 then
   err.clear
   set D_Conn=nothing
   getConn=null
   exit function
 end if
 set getConn=D_Conn
End Function

'==============================================
'函数功能:获取数据库RecordSet对象
'说明:此函数获得的RecordSet对象同Connection对象的execute执行方法一样
'参数:SqlStr  执行的SQL语言
'返回值:数据库RecordSet对象
'==============================================
Public Function getRs(SqlStr)
if SqlStr="" then
 getRs=null
 exit function
end if
Set getRs=getConn().execute(SqlStr)
End Function

'==============================================
'函数功能:获取数据库RecordSet对象
'说明:此函数获得的RecordSet对象建立RecordSet对象一样
'返回值:数据库RecordSet对象
'==============================================
Public Function getBaseRs()
 if isObject(D_Rs) and not isNull(D_Rs) then
   set getDbRs=D_Rs
   exit function
 else
   set D_Rs = server.CreateObject("ADODB.Recordset")
 end if
 Set getDbRs=D_Rs
End Function

'==============================================
'函数功能:获取数据库连接驱动字符串
'说明:此函数是根据D_dbType变量得到不到的字符串
'返回值:数据库连接驱动字符串
'==============================================
Private Function getConnStr()
  Select case D_DbType
      case "Access"
     getConnStr=getAccessConnStr()
   case "SQL"
        getConnStr=getSqlConnStr()
   case else
     getConnStr=""
  end Select
End Function

'==============================================
'函数功能:获取Access数据库连接驱动字符串
'说明:此函数是根据D_DbPath变量得到字符串
'返回值:Access数据库连接驱动字符串
'==============================================
Private Function getAccessConnStr()
  if D_DbPath<>"" then
    getAccessConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(D_DbPath)
  else
    getAccessConnStr=""
  end if
End Function

'==============================================
'函数功能:获取SQL数据库连接驱动字符串
'说明:此函数是根据D_SqlDatabaseName和D_SqlUsername和D_SqlPassword和D_SqlLocalName变量得到字符串
'返回值:SQL数据库连接驱动字符串
'==============================================
Private Function getSqlConnStr()
  if D_SqlDatabaseName<>"" then
    getSqlConnStr = "Provider = Sqloledb; User ID = " & D_SqlUsername & "; Password = " & D_SqlPassword & "; Initial Catalog = " & D_SqlDatabaseName & "; Data Source = " & D_SqlLocalName & ";"
  else
    getSqlConnStr=""
  end if
End Function

End Class
%> 

标签：

相关文章