asp Access和ms SQL2000 2005共用数据库操作类

<%

'====================================
'H----------Access和SQL共用数据库操作类,所有SQL语句以SQL为准
'====================================
Class DbClass
    Public Conn
 Private ConnStr
 Private ErrorInfo

    '初始化类
    Public Sub Class_Initialize
  Select Case Cfg.DBType
   Case "MSSQL"
    ConnStr = "Provider = Sqloledb; User ID = "& Cfg.SqlUsername &"; Password = "& Cfg.SqlPassword &"; Initial Catalog = "& Cfg.SqlDatabaseName &"; Data Source = "& Cfg.SqlLocalName &";"
   Case "MSSQL_W"
    ConnStr = "Provider = Sqloledb;Integrated Security = SSPI;Persist Security Info = False;Data Source =.;Initial Catalog = "& Cfg.SqlDatabaseName &";"
   Case "AC"
    ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = "& Server.Mappath(Cfg.dbPath)
   Case Else
    Response.Write "<div style=""font-family:Arial,Verdana; font-size:12px;"">"& Cfg.sysVersion &"配置错误("& Now() &") <a href=""javascript教程:history.back();"">返回</a> <a href=""javascript:location.reload();"">重试</a><br /><br />错误源:<br /><span style=""color:#FF0000"">系统不支持的数据库类型</span><br />错误描述:<br /><span style=""color:#FF0000"">无法识别的数据库类型</span>"
    Response.Write "</div>"
    Response.End()
  End Select
    End Sub

    '函数:创建数据库链接
    Public Function DbOpen()
  If IsObject(Conn) Then Exit Function
  Set Conn = Server.CreateObject(Cfg.nConnectionObject)
  If Conn.State = 1 Then Exit Function
  On Error Resume Next
  Conn.Open ConnStr
  If Err.Number Then
   ErrorInfo = Err.Description
   Err.Clear
   Set Conn = Nothing
   Response.Write "<div style=""font-family:Arial,Verdana; font-size:12px;"">"& Cfg.sysVersion &"执行错误("& Now() &") <a href=""javascript:history.back();"">返回</a> <a href=""javascript:location.reload();"">重试</a><br /><br />错误源:<br /><span style=""color:#FF0000"">数据库连接错误</span><br />错误描述:<br /><span style=""color:#FF0000"">" & ErrorInfo & "</span>"
   Response.Write "</div>"
   Response.End()
  End If
    End Function

    '函数:关闭数据库链接
    '参数:链接串
    Public Function DbClose()
  If Not IsObject(Conn) Then Exit Function
  If Conn.State = 0 Then Exit Function
  Conn.Close:Set Conn = Nothing
 End Function

    '函数:创建数据库RecordSet对象
    '参数:链接串
    Public Function RecordSet(obj)
        Set obj = Server.CreateObject(Cfg.nRecordSetObject)
    End Function

    '函数:根据当前数据库类型转换Sql脚本
    '参数:Sql串
    '返回:转换结果Sql串
    Public Function SqlTran(Sql)
        If Cfg.DBType = "AC" Then
            SqlTran = Sql2Access(Sql)
        Else
            SqlTran = Sql
        End If
    End Function

    '函数:数据库脚本执行(代Sql转换)
    '参数:Sql脚本
    '返回:执行结果
    '说明:本执行可自动根据数据库类型对部分Sql基础语法进行转换执行
    Public Function Execute(Sql)
        Sql = SqlTran(Sql)
  On Error Resume Next
  DbOpen()
        Set Execute = Conn.Execute(Sql)
        If Err.Number <> 0 Then
            Response.Write "<div style=""font-family:Arial,Verdana; font-size:12px;"">"& Cfg.sysVersion &"SQL语句执行错误("& Now() &") <a href=""javascript:history.back();"">返回</a> <a href=""javascript:location.reload();"">重试</a><br /><br />错误源:<br /><span style=""color:#FF0000"">"& Err.Source &"</span><br />错误描述:<br /><span style=""color:#FF0000"">"& Err.Description &"</span>"
   if Cfg.PrintErrorSql Then Response.Write "<br />错误语句如下:<br /><span style=""color:#FF0000"">"& Sql &"</span>"
   Response.Write "</div>"
   Response.End()
  End If
  If Err <> 0 Then Err.Clear
    End Function

    '函数:执行SQL返回二维数组
    '参数:Sql脚本
    '返回:二维数组对象
    Public Function Query(Sql)
  Dim RsTmp,TmpArray
  Sql = SqlTran(Sql)
  On Error Resume Next
  DbOpen()
  Set RsTmp=Conn.Execute(Sql)
        If Err.Number <> 0 Then
            Response.Write "<div style=""font-family:Arial,Verdana; font-size:12px;"">"& Cfg.sysVersion &"SQL语句执行错误("& Now() &") <a href=""javascript:history.back();"">返回</a> <a href=""javascript:location.reload();"">重试</a><br /><br />错误源:<br /><span style=""color:#FF0000"">"& Err.Source &"</span><br />错误描述:<br /><span style=""color:#FF0000"">"& Err.Description &"</span>"
   if Cfg.PrintErrorSql Then Response.Write "<br />错误语句如下:<br /><span style=""color:#FF0000"">"& Sql &"</span>"
   Response.Write "</div>"
   Response.End()
  End If
  If RsTmp.Eof Or RsTmp.Bof Then Exit Function
  TmpArray=RsTmp.GetRows()
  Set RsTmp=Nothing
  Query=TmpArray
    End Function
 
  '函数:执行SQL返回刚插入数据库记录的ID
 Public Function getIdenId(tableName)
  getIdenId=Conn.Execute("select IDENT_CURRENT('[" & cfg.TablePrefix & tableName & "]')")(0)
 End Function

 '[获取指定单字段记录]
 'scCol使用“|”分别分割唯一字段名,表名,条件约束字段名
 'scValue传递的指定参数
 'scNoneText无记录时的替代文字
 'Codes By konghu
 Public Function GetSpecialCol(scCol,scValue,scNoneText)
  Dim arrCol,Sql,Tmp
  arrCol = Split(scCol,"@")
  If IsNumeric(scNoneText) Then
   Sql = "Select " & arrCol(0) & " From ["& Cfg.tablePrefix &"" & arrCol(1) & "] Where " & arrCol(2) & " = " & scValue
  Else
   Sql = "Select " & arrCol(0) & " From ["& Cfg.tablePrefix &"" & arrCol(1) & "] Where " & arrCol(2) & " = '" & scValue & "'"
  End If
  'Response.Write(Sql&"<br>")
  Tmp = Query(Sql)
  If IsNull(Tmp) Or IsEmpty(Tmp) Then
   GetSpecialCol = scNoneText
  Else
   if isNull(Tmp(0,0)) = false then
    GetSpecialCol = Tmp(0,0)
   else
    GetSpecialCol = scNoneText
   end if
  End If
 End Function
 
 '作用:验证数据是否已存在(true不存在/false存在)
 '参数:值,字段,附加条件,表,ID
 Public Function ChkSameValue(value,field,whereStr,table,id)
  DbOpen()
  Dim temp,whereString
  If whereStr = "" Then
   whereString = ""
  Else
   whereString = whereStr &" AND "
  End if
  If id = "" Or IsNull(id) Or IsEmpty(id) then
   temp = Conn.Execute("SELECT COUNT(*) FROM ["& Cfg.tablePrefix & table &"] WHERE "& whereString & field &" = '"& value &"'")(0)
  Else
   temp = Conn.Execute("SELECT COUNT(*) FROM ["& Cfg.tablePrefix & table &"] WHERE "& whereString & field &" = '"& value &"' AND id <> "& id)(0)
  End If
  rem response.write("SELECT COUNT(*) FROM ["& Cfg.tablePrefix & table &"] WHERE "& whereString & field &" = '"& value &"'"&"<br>")
  If temp = 0 Then
   ChkSameValue = True
  Else
   ChkSameValue = False
  End if
 End Function

 '//批量语句事务处理
 Function BeginTran(this)
  If this = "" or Isnull(this) Then exit Function
  Dim I
  On Error Resume next
  DbOpen()
  this = split(this,"$split$")
  Conn.BeginTrans'//启动事务
  For I = 0 To uBound(this)
   response.write(this(I)&"<br>")
   Conn.Execute(this(I))
   If Conn.Errors.Count>0 Then
    Response.Write "<div style=""font-family:Arial,Verdana; font-size:12px;"">"& Cfg.sysVersion &"SQL语句执行错误("& Now() &") <a href=""javascript:history.back();"">返回</a> <a href=""javascript:location.reload();"">重试</a><br /><br />错误源:<br /><span style=""color:#FF0000"">"& Err.Source &"</span><br />错误描述:<br /><span style=""color:#FF0000"">"& Err.Description &"</span>"
    Conn.Errors.Clear
    Conn.RollBackTrans
    response.End()
   End If
  Next
  Conn.CommitTrans'//提交事务 
 End Function

    '函数:SqlServer to Access(97-2000)
    '参数:Sql,数据库类型(ACCESS,SQLSERVER)
    '说明:
    Private Function Sql2Access(Sql)
        Dim regEx, Matches, Match
        '创建正则对象
        Set regEx = New RegExp
        regEx.IgnoreCase = True
        regEx.Global = True
        regEx.MultiLine = True

        '转:GetDate()
        regEx.Pattern = "(?=[^']?)GETDATE()(?=[^']?)"
        Sql = regEx.Replace(Sql,"NOW()")

        '转:UPPER()
        regEx.Pattern = "(?=[^']?)UPPER([s]?(.+?)[s]?)(?=[^']?)"
        Sql = regEx.Replace(Sql,"UCASE($1)")

        '转:日期表示方式
        '说明:时间格式必须是2004-23-23 11:11:10 标准格式
        regEx.Pattern = "'([d]{4,4}-[d]{1,2}-[d]{1,2}(?:[s][d]{1,2}:[d]{1,2}:[d]{1,2})?)'"
        Sql = regEx.Replace(Sql,"#$1#")

        regEx.Pattern = "DATEDIFF((.*?),"
        Set Matches = regEx.Execute(Sql)
        Dim temStr
        For Each Match In Matches
            temStr = "DATEDIFF("
            Select Case Trim(LCase(Match.SubMatches(0)))
                Case "s" :
                    temStr = temStr &"'s',"
                Case "n" :
                    temStr = temStr &"'n',"
                Case "h" :
                    temStr = temStr &"'h',"
                Case "d" :
                    temStr = temStr &"'d',"
                Case "m" :
                    temStr = temStr &"'m',"
                Case "y" :
                    temStr = temStr &"'y',"
            End Select
            Sql = Replace(Sql,Match.Value,temStr,1,1)
        Next

        '转:Insert函数
        regEx.Pattern = "CHARINDEX([s]?'(.+?)'[s]?,[s]?'(.+?)'[s]?)[s]?"
        Sql = regEx.Replace(Sql,"INSTR('$2','$1')")

        Set regEx = Nothing
        Sql2Access = Sql
    End Function

 '************************************
 'GetConn返回当链接对象
 '************************************
 Public Property Get GetConn
  DbOpen()
  Set GetConn = Conn
 End Property

 '注消类
    Private Sub Class_Terminate
        DbClose
    End Sub
