SQL Cache Dependency

Category:

C#, VB.NET, ASP.NET

Type:

Classes

Difficulty:

Advanced

Author:

Anonymous

Version Compatibility: Visual Basic.NET

More information:

Add the following to the web.config file in the section ' NB. DatabaseD is a label of your choice. Adjust the connectionstringname to use your connectionstring ' DatabaseD in the web.config and the namespace of the database layer must match. ' ' polltime is the milliseconds the asp.net web server polls the database for cache expiration ' ' ' ' ' ' ' ' ' ' Set the SQL database to perform CacheDependency ' ' run the following command from a command window ' C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -S "servername" -E -d databasename -ed ' ' this creates the aspnet_sql table ' ' run the following command from a command window for each database table ' C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -S "servername" -E -d databasename -t tablename -et ' this creates the entry in the aspnet_sql table and a trigger for insert,update,delete functions ' ' create a dataset in app_code for the table/query thats required ' ' for the database layer, follow the instructions below (also see the code) ' ' SQL Cached Dependency for dsGenericDS, with time based cache contingency for multiple table queries ' ' Name the DatabaseLayer Class as GenericDS_DatabaseLayer ' eg Project_DatabaseLayer ' ' Replace all references of GenericDS with the suffix of the dataset, eg Project for dsProject ' Replace all references of GenericTA with the TableAdapter name, eg, Project

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Web.Caching

#Region " GenericTA TableAdapter Namespace Extensions "
Namespace dsGenericTATableAdapters

Partial Class GenericTATableAdapter

Public CacheName As String

Public TablesReferenced As New Collection

Public Sub AddTableReferenced(ByVal TableName As String)
            Try
                TablesReferenced.Add(TableName)
            Catch ex As Exception

End Try
        End Sub

Public Function CacheExpired() As Boolean
            Dim TableName As String
            Dim CacheTime As Date
            CacheExpired = False
            If HttpContext.Current.Cache.Item(CacheName) Is Nothing Then
                CacheExpired = True
                Exit Function
            End If
            CacheTime = HttpContext.Current.Cache.Item(CacheName & "_cachetime")
            For Each TableName In TablesReferenced
                If HttpContext.Current.Cache("key_" & TableName) = Nothing Then
                    CacheExpired = True
                    Exit Function
                Else
                    If HttpContext.Current.Cache("key_" & TableName) > CacheTime Then
                        CacheExpired = True
                        Exit Function
                    End If
                End If
            Next
        End Function

' Add a SetCommandTimeout Property
        Public Sub SetCommandTimeOut(ByVal timeOut As Integer)
            For Each command As SqlCommand In Me.CommandCollection
                command.CommandTimeout = timeOut
            Next
        End Sub

Public Sub CacheDataset(ByVal Data As dsGenericDS.GenericDSDataTable)
            Dim lSQLCacheDependency As SqlCacheDependency
            Dim TableName As String
            HttpContext.Current.Cache.Insert(CacheName, Data)
            'Insert CacheKeys for all Tables
            For Each TableName In TablesReferenced
                lSQLCacheDependency = New SqlCacheDependency("DatabaseD", TableName)
                If HttpContext.Current.Cache("key_" & TableName) Is Nothing Then
                    HttpContext.Current.Cache.Insert("key_" & TableName, Now, lSQLCacheDependency, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration)
                End If
            Next
            ' insert date time of cache
            HttpContext.Current.Cache.Insert(CacheName & "_cachetime", Now)
        End Sub

Public Function CachedDataset() As dsGenericDS.GenericDSDataTable
            Return HttpContext.Current.Cache.Item(CacheName)
        End Function

Public ReadOnly Property Commands() As SqlCommand()
            Get
                Commands = Me.CommandCollection
            End Get
        End Property

End Class

End Namespace
#End Region

Public Class GenericDS_DatabaseLayer

Public Function GetAll() As dsGenericDS.GenericDSDataTable
        Dim dsData As New dsGenericDS
        Dim TA As New dsGenericTATableAdapters.GenericTATableAdapter
        TA.CacheName = "Get_GenericDS"
        TA.AddTableReferenced("GenericDS")
        If TA.CacheExpired Then
            TA.GetAll(dsData.GenericDS)
            TA.CacheDataset(dsData.GenericDS)
        End If
        Return TA.CachedDataset()
    End Function

'Public Function GetSingle(ByVal GenericDSId As Integer) As dsGenericDS.GenericDSDataTable
    '    Dim dsData As New dsGenericDS
    '    Dim TA As New dsGenericTATableAdapters.GenericTATableAdapter
    '    TA.CacheName = "Get_GenericDS_GenericDSId=" & GenericDSId
    '    TA.AddTableReferenced("GenericDS")
    '    If TA.CacheExpired Then
    '        TA.GetSingle(dsData.GenericDS, GenericDSId)
    '        TA.CacheDataset(dsData.GenericDS)
    '    End If
    '    Return TA.CachedDataset()
    'End Function

End Class