Robin Hames

Adding SQL Cache Dependencies to the Loosely coupled .NET Cache Provider

Rhames — Thu, 13 Sep 2012 11:24:45 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2012/09/13/adding-sql-cache-dependencies-to-the-loosely-coupled-.net-cache.aspx

This post adds SQL Cache Dependency support to the loosely coupled .NET Cache Provider that I described in the previous post (http://geekswithblogs.net/Rhames/archive/2012/09/11/loosely-coupled-.net-cache-provider-using-dependency-injection.aspx). The sample code is available on github at https://github.com/RobinHames/CacheProvider.git.

Each time we want to apply a cache dependency to a call to fetch or cache a data item we need to supply an instance of the relevant dependency implementation. This suggests an Abstract Factory will be useful to create cache dependencies as needed. We can then use Dependency Injection to inject the factory into the relevant consumer.

Castle Windsor provides a typed factory facility that will be utilised to implement the cache dependency abstract factory (see http://docs.castleproject.org/Windsor.Typed-Factory-Facility-interface-based-factories.ashx).

Cache Dependency Interfaces

First I created a set of cache dependency interfaces in the domain layer, which can be used to pass a cache dependency into the cache provider.

ICacheDependency

The ICacheDependency interface is simply an empty interface that is used as a parent for the specific cache dependency interfaces. This will allow us to place a generic constraint on the Cache Dependency Factory, and will give us a type that can be passed into the relevant Cache Provider methods.

namespace CacheDiSample.Domain.CacheInterfaces

    public interface ICacheDependency

ISqlCacheDependency.cs

The ISqlCacheDependency interface provides specific SQL caching details, such as a Sql Command or a database connection and table. It is the concrete implementation of this interface that will be created by the factory in passed into the Cache Provider.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace CacheDiSample.Domain.CacheInterfaces

    public interface ISqlCacheDependency : ICacheDependency

        ISqlCacheDependency Initialise(string databaseConnectionName, string tableName);

        ISqlCacheDependency Initialise(System.Data.SqlClient.SqlCommand sqlCommand);

If we want other types of cache dependencies, such as by key or file, interfaces may be created to support these (the sample code includes an IKeyCacheDependency interface).

Modifying ICacheProvider to accept Cache Dependencies

Next I modified the exisitng ICacheProvider<T> interface so that cache dependencies may be passed into a Fetch method call. I did this by adding two overloads to the existing Fetch methods, which take an IEnumerable<ICacheDependency> parameter (the IEnumerable allows more than one cache dependency to be included). I also added a method to create cache dependencies. This means that the implementation of the Cache Provider will require a dependency on the Cache Dependency Factory. It is pretty much down to personal choice as to whether this approach is taken, or whether the Cache Dependency Factory is injected directly into the repository or other consumer of Cache Provider. I think, because the cache dependency cannot be used without the Cache Provider, placing the dependency on the factory into the Cache Provider implementation is cleaner.

ICacheProvider.cs

using System;

using System.Collections.Generic;

namespace CacheDiSample.Domain.CacheInterfaces

    public interface ICacheProvider<T>

        T Fetch(string key, Func<T> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry);

        T Fetch(string key, Func<T> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

            IEnumerable<ICacheDependency> cacheDependencies);

        IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry);

        IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData,

            DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

            IEnumerable<ICacheDependency> cacheDependencies);

        U CreateCacheDependency<U>()

            where U : ICacheDependency;

Cache Dependency Factory

Next I created the interface for the Cache Dependency Factory in the domain layer.

ICacheDependencyFactory.cs

namespace CacheDiSample.Domain.CacheInterfaces

    public interface ICacheDependencyFactory

        T Create<T>()

            where T : ICacheDependency;

        void Release<T>(T cacheDependency)

            where T : ICacheDependency;

I used the ICacheDependency parent interface as a generic constraint on the create and release methods in the factory interface.

Now the interfaces are in place, I moved on to the concrete implementations.

ISqlCacheDependency Concrete Implementation

The concrete implementation of ISqlCacheDependency will need to provide an instance of System.Web.Caching.SqlCacheDependency to the Cache Provider implementation. Unfortunately this class is sealed, so I cannot simply inherit from this. Instead, I created an interface called IAspNetCacheDependency that will provide a Create method to create an instance of the relevant System.Web.Caching Cache Dependency type. This interface is specific to the ASP.NET implementation of the Cache Provider, so it should be defined in the same layer as the concrete implementation of the Cache Provider (the MVC UI layer in the sample code).

IAspNetCacheDependency.cs

using System.Web.Caching;

namespace CacheDiSample.CacheProviders

    public interface IAspNetCacheDependency

        CacheDependency CreateAspNetCacheDependency();

Next, I created the concrete implementation of the ISqlCacheDependency interface. This class also implements the IAspNetCacheDependency interface. This concrete implementation also is defined in the same layer as the Cache Provider implementation.

AspNetSqlCacheDependency.cs

using System.Web.Caching;

using CacheDiSample.Domain.CacheInterfaces;

namespace CacheDiSample.CacheProviders

    public class AspNetSqlCacheDependency : ISqlCacheDependency, IAspNetCacheDependency

        private string databaseConnectionName;

        private string tableName;

        private System.Data.SqlClient.SqlCommand sqlCommand;

        #region ISqlCacheDependency Members

        public ISqlCacheDependency Initialise(string databaseConnectionName, string tableName)

            this.databaseConnectionName = databaseConnectionName;

            this.tableName = tableName;

            return this;

        public ISqlCacheDependency Initialise(System.Data.SqlClient.SqlCommand sqlCommand)

            this.sqlCommand = sqlCommand;

            return this;

        #endregion

        #region IAspNetCacheDependency Members

        public System.Web.Caching.CacheDependency CreateAspNetCacheDependency()

            if (sqlCommand != null)

                return new SqlCacheDependency(sqlCommand);

            else

                return new SqlCacheDependency(databaseConnectionName, tableName);

        #endregion

ICacheProvider Concrete Implementation

The ICacheProvider interface is implemented by the CacheProvider class. This implementation is modified to include the changes to the ICacheProvider interface.

First I needed to inject the Cache Dependency Factory into the Cache Provider:

 private ICacheDependencyFactory cacheDependencyFactory;

 public CacheProvider(ICacheDependencyFactory cacheDependencyFactory)

     if (cacheDependencyFactory == null)

         throw new ArgumentNullException("cacheDependencyFactory");

    this.cacheDependencyFactory = cacheDependencyFactory;

Next I implemented the CreateCacheDependency method, which simply passes on the create request to the factory:

public U CreateCacheDependency<U>() where U : ICacheDependency

    return this.cacheDependencyFactory.Create<U>();

The signature of the FetchAndCache helper method was modified to take an additional IEnumerable<ICacheDependency> parameter:

private U FetchAndCache<U>(string key, Func<U> retrieveData,

    DateTime? absoluteExpiry, TimeSpan? relativeExpiry, IEnumerable<ICacheDependency> cacheDependencies)

and the following code added to create the relevant System.Web.Caching.CacheDependency object for any dependencies and pass them to the HttpContext Cache:

CacheDependency aspNetCacheDependencies = null;

if (cacheDependencies != null)

    if (cacheDependencies.Count() == 1)

        // We know that the implementations of ICacheDependency will also implement IAspNetCacheDependency

        // so we can use a cast here and call the CreateAspNetCacheDependency() method

        aspNetCacheDependencies = ((IAspNetCacheDependency)cacheDependencies.ElementAt(0)).CreateAspNetCacheDependency();

    else if (cacheDependencies.Count() > 1)

        AggregateCacheDependency aggregateCacheDependency = new AggregateCacheDependency();

        foreach (ICacheDependency cacheDependency in cacheDependencies)

            // We know that the implementations of ICacheDependency will also implement IAspNetCacheDependency

            // so we can use a cast here and call the CreateAspNetCacheDependency() method

            aggregateCacheDependency.Add(((IAspNetCacheDependency)cacheDependency).CreateAspNetCacheDependency());

        aspNetCacheDependencies = aggregateCacheDependency;

HttpContext.Current.Cache.Insert(key, value, aspNetCacheDependencies, absoluteExpiry.Value, relativeExpiry.Value);

The full code listing for the modified CacheProvider class is shown below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Caching;

using CacheDiSample.Domain.CacheInterfaces;

namespace CacheDiSample.CacheProviders

    public class CacheProvider<T> : ICacheProvider<T>

        private ICacheDependencyFactory cacheDependencyFactory;

        public CacheProvider(ICacheDependencyFactory cacheDependencyFactory)

            if (cacheDependencyFactory == null)

                throw new ArgumentNullException("cacheDependencyFactory");

            this.cacheDependencyFactory = cacheDependencyFactory;

        public T Fetch(string key, Func<T> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

            return FetchAndCache<T>(key, retrieveData, absoluteExpiry, relativeExpiry, null);

        public T Fetch(string key, Func<T> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

            IEnumerable<ICacheDependency> cacheDependencies)

            return FetchAndCache<T>(key, retrieveData, absoluteExpiry, relativeExpiry, cacheDependencies);

        public IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

            return FetchAndCache<IEnumerable<T>>(key, retrieveData, absoluteExpiry, relativeExpiry, null);

        public IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

            IEnumerable<ICacheDependency> cacheDependencies)

            return FetchAndCache<IEnumerable<T>>(key, retrieveData, absoluteExpiry, relativeExpiry, cacheDependencies);

        public U CreateCacheDependency<U>() where U : ICacheDependency

            return this.cacheDependencyFactory.Create<U>();

        #region Helper Methods

        private U FetchAndCache<U>(string key, Func<U> retrieveData,

            DateTime? absoluteExpiry, TimeSpan? relativeExpiry, IEnumerable<ICacheDependency> cacheDependencies)

            U value;

            if (!TryGetValue<U>(key, out value))

                value = retrieveData();

                if (!absoluteExpiry.HasValue)

                    absoluteExpiry = Cache.NoAbsoluteExpiration;

                if (!relativeExpiry.HasValue)

                    relativeExpiry = Cache.NoSlidingExpiration;

                CacheDependency aspNetCacheDependencies = null;

                if (cacheDependencies != null)

                    if (cacheDependencies.Count() == 1)

                        // We know that the implementations of ICacheDependency will also implement IAspNetCacheDependency

                        // so we can use a cast here and call the CreateAspNetCacheDependency() method

                        aspNetCacheDependencies =

                            ((IAspNetCacheDependency)cacheDependencies.ElementAt(0)).CreateAspNetCacheDependency();

                    else if (cacheDependencies.Count() > 1)

                        AggregateCacheDependency aggregateCacheDependency = new AggregateCacheDependency();

                        foreach (ICacheDependency cacheDependency in cacheDependencies)

                            // We know that the implementations of ICacheDependency will also implement IAspNetCacheDependency

                            // so we can use a cast here and call the CreateAspNetCacheDependency() method

                            aggregateCacheDependency.Add(

                                ((IAspNetCacheDependency)cacheDependency).CreateAspNetCacheDependency());

                        aspNetCacheDependencies = aggregateCacheDependency;

                HttpContext.Current.Cache.Insert(key, value, aspNetCacheDependencies, absoluteExpiry.Value, relativeExpiry.Value);

            return value;

        private bool TryGetValue<U>(string key, out U value)

            object cachedValue = HttpContext.Current.Cache.Get(key);

            if (cachedValue == null)

                value = default(U);

                return false;

            else

try

                    value = (U)cachedValue;

                    return true;

                catch

                    value = default(U);

                    return false;

        #endregion

Wiring up the DI Container

Now the implementations for the Cache Dependency are in place, I wired them up in the existing Windsor CacheInstaller. First I needed to register the implementation of the ISqlCacheDependency interface:

container.Register(

    Component.For<ISqlCacheDependency>()

    .ImplementedBy<AspNetSqlCacheDependency>()

    .LifestyleTransient());

Next I registered the Cache Dependency Factory. Notice that I have not implemented the ICacheDependencyFactory interface. Castle Windsor will do this for me by using the Type Factory Facility. I do need to bring the Castle.Facilities.TypedFacility namespace into scope:

using Castle.Facilities.TypedFactory;

Then I registered the factory:

container.AddFacility<TypedFactoryFacility>();

container.Register(

    Component.For<ICacheDependencyFactory>()

    .AsFactory());

The full code for the CacheInstaller class is:

using Castle.MicroKernel.Registration;

using Castle.MicroKernel.SubSystems.Configuration;

using Castle.Windsor;

using Castle.Facilities.TypedFactory;

using CacheDiSample.Domain.CacheInterfaces;

using CacheDiSample.CacheProviders;

namespace CacheDiSample.WindsorInstallers

    public class CacheInstaller : IWindsorInstaller

        public void Install(IWindsorContainer container, IConfigurationStore store)

            container.Register(

                Component.For(typeof(ICacheProvider<>))

                .ImplementedBy(typeof(CacheProvider<>))

                .LifestyleTransient());

            container.Register(

                Component.For<ISqlCacheDependency>()

                .ImplementedBy<AspNetSqlCacheDependency>()

                .LifestyleTransient());

            container.AddFacility<TypedFactoryFacility>();

            container.Register(

                Component.For<ICacheDependencyFactory>()

                .AsFactory());

Configuring the ASP.NET SQL Cache Dependency

There are a couple of configuration steps required to enable SQL Cache Dependency for the application and database. From the Visual Studio Command Prompt, the following commands should be used to enable the Cache Polling of the relevant database tables:

aspnet_regsql -S <servername> -E -d <databasename> –ed

aspnet_regsql -S <servername> -E -d CacheSample –et –t <tablename>

(The –t option should be repeated for each table that is to be made available for cache dependencies).

Finally the SQL Cache Polling needs to be enabled by adding the following configuration to the <system.web> section of web.config:

<caching>

    <sqlCacheDependency pollTime="10000" enabled="true">

        <databases>

            <add name="BloggingContext" connectionStringName="BloggingContext"/>

        </databases>

    </sqlCacheDependency>

</caching>

(obviously the name and connection string name should be altered as required).

Using a SQL Cache Dependency

Now all the coding is complete. To specify a SQL Cache Dependency, I can modify my BlogRepositoryWithCaching decorator class (see the earlier post) as follows:

public IList<Blog> GetAll()

    var sqlCacheDependency = cacheProvider.CreateCacheDependency<ISqlCacheDependency>()

        .Initialise("BloggingContext", "Blogs");

    ICacheDependency[] cacheDependencies = new ICacheDependency[] { sqlCacheDependency };

    string key = string.Format("CacheDiSample.DataAccess.GetAll");

    return cacheProvider.Fetch(key, () =>

        return parentBlogRepository.GetAll();

},

        null, null, cacheDependencies)

    .ToList();

This will add a dependency of the “Blogs” table in the database. The data will remain in the cache until the contents of this table change, then the cache item will be invalidated, and the next call to the GetAll() repository method will be routed to the parent repository to refresh the data from the database.

Loosely coupled .NET Cache Provider using Dependency Injection

Rhames — Tue, 11 Sep 2012 13:11:10 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2012/09/11/loosely-coupled-.net-cache-provider-using-dependency-injection.aspx

I have recently been reading the excellent book “Dependency Injection in .NET”, written by Mark Seemann, which I strongly recommend.

Reading the ideas around Dependency Injection made me realise that the Cache Provider code I wrote about earlier (see http://geekswithblogs.net/Rhames/archive/2011/01/10/using-the-asp.net-cache-to-cache-data-in-a-model.aspx) could be refactored to use Dependency Injection, which should produce cleaner code.

The goals are to:

Separate the cache provider implementation (using the ASP.NET data cache) from the consumers (loose coupling). This will also mean that the dependency on System.Web for the cache provider does not ripple down into the layers where it is being consumed (such as the domain layer).
Provide a decorator pattern to allow a consumer of the cache provider to be implemented separately from the base consumer (i.e. if we have a base repository, we can decorate this with a caching version). Although I used the term repository, in reality the cache consumer could be just about anything.
Use constructor injection to provide the Dependency Injection, with a suitable DI container (I use Castle Windsor).

The sample code for this post is available on github, https://github.com/RobinHames/CacheProvider.git

ICacheProvider

In the sample code, the key interface is ICacheProvider, which is in the domain layer.

using System;

using System.Collections.Generic;

namespace CacheDiSample.Domain

    public interface ICacheProvider<T>

        T Fetch(string key, Func<T> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry);

        IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry);

This interface contains two methods to retrieve data from the cache, either as a single instance or as an IEnumerable. the second paramerter is of type Func<T>. This is the method used to retrieve data if nothing is found in the cache.

The ASP.NET implementation of the ICacheProvider interface needs to live in a project that has a reference to system.web, typically this will be the root UI project, or it could be a separate project. The key thing is that the domain or data access layers do not need system.web references adding to them.

In my sample MVC application, the CacheProvider is implemented in the UI project, in a folder called “CacheProviders”:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Caching;

using CacheDiSample.Domain;

namespace CacheDiSample.CacheProvider

    public class CacheProvider<T> : ICacheProvider<T>

        public T Fetch(string key, Func<T> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

            return FetchAndCache<T>(key, retrieveData, absoluteExpiry, relativeExpiry);

        public IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

            return FetchAndCache<IEnumerable<T>>(key, retrieveData, absoluteExpiry, relativeExpiry);

        #region Helper Methods

        private U FetchAndCache<U>(string key, Func<U> retrieveData, DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

            U value;

            if (!TryGetValue<U>(key, out value))

                value = retrieveData();

                if (!absoluteExpiry.HasValue)

                    absoluteExpiry = Cache.NoAbsoluteExpiration;

                if (!relativeExpiry.HasValue)

                    relativeExpiry = Cache.NoSlidingExpiration;

                HttpContext.Current.Cache.Insert(key, value, null, absoluteExpiry.Value, relativeExpiry.Value);

            return value;

        private bool TryGetValue<U>(string key, out U value)

            object cachedValue = HttpContext.Current.Cache.Get(key);

            if (cachedValue == null)

                value = default(U);

                return false;

            else

try

                    value = (U)cachedValue;

                    return true;

                catch

                    value = default(U);

                    return false;

        #endregion

The FetchAndCache helper method checks if the specified cache key exists, if it does not, the Func<U> retrieveData method is called, and the results are added to the cache.

Using Castle Windsor to register the cache provider

In the MVC UI project (my application root), Castle Windsor is used to register the CacheProvider implementation, using a Windsor Installer:

using Castle.MicroKernel.Registration;

using Castle.MicroKernel.SubSystems.Configuration;

using Castle.Windsor;

using CacheDiSample.Domain;

using CacheDiSample.CacheProvider;

namespace CacheDiSample.WindsorInstallers

    public class CacheInstaller : IWindsorInstaller

        public void Install(IWindsorContainer container, IConfigurationStore store)

            container.Register(

                Component.For(typeof(ICacheProvider<>))

                .ImplementedBy(typeof(CacheProvider<>))

                .LifestyleTransient());

Note that the cache provider is registered as a open generic type.

Consuming a Repository

I have an existing couple of repository interfaces defined in my domain layer:

IRepository.cs

using System;

using System.Collections.Generic;

using CacheDiSample.Domain.Model;

namespace CacheDiSample.Domain.Repositories

    public interface IRepository<T>

        where T : EntityBase

        T GetById(int id);

        IList<T> GetAll();

IBlogRepository.cs

using System;

using CacheDiSample.Domain.Model;

namespace CacheDiSample.Domain.Repositories

    public interface IBlogRepository : IRepository<Blog>

        Blog GetByName(string name);

These two repositories are implemented in the DataAccess layer, using Entity Framework to retrieve data (this is not important though). One important point is that in the BaseRepository implementation of IRepository, the methods are virtual. This will allow the decorator to override them.

The BlogRepository is registered in a RepositoriesInstaller, again in the MVC UI project.

using Castle.MicroKernel.Registration;

using Castle.MicroKernel.SubSystems.Configuration;

using Castle.Windsor;

using CacheDiSample.Domain.CacheDecorators;

using CacheDiSample.Domain.Repositories;

using CacheDiSample.DataAccess;

namespace CacheDiSample.WindsorInstallers

    public class RepositoriesInstaller : IWindsorInstaller

        public void Install(IWindsorContainer container, IConfigurationStore store)

            container.Register(Component.For<IBlogRepository>()

                .ImplementedBy<BlogRepository>()

                .LifestyleTransient()

                .DependsOn(new

                                    nameOrConnectionString = "BloggingContext"

                                }));

Now I can inject a dependency on the IBlogRepository into a consumer, such as a controller in my sample code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using CacheDiSample.Domain.Repositories;

using CacheDiSample.Domain.Model;

namespace CacheDiSample.Controllers

    public class HomeController : Controller

        private readonly IBlogRepository blogRepository;

        public HomeController(IBlogRepository blogRepository)

            if (blogRepository == null)

                throw new ArgumentNullException("blogRepository");

            this.blogRepository = blogRepository;

        public ActionResult Index()

            ViewBag.Message = "Welcome to ASP.NET MVC!";

            var blogs = blogRepository.GetAll();

            return View(new Models.HomeModel { Blogs = blogs });

        public ActionResult About()

            return View();

Consuming the Cache Provider via a Decorator

I used a Decorator pattern to consume the cache provider, this means my repositories follow the open/closed principle, as they do not require any modifications to implement the caching. It also means that my controllers do not have any knowledge of the caching taking place, as the DI container will simply inject the decorator instead of the root implementation of the repository.

The first step is to implement a BlogRepository decorator, with the caching logic in it. Note that this can reside in the domain layer, as it does not require any knowledge of the data access methods.

BlogRepositoryWithCaching.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using CacheDiSample.Domain.Model;

using CacheDiSample.Domain;

using CacheDiSample.Domain.Repositories;

namespace CacheDiSample.Domain.CacheDecorators

    public class BlogRepositoryWithCaching : IBlogRepository

        // The generic cache provider, injected by DI

        private ICacheProvider<Blog> cacheProvider;

        // The decorated blog repository, injected by DI

        private IBlogRepository parentBlogRepository;

        public BlogRepositoryWithCaching(IBlogRepository parentBlogRepository, ICacheProvider<Blog> cacheProvider)

            if (parentBlogRepository == null)

                throw new ArgumentNullException("parentBlogRepository");

            this.parentBlogRepository = parentBlogRepository;

            if (cacheProvider == null)

                throw new ArgumentNullException("cacheProvider");

            this.cacheProvider = cacheProvider;

        public Blog GetByName(string name)

            string key = string.Format("CacheDiSample.DataAccess.GetByName.{0}", name);

            // hard code 5 minute expiry!

            TimeSpan relativeCacheExpiry = new TimeSpan(0, 5, 0);

            return cacheProvider.Fetch(key, () =>

                return parentBlogRepository.GetByName(name);

},

                null, relativeCacheExpiry);

        public Blog GetById(int id)

            string key = string.Format("CacheDiSample.DataAccess.GetById.{0}", id);

            // hard code 5 minute expiry!

            TimeSpan relativeCacheExpiry = new TimeSpan(0, 5, 0);

            return cacheProvider.Fetch(key, () =>

                return parentBlogRepository.GetById(id);

},

                null, relativeCacheExpiry);

        public IList<Blog> GetAll()

            string key = string.Format("CacheDiSample.DataAccess.GetAll");

            // hard code 5 minute expiry!

            TimeSpan relativeCacheExpiry = new TimeSpan(0, 5, 0);

            return cacheProvider.Fetch(key, () =>

                return parentBlogRepository.GetAll();

},

                null, relativeCacheExpiry)

            .ToList();

