Appfabric caching for database dependency

Question

We believe the AppFabric caching is a good fit for the caching requirement. However, we also want to implement some sort of database dependency, i.e. the cache should sync with the backend database asynchronously. The read-through and write behind feature seems interesting, could anyone please help point us a direction how can we leverage these features in achieving the auto sync behavior between the appfabric and database? Thanks a lot!

Answer 1

SqlDependency can be used to notify to your application about modifications in database. To use this you need to enable service broker on database level, please go through these limitation before implementation this solution

using (SqlConnection connection = new SqlConnection(yourConnectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(databaseSqlToBeMonitered, connection))
    {
       SqlDependency dependency = new SqlDependency(command);
       dependency.OnChange += new OnChangeEventHandler((a, b) =>
                                {
                    //Remove data from cache
                                });

       command.ExecuteReader().Close();
    }
}