ASP Database Helper

Dbhelper.asp

<%

' 数据库操作助手文件

Class Class_DbHelper

    ' 获取数据库版本
    Public Function version()
        Dim versionString, v:v = Null
        versionString = Conn.Execute("SELECT SERVERPROPERTY('productversion')")(0) ' 2000+
        If (UtilHelper.checkNull(versionString)) Then
            versionString = Conn.Execute("SELECT @@VERSION")(0) ' 6.5、7.0
        End If
        If (NOT UtilHelper.checkNull(versionString)) Then
            versionString = Left(versionString, InStr(versionString, ".") - 1)
            Select Case versionString
                Case 6
                    v = 6.5
                Case 7
                    v = 7.0
                Case 8
                    v = 2000
                Case 9
                    v = 2005
                Case 10
                    v = 2008
                Case 11
                    v = 2012
            End Select       
        End If

        version = v
    End Function

    ' get last insert id
    Public Function getLastInsertId()
        getLastInsertId = Conn.Execute("SELECT @@IDENTITY")(0)
    End Function

    Public Function getRowCount()
        getRowCount = Conn.Execute("SELECT @@ROWCOUNT")(0)
    End Function

    ' 插入数据库记录（单条记录）
    Public Function insert(ByVal tableName, ByVal columns)
        Dim i, j, fields(), values(), c, temp, valueTemp, sql
        temp = Array(1)
        c = Ubound(columns)
        ReDim fields(c)
        ReDim values(c)
        For i = 0 To Ubound(columns)
            temp = Split(columns(i), ":")
            If (Ubound(temp) >= 1) Then
                fields(i) = quoteColumnName(temp(0))
                values(i) = quoteValue(Right(columns(i), Len(columns(i)) - Len(temp(0)) - 1))
            End If
        Next
        sql = "INSERT INTO " & quoteTableName(tableName) & "(" & Join(fields, ", ") & ") VALUES(" & Join(values, ", ") & ")"
        exec(sql)
    End Function

    ' 插入数据库记录（多条记录）
    Public Function insertRows(ByVal tableName, ByVal fields, ByVal values)
        Dim i, j, tempFields, tempValues, temp, sql, fCount, vCount
        fCount = Ubound(fields)
        ReDim tempFields(fCount)
        For i = 0 To fCount
            tempFields(i) = quoteColumnName(fields(i))
        Next

        vCount = Ubound(values)
        ReDim tempValues(vCount)
        For i = 0 To vCount
            ReDim temp(Ubound(values(i)))
            For j = 0 To Ubound(values(i))
                temp(j) = quoteValue(values(i)(j))
            Next
            tempValues(i) = "SELECT " & Join(temp, ", ")
        Next
        sql = "INSERT INTO " & quoteTableName(tableName) & "(" & Join(tempFields, ", ") & ")" & " " & Join(tempValues, " UNION ALL ")

        exec(sql)
    End Function

    ' 更新数据库记录
    Public Function update(ByVal tableName, ByVal columns, ByVal conditions)
        Dim i, j, sql, temp, setList(), c, fieldName, fieldValue
        c = Ubound(columns)
        ReDim setList(c)
        For i = 0 To c
            temp = Split(columns(i), ":")
            fieldName = temp(0)
            fieldValue = Right(columns(i), Len(columns(i)) - Len(temp(0)) - 1)            
            setList(i) = quoteColumnName(fieldName) & " = " & quoteValue(fieldValue)
        Next
        sql = "UPDATE " & quoteTableName(tableName) & " SET "
        sql = sql & Join(setList, ", ")
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql & " WHERE " & conditions
        End If

        update = exec(sql)
    End Function
    
    Public Function delete(ByVal tableName, ByVal conditions)
        Dim sql:sql = "DELETE FROM " & quoteTableName(tableName)
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql &  " WHERE " & conditions
        End If

        delete = exec(sql)
    End Function

    ' 返回符合条件的记录行数
    Public Function count(ByVal tableName, ByVal conditions)
        Dim sql:sql = "SELECT COUNT(*) FROM " & quoteTableName(tableName)
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql & " WHERE " & conditions
        End If
        count = Conn.Execute(sql)(0)
    End Function

    ' 根据条件判断记录是否存在
    Public Function exist(ByVal tableName, ByVal conditions)
        If (count(tableName, conditions) = 0) Then
            exist = False
        Else
            exist = True
        End If
    End Function

    ' 更新计数字段
    Public Function updateCounter(ByVal tableName, ByVal columns, ByVal conditions)
        If (NOT UtilHelper.checkNull(tableName) AND IsArray(columns)) Then
            Dim sql:sql = "UPDATE " & quoteTableName(tableName) & " SET "
            Dim i, temp, fieldName, fieldValue, c
            For i = 0 To Ubound(columns)
                temp = Split(columns(i), ":")
                c = Ubound(temp)
                If (temp(1) = 0) Then
                    sql = ""
                Else
                    sql = sql & quoteColumnName(temp(0)) & " = " & quoteColumnName(temp(0))
                    If (temp(1) > 1) Then
                        sql = sql & " - "
                    Else
                        sql = sql & " + "
                    End If
                    sql = sql & temp(1)
                End If
                If (NOT UtilHelper.checkNull(sql) AND NOT UtilHelper.checkNull(conditions)) Then
                    sql = sql & " WHERE " & conditions
                End If
            Next
            If (NOT UtilHelper.checkNull(sql)) Then
                Conn.Execute(sql)
            End If
        End If
    End Function

    ' 返回第一条记录的第一个字段值
    Public Function queryScalar(ByVal tableName, ByVal selectString, ByVal conditions, ByVal order)
        Dim sql:sql = "SELECT TOP 1 " & quoteColumnName(selectString) & " FROM " & quoteTableName(tableName)
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql & " WHERE " & conditions
        End If
        If (NOT UtilHelper.checkNull(order)) Then
            sql = sql & " ORDER BY " & order
        End If
        Dim rs
        Set rs = Server.CreateObject("ADODB.RecordSet")
        rs.Open sql, Conn, 0, 1
        If (NOT rs.Bof AND NOT rs.Eof) Then
            queryScalar = rs(0)
        Else
            queryScalar = False
        End If
        rs.Close()
        set rs = Nothing
    End Function

    ' 查询多条数据
    Public Function queryAll(ByVal tableName, ByVal selectString, ByVal conditions, ByVal order)
        Dim sql:sql = "SELECT " & quoteColumnName(selectString) & " FROM " & quoteTableName(tableName)
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql & " WHERE " & conditions
        End If
        If (NOT UtilHelper.checkNull(order)) Then
            sql = sql & " ORDER BY " & order
        End If

        Set queryAll = Conn.Execute(sql)
    End Function

    ' 查询一行数据
    Public Function queryRow(ByVal tableName, ByVal selectString, ByVal conditions, ByVal order)
        Dim sql:sql = "SELECT TOP 1 " & quoteColumnName(selectString) & " FROM " & quoteTableName(tableName)
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql & " WHERE " & conditions
        End If
        If (NOT UtilHelper.checkNull(order)) Then
            sql = sql & " ORDER BY " & order
        End If
        Dim rs
        Set rs = Server.CreateObject("ADODB.RecordSet")
        rs.Open sql, Conn, 0, 1

        Set queryRow = rs
    End Function

    ' 返回记录的第一个字段值列表
    Public Function queryColumn(ByVal tableName, ByVal columnName, ByVal conditions, ByVal order)
        Dim columns
        Dim sql:sql = "SELECT " & quoteColumnName(columnName) & " FROM " & quoteTableName(tableName)
        If (NOT UtilHelper.checkNull(conditions)) Then
            sql = sql & " WHERE " & conditions
        End If
        If (NOT UtilHelper.checkNull(order)) Then
            sql = sql & " ORDER BY " & order
        End If
        Dim rs
        Set rs = Conn.Execute(sql)
        If rs.Eof OR rs.Bof Then
            columns = Array()
        Else
            Dim results(), arr, c, i            
            arr = rs.getRows()
            c = Ubound(arr, 2)
            ReDim results(c)
            For i = 0 To c
                results(i) = arr(0, i)
            Next
            columns = results
        End If
        rs.Close()
        Set rs = Nothing

        queryColumn = columns
    End Function

    ' 根据主键查询数据
    Public Function findByPk(ByVal tableName, ByVal columnName, ByVal primaryKeyName, ByVal primaryKey)
        If (UtilHelper.checkNull(primaryKeyName)) Then
            primaryKeyName = "id"
        End If
        Dim rs
        Set rs = Server.CreateObject("ADODB.RecordSet")
        Dim sql:sql = "SELECT " & quoteColumnName(columnName) & " FROM " & quoteTableName(tableName) & " WHERE " & quoteColumnName(primaryKeyName) & " = " & CLng(primaryKey)
        rs.Open sql, Conn, 0, 1

        Set findByPk = rs
    End Function

    ' 根据 SQL 查询数据
    Public Function findBySql(ByVal sql)
        Dim rs
        Set rs = Server.CreateObject("ADODB.RecordSet")
        rs.Open sql, Conn, 0, 1

        Set findBySql = rs
    End Function

    ' 执行 SQL 语句
    ' 备注：如果是 UPDATE 或者 DELETE 操作，则返回影响的记录行数
    Public Function exec(sql)
        Conn.Execute(sql)
        Dim sqlType:sqlType = UCase(Left(sql, 6))
        If (ArrayHelper.inArray(sqlType, Array("UPDATE", "DELETE"))) Then
            exec = getRowCount()
        End If
    End Function

    Public Function quoteTableName(ByVal name)
        If (UtilHelper.RegPattern("^.*( JOIN | AS ).*$", UCase(name))) Then
            quoteTableName = name
        Else
            quoteTableName = "[" & name & "]"
        End If
    End Function

    Public Function quoteColumnName(ByVal name)
        Dim t, max
        t = Split(name, ",")
        max = Ubound(t)
        If (max < 0) Then
            quoteColumnName = name
        Else
            Dim i, v, tempValue
            For i = 0 To max
                v = Trim(t(i))
                If (UtilHelper.RegPattern("^[TOP 0..9].*$", UCase(v))) Then
                    tempValue = Split(v, " ")
                    If (ArrayHelper.isset(tempValue, 2)) Then
                        If (InStr(tempValue(2), ".") > 0) Then
                            tempValue(2) = "[" & Replace(tempValue(2), ".", "].[") & "]"
                        Else
                            tempValue(2) = "[" & tempValue(2) & "]"
                        End If
                    End If
                    t(i) = Join(tempValue, " ")
                Else
                    If (NOT (UtilHelper.RegPattern("^\w*\(.*\)*$", v) OR UtilHelper.RegPattern("^\[\w+\]$", v))) Then
                        t(i) = "[" & Trim(t(i)) & "]"
                        If (InStr(v, ".") > 0) Then
                            v = "[" & Replace(v, ".", "].[") & "]"
                            t(i) = v
                        End If
                    End If
                End If
            Next
            quoteColumnName = Join(t, ", ")
        End If
    End Function

    Public Function quoteValue(ByVal value)
        If (UtilHelper.isNumeric(value)) Then
            quoteValue = "'" & value & "'"
        ElseIf (TypeName(value) = "String") Then
            If (UCase(value) = "NULL") Then
                quoteValue = UCase(value)
            Else
                quoteValue = "N'" & Replace(value, "'", "''") & "'"
            End If
        Else
            quoteValue = "'" & Replace(value, "'", "''") & "'"
        End If
    End Function

End Class

Dim DbHelper
set DbHelper = new Class_DbHelper
%>