李大仁博客

Title: "Saving Database Connection in ASP Using Session"
Date: "2009-05-16"
Categories:

• "Source and Coding"
  Tags:
• "ASP Coding"
• "VBScript"
• "Database"

There are many ways to save a database connection in ASP. However, for a client, saving the database connection in the same session greatly reduces the number of times the database connection needs to be established. Using just one connection can complete all the work and reduce the consumption of creating, allocating, and recycling data connections in the connection pool. Additionally, when the user closes the browser, the session is automatically eliminated, allowing for the automatic release of the data connection when the user logs out.

However, there are drawbacks to using session storage. Having too many users will create different sessions, which increases the server-side consumption of storing sessions. Furthermore, due to the limited nature of session IDs, conflicts are inevitable. In Java, this is resolved by using EJB, where Session Bean is a good solution.

Below is the code for using session storage for data connection in ASP:

function BuildConn
    dim ConnStr
    dim path
    dim conn
    path = RootPath & "DBName"    'Set the database path
    ConnStr="Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(path)
    On Error Resume Next
    Set conn= Server.CreateObject("ADODB.Connection")
    conn.open ConnStr
    If Err Then
        Err.Clear
        set conn=nothing
    end if
    set BuildConn = conn
end function

sub GetConn
    'Use Session to control the database connection
    'Note that this is only suitable for applications with a certain number of connections. As the number of connected users increases, the number of sessions also increases.
    set temp = BuildConn
    if(session("DBConn") = "") then
        session("DBConn") = temp
    end if
    conn = session("DBConn") 'Set conn
end sub

sub CloseConn
    conn = session("DBConn") 'Set conn
    if conn <> "" then
        conn.close
    end if
end sub