The key things in this caching repository are:

I inject into the repository the ICacheProvider<Blog> implementation, via the constructor. This will make the cache provider functionality available to the repository.
I inject the parent IBlogRepository implementation (which has the actual data access code), via the constructor. This will allow the methods implemented in the parent to be called if nothing is found in the cache.
I override each of the methods implemented in the repository, including those implemented in the generic BaseRepository. Each override of these methods follows the same pattern. It makes a call to the CacheProvider.Fetch method, and passes in the parentBlogRepository implementation of the method as the retrieval method, to be used if nothing is present in the cache.

Configuring the Caching Repository in the DI Container

The final piece of the jigsaw is to tell Castle Windsor to use the BlogRepositoryWithCaching implementation of IBlogRepository, but to inject the actual Data Access implementation into this decorator. This is easily achieved by modifying the RepositoriesInstaller to use Windsor’s implicit decorator wiring:

using Castle.MicroKernel.Registration;

using Castle.MicroKernel.SubSystems.Configuration;

using Castle.Windsor;

using CacheDiSample.Domain.CacheDecorators;

using CacheDiSample.Domain.Repositories;

using CacheDiSample.DataAccess;

namespace CacheDiSample.WindsorInstallers

    public class RepositoriesInstaller : IWindsorInstaller

        public void Install(IWindsorContainer container, IConfigurationStore store)

            // Use Castle Windsor implicit wiring for the blog repository decorator

            // Register the outermost decorator first

            container.Register(Component.For<IBlogRepository>()

                .ImplementedBy<BlogRepositoryWithCaching>()

                .LifestyleTransient());

            // Next register the IBlogRepository implementation to inject into the outer decorator

            container.Register(Component.For<IBlogRepository>()

                .ImplementedBy<BlogRepository>()

                .LifestyleTransient()

                .DependsOn(new

                                    nameOrConnectionString = "BloggingContext"

                                }));

This is all that is needed. Now if the consumer of the repository makes a call to the repositories method, it will be routed via the caching mechanism. You can test this by stepping through the code, and seeing that the DataAccess.BlogRepository code is only called if there is no data in the cache, or this has expired.

The next step is to add the SQL Cache Dependency support into this pattern, this will be a future post.

Using JQuery Ajax method to pass Json to a MVC3 Action and return a partial view

Rhames — Mon, 06 Aug 2012 21:23:52 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2012/08/06/using-jquery-ajax-method-to-pass-json-to-a-mvc3.aspx

ASP.NET MVC3 provides out of the box support for binding a Json data object into a Model on postback. This maybe used with a JQuery Ajax function call to post selected data back to an action. It is also possible to return a partial view from the same action, and refresh this from the ajax success function. For example:

In my MVC3 Razor project, I create two model classes:

public class NameResponseModel
{
    public string Name { get; set; }
    public DateTime CurrentDateTime { get; set; }
    public IList<int> Numbers { get; set; }
}

public class UpdateNameModel
{
    public string Name { get; set; }
    public IEnumerable<int> Numbers { get; set; }
}

The UpdateNameModel will be used to retrieve data submitted by the Ajax method call. It is this model that the Json object will bind to. The NameResponseModel class is used to pass information back to the UI via a template view.

In the Views/Shared/DisplayTemplates folder, I created a template that is strongly typed to the NameResponseModel class, called NameResponseModel.cshtml:

@model MvcJQuery.Models.NameResponseModel

<div>
<div class="display-label">
    Name: @Html.DisplayFor(m => m.Name)
</div>
<div class="display-label">
    Current Date Time: @Html.DisplayFor(m => m.CurrentDateTime)
</div>
@{
    var numbersCount = Model.Numbers.Count;

    for(int numberIndex = 0; numberIndex < numbersCount; numberIndex++)
    {
        <div class="display-label">
            Number: @numberIndex = @Html.DisplayFor(m => m.Numbers[numberIndex])
        </div>
    }
}
</div>

This template simply displays the contents of the associated NameResponseModel object.

Next, in the Home Controller, I added the following action:

public ActionResult UpdateName(UpdateNameModel updateNameModel)
{
    return PartialView("DisplayTemplates/NameResponseModel", new NameResponseModel
    {
        Name = updateNameModel.Name,
        CurrentDateTime = DateTime.Now,
        Numbers = updateNameModel.Numbers.ToList()
    });
}

This action takes a UpdateNameModel object as a parameter and simply copies this into a new instance of the NameResponseModel. It then returns the display template as a partial view.

Finally, my Home/Index.cshtml view looks like this:

@{
    ViewBag.Title = "Home Page";
}

<script type="text/javascript">
    $(function () {
        $('#UpdateName').click(function () {
            var inputName = $('#Name').val();
            var dataResponse = { Name: inputName, Numbers: [1, 45, 67, 89] };
            $.ajax({
                type: 'POST',
                contentType: 'application/json; charset=utf-8',
                data: JSON.stringify(dataResponse),
                dataType: 'html',
                url: '@Url.Action("UpdateName")',
                success: function(result) {
                    $('#Response').html(result);
                }
            });
        });
    });
</script>

<h2>@ViewBag.Message</h2>
<p>
    To learn more about ASP.NET MVC visit <a href="http://asp.net/mvc" title="ASP.NET MVC Website">http://asp.net/mvc</a>.
</p>

<div id="Response"></div>

<input type="text" id="Name" />
<button type="button" id="UpdateName">Update</button>

I’ve added a div with the Id=”Response”. This will be used to display the partial view following an Ajax update. When the “Update” button is clicked, the click event creates a Json object with the contents of the “Name” text input and an array of integers. Note that the names of the Json items must match exactly the names within the data model that the Json will be bound to. MVC3 will quite happily handle complex data binding, so the array of integers will be bound successfully to the Numbers IEnumerable<int> parameter in the UpdateNameModel class.

Other points to note are the contentType parameter of the Ajax call is set to 'application/json; charset=utf-8'. This indicates that the parameter passed by the Ajax call will be a Json object. The Json object itself needs to be converted to a string, JSON.stringify will perform this function for us. The dataType parameter in the Ajax call is set to ‘html’. This indicates that the data returned by the server is expected to be html (the rendered partial view in this case). The success callback then simply loads this returned html into the “Response” div.

Hope this helps somebody! It took me a while, and a lot of googling, to figure it out!

Data Caching Architecture: Using the ASP.NET Cache to cache data in a Model or Business Object layer, without a dependency on System.Web in the layer - Part Two. Adding Cache Dependency Support

Rhames — Wed, 26 Jan 2011 10:56:02 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2011/01/26/data-caching-architecture-using-the-asp.net-cache-to-cache-data.aspx

Adding Cache Dependency Support

This second part of my article on adding cache support to applications will extend the sample application developed in part one to add support for cache dependencies such as the SqlCacheDependency.

Part One of this article is at:

http://geekswithblogs.net/Rhames/archive/2011/01/10/using-the-asp.net-cache-to-cache-data-in-a-model.aspx

ICacheDependency Interface

We will need to pass cache dependencies to the Cache Provider implementation, so the first step is to create an empty interface called ICacheDependency in the CacheSample.Caching project:

namespace CacheSample.Caching

{

public interface ICacheDependency

{

}

We can then add overloaded definitions for the Fetch methods in the ICacheProvider<T> interface that will take instances of ICacheDependency. As there could be more than one dependency, the overloads will have a parameter of type IEnumerable<ICacheDependency>, as shown below:

public interface ICacheProvider<T> : ICacheProvider

{

T Fetch(string key, Func<T> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry);

T Fetch(string key, Func<T> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

IEnumerable<ICacheDependency> cacheDependencies);

IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry);

IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

IEnumerable<ICacheDependency> cacheDependencies);

}

Each type of cache dependency will have its own set of parameters, and therefore will require its own interface, which will then be implemented by the cache provider. For this sample I will implement a SQL cache dependency and a Key cache dependency (which gives a dependency on other cache items, identified by a set of cache keys). For these two types of cache dependency, there will be two interfaces, both of which will inherit from ICacheDependency:

namespace CacheSample.Caching

{

public interface ISqlCacheDependency : ICacheDependency

{

string DatabaseConnectionName { get; set; }

string TableName { get; set; }

System.Data.SqlClient.SqlCommand SqlCommand { get; set; }

}

namespace CacheSample.Caching

{

public interface IKeyCacheDependency : ICacheDependency

{

string[] Keys { get; set; }

}

Dependency Injection of Cache Dependency Implementations

We will need to configure the implementations of each cache dependency interface in the application configuration file so that dependency injection may be used to create instances of the dependencies at runtime. For this we will need an additional custom configuration element with properties for the interface being implemented and the type of the class that implements the interface. As there may be several cache dependency implementations there will also be an associated configuration collection.

CacheDependency Custom Configurations

In the CacheSample.Caching project, create a class called CacheDependencyConfigurationElement, in the Configuration folder. The C# code for this class is shown below:

using System;

using System.Configuration;

namespace CacheSample.Caching.Configuration

{

internal class CacheDependencyConfigurationElement : ConfigurationElement

{

[ConfigurationProperty("interface", IsRequired = true)]

public string CacheDependencyInterface

{

get

{

return (string)this["interface"];

}

[ConfigurationProperty("type", IsRequired = true)]

public string CacheDependencyType

{

get

{

return (string)this["type"];

}

Create a second class called CacheDependencyConfigurationElementCollection, with the following C# code:

using System;

using System.Configuration;

namespace CacheSample.Caching.Configuration

{

internal class CacheDependencyConfigurationElementCollection :

ConfigurationElementCollection

{

protected override string ElementName

{

get

{

return "cacheDependency";

}

protected override ConfigurationElement CreateNewElement()

{

return new CacheDependencyConfigurationElement();

}

public override ConfigurationElementCollectionType CollectionType

{

get

{

return ConfigurationElementCollectionType.BasicMap;

}

protected override object GetElementKey(ConfigurationElement element)

{

return ((CacheDependencyConfigurationElement)element).

CacheDependencyInterface;

}

public CacheDependencyConfigurationElement this[int index]

{

get

{

return (CacheDependencyConfigurationElement)BaseGet(index);

}

public new CacheDependencyConfigurationElement this[string Interface]

{

get

{

return (CacheDependencyConfigurationElement)BaseGet(Interface);

}

Finally to complete the custom configuration code, add the following property to the CacheProviderConfigurationSection class:

[ConfigurationProperty("cacheDependencies")]

public CacheDependencyConfigurationElementCollection CacheDependencies

{

get

{

return (CacheDependencyConfigurationElementCollection)

base["cacheDependencies"];

}

Cache Dependency Factory

Instances of the cache dependency implementations will be retrieved using a factory class, which will function in a similar manner to the Cache Provider Factory. However, there is one key difference between the Cache Dependency Factory and the Cache Provider Factory. Whereas instances of Cache Providers can be reused, we will have to create a new Cache Dependency instance each time the dependency is required, as it will be passed directly into the cache system. We do not want to have to make an expensive call to Activator.CreateInstance() every time, so we need an efficient method of creating our cache dependency instances at runtime. My solution is to provide a CacheDependencyCreator generic class, which has a Create() method that will create a new instance of the relevant ICacheDependency generic type by called new T(). The first time a particular cache dependency is requested, the factory class will create the relevant creator class, and store this in a static dictionary for use in future requests.

Cache Dependency Creator class

Create a class called CacheDependencyCreator in the CacheSample.Caching project, under the CacheDependency folder. The C# code for the CacheDependencyCreator is shown below:

using System;

namespace CacheSample.Caching

{

///<summary>

/// Provide an efficient mechanism for creating new instances

/// of ICacheDependency implementations at runtime

///</summary>

internal class CacheDependencyCreator

{

///<summary>

/// Create a new instance of the relevant ICacheDependency implementation

///</summary>

///<returns></returns>

public virtual ICacheDependency Create()

{

throw new NotImplementedException();

}

///<summary>

/// Provide an efficient mechanism for creating new instances

/// of ICacheDependency implementations at runtime

///</summary>

///<typeparam name="T">The ICacheDependency type</typeparam>

internal class CacheDependencyCreator<T> : CacheDependencyCreator

where T : ICacheDependency, new()

{

///<summary>

/// Create a new instance of the relevant ICacheDependency implementation

///</summary>

///<returns></returns>

public override ICacheDependency Create()

{

return new T();

}

Note that there is a non-generic parent class for CacheDependencyCreator that will be used as a type for the static dictionary in the Cache Dependency Facotry.

Cache Dependency Factory class

When a request for a cache dependency instance is made, the factory will first check for the relevant CacheDependencyCreator<> object in the dictionary. If it is present, it will call the Create() method on the relevant creator object. If the dictionary does not contain an instance of the relevant creator, one will be created using Activator.CreateInstance().

The C# code for the Cache Dependency Factory class is shown below:

using System;

using System.Collections.Generic;

using System.Linq;

using CacheSample.Caching.Configuration;

namespace CacheSample.Caching

{

public static class CacheDependencyFactory

{

private static Dictionary<Type, CacheDependencyCreator>

cacheDependencyCreators = new Dictionary<Type,

CacheDependencyCreator>();

private static object syncRoot = new object();

///<summary>

/// Create a new cache dependency instance for the cache dependency

/// interface specified by <typeparamref name="T"/>

///</summary>

///<typeparam name="T">The ICacheDependency interface being

/// requested</typeparam>

///<returns>An instance of the implementation of the cache dependency

/// interface</returns>

public static T CreateCacheDependency<T>()

where T : ICacheDependency

{

ICacheDependency cacheDependency = null;

Type typeOfT = typeof(T);

lock (syncRoot)

{

if (cacheDependencyCreators.ContainsKey(typeOfT))

// The cacheDependencyCreators dictionary already

// has a creator for this type of cache dependency,

// so create the instance by calling its Create() method

cacheDependency = ((CacheDependencyCreator)

cacheDependencyCreators[typeOfT]).Create();

else

{

// Get the cache dependeny configuration for the cache dependency

// interface type

var cacheDependencyConfiguration =

CacheProviderConfigurationSection.Current.

CacheDependencies[typeOfT.Name];

if (cacheDependencyConfiguration != null)

{

// Get the type for the implementation of the specified cache

// dependency interface

string strCacheDependencyImplementationType =

cacheDependencyConfiguration.CacheDependencyType;

Type typeOfCacheDependencyImplementation =

Type.GetType(strCacheDependencyImplementationType);

// Get the Type reference for the CacheDependencyCreator

// generic class

Type typeOfCacheDependencyCreator =

typeof(CacheDependencyCreator<>);

if (typeOfCacheDependencyImplementation != null

&& typeOfCacheDependencyCreator != null)

{

// Get the type reference for

// CacheDependencyCreator<cache interface implementation>

Type typeOfCacheDependencyCreatorForImplementationOfT =

typeOfCacheDependencyCreator.MakeGenericType(

new Type[]

{ typeOfCacheDependencyImplementation });

if (typeOfCacheDependencyCreatorForImplementationOfT

!= null)

{

// Create the CacheDependencyCreator<

// cache interface implementation> instance

var cacheDependencyCreator =

(CacheDependencyCreator)Activator.CreateInstance(

typeOfCacheDependencyCreatorForImplementationOfT);

// Add the CacheDependencyCreator<cache interface

// implementation> instance to the dictionary

cacheDependencyCreators.Add(

typeOfT, cacheDependencyCreator);

// Create the Cache Dependency instance

cacheDependency = cacheDependencyCreator.Create();

}

return (T)cacheDependency;

}

Implementing ICacheDependency interfaces

My original intention when implementing the ICacheDependency interfaces was to create classes within the CacheSample.CacheProvider that extend the System.Web.Caching.CacheDependency and System.Web.Caching.SqlCacheDependency classes. However the System.Web.Caching.SqlCacheDependency class is sealed, so my implementation cannot inherit from this.

My alternative was to create an interface in the CacheSample.CacheProvider project called IAspNetCacheDependency. This interface contains a single method that returns an instance of System.Web.Caching.CacheDependency. My implementations of the ICacheDependency interfaces will also implement this Asp.Net specific interface, so that the Cache Provider implementation can use this method to create the relevant System.Web.Caching.CacheDependency object.

The C# code for the IAspNetCacheDependency interface is shown below:

using System.Web.Caching;

namespace CacheSample.CacheProvider

{

public interface IAspNetCacheDependency

{

CacheDependency CreateAspNetCacheDependency();

}

AspNetSqlCacheDependency implementation of ISqlCacheDependency

Add a class called AspNetSqlCacheDependency to the CacheSample.CacheProvider project to implement both the ISqlCacheDependency and IAspNetCacheDependency interfaces. For ISqlCacheDependency it is simply a matter of implementing the relevant properties. For the IAspNetCacheDependency interface, the CreateAspNetCacheDependency() method is implemented to return an instance of System.Web.Caching.SqlCacheDependency with the appropriate properties set. The C# code for AspNetCacheDependency is shown below:

using System.Web.Caching;

using CacheSample.Caching;

namespace CacheSample.CacheProvider

{

public class AspNetSqlCacheDependency :

ISqlCacheDependency, IAspNetCacheDependency

{

#region ISqlCacheDependency Members

public string DatabaseConnectionName

{

get;

set;

}

public string TableName

{

get;

set;

}

public System.Data.SqlClient.SqlCommand SqlCommand

{

get;

set;

}

#endregion

#region IAspNetCacheDependency Members

public System.Web.Caching.CacheDependency CreateAspNetCacheDependency()

{

if (SqlCommand != null)

return new SqlCacheDependency(SqlCommand);

else

return new SqlCacheDependency(DatabaseConnectionName, TableName);

}

#endregion

}

AspNetKeyCacheDependency implementation of IKeyCacheDependency

A second class called AspNetKeyCacheDependency is created in the CacheSample.CacheProvider project to implement the IKeyCacheDependency interface, using the C# code shown below:

using System.Web.Caching;

using CacheSample.Caching;

namespace CacheSample.CacheProvider

{

public class AspNetKeyCacheDependency :

IKeyCacheDependency, IAspNetCacheDependency

{

#region IKeyCacheDependency Members

public string[] Keys

{

get;

set;

}

#endregion

#region IAspNetCacheDependency Members

public CacheDependency CreateAspNetCacheDependency()

{

return new CacheDependency(null, Keys);

}

#endregion

}

Implementing the ICacheProvider.Fetch() method overloads

The helper method FetchAndCache<U>() in the AspNetCacheProvider class is extended to take an additional IEnumerable<ICacheDependency> parameter. The Fetch method implementations then call this helper method, as shown below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Caching;

using CacheSample.Caching;

namespace CacheSample.CacheProvider

{

public class AspNetCacheProvider<T> : ICacheProvider<T>

{

#region ICacheProvider<T> Members

public T Fetch(string key, Func<T> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

{

return FetchAndCache<T>(key, retrieveData, absoluteExpiry,

relativeExpiry, null);

}

public T Fetch(string key, Func<T> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

IEnumerable<ICacheDependency> cacheDependencies)

{

return FetchAndCache<T>(key, retrieveData,

absoluteExpiry, relativeExpiry, cacheDependencies);

}

public IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

{

return FetchAndCache<IEnumerable<T>>(key, retrieveData,

absoluteExpiry, relativeExpiry, null);

}

public IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

IEnumerable<ICacheDependency> cacheDependencies)

{

return FetchAndCache<IEnumerable<T>>(key, retrieveData,

absoluteExpiry, relativeExpiry, cacheDependencies);

}

#endregion

#region Helper Methods

private U FetchAndCache<U>(string key, Func<U> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry,

IEnumerable<ICacheDependency> cacheDependencies)

{

U value;

if (!TryGetValue<U>(key, out value))

{

value = retrieveData();

if (!absoluteExpiry.HasValue)

absoluteExpiry = Cache.NoAbsoluteExpiration;

if (!relativeExpiry.HasValue)

relativeExpiry = Cache.NoSlidingExpiration;

CacheDependency aspNetCacheDependencies = null;

if (cacheDependencies != null)

{

if (cacheDependencies.Count() == 1)

// We know that the implementations of ICacheDependency

// will also implement IAspNetCacheDependency

// so we can use a cast here and call the

// CreateAspNetCacheDependency() method

aspNetCacheDependencies =

((IAspNetCacheDependency)cacheDependencies

.ElementAt(0)).CreateAspNetCacheDependency();

else if(cacheDependencies.Count() > 1)

{

AggregateCacheDependency aggregateCacheDependency =

new AggregateCacheDependency();

foreach(ICacheDependency cacheDependency in cacheDependencies)

{

// We know that the implementations of ICacheDependency

// will also implement IAspNetCacheDependency

// so we can use a cast here and call the

// CreateAspNetCacheDependency() method

aggregateCacheDependency

.Add(((IAspNetCacheDependency)cacheDependency)

.CreateAspNetCacheDependency());

}

aspNetCacheDependencies = aggregateCacheDependency;

}

HttpContext.Current.Cache.Insert(key, value,

aspNetCacheDependencies,

absoluteExpiry.Value, relativeExpiry.Value);

}

return value;

}

private bool TryGetValue<U>(string key, out U value)

{

object cachedValue = HttpContext.Current.Cache.Get(key);

if (cachedValue == null)

{

value = default(U);

return false;

}

else

{

try

{

value = (U)cachedValue;

return true;

}

catch

{

value = default(U);

return false;

}

#endregion

}

If any cache dependencies are passed to the FetchAndCache<U> method, they are cast to the IAspNetCacheDependency interface, then the CreateAspNetCacheDependency() method is called to create an instance of the relevant System.Web.Caching.CacheDependency class to include in the HttpContext.Current.Cache.Insert() method call.

Specifying the ICacheDependency implementations in Web.Config

We need to add a <cacheDependencies> collection to the <cacheProvider> element in the application configuration file, and then add individual <cacheDependency> elements to this collection for each of the ICacheDependency implementations, as show below:

<cacheProvider

type="CacheSample.CacheProvider.AspNetCacheProvider`1,

CacheSample.CacheProvider, Version=1.0.0.0, Culture=neutral,

PublicKeyToken=null">

<cacheDependencyinterface="IKeyCacheDependency"

type="CacheSample.CacheProvider.AspNetKeyCacheDependency,

CacheSample.CacheProvider, Version=1.0.0.0,

Culture=neutral, PublicKeyToken=null" />

<cacheDependencyinterface="ISqlCacheDependency"

type="CacheSample.CacheProvider.AspNetSqlCacheDependency,

CacheSample.CacheProvider, Version=1.0.0.0,

Culture=neutral, PublicKeyToken=null" />

</cacheDependencies>

</cacheProvider>

This will allow the CacheDependencyFactory class to use dependency injection to get the implementations of the relevant interfaces at runtime.

Using the ISqlCacheDependency in the Sale.GetSales() method

We can now modify the Sale.GetSales() method to include a cache dependency on the Sale table in the database.

First we create a cache dependency instance of the ISqlCacheDependency interface, using the factory, and set its properties, as shown below:

var sqlCacheDependency = CacheSample.Caching.CacheDependencyFactory

.CreateCacheDependency<CacheSample.Caching.ISqlCacheDependency>();

sqlCacheDependency.DatabaseConnectionName = "CacheSample";

sqlCacheDependency.TableName = "Sale";

We can then pass this cache dependency instance in the call to the CacheProvider Fetch method. The full code for GetSales is shown below:

public static IEnumerable<Sale> GetSales(int? highestDayCount)

{

string cacheKey = string.Format(

"CacheSample.BusinessObjects.GetSalesWithCache_SqlCommandDependency({0})",

highestDayCount);

string connectionStr = System.Configuration.ConfigurationManager

.ConnectionStrings["CacheSample"].ConnectionString;

var sqlCacheDependency = CacheSample.Caching.CacheDependencyFactory

.CreateCacheDependency<CacheSample.Caching.ISqlCacheDependency>();

sqlCacheDependency.DatabaseConnectionName = "CacheSample";

sqlCacheDependency.TableName = "Sale";

CacheSample.Caching.ICacheDependency[] cacheDependencies =

new Caching.ICacheDependency[] { sqlCacheDependency };

return CacheSample.Caching.CacheProviderFactory

.GetCacheProvider<Sale>().Fetch(cacheKey,

delegate()

{

List<Sale> sales = new List<Sale>();

SqlParameter highestDayCountParameter =

new SqlParameter("@HighestDayCount", SqlDbType.SmallInt);

if (highestDayCount.HasValue)

highestDayCountParameter.Value = highestDayCount;

else

highestDayCountParameter.Value = DBNull.Value;

using (SqlConnection sqlConn = new SqlConnection(connectionStr))

using (SqlCommand sqlCmd = sqlConn.CreateCommand())

{

sqlCmd.CommandText = "spGetRunningTotals";

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(highestDayCountParameter);

sqlConn.Open();

using (SqlDataReader dr = sqlCmd.ExecuteReader())

{

while (dr.Read())

{

Sale newSale = new Sale();

newSale.DayCount = dr.GetInt16(0);

newSale.Sales = dr.GetDecimal(1);

newSale.RunningTotal = dr.GetDecimal(2);

sales.Add(newSale);

}

return sales;

null,

cacheDependencies

);

}

As we only provided a Fetch method in ICacheProvider that takes IEnumerable<ICacheDependency>, we create an array of ICacheDependency and add our SqlCacheDependency object to this. However, it would be simple to provide a Fetch overload that only takes a single ICacheDependency instead.

Configuring the Sql Server and ASP.NET application for SQL Cache Dependency

As the code will use the ASP.NET Sql cache dependency polling method (as supported by SQL Server 2000 onwards) we need to carry out some additional configuration.

Open the Visual Studio command prompt and run the following command to enable caching support in the CacheSample database:

aspnet_regsql -S <servername> -E -d CacheSample –ed

Next enable the Sale table for caching support by running the following command:

aspnet_regsql -S <servername> -E -d CacheSample –et –t Sale

We also have to add the following to the web.config file, inside the <system.web> element:

<sqlCacheDependencypollTime="10000"enabled="true">

<addname="CacheSample"connectionStringName="CacheSample"/>

</databases>

</sqlCacheDependency>

</caching>

Testing the Cache Dependency

Using the web page developed in part one, the first time the GetSales() method is called, I get the following result:

Count of Sales: 4999, Last DayCount: 4999, Total Sales: 187138020.0000. Query took 979 ms

Subsequent calls to GetSales() give the following results. Note that the cache is now beings used, so the time recorded by the StopWatch is 0ms:

Count of Sales: 4999, Last DayCount: 4999, Total Sales: 187138020.0000. Query took 0 ms

Next add a new row to the Sale table:

INSERT INTO [CacheSample].[dbo].[Sale]

([DayCount]

,[Sales])

VALUES

(5000, 100)

This should invalidate the cache, making the next call to GetSales() get the data from the database again:

Count of Sales: 5000, Last DayCount: 5000, Total Sales: 187138120.0000. Query took 499 ms

Data Caching Architecture: Using the ASP.NET Cache to cache data in a Model or Business Object layer, without a dependency on System.Web in the layer - Part One.

Rhames — Mon, 10 Jan 2011 23:35:06 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2011/01/10/using-the-asp.net-cache-to-cache-data-in-a-model.aspx

ASP.NET applications can make use of the System.Web.Caching.Cache object to cache data and prevent repeated expensive calls to a database or other store. However, ideally an application should make use of caching at the point where data is retrieved from the database, which typically is inside a Business Objects or Model layer. One of the key features of using a UI pattern such as Model-View-Presenter (MVP) or Model-View-Controller (MVC) is that the Model and Presenter (or Controller) layers are developed without any knowledge of the UI layer. Introducing a dependency on System.Web into the Model layer would break this independence of the Model from the View.

This article gives a solution to this problem, using dependency injection to inject the caching implementation into the Model layer at runtime. This allows caching to be used within the Model layer, without any knowledge of the actual caching mechanism that will be used.

Create a sample application to use the caching solution

Create a test SQL Server database

This solution uses a SQL Server database with the same Sales data used in my previous post on calculating running totals. The advantage of using this data is that it gives nice slow queries that will exaggerate the effect of using caching!

To create the data, first create a new SQL database called CacheSample. Next run the following script to create the Sale table and populate it:

USE CacheSample

CREATE TABLE Sale(DayCount smallint, Sales money)

CREATE CLUSTERED INDEX ndx_DayCount ON Sale(DayCount)

INSERT Sale VALUES (1,120)

INSERT Sale VALUES (2,60)

INSERT Sale VALUES (3,125)

INSERT Sale VALUES (4,40)

DECLARE @DayCount smallint, @Sales money

SET @DayCount = 5

SET @Sales = 10

WHILE @DayCount < 5000

BEGIN

INSERT Sale VALUES (@DayCount,@Sales)

SET @DayCount = @DayCount + 1

SET @Sales = @Sales + 15

END

Next create a stored procedure to calculate the running total, and return a specified number of rows from the Sale table, using the following script:

USE [CacheSample]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Robin

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[spGetRunningTotals]

-- Add the parameters for the stored procedure here

@HighestDayCount smallint = null

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

IF @HighestDayCount IS NULL

SELECT @HighestDayCount = MAX(DayCount) FROM dbo.Sale

DECLARE @SaleTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @DayCount smallint,

@Sales money,

@RunningTotal money

SET @RunningTotal = 0

SET @DayCount = 0

DECLARE rt_cursor CURSOR

FOR

SELECT DayCount, Sales

FROM Sale

ORDER BY DayCount

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @DayCount,@Sales

WHILE @@FETCH_STATUS = 0 AND @DayCount <= @HighestDayCount

BEGIN

SET @RunningTotal = @RunningTotal + @Sales

INSERT @SaleTbl VALUES (@DayCount,@Sales,@RunningTotal)

FETCH NEXT FROM rt_cursor INTO @DayCount,@Sales

END

CLOSE rt_cursor

DEALLOCATE rt_cursor

SELECT DayCount, Sales, RunningTotal

FROM @SaleTbl

END

Create the Sample ASP.NET application

In Visual Studio create a new solution and add a class library project called CacheSample.BusinessObjects and an ASP.NET web application called CacheSample.UI.

The CacheSample.BusinessObjects project will contain a single class to represent a Sale data item, with all the code to retrieve the sales from the database included in it for simplicity (normally I would at least have a separate Repository or other object that is responsible for retrieving data, and probably a data access layer as well, but for this sample I want to keep it simple).

The C# code for the Sale class is shown below:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

namespace CacheSample.BusinessObjects

{

public class Sale

{

public Int16 DayCount { get; set; }

public decimal Sales { get; set; }

public decimal RunningTotal { get; set; }

public static IEnumerable<Sale> GetSales(int? highestDayCount)

{

List<Sale> sales = new List<Sale>();

SqlParameter highestDayCountParameter = new

SqlParameter("@HighestDayCount", SqlDbType.SmallInt);

if (highestDayCount.HasValue)

highestDayCountParameter.Value = highestDayCount;

else

highestDayCountParameter.Value = DBNull.Value;

string connectionStr =

System.Configuration.ConfigurationManager

.ConnectionStrings["CacheSample"].ConnectionString;

using(SqlConnection sqlConn = new SqlConnection(connectionStr))

using (SqlCommand sqlCmd = sqlConn.CreateCommand())

{

sqlCmd.CommandText = "spGetRunningTotals";

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(highestDayCountParameter);

sqlConn.Open();

using (SqlDataReader dr = sqlCmd.ExecuteReader())

{

while (dr.Read())

{

Sale newSale = new Sale();

newSale.DayCount = dr.GetInt16(0);

newSale.Sales = dr.GetDecimal(1);

newSale.RunningTotal = dr.GetDecimal(2);

sales.Add(newSale);

}

return sales;

}

The static GetSale() method makes a call to the spGetRunningTotals stored procedure and then reads each row from the returned SqlDataReader into an instance of the Sale class, it then returns a List of the Sale objects, as IEnnumerable<Sale>. A reference to System.Configuration needs to be added to the CacheSample.BusinessObjects project so that the connection string can be read from the web.config file.

In the CacheSample.UI ASP.NET project, create a single web page called ShowSales.aspx, and make this the default start up page. This page will contain a single button to call the GetSales() method and a label to display the results. The html mark up and the C# code behind are shown below:

ShowSales.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ShowSales.aspx.cs" Inherits="CacheSample.UI.ShowSales" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Cache Sample - Show All Sales</title>

</head>

<body>

<div>

<asp:Button ID="btnTest1" runat="server" onclick="btnTest1_Click"

Text="Get All Sales" />

<asp:Label ID="lblResults" runat="server"></asp:Label>

</div>

</form>

</body>

</html>

ShowSales.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using CacheSample.BusinessObjects;

namespace CacheSample.UI

{

public partial class ShowSales : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnTest1_Click(object sender, EventArgs e)

{

System.Diagnostics.Stopwatch stopWatch =

new System.Diagnostics.Stopwatch();

stopWatch.Start();

var sales = Sale.GetSales(null);

var lastSales = sales.Last();

stopWatch.Stop();

lblResults.Text = string.Format(

"Count of Sales: {0}, Last DayCount: {1}, Total Sales: {2}. Query took {3} ms",

sales.Count(),

lastSales.DayCount,

lastSales.RunningTotal,

stopWatch.ElapsedMilliseconds);

}

Finally we need to add a connection string to the CacheSample SQL Server database, called CacheSample, to the web.config file:

<?xmlversion="1.0"?>

<add name="CacheSample"

connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=CacheSample"

providerName="System.Data.SqlClient" />

</connectionStrings>

<system.web>

</system.web>

</configuration>

Run the application and click the button a few times to see how long each call to the database takes. On my system, each query takes about 450ms.

Next I shall look at a solution to use the ASP.NET caching to cache the data returned by the query, so that subsequent requests to the GetSales() method are much faster.

Adding Data Caching Support

I am going to create my caching support in a separate project called CacheSample.Caching, so the next step is to add a class library to the solution. We shall be using the application configuration to define the implementation of our caching system, so we need a reference to System.Configuration adding to the project.

ICacheProvider<T> Interface

The first step in adding caching to our application is to define an interface, called ICacheProvider, in the CacheSample.Caching project, with methods to retrieve any data from the cache or to retrieve the data from the data source if it is not present in the cache. Dependency Injection will then be used to inject an implementation of this interface at runtime, allowing the users of the interface (i.e. the CacheSample.BusinessObjects project) to be completely unaware of how the caching is actually implemented. As data of any type maybe retrieved from the data source, it makes sense to use generics in the interface, with a generic type parameter defining the data type associated with a particular instance of the cache interface implementation. The C# code for the ICacheProvider interface is shown below:

using System;

using System.Collections.Generic;

namespace CacheSample.Caching

{

public interface ICacheProvider

{

}

public interface ICacheProvider<T> : ICacheProvider

{

T Fetch(string key,

Func<T> retrieveData,

DateTime? absoluteExpiry,

TimeSpan? relativeExpiry);

IEnumerable<T> Fetch(string key,

Func<IEnumerable<T>> retrieveData,

DateTime? absoluteExpiry,

TimeSpan? relativeExpiry);

}

The empty non-generic interface will be used as a type in a Dictionary generic collection later to store instances of the ICacheProvider<T> implementation for reuse, I prefer to use a base interface when doing this, as I think the alternative of using object makes for less clear code.

The ICacheProvider<T> interface defines two overloaded Fetch methods, the difference between these is that one will return a single instance of the type T and the other will return an IEnumerable<T>, providing support for easy caching of collections of data items.

Both methods will take a key parameter, which will uniquely identify the cached data, a delegate of type Func<T> or Func<IEnumerable<T>> which will provide the code to retrieve the data from the store if it is not present in the cache, and absolute or relative expiry policies to define when a cached item should expire. Note that at present there is no support for cache dependencies, but I shall be showing a method of adding this in part two of this article.

CacheProviderFactory Class

We need a mechanism of creating instances of our ICacheProvider<T> interface, using Dependency Injection to get the implementation of the interface. To do this we shall create a CacheProviderFactory static class in the CacheSample.Caching project. This factory will provide a generic static method called GetCacheProvider<T>(), which shall return instances of ICacheProvider<T>. We can then call this factory method with the relevant data type (for example the Sale class in the CacheSample.BusinessObject project) to get a instance of ICacheProvider for that type (e.g. call CacheProviderFactory.GetCacheProvider<Sale>() to get the ICacheProvider<Sale> implementation).

The C# code for the CacheProviderFactory is shown below:

using System;

using System.Collections.Generic;

using CacheSample.Caching.Configuration;

namespace CacheSample.Caching

{

public static class CacheProviderFactory

{

private static Dictionary<Type, ICacheProvider> cacheProviders

= new Dictionary<Type, ICacheProvider>();

private static object syncRoot = new object();

///<summary>

/// Factory method to create or retrieve an implementation of the

/// ICacheProvider interface for type <typeparamref name="T"/>.

///</summary>

///<typeparam name="T">

/// The type that this cache provider instance will work with

///</typeparam>

///<returns>An instance of the implementation of ICacheProvider for type

///<typeparamref name="T"/>, as specified by the application

/// configuration</returns>

public static ICacheProvider<T> GetCacheProvider<T>()

{

ICacheProvider<T> cacheProvider = null;

// Get the Type reference for the type parameter T

Type typeOfT = typeof(T);

// Lock the access to the cacheProviders dictionary

// so multiple threads can work with it

lock (syncRoot)

{

// First check if an instance of the ICacheProvider implementation

// already exists in the cacheProviders dictionary for the type T

if (cacheProviders.ContainsKey(typeOfT))

cacheProvider = (ICacheProvider<T>)cacheProviders[typeOfT];

else

{

// There is not already an instance of the ICacheProvider in

// cacheProviders for the type T

// so we need to create one

// Get the Type reference for the application's implementation of

// ICacheProvider from the configuration

Type cacheProviderType =

Type.GetType(CacheProviderConfigurationSection.Current.

CacheProviderType);

if (cacheProviderType != null)

{

// Now get a Type reference for the Cache Provider with the

// type T generic parameter

Type typeOfCacheProviderTypeForT =

cacheProviderType.MakeGenericType(new Type[] { typeOfT });

if (typeOfCacheProviderTypeForT != null)

{

// Create the instance of the Cache Provider and add it to

// the cacheProviders dictionary for future use

cacheProvider =

(ICacheProvider<T>)Activator.

CreateInstance(typeOfCacheProviderTypeForT);

cacheProviders.Add(typeOfT, cacheProvider);

}

return cacheProvider;

}

As this code uses Activator.CreateInstance() to create instances of the ICacheProvider<T> implementation, which is a slow process, the factory class maintains a Dictionary of the previously created instances so that a cache provider needs to be created only once for each type.

The type of the implementation of ICacheProvider<T> is read from a custom configuration section in the application configuration file, via the CacheProviderConfigurationSection class, which is described below.

CacheProviderConfigurationSection Class

The implementation of ICacheProvider<T> will be specified in a custom configuration section in the application’s configuration. To handle this create a folder in the CacheSample.Caching project called Configuration, and add a class called CacheProviderConfigurationSection to this folder. This class will extend the System.Configuration.ConfigurationSection class, and will contain a single string property called CacheProviderType. The C# code for this class is shown below:

using System;

using System.Configuration;

namespace CacheSample.Caching.Configuration

{

internal class CacheProviderConfigurationSection : ConfigurationSection

{

public static CacheProviderConfigurationSection Current

{

get

{

return (CacheProviderConfigurationSection)

ConfigurationManager.GetSection("cacheProvider");

}

[ConfigurationProperty("type", IsRequired=true)]

public string CacheProviderType

{

get

{

return (string)this["type"];

}

Adding Data Caching to the Sales Class

We now have enough code in place to add caching to the GetSales() method in the CacheSample.BusinessObjects.Sale class, even though we do not yet have an implementation of the ICacheProvider<T> interface.

We need to add a reference to the CacheSample.Caching project to CacheSample.BusinessObjects so that we can use the ICacheProvider<T> interface within the GetSales() method.

Once the reference is added, we can first create a unique string key based on the method name and the parameter value, so that the same cache key is used for repeated calls to the method with the same parameter values. Then we get an instance of the cache provider for the Sales type, using the CacheProviderFactory, and pass the existing code to retrieve the data from the database as the retrievalMethod delegate in a call to the Cache Provider Fetch() method. The C# code for the modified GetSales() method is shown below:

public static IEnumerable<Sale> GetSales(int? highestDayCount)

{

string cacheKey =

string.Format("CacheSample.BusinessObjects.GetSalesWithCache({0})",

highestDayCount);

return CacheSample.Caching.CacheProviderFactory.

GetCacheProvider<Sale>().Fetch(cacheKey,

delegate()

{

List<Sale> sales = new List<Sale>();

SqlParameter highestDayCountParameter = new

SqlParameter("@HighestDayCount", SqlDbType.SmallInt);

if (highestDayCount.HasValue)

highestDayCountParameter.Value = highestDayCount;

else

highestDayCountParameter.Value = DBNull.Value;

string connectionStr =

System.Configuration.ConfigurationManager.

ConnectionStrings["CacheSample"].ConnectionString;

using (SqlConnection sqlConn = new SqlConnection(connectionStr))

using (SqlCommand sqlCmd = sqlConn.CreateCommand())

{

sqlCmd.CommandText = "spGetRunningTotals";

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(highestDayCountParameter);

sqlConn.Open();

using (SqlDataReader dr = sqlCmd.ExecuteReader())

{

while (dr.Read())

{

Sale newSale = new Sale();

newSale.DayCount = dr.GetInt16(0);

newSale.Sales = dr.GetDecimal(1);

newSale.RunningTotal = dr.GetDecimal(2);

sales.Add(newSale);

}

return sales;

null,

new TimeSpan(0, 10, 0));

}

This example passes the code to retrieve the Sales data from the database to the Cache Provider as an anonymous method, however it could also be written as a lambda. The main advantage of using an anonymous function (method or lambda) is that the code inside the anonymous function can access the parameters passed to the GetSales() method. Finally the absolute expiry is set to null, and the relative expiry set to 10 minutes, to indicate that the cache entry should be removed 10 minutes after the last request for the data.

As the ICacheProvider<T> has a Fetch() method that returns IEnumerable<T>, we can simply return the results of the Fetch() method to the caller of the GetSales() method.

This should be all that is needed for the GetSales() method to now retrieve data from a cache after the first time the data has be retrieved from the database.

Implementing a ASP.NET Cache Provider

The final step is to actually implement the ICacheProvider<T> interface, and add the implementation details to the web.config file for the dependency injection.

The cache provider implementation needs to have access to System.Web. Therefore it could be placed in the CacheSample.UI project, or in its own project that has a reference to System.Web. Implementing the Cache Provider in a separate project is my favoured approach.

Create a new project inside the solution called CacheSample.CacheProvider, and add references to System.Web and CacheSample.Caching to this project. Add a class to the project called AspNetCacheProvider. Make the class a generic class by adding the generic parameter <T> and indicate that the class implements ICacheProvider<T>.

The C# code for the AspNetCacheProvider class is shown below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Caching;

using CacheSample.Caching;

namespace CacheSample.CacheProvider

{

public class AspNetCacheProvider<T> : ICacheProvider<T>

{

#region ICacheProvider<T> Members

public T Fetch(string key, Func<T> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

{

return FetchAndCache<T>(key, retrieveData,

absoluteExpiry, relativeExpiry);

}

public IEnumerable<T> Fetch(string key, Func<IEnumerable<T>> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

{

return FetchAndCache<IEnumerable<T>>(key, retrieveData,

absoluteExpiry, relativeExpiry);

}

#endregion

#region Helper Methods

private U FetchAndCache<U>(string key, Func<U> retrieveData,

DateTime? absoluteExpiry, TimeSpan? relativeExpiry)

{

U value;

if (!TryGetValue<U>(key, out value))

{

value = retrieveData();

if (!absoluteExpiry.HasValue)

absoluteExpiry = Cache.NoAbsoluteExpiration;

if (!relativeExpiry.HasValue)

relativeExpiry = Cache.NoSlidingExpiration;

HttpContext.Current.Cache.Insert(key, value,

null, absoluteExpiry.Value, relativeExpiry.Value);

}

return value;

}

private bool TryGetValue<U>(string key, out U value)

{

object cachedValue = HttpContext.Current.Cache.Get(key);

if (cachedValue == null)

{

value = default(U);

return false;

}

else

{

try

{

value = (U)cachedValue;

return true;

}

catch

{

value = default(U);

return false;

}

#endregion

}

The two interface Fetch() methods call a private method called FetchAndCache(). This method first checks for a element in the HttpContext.Current.Cache with the specified cache key, and if so tries to cast this to the specified type (either T or IEnumerable<T>). If the cached element is found, the FetchAndCache() method simply returns it. If it is not found in the cache, the method calls the retrievalMethod delegate to get the data from the data source, and then adds this to the HttpContext.Current.Cache.

The final step is to add the AspNetCacheProvider class to the relevant custom configuration section in the CacheSample.UI.Web.Config file. To do this there needs to be a <configSections> element added as the first element in <configuration>. This will match a custom section called <cacheProvider> with the CacheProviderConfigurationSection. Then we add a <cacheProvider> element, with a type property set to the fully qualified assembly name of the AspNetCacheProvider class, as shown below:

<?xmlversion="1.0"?>

<section name="cacheProvider"

type="CacheSample.Caching.Configuration.CacheProviderConfigurationSection, CacheSample.Caching" />

</configSections>

<add name="CacheSample"

connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=CacheSample"

providerName="System.Data.SqlClient" />

</connectionStrings>

</cacheProvider>

<system.web>

</system.web>

</configuration>

One point to note is that the fully qualified assembly name of the AspNetCacheProvider class includes the notation `1 after the class name, which indicates that it is a generic class with a single generic type parameter.

The CacheSample.UI project needs to have references added to CacheSample.Caching and CacheSample.CacheProvider so that the actual application is aware of the relevant cache provider implementation.

Conclusion

After implementing this solution, you should have a working cache provider mechanism, that will allow the middle and data access layers to implement caching support when retrieving data, without any knowledge of the actually caching implementation. If the UI is not ASP.NET based, if for example it is Winforms or WPF, the implementation of ICacheProvider<T> would be written around whatever technology is available. It could even be a standalone caching system that takes full responsibility for adding and removing items from a global store.

The next part of this article will show how this caching mechanism may be extended to provide support for cache dependencies, such as the System.Web.Caching.SqlCacheDependency.

Another possible extension would be to cache the cache provider implementations instead of storing them in a static Dictionary in the CacheProviderFactory. This would prevent a build up of seldom used cache providers in the application memory, as they could be removed from the cache if not used often enough, although in reality there are probably unlikely to be vast numbers of cache provider implementation instances, as most applications do not have a massive number of business object or model types.

Trim Leading Zeros from a string in SQL Server

Rhames — Fri, 21 Aug 2009 08:16:06 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2009/08/21/trim-leading-zeros-from-a-string-in-sql-server.aspx

This user defined function is based on a function posted at this very useful site: http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx.

I have modified it slightly to handle spaces within the input string; by first replacing any existing spaces with ‘¬’ (this seems to be a suitably obscure character that is never likely to occur in the input string).

CREATE FUNCTION RemoveLeadingZerosFromVarChar

(

-- Add the parameters for the function here

@inputStr varchar(max)

)

RETURNS varchar(max)

BEGIN

-- Declare the return variable here

DECLARE @Result varchar(max)

-- This function works by replacing all '0' characters with a ' ' (space)

-- character, then using LTRIM to strip all the leading spaces

-- Finally all ' ' characters remaining are changed back into '0' characters

-- (this restores any '0' characters indise the input string)

-- To handle actual spaces within the input string, first any spaces are

-- replaced with an obscure character that is never likely to occur ('¬')

-- so that this can be replaced with spaces again at the end.

SELECT @Result = replace(replace(ltrim(replace(

replace(ltrim(@inputStr), ' ', '¬') -- replace existing spaces with '¬'

, '0', ' ') -- replace '0' with ' '

) -- end of LTRIM to remove leading '0's that have been changed to ' 's

, ' ', '0') -- change ' ' back to '0'

, '¬', ' ') -- change '¬' back to ' '

-- Return the result of the function

RETURN @Result

END

Using int.TryParse() within a LINQ where clause

Rhames — Wed, 12 Aug 2009 09:37:22 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2009/08/12/using-int.tryparse-within-a-linq-where-clause.aspx

I pretty new to LINQ, and I’m keen to get more experience using it, so whenever an opportunity arises I like to try writing LINQ queries.

I needed to write a method to extract a comma separated list of numbers from a config file, and return this as List<int>. I was looking at ways to do this using LINQ, but hit a problem. I wanted my LINQ query to filter out any values in the CSV string that could not be parsed as an int, without causing an exception. Using int.TryParse() seemed like a possible solution, but I had problems because the TryParse() method has an out parameter to store the parsed result. I ended up with the code below, which seems to work, but looks very messy, because I think it is parsing the string twice.

Anybody have any suggestions of how to improve this code?

public static List<int> AuthorisedGroups

{

get

{

string[] authorisedGroupsStr = ConfigurationManager.AppSettings["AuthorisedGroups"].Split(new char[] { ',' });

int authorisedGroupInt;

var authorisedGroupsInt = from authorisedGroupStr in authorisedGroupsStr

where int.TryParse(authorisedGroupStr, out authorisedGroupInt)

select int.Parse(authorisedGroupStr);

return authorisedGroupsInt.ToList();

}

Getting the Time part only from a SQL Server DateTime

Rhames — Tue, 11 Aug 2009 11:24:50 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2009/08/11/getting-the-time-part-only-from-a-sql-server-datetime.aspx

Just some code to get the time part only from a DateTime field (is SQL Server 2000 or 2005), with the date set to a reference date

declare @timetest datetime

declare @refdate datetime

set @timetest = getdate()

set @refdate = '30 Dec 1899 00:00:00'

select @timetest

, @refdate

, dateadd(day, datediff(day, @refdate , @timetest), @refdate ) -- date part only

, dateadd(day, datediff(day, @refdate , @timetest) * -1, @timetest) -- time part only, with reference date

Instead of @refdate, you could use 0.

SQL server script to generate CREATE INDEX commands

Rhames — Fri, 21 Nov 2008 12:30:01 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2008/11/21/sql-server-script-to-generate-create-index-commands.aspx

This script generates the “CREATE INDEX” scripts for a SQL Server database:

set nocount on

declare @index table

(

object_id int,

objectName sysname,

index_id int,

indexName sysname,

fill_factor tinyint,

allow_row_locks bit,

allow_page_locks bit,

is_padded bit,

indexText varchar(max),

indexTextEnd varchar(max)

)

declare @indexColumn table

(

object_id int,

index_id int,

column_id int,

index_column_id int,

max_index_column_id int,

is_descending_key bit,

is_included_column bit,

columnName varchar(255),

indexText varchar(max) null

)

insert into @index

select

i.object_id,

object_name(i.object_id),

i.index_id,

i.name,

fill_factor,

allow_row_locks,

allow_page_locks,

is_padded,

'CREATE NONCLUSTERED INDEX [' + i.name + '] ON [dbo].[' + object_name(i.object_id) + '] ' + char(13),

'WITH (PAD_INDEX = ' +

CASE WHEN is_padded = 1 THEN ' ON ' ELSE ' OFF ' END +

', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ' +

CASE WHEN allow_row_locks = 1 THEN ' ON ' ELSE ' OFF ' END +

', ALLOW_PAGE_LOCKS = ' +

CASE WHEN allow_page_locks = 1 THEN ' ON ' ELSE ' OFF ' END +

CASE WHEN fill_factor > 0 THEN ', FILLFACTOR = ' + convert(varchar(3), fill_factor) ELSE '' END +

')' + CHAR(13)

from sys.indexes i

where i.type = 2 and i.is_unique_constraint = 0

and objectproperty(i.object_id , 'IsUserTable') = 1

order by object_name(i.object_id), i.name

insert into @indexColumn

select

i.object_id,

i.index_id,

ic.column_id,

ic.index_column_id,

max(ic.index_column_id) over (partition by i.object_id, i.index_id, is_included_column),

is_descending_key,

is_included_column,

'[' + c.name + ']',

null

from @index i

join sys.index_columns ic

on i.object_id = ic.object_id

and i.index_id = ic.index_id

join sys.columns c

on ic.object_id = c.object_id

and ic.column_id = c.column_id

order by i.object_id, i.index_id, ic.index_column_id

declare @fields varchar(max)

declare @object_id int, @index_id int

select @fields = null, @object_id = -1, @index_id = -1

update @indexColumn

set @fields = indexText =

case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)

then isnull(@fields + ', ', ' ') + columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end

else columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end

end,

@object_id = case when object_id <> @object_id

then object_id else @object_id end,

@index_id = case when index_id <> @index_id

then index_id else @index_id end

from @indexColumn

where is_included_column = 0

select @fields = null, @object_id = -1, @index_id = -1

update @indexColumn

set @fields = indexText =

case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)

then isnull(@fields + ', ', ' ') + columnName

else columnName

end,

@object_id = case when object_id <> @object_id

then object_id else @object_id end,

@index_id = case when index_id <> @index_id

then index_id else @index_id end

from @indexColumn

where is_included_column = 1

update @index

set indexText = i.indexText + '( ' + char(13) + char(9) + ic.indexText + char(13) + ') '

from @index i join @indexColumn ic

on i.object_id = ic.object_id

and i.index_id = ic.index_id

and ic.index_column_id = ic.max_index_column_id

and ic.is_included_column = 0

update @index

set indexText = i.indexText + 'INCLUDE ( ' + char(13) + char(9) + ic.indexText + char(13) + ') '

from @index i join @indexColumn ic

on i.object_id = ic.object_id

and i.index_id = ic.index_id

and ic.index_column_id = ic.max_index_column_id

and ic.is_included_column = 1

update @index

set indexText = indexText + indexTextEnd

from @index

select indexText

--, objectName, indexName

from @index

SQL Server datetime - getting the date part only

Rhames — Wed, 19 Nov 2008 15:17:14 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2008/11/19/sql-server-datetime---getting-the-date-part-only.aspx

I am always forgetting the format for using DATEADD and DATEPART functions to get just part of a SQL Server datetime (e.g. just the date), so I am posting it here so I’ll always know where to find it.

declare @date datetime

set @date = getdate()

select @date, dateadd(day, datediff(day, 0, @date), 0)

SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values.

Rhames — Thu, 30 Oct 2008 10:58:40 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2008/10/30/sql-server-checksum-with-single-quotes--gives-duplicate-values.aspx

SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values.

A database I have been working on uses a checksum across address data to try to establish whether an entered address already exists in the database.

A problem occurred because two addresses had the same checksum value, but the actual address was subtlety different. The difference was that one had the street address as “St James’ Street”, whereas the other had “St James Street”.

The following test script shows that the single quote does not appear to alter the checksum calculation:

declare @t1 table (s varchar(50))

insert into @t1 values ('10 St James Street')

insert into @t1 values ('10 St James'' Street')

insert into @t1 values ('1''0'' S''t'' J''a''m''e''s'' S''t''r''e''e''t')

select checksum(s), * from @t1

These three rows all give the same checksum value (178448437).

Why you should always specify the SqlDbType for an ADO.NET SqlParameter object.

Rhames — Wed, 29 Oct 2008 15:50:30 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2008/10/29/why-you-should-always-specify-the-sqldbtype-for-an-ado.net.aspx

How not specifying the ADO.NET SqlParameter DbType can lead to a horrible query execution plan!

I was looking into a query that was performing much worse than expected. The query was a simple select of the form:

select

e.EmployeeID,

count(r.EmployeeRoleID)

from Employee e

left join EmployeeRole r

on e.EmployeeID = r.EmployeeID

where e.EmployeeRef = @EmployeeRef

group by e.EmployeeID

I expected the query plan for this to be fairly straightforward, a couple of Index Seeks and a Left Outer Join, but on examining the query plan, I saw something horrendous. There were 3 Nest Outer Joins, and a Sort that was taking 53% of the execution cost.

Further examination revealed that although the EmployeeRef field on the Employee table was a varchar(50), the @EmployeeRef parameter was being passed in as a nvarchar(6)!

I tracked this down to the use of the ADO.NET SqlParameter(String, Object) constructor. The developer had used this constructor thus:

SqlParameter param1 = new SqlParameter("@EmployeeRef", "XXX123");

sqlCommand.Parameters.Add(param1);

This constructor only initialises the name and the value of the SqlParameter object. As the SqlDbType is not specified, the CLR infers the type, in this case as nvarchar(6).

I changed the above code to:

sqlCommand.Parameters.Add("@EmployeeRef",

SqlDbType.VarChar, 50).Value = "XXX123";

This gave the expected query execution plan, and the cost of the statement was reduced by about 50%.

I tend to avoid any usage of the SqlParameter constructors that do not specify the SqlDbType, unless I am sure I will set the type explicitly later. For the same reason I avoid the SqlParameter.AddWithValue() method:

sqlCommand.Parameters.AddWithValue("@EmployeeRef", "XXX123");

I have on more than one occasion seen the following code, which is obviously wrong, but can give very subtle errors:

sqlCommand.Parameters.AddWithValue("@EmployeeRef", SqlDbType.Int);

Creating SQL Wildcards with Recursion and a Common Table Expression in SQL Server 2005

Rhames — Tue, 28 Oct 2008 15:21:53 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2008/10/28/creating-sql-wildcards-with-recursion-and-a-common-table-expression.aspx

Creating Test Wildcards with a Common Table Expression in SQL Server 2005

I needed to create a SQL Server table of test wildcard strings ranging from ‘AA%’ through to ‘ZZ%’. This seemed to be a prime candidate for using recursion with a Common Table Expression (CTE). After a little playing around I came up with the following:

with wildcards as

(select char(65) + char(65) + '%' as wildcard, 65 as num1, 65 as num2

union all

select char(

case when num2 = 90 then num1 + 1

else num1 end)

+ char(

case when num2 = 90 then 65

else num2 + 1 end)

+ '%',

case when num2 = 90 then num1 + 1

else num1 end,

case when num2 = 90 then 65

else num2 + 1 end

from wildcards where num1 < 91

and (num1 <> 90 or num2 <> 90)

)

Obviously, to actually put the results somewhere useful, I had an INSERT statement which selected from the Wildcards CTE:

insert into TestFilter (Filter)

select wildcard from wildcards option(MaxRecursion 676)

Selecting the num1 and num2 values from the Wildcards CTE shows you what is going on:

select * from wildcards option(MaxRecursion 676)

Calculating Running Totals in SQL Server 2005, The optimal solution?

Rhames — Tue, 28 Oct 2008 10:22:45 GMT

Originally posted on: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

Using “Update to a local variable” to calculate running totals in SQL.

Recently I was looking at an existing view on a client's SQL server 2005 database. This view calculated the running total for a transaction amount from a table, but was performing very poorly.

I had always believed there were three different methods for calculating a running total using TSQL:

1. Use a nested sub-query

2. Use a self join

3. Use Cursors

My own personal preference was to use the cursors option. If the cursor guidelines are followed, I've always found this to be the quickest, because the other two methods involve multiple scans of the table. The key for the cursor method is to ensure the data you are "cursoring" through is in the correct order, as the query optimzier does not understand cursors. This usually means cursoring through the data by clustered index, or copying the data into a temp table / table var first, in the relevant order.

A blog posted by Garth Wells back in 2001 gives these three techniques (http://www.sqlteam.com/article/calculating-running-totals)

I came across a fourth technique for the running total calculation, which is related to the cursor method. Like the cursor method, it involves a single scan of the source table, then inserting the calculated running total for each row into a temp table or table variable. However, instead of using a cursor, it makes use of the following UPDATE command syntax:

UPDATE table

SET variable = column = expression

The TSQL to calculate the running total is:

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl

SELECT DayCount, Sales, null

FROM Sales

ORDER BY DayCount

UPDATE @SalesTbl

SET @RunningTotal = RunningTotal = @RunningTotal + Sales

FROM @SalesTbl

SELECT * FROM @SalesTbl

I tested this query along with the other three methods on a simple set of test data (actually the same test data from Garth Wells’ blog mentioned above).

The results of my test runs are:

Method	Time Taken
Nested sub-query	9300 ms
Self join	6100 ms
Cursor	400 ms
Update to local variable	140 ms

I was surprised just how much faster using the “Update to a local variable” method was. I expected it to be similar to the cursor method, as both involve a single scan of the source table, and both calculate the running total once only for each row in the table. The Nested Sub-query and Self join methods are so much slower because they involve the repeated recalculation of all of the previous running totals.

Note: There is a pretty big assumption in using the “Update to local variable” method. This is that the Update statement will update the rows in the temp table in the correct order. There is no simple way to specify the order for an Update statement, so potentially this method could fail, although I have not seen this actually happen yet!

I think that if I use a table variable, then the update will probably be in the correct order, because there are no indexes for the query optimizer to use, and parallellism will not occur. However, I can't be sure about this!

The following script was used to create the test data:

CREATE TABLE Sales (DayCount smallint, Sales money)

CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)

go

INSERT Sales VALUES (1,120)

INSERT Sales VALUES (2,60)

INSERT Sales VALUES (3,125)

INSERT Sales VALUES (4,40)

DECLARE @DayCount smallint, @Sales money

SET @DayCount = 5

SET @Sales = 10

WHILE @DayCount < 5000

BEGIN

INSERT Sales VALUES (@DayCount,@Sales)

SET @DayCount = @DayCount + 1

SET @Sales = @Sales + 15

END

The queries used in my tests for the other three methods are posted below:

1. Nested Sub-query

SELECT DayCount,

Sales,

Sales+COALESCE((SELECT SUM(Sales)

FROM Sales b

WHERE b.DayCount < a.DayCount),0)

AS RunningTotal

FROM Sales a

ORDER BY DayCount

2. Self join

SELECT a.DayCount,

a.Sales,

SUM(b.Sales)

FROM Sales a

INNER JOIN Sales b

ON (b.DayCount <= a.DayCount)

GROUP BY a.DayCount,a.Sales

ORDER BY a.DayCount,a.Sales

3. Cursor

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @DayCount smallint,

@Sales money,

@RunningTotal money

SET @RunningTotal = 0

DECLARE rt_cursor CURSOR

FOR

SELECT DayCount, Sales

FROM Sales

ORDER BY DayCount

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @DayCount,@Sales

WHILE @@FETCH_STATUS = 0

BEGIN

SET @RunningTotal = @RunningTotal + @Sales

INSERT @SalesTbl VALUES (@DayCount,@Sales,@RunningTotal)

FETCH NEXT FROM rt_cursor INTO @DayCount,@Sales

END

CLOSE rt_cursor

DEALLOCATE rt_cursor

SELECT * FROM @SalesTbl