End Class

Class SQLString'//SQL生成类
 '************************************
 '变量定义
 '************************************
 'sTableName ---- 表名
 'iSQLType ----SQL语句类型:0-增加,1-更新,2-删除,3-查询
 'sWhere ---- 条件
 'sOrder ---- 排序方式
 'sSQL ----值
 
 Private sTableName,iSQLType,sWhere,sOrder,sSQL
 
 '************************************
 '类初始化/结束
 '************************************
 Private Sub Class_Initialize()
  sTableName=""
  iSQLType=0
  sWhere=""
  sOrder=""
  sSQL=""
 End Sub
 
 Private Sub Class_Terminate()
 
 End Sub
 
 '************************************
 '属性
 '************************************
 '设置表名的属性
 Public Property Let TableName(value)
  sTableName=value
 End Property
 
 '设置条件
 Public Property Let Where(value)
  sWhere=value
 End Property
 
 '设置排序方式
 Public Property Let Order(value)
  sOrder=value
 End Property
 
 '设置查询语句的类型
 Public property Let SQLType(value)
  iSQLType=value
  Select case iSQLType
   Case 0
    sSQL="insert into {#0} ({#1}) values ({#2})"
   Case 1
    sSQL="update {#0} set {#1}={#2}"
   Case 2
    sSQL="delete from {#0} "
   Case 3
    sSQL="select {#1} from {#0} "
  End Select
 End Property
 
 '************************************
 '函数
 '************************************
 '增加字段(字段名称,字段值)
 
 Public Sub AddField(sFieldName,sValue)
  Select Case iSQLType
   Case 0
    sSQL=replace(sSQL,"{#1}",sFieldName & ",{#1}")
    sSQL=replace(sSQL,"{#2}","'" & sValue & "',{#2}")
   Case 1
    sSQL=replace(sSQL,"{#1}",sFieldName)
    sSQL=replace(sSQL,"{#2}","'" & sValue & "',{#1}={#2}")
   Case 3
    sSQL=replace(sSQL,"{#1}",sFieldName & ",{#1}")
  End Select
 End Sub
 
 '返回SQL语句
 Public Function ReturnSQL()
  sSQL=replace(sSQL,"{#0}",sTableName)
  Select Case iSQLType
  Case 0
   sSQL=replace(sSQL,",{#1}","")
   sSQL=replace(sSQL,",{#2}","")
  Case 1
   sSQL=replace(sSQL,",{#1}={#2}","")
  Case 3
   sSQL=replace(sSQL,",{#1}","")
  End Select
  
  If sWhere<>"" Then
   sSQL=sSQL & " where " & sWhere
  End If
  If sOrder<>"" Then
   sSQL=sSQL & " order by " & sOrder
  End If
  ReturnSQL=sSQL
 End Function
 
 '清空语句
 Public Sub Clear()
  sTableName=""
  iSQLType=0
  sWhere=""
  sOrder=""
  sSQL=""
 End Sub
 
