Caching in a desktop application using ObjectCache and LocalDB

Last Wednesday I decided to convert my application’s caching from a file-based cache to a database-based cache implementation, mostly to try out LocalDB, which I had never used before. Unfortunately, due to power outages after that, I could only test it at home on my decent-spec computer on Sunday, at which point I decided that the database-based cache is way too slow.

The application caches thumbnails, and one of my requirements is that when the file browser is used to browse a directory that’s already cached, the thumbnails should be retrieved almost instantly. My file-based cache, which uses binary serialization, does just that… But the database does not. I suppose I should have anticipated that; the round-trip to the database takes a little longer.

Although the database-based caching was too slow for this particular need, it could be used for other applications. From that perspective, I am happy to have tried out LocalDB, because now I know how easy it is to use. For that reason, and also because it took so long to type up the draft last week, I’ve decided to publish this post anyway. What follows is the post as I typed it up last Wednesday, before my decision not to use that caching implementation.

RomyViewWithDBCache.zip

My application has used the System.Runtime.ObjectCache caching pattern for a long time now, but I resisted writing about it, since my implementation used binary serialization to the file system, which didn’t create the most easy-to-read code. In fact, some parts of it were downright ugly, like working around the possibility of any files in the cache having a length greater than the infamous MAX_PATH character limit. (Any workaround of that will always be a hack.) Also the mess that this produced on the hard drive was the ugliest part, and working backwards from the cache files to the files and directories they represented was always a mess. (Update: Ugly but fast – the file-based implementation is unfortunately still the one being used, and the shared download linked in this post is instead a branch that uses the DB cache, which I’m keeping only as a LocalDB code example.)

I have just finished implementing the (thumbnail) caching in my application, still using the System.Runtime.ObjectCache caching pattern, with the cache implemented in a LocalDB SQL server.

Hold on… Maybe I need to take a couple of steps back to put this into context:

  • Why cache thumbnails? My application can view images and play videos. It also has a limited file browser (and displays the file types that it can view plus one or two others), thus it displays thumbnails, and has its own cache, much like popular programs ACDSee or XNView.
  • Why System.Runtime.ObjectCache? ObjectCache is a really useful abstract caching class, added to the .Net Framework way back when version 4 was released. I’ve never seen any articles written about it anywhere and I don’t understand why it isn’t more popular. It defines a familiar pattern you can use for caching (similar to ASP.Net), but leaves you to implement the actual cache store however you choose.
  • Why LocalDB? LocalDB is something I’d never used before. It’s the current “embedded” database you can use in Visual Studio 2012/2013, and is actually a full working SQL server… This gives you all the power of a full SQL server, even for an application that isn’t connected to anything.

What it all comes down to is, now I can reuse some boiler-plate code for opening queries/stored procedures, so my cache implementation is much simpler. Oh, there are still some complex bits in the code, but I will not write much about that apart from mentioning it here:

  • The static method I use to get a file or directory’s thumbnail is also in this class, but I won’t detail how it works because that is outside the scope of the caching and SQL helper classes. (Getting thumbnails gets complicated. I use the Windows ShellItemImageFactory for some thumbnails, but not others. For small or transparent images and icons, I don’t like the shell thumbnail because I don’t want to lose the transparency. Directory thumbnails get returned with a white background, which I have to remove programmatically using a hacky flood-fill-the-background technique.)

To my surprise, the database-based cache store turns out to be slower than the file-based cache store, which was so fast that cache retrieval was almost instant. (Although I have not tested it on a fast PC. I wrote it at work yesterday in my spare time, intending to take it home and fine-tune it on my new PC at home, but thanks to a power failure, I couldn’t even switch on the PC.)

To make up for this, I added a “2nd layer” to the cache, just for items cached in the current application session. For this, I took advantage of the fact that my cache item class was already serializable, and just serialized it in binary to a dictionary.

My thumbnail database is very simple: just a single table and a couple of simple stored procedures for getting, setting and deleting cache items. I will most probably change and improve things; this is just my first implementation using a database, but the reason I am writing this is to share how easy it is to come up with your own personal cache storing implementation based on the abstract ObjectCache class… And also how quick it is… It literally took less than 5 minutes to implement. (Granted my boilerplate code for calling SQL – written such that it doesn’t care what parameters you pass to SQL, and it reads the schema of whatever datasets are returned – was mostly already written.)

I’m not going to past any of the actual cache class implementation code here, because it’s really as easy as:

  • Derive a class from ObjectCache and click the arrow that adds dummy methods for all the abstract methods you need to implement.
  • Add the implementation to get/set the cache items as needed according to whatever your backing store is.

Instead, what follows is some code from my SQL helper class…  Also not rocket science, but I find having a few helper methods like this that I can then reuse with whatever database going forward, is really convenient. I had written most of this helper code already; but added the synchronous methods to it when working on this. All the methods are overloads of a couple that do all the work, which are as follows:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace Romy.Core.Misc
{
    public static class Example
    {
        private static int commandTimeout = 300;

        private static string connectionString;

        public static int CommandTimeout
        {
            get { return Example.commandTimeout; }
        }

        public static string ConnectionString
        {
            get
            {
                var connectionString = Example.connectionString;
                var upperCaseConnectionString = connectionString.ToUpperInvariant();

                if (!upperCaseConnectionString.Contains("CONNECTION TIMEOUT") &&
                    !upperCaseConnectionString.Contains("CONNECTTIMEOUT"))
                {
                    connectionString += ";Connection Timeout=60;";
                }

                if (!upperCaseConnectionString.Contains("ASYNCHRONOUS PROCESSING=TRUE"))
                    connectionString += ";Asynchronous Processing=true";

                return connectionString;
            }
            set { connectionString = value; }
        }

        public static DataTable OpenQuery(string query, Dictionary<string, object> parameters, CommandType commandType = CommandType.StoredProcedure)
        {
            try
            {
                using (var connection = new SqlConnection(ConnectionString))
                {
                    using (var adapter = new SqlDataAdapter(query, connection))
                    {
                        var table = new DataTable();
                        adapter.SelectCommand.CommandType = commandType;
                        adapter.SelectCommand.CommandTimeout = CommandTimeout;

                        foreach (var kvp in parameters)
                            adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);

                        if (connection.State != ConnectionState.Open)
                            connection.Open();

                        using (var reader = adapter.SelectCommand.ExecuteReader())
                        {
                            using (var schemaTable = reader.GetSchemaTable())
                            {
                                foreach (DataRow row in schemaTable.Rows)
                                    table.Columns.Add((string)row["ColumnName"], Type.GetType(row["DataType"].ToString()));
                            }

                            var columns = table.Columns.ToArray<DataColumn>();

                            while (reader.Read())
                                table.Rows.Add(columns.Select(c => reader[c.Ordinal]).ToArray());
                        }
                        return table;
                    }
                }
            }
            catch (Exception ex) { ex.Log(); }
            return new DataTable();
        }

        public static int ExecuteQuery(string query, Dictionary<string, object> parameters, CommandType commandType = CommandType.StoredProcedure)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var adapter = new SqlDataAdapter(query, connection))
                {
                    adapter.SelectCommand.CommandType = commandType;
                    adapter.SelectCommand.CommandTimeout = CommandTimeout;

                    foreach (var kvp in parameters)
                        adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    return adapter.SelectCommand.ExecuteNonQuery();
                }
            }
        }

        public static Task<int> UpdateSqlAsync(string query, CancellationToken cancellationToken)
        {
            if (cancellationToken.IsCancellationRequested)
                return Task.FromResult(0);

            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var adapter = new SqlDataAdapter("Not used", connection))
                {
                    adapter.UpdateCommand = new SqlCommand(query, connection);
                    adapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
                    adapter.UpdateCommand.CommandTimeout = CommandTimeout;

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    return adapter.UpdateCommand.ExecuteNonQueryAsync();
                }
            }
        }

        private static async Task<DataTable> OpenQueryAsync(string query, Dictionary<string, object> parameters, CommandType commandType, CancellationToken cancellationToken)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var adapter = new SqlDataAdapter(query, connection))
                {
                    var table = new DataTable();
                    adapter.SelectCommand.CommandType = commandType;
                    adapter.SelectCommand.CommandTimeout = CommandTimeout;

                    foreach (var kvp in parameters)
                        adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    using (var reader = await adapter.SelectCommand.ExecuteReaderAsync(cancellationToken))
                    {
                        using (var schemaTable = reader.GetSchemaTable())
                        {
                            foreach (DataRow row in schemaTable.Rows)
                            {
                                cancellationToken.ThrowIfCancellationRequested();
                                table.Columns.Add((string)row["ColumnName"], Type.GetType(row["DataType"].ToString()));
                            }
                        }

                        var columns = table.Columns.ToArray<DataColumn>();

                        while (await reader.ReadAsync())
                        {
                            cancellationToken.ThrowIfCancellationRequested();
                            table.Rows.Add(columns.Select(c => reader[c.Ordinal]).ToArray());
                        }
                    }
                    return table;
                }
            }
        }

        private static async Task<int> ExecuteQueryAsync(string query, Dictionary<string, object> parameters, CommandType commandType, CancellationToken cancellationToken)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var adapter = new SqlDataAdapter(query, connection))
                {
                    var table = new DataTable();
                    adapter.SelectCommand.CommandType = commandType;
                    adapter.SelectCommand.CommandTimeout = CommandTimeout;

                    foreach (var kvp in parameters)
                        adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    return await adapter.SelectCommand.ExecuteNonQueryAsync();
                }
            }
        }
    }
}
Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s