Improving data access class [closed]

问题:

A few years ago we (as a company) faced the scenario of getting our developers to stop writing classic asp pages and switch to .net (and in turn system.data for data access).

As a supposedly ‘short term’ measure I wrote the following class to make the switchover easier for those not used to system.data & all its new objects:

http://www.heavencore.co.uk/forum/viewthread.php?thread_id=185

The main purpose of this class was to keep usage as similar to classic asp as possible & keep the usage VERY simple (plus to add email alerts for error catching yada yada yada):

Public db As New SimpleDataAccess
Public RS As New DataTable

ConnectDatabase()
db.Execute_Query(RS, "SELECT * FROM whatever WHERE IntColumn = " & tools.parseint(EmployeeID, 0) & " or TextColumn = '" & db,Escape("bla'blabla") & "' ORDER BY IntColumn")
For Each DB_Row As DataRow In RS.Rows
    response.Write(DB_Row("IntColumn"))
Next
CloseDatabase()

Now, the two main reasons why this class sucks:

• Sql Injection stuff (parseint & quote escaping) has to be done outside the class - prone to forgetfulness - Parameterised queries definitely need to be introduced here!
• CloseDatabase() has to be called manually at the end of the page - this is often forgot and a connection is left open to the server - even after the page has finished rendering etc

Reasons why this class was good:

• Usage of the class was very simple and allowed old classic asp code to be VERY easily converted to .net
• Email alerts for query & connection errors was handled invisibly in the class itself
• It has worked perfectly for 2+ years now, no problems

My Question:

Are there any other classes / suggestions out there that will allow me to either replace this class but retain the VERY simple usage OR what would be the best way to modify the Execute_Query() and Execute_NonQuery() methods to handle parameterised queries?

Simplicity is key!

PS: Where is a good place to post large chunks of code for use in SO questions? Pastebin etc only keep stuff for a month...

回答1:

I think this might help you. A large chunk of code. This handles almost every thing you need. You can pass parameters in an array. Also can use with stored procedures as well. Nothing to worry about connection closing.

Hope this helps.

回答2:

I've always used the functions that came from http://www.fmstocks.com/ (A sample Asp Classic MS application). It's very simple and works with parameters

Example usage:

set rs = RunSQLReturnRs("Select * from Usuario where UsuarioID = ?", _
                        array(mp("@UsuarioID", adInteger, 0, UsuarioID)))
If not rs.eof then
    UsuarioName = rs("FullName")
end if 

DbHelper.ASP full code (slightly modified over the years)

<!--#include file="../bus/adovbs.inc"-->
<%
Function GetConnectionString()
    GetConnectionString = "file name=c:\udl\miconnstring.udl"
End Function

Function mp(PName , PType , PSize, PValue)
    mp = Array(PName, PType, PSize, PValue)
End Function

Function RunSPReturnRS(strSP, params())
    On Error Resume next

    ' Create the ADO objects
    Dim rs , cmd
    Set rs = server.createobject("ADODB.Recordset")
    Set cmd = server.createobject("ADODB.Command")

    ' Init the ADO objects  & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc
    cmd.CommandTimeout = 900 ' 15 minutos

    collectParams cmd, params

    ' Execute the query for readonly
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if

    ' Disconnect the recordset
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Set rs.ActiveConnection = Nothing

    ' Return the resultant recordset
    Set RunSPReturnRS = rs

End Function

Function RunSP(strSP , params())
    On Error resume next

    ' Create the ADO objects
    Dim cmd
    Set cmd = server.createobject("ADODB.Command")

    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc
    cmd.CommandTimeout = 900 ' 15 minutos
    collectParams cmd, params

    ' Execute the query without returning a recordset
    cmd.Execute , , adExecuteNoRecords
    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if


    ' Disconnect the recordset and clean up
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing

    Exit Function

End Function

Function RunSQL(strSP , params())
    On Error resume next

    ' Create the ADO objects
    Dim cmd
    Set cmd = server.createobject("ADODB.Command")

    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdText
    cmd.CommandTimeout = 900 ' 15 minutos
    collectParams cmd, params

    ' Execute the query without returning a recordset
    cmd.Execute , , adExecuteNoRecords
    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if

    ' Cleanup
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing

    Exit Function

End Function

Function RunSQLReturnRS(sqlstmt, params())
    On Error Resume next

    ' Create the ADO objects
    Dim rs , cmd
    Set rs = server.createobject("ADODB.Recordset")
    Set cmd = server.createobject("ADODB.Command")

    ' Init the ADO objects  & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = sqlstmt
    cmd.CommandType = adCmdText
    cmd.CommandTimeout = 900 ' 15 minutos

    collectParams cmd, params

    ' Execute the query for readonly
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if

    ' Disconnect the recordset
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Set rs.ActiveConnection = Nothing

    ' Return the resultant recordset
    Set RunSQLReturnRS = rs

End Function


Function RunSPReturnInteger(strSP , params())
    On Error resume next

    ' Create the ADO objects
    Dim cmd
    Set cmd = server.createobject("ADODB.Command")

    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc
    cmd.CommandTimeout = 900 ' 15 minutos
    collectParams cmd, params

    ' Assume the last parameter is outgoing
    cmd.Parameters.Append cmd.CreateParameter("@retval", adInteger, adParamOutput, 4)

    ' Execute without a resulting recordset and pull out the "return value" parameter
    cmd.Execute , , adExecuteNoRecords
    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if
    RunSPReturnInteger = cmd.Parameters("@retval").Value

    ' Disconnect the recordset, and clean up
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing

    Exit Function
End Function

Private Sub collectParams(cmd , argparams())
    Dim params , v
    Dim i , l , u

    params = argparams


    For i = LBound(params) To UBound(params)
        l = LBound(params(i))
        u = UBound(params(i))

        ' Check for nulls.
        If u - l = 3 Then
            If VarType(params(i)(3)) = vbString Then
                If params(i)(3) = "" then
                    v = null
                else
                    v = params(i)(3)
                end if
            Else
                v = params(i)(3)
            End If

            If params(i)(1) = adLongVarChar Then
                Dim p 'As New Parameter
                Set p = cmd.CreateParameter(params(i)(0), params(i)(1), adParamInput)
                p.Attributes = adParamLong + adParamSigned
                If Not IsNull(v) Then
                    'Seteo para text columns is not null
                    p.AppendChunk v
                    p.Size = Len(v)
                Else
                    'Seteo para text columns is null
                    p.Value = v
                    p.Size = 10000
                End If
                cmd.Parameters.Append p
            Else
                cmd.Parameters.Append cmd.CreateParameter(params(i)(0), params(i)(1), adParamInput, params(i)(2), v)
            End If
        Else
            RaiseError m_modName, "collectParams(...): incorrect # of parameters"
        End If
    Next
End Sub

%>