End Class
%>

时间: 2024-09-20 21:02:54

asp Access和ms SQL2000 2005共用数据库操作类的相关文章

ASP.NET[C#]的ACCESS数据库操作类

access|asp.net|数据|数据库 ASP.NET[C#]的ACCESS数据库操作类       //网上很多都是操作SQL SER 的,整理了一下,不是很完善,但不影响使用,呵呵--       //private string datapatch = ConfigurationSettings.AppSettings["acessconn"];//数据库地址                                            private string

[原创] EasyASP v1.5简化ASP开发,包含数据库操作类

EasyASP v1.5简化ASP开发,包含数据库操作类 EasyASP是一个方便快速开发ASP的类,其中包含了一个数据库控制类(原clsDbCtrl.asp,对原代码作了优化和修改,包含对数据库的各类操作及存储过程的调用,全部封装在Easp.db中,使用起来会更方便,调用也更简单).而Easp类中提供了大量实用的ASP通用过程及方法,可以简化大部分的ASP操作.目前只提供了VBScript版,JScript版将来可能会提供.详细说明请下载帮助手册,里面有非常详细的使用方法说明及源码范例. 源码

ASP通用数据库操作类源代码

<%'=========================================================================='文件名称:clsDbCtrl.asp'功 能:数据库操作类'作 者:coldstone (coldstone[在]qq.com)'程序版本:v1.0.5'完成时间:2005.09.23'修改时间:2007.10.30'版权声明:可以在任意作品中使用本程序代码,但请保留此版权信息.'          如果你修改了程序中的代码并得到更好的应用,

asp数据库操作类

<%'=========================================================================='文件名称:clsDbCtrl.asp'功 能:数据库操作类'作 者:coldstone (coldstone[在]qq.com)'程序版本:v1.0.5'完成时间:2005.09.23'修改时间:2007.10.30'版权声明:可以在任意作品中使用本程序代码,但请保留此版权信息.' 如果你修改了程序中的代码并得到更好的应用,请发送一份给我,谢

Access数据库操作类

Access数据库操作类AccessHelper using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControl

一个简单的asp数据库操作类

数据|数据库 <%'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'数据库操作类'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'名称:Class_DBOperate'版本:0.2'作者:qihangnet'更新:2005年6月14日'作用:简化数据库操作的流程'授权:免费使用'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Class Class_DBOperate '************************

一个ASP.NET的MYSQL的数据库操作类自己封装的_实用技巧

/** * 类说明:对MYSQL数据库的操作类 */ using System; using System.Data; using MySql.Data.MySqlClient; namespace Niunan.BYLW.Utility { /// <summary>对MYSQL数据库的操作类 /// /// </summary> public class MYSQLHelper { private MySqlConnection conn = null; private MyS

asp.net Oracle 数据库操作类

操作类 using System; using System.Data; using System.Configuration; using System.Data.OracleClient; using System.Text; using System.Windows.Forms; using System.Xml; using Transactions; /// <summary> /// DB 的摘要说明 Written By Luos.Luo ,the creator of Sale

谁有asp.net连接oracle数据库操作类,要存储过程模式的?

问题描述 谁有asp.net连接oracle数据库操作类,要存储过程模式的? 解决方案 解决方案二:这个还没有用过,帮你up把解决方案三:我也没有用过oracle