The SQL Server cache provider allows you to save long running results to a SQL Server database.  The table will automatically be created for saving cache items.  Objects are binary serialized and then saved to the database.  Objects do not need to be marked as Serializable to be saved but they do need to have a default empty constructor.  By default, expired items are removed from the cache every minute.  If the cache is over the MaxMegabytes or MaxCacheItems items will be removed oldest first until the cache is 75% of the MaxMegabytes and MaxCacheItems.

Required

A reference to System.Data is required.

Defaults for the Sqlite cache provider

• MaxMegabytes – 50
• MaxCacheItems – Int32.MaxValue
• IsAsyncSetAndRemove - false
• DefaultExpirationInMinutes – 60 * 24  (1 day)
• CacheTableName - SmartCache
• ScavengingIntervalMilliseconds – 60000 (1 minute)
• ScavengingTypeOption – Oldest
• IsEncrypted – false
• IsCompressed - false

Recommended options for optimal performance

• Use a memory cache provider for the primary cache and a SQL Server cache provider for the secondary cache
• Set IsAsyncSetAndRemove to true for increased performance when saving and removing.  You will then need to call Flush before your application exits to ensure queued items are written.

This is the script that is run if the CacheTableName does not exist in the database:

CREATE TABLE SmartCache (

       Id BigInt NOT NULL,

       CacheKey Text NOT NULL,

       CacheRegion nvarchar(254) NOT NULL,

       CacheValue Image NOT NULL,

       LastAccess DateTime NOT NULL,

       CreateDate DateTime NOT NULL,

       SlidingExpirationTimeInSeconds BigInt,

       AbsoluteExpirationTime DateTime,

    CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED([Id] ASC) WITH (PAD_INDEX  = OFF,

    STATISTICS_NORECOMPUTE  = OFF,

    IGNORE_DUP_KEY = OFF,

    ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON)

    ON [PRIMARY]) ON [PRIMARY];

 

Example

        

    public class SqlServerCacheExample

    {

       

        public void SqlServerCacheExampleUsage()

        {

            //Build parameters for the report

            Dictionary<string, object> parms = new Dictionary<string, object>();

            parms.Add("Month", DateTime.Now.Month);

            parms.Add("Year", DateTime.Now.Year);

 

            //Get the cache key based on the report parameters

            string cacheKey = Cache.Instance.BuildCacheKey("MonthlySalesReport", parms);

 

            //Get the report from the cache, if it does not exist, call the ReportLogic.MonthlySalesReport, save it to the cache and return it

            List<ReportDetail> result = Cache.Instance.Get<List<ReportDetail>>(cacheKey, NetCachingLibraryTest.ReportLogic.MonthlySalesReport, parms);

        }

    }

    public static class Cache

    {

        private static SmartCache _smartCache;

        public static SqlServerCacheProvider SecondaryCache { get; set; }

        private static void Setup()

        {

            string connectionString = "Server=MyServer; Database=MyDatabase; Trusted_Connection=True";

            SecondaryCache = new SqlServerCacheProvider(connectionString);

           

            SmartConfig config = new SmartConfig(new MemoryCacheProvider(), SecondaryCache);

 

            //Specify UserName and LicenseKey for Licensed Mode

            //config.UserName = "my user name";

            //config.LicenseKey = "my license key";

 

            _smartCache = new SmartCache(config);

        }

        public static SmartCache Instance

        {

            get

            {

                if (_smartCache == null)

                    Setup();

                return _smartCache;

            }

        }

    }

    public static class ReportLogic

    {

        //Example long running report

        public static List<ReportDetail> MonthlySalesReport(Dictionary<string, object> parms)

        {

            System.Threading.Thread.Sleep(1000);

            List<ReportDetail> list = new List<ReportDetail>();

            list.Add(new ReportDetail("Midwest", 233457.47m));

            list.Add(new ReportDetail("Southern", 234789.88m));

            return list;

        }

    }