Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB
Expertise: Intermediate
Aug 24, 2004

Cache Your Repeated Calls to the Database

Use this code to cache your repeated calls to the database:

Dim mLookup As Scripting.Dictionary
Dim cn As ADODB.Connection
Enum enumLookup
    ltCustomers
    ltVendors
    ltSalespeople
End Enum
Private Sub Form_Load()
    Dim rstest As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=sqloledb;data source=myserver;initial catalog=mydb;integrated
security=sspi"
    cn.Open
    Set rstest = GetCache(ltCustomers)
End Sub
Private Function GetCache(eLookupType As enumLookup, Optional bRebind As Boolean) As
ADODB.Recordset

    Dim sSQL As String
    Dim rs As New ADODB.Recordset
    
    If mLookup Is Nothing Then
        Set mLookup = New Scripting.Dictionary
    End If
    'Remove if necessary
    If bReplace Then
        If mLookup.Exists("L" & CStr(eLookupType)) Then
            mLookup.Remove ("L" & CStr(eLookupType))
        End If
    End If
    Select Case eLookupType
        Case ltCustomers
            sSQL = "SELECT * FROM Customers"
        Case ltVendors
            sSQL = "SELECT * FROM Vendors"
        Case ltSalespeople
            sSQL = "SELECT * FROM Salespeople"
    End Select
    'Cache the dataset
    If mLookup.Exists("L" & CStr(eLookupType)) Then
        Set GetCache = pLookupDictionary.Item("L" & CStr(eLookupType))
    Else
        GetCache.Open sSQL, cn
        pLookupDictionary.Add "L" & CStr(eLookupType), GetCache
    End If
    
    Set rs = Nothing
End Function
Private Sub Form_Unload(Cancel As Integer)
    cn.Close
    Set cn = Nothing
    Set mLookup = Nothing
End Sub
Jamie Longstreet
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap