<%
'--------------------------------------------------------------------------------------------------
' MODULE:   db.asp
' AUTHOR:   © www.u229.no
' CREATED:  November 2008
'--------------------------------------------------------------------------------------------------
' COMMENT: A generic routine for the most common database operations.
'          Supports SQL Server 2008. Add your own connection strings as you like.
'--------------------------------------------------------------------------------------------------
' ROUTINES:
'
' - Function DB(sSQL, sReturnType, iCursorType, iCursorLocation, iLockType)
'--------------------------------------------------------------------------------------------------
' EXAMPLE:  Passing default cursor values. Use the property names if you like.
'
'    sSQL = "SELECT * FROM MyTable"
'    sData = DB(sSQL, "GETSTRING", 0, 2, 1)
'--------------------------------------------------------------------------------------------------

'// OBJECTSTATE ENUM VALUES
Const adStateClosed  = 0  	   '// The object is closed
Const adStateOpen =	1 	   '// The object is open
Const adStateConnecting = 2        '// The object is connecting
Const adStateExecuting = 4         '// The object is executing a command
Const adStateFetching =	8          '// The rows of the object are being retrieved

'// CURSORTYPE ENUM VALUES
Const adOpenUnspecified = -1    '// Does not specify the type of cursor.
Const adOpenForwardOnly = 0     '// Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.
Const adOpenKeyset = 1          '// Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.
Const adOpenDynamic = 2         '// Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.
Const adOpenStatic = 3          '// Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

'// LOCKTYPE ENUM VALUES
Const adLockUnspecified = -1       '// Unspecified type of lock. Clones inherits lock type from the original Recordset.
Const adLockReadOnly = 1           '// Read-only records
Const adLockPessimistic = 2        '// Pessimistic locking, record by record. The provider lock records immediately after editing
Const adLockOptimistic = 3         '// Optimistic locking, record by record. The provider lock records only when calling update
Const adLockBatchOptimistic = 4    '// Optimistic batch updates. Required for batch update mode

'// CURSORLOCATION ENUM VALUES
Const adUseNone = 1      '// OBSOLETE (appears only for backward compatibility). Does not use cursor services
Const adUseServer = 2    '// Default. Uses a server-side cursor
Const adUseClient = 3    '// Uses a client-side cursor supplied by a local cursor library.
                         '// For backward compatibility, the synonym adUseClientBatch is also supported

'// YOUR CONNECTIONSTRING
Const CONNECTION_STRING = "Provider=SQLNCLI10;Server=MyDBServerName;Database=MyDBName;Uid=XX;Pwd=XX;"

'------------------------------------------------------------------------------------------------------------
' Comment:
'------------------------------------------------------------------------------------------------------------
Function DB(sSQL, sReturnType, iCursorType, iCursorLocation, iLockType)
    On Error Resume Next

    Dim oConn: Set oConn = CreateObject("ADODB.Connection")
    Dim oRs: Set oRs = CreateObject("ADODB.Recordset")

    '// SET PROPERTIES FOR THE CONNECTION OBJECT
    With oConn
        If .State = adStateOpen Then .Close
        .ConnectionString = CONNECTION_STRING
        .open
    End With

    '// SET PROPERTIES FOR THE RECORDSET OBJECT
    With oRs
        If .State = adStateOpen Then .Close
        .CursorType = iCursorType
        .CursorLocation = iCursorLocation
        .LockType = iLockType
        .ActiveConnection = oConn
        .Source = sSQL
        .open
    End With

    Select Case UCase(sReturnType)
        '// THE GETROWS METHOD RETURNS A 2 DIMENSIONAL ARRAY
        Case "GETROWS": DB = oRs.GetRows

        '// RETURN A RECORDSET. SETS A POINTER TO THE RECORDSET OBJECT
        Case "RECORDSET": Set DB = oRs

        '// GETSTRING(STRINGFORMAT, NUMROWS, COLUMNDELIMITER, ROWDELIMITER, NULLEXPR)
        '// DEFAULT RECORD: value chr(9) value chr(9) value chr(13)
        Case "GETSTRING": DB = oRs.GetString()

        '// RETURN A BOOLEAN
        Case "BOOLEAN": DB = (Err.Number = 0 Or (Err.Number = 3021 Or Err.Number = 3704))

        Case Else
    End Select

    If Err Then
        '// 3021 = EOF/BOF | 3704 = OPERATION IS NOT ALLOWED WHEN THE OBJECT IS CLOSED/(NO RECORDS FOUND)
        '// Your error handling here:
	'// If Err.Number <> 3021 And Err.Number <> 3704 Then ...
    End If
    
    Set oConn = Nothing
    Set oRs = Nothing

End Function
%>


'============================================================
' EXAMPLE 1: GetRows - 2 Dimensional array
'============================================================

'// Returns a 2 dimensional array
aRecords = DB("SELECT * FROM MyTable", "GetRows", 0, 2, 1)

For i = LBound(aRecords) To UBound(aRecords, 2)
    Response.Write aRecords(1, i) & "<br />"
    Response.Write aRecords(2, i) & "<br />"
    '// etc
Next


'============================================================
' EXAMPLE 2: Recordset
'============================================================

Set oRs = DB("SELECT * FROM MyTable", "RECORDSET", 0, 2, 1)

If Not IsEmpty(oRs) Or oRs.EOF Then

    Do While Not oRs.EOF
        Response.Write oRs(0) & "<br />"
        Response.Write oRs(1) & "<br />"
        oRs.MoveNext
    Loop

End If

'============================================================
' EXAMPLE 3: GetString
'============================================================

sGetString = DB("SELECT * FROM MyTable", "GetString", 0, 2, 1)

'// Chr(9) is the default column delimiter
aColumns = Split(sGetString, Chr(9))

For i = LBound(aColumns) To UBound(aColumns)
    Response.Write aColumns(i) & "<br />"
Next

'============================================================
' EXAMPLE 4: Boolean (Great for update and delete operations)
'============================================================

bSuccess = DB("DELETE FROM MyTable WHERE id=43", "BOOLEAN", 0, 2, 1)