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

WEBINAR:

On-Demand

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017


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
Thanks for your registration, follow us on our social networks to keep up-to-date