How to implement a caching model without violating MVC pattern?

HttpContext Cache attributes don't work well with MVC plumbing. They work for a page or public controller methods. If you want to load multiple content from different sources without using ajax or without making multiple calls, you need to use cache calls directly.

There are many third party tools for caching. You are not restricted to HttpContext.

This example will show you how to load data into cache and update that only if we need to. This example will use table as a source and add triggers to track changes on the table.

We need both update and delete triggers in our data source to track changes. I provided scripts here and included source code for simple mvc and EF code first application at github for this sample. You can check that for cache usage and code first usage for stored procs.

Lets create our dummy source table:

------------------------------------------------------------------

USE [MarketDb]
GO

/****** Object:  Table [dbo].[MarketEvents]    Script Date: 04/16/2012 22:08:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MarketEvents](
    [MarketEventId] [int] IDENTITY(1,1) NOT NULL,
    [CompanyId] [int] NOT NULL,
    [Campaign] [nvarchar](50) NOT NULL,
    [CampaignType] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](50) NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [RowGuid] [uniqueidentifier] NOT NULL,
    [EventStatus] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [MarketEventId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
 

We are targetting campaign column as a source. We will add one table to track all cache updates. It will store one record for each table. When we make change to data, this record will keep update time. We will compare our cache time to this to decide if we need to load fresh data.

CREATE TABLE [dbo].[CacheTrack](
    [CacheTrackId] [int] IDENTITY(1,1) NOT NULL primary key,
    [TableName] [nvarchar](50) NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
 )

Triggers to update changes at our column: DELETE TRIGGER

create TRIGGER [dbo].[dMarketEventsCode] ON [dbo].[MarketEvents]

AFTER delete  AS 

BEGIN
    DECLARE @Count int;
--===================================================
--    After Trigger for delete
--  Trigger type is after so this will happen after all update operation is done.
--===================================================
    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        
        -- Insert record into CacheTrack if not exists, otherwise update that         
        If NOt exists (select CacheTrackId from CacheTrack where tablename = 'MarketEvents')
         
            INSERT INTO dbo.CacheTrack
                (TableName
                ,UpdateTime                
                )
            SELECT 
                'MarketEvents'               
                ,Getutcdate() ;               
         
        ELSE
            UPDATE dbo.CacheTrack
            SET    UpdateTime =    Getutcdate()            
            where TableName = 'MarketEvents';
         
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END

------UPDATE TRIGGER

CREATE TRIGGER [dbo].[uMarketEventsCode] ON [dbo].[MarketEvents] 
AFTER UPDATE,INSERT  AS 
BEGIN
    DECLARE @Count int;
--===================================================
--    After Trigger to detect changes for Campaign. we need to detect those to populate cache
--  Trigger type is after so this will happen after all update operation is done.
--===================================================
    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;
    print 'uMarketEventsCode fired';
    
    BEGIN TRY
        --DEBUG
        ----select 'inserted',* from inserted
        ----select 'deleted',* from deleted
        ----select i.Campaign  FROM Inserted i
        ----    LEFT JOIN Deleted d ON i.MarketEventId = d.MarketEventId        
        ----    WHERE ISNULL(i.Campaign, '<some null value>') != ISNULL(d.Campaign, '<some null value>')
            
        --Update only if column data changes
        --Update function does not care if column data changed or not,It returns true if statement contains this column
        --We can check previous value to make sure it is really a change on this column. If it is an insert op, it does not have deleted record
        --Dont forget that trigger's inserted or deleted may contain multiple rows. It fires for all the inserts,updates..
        IF UPDATE(Campaign) and          
           exists(select i.Campaign  FROM Inserted i
             LEFT JOIN Deleted d ON i.MarketEventId = d.MarketEventId        
            WHERE ISNULL(i.Campaign, '<some null value>') != ISNULL(d.Campaign, '<some null value>'))
             
            
        -- Insert record into CacheTrack if not exists, otherwise update that
        BEGIN
        print 'uMarketEventsCode add record';
            If NOt exists (select CacheTrackId from CacheTrack where tablename = 'MarketEvents')
             
                INSERT INTO dbo.CacheTrack
                    (TableName
                    ,UpdateTime                
                    )
                SELECT 
                    'MarketEvents'               
                    ,Getutcdate() ;               
             
            ELSE
                UPDATE dbo.CacheTrack
                SET    UpdateTime =    Getutcdate()            
                where TableName = 'MarketEvents';
             

        
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Our controller will just make a call to populate data from a private method. Inside that we check if cache is new. If it needs to be updated, we feed data. In this example, we are storing cache for one companyid. You can wrap this call in a class to add more features. At the end, you can cache part of the data but refresh that whenever change happens. We dont need to check update table regularly. We can check it when we need data. We are assuming that our update time check is much faster than actual data load. This happens if you are making calculations or accessing million rows for some lookup items.

This example shows dynamic cache in MVC.

        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";
            int activecompanyid = 1;

            //this will load from cache and use it until we need to update the cache
            ViewBag.Mailcodes = GetCacheMarketCodes(activecompanyid);
            return View();
        }


        public const string RefreshKeyword = "refreshed";

        /// <summary>
        /// we will cache market codes
        /// cache will be updated if something changes from website or db.
        /// Db triggers handle changes and record in a simple table. We check that 
        /// every time,so we can skip expensive queries and only check audit tables to refresh cache.
        /// </summary>
        /// <returns></returns>
        private string[] GetCacheMarketCodes(int companyid)
        {
            //keep update flag for mailcodes. we will update this when some change happens at db
            string updateflag = (string)HttpContext.Cache["MailCodesUpdate" + companyid];
            bool needsupdate = CheckTriggerRecordsForUpdate("MarketEvents", updateflag);
            
            
            if (needsupdate ||
                HttpContext.Cache["MailCodes" + companyid] == null)
            {

                using (var db = new MarketContext(ConnectionStr))
                {


                    //make call to db to pget items
                    var listitems =
                        (from x in db.MarketEvents orderby x.Campaign select x.Campaign).Distinct().OrderBy(a => a).
                            ToArray();

                    //Add item to cache with expiration time, sliding, priority and callback if needed
                    HttpContext.Cache["MailCodes" + companyid] = listitems;
                    

                    //set as refreshed to track when we refreshed this
                    HttpContext.Cache["MailCodesUpdate" + companyid] = RefreshKeyword + DateTime.UtcNow.ToString();

                }
            }

            return HttpContext.Cache["MailCodes" + companyid] as string[];
        }



        private bool CheckTriggerRecordsForUpdate(string tablename, string updateflag)
        {
            if (updateflag.HasSomething() && updateflag.Contains(RefreshKeyword))
            {
                using (var db = new MarketContext(ConnectionStr))
                {
                    var entry = db.ProcGetCacheUpdateTime(tablename).FirstOrDefault();
                    //now check if we have something in cache records
                    if (entry != null)
                    {
                        DateTime lastupdate = DateTime.Parse(updateflag.Replace(RefreshKeyword, ""));
                        //compare
                        if (lastupdate >= entry.UpdateTime)
                        {
                            //dont need update. our cache is better than what triggered in db.
                            return false;
                        }
                    }
                    else
                    {
                        return false; //no need to refresh that. we dont have any entry in cache table
                    }
                }
            }

            return true;
        }

Link to Github code: https://github.com/omercs/CacheMvc