How to periodically flush dapper.net cache when used with SQL Server

Question

Can someone please explain what this means (from the Dapper.net website)

Limitations and caveats

Dapper caches information about every query it runs, this allow it to materialize objects quickly and process parameters quickly. The current implementation caches this information in a ConcurrentDictionary object. The objects it stores are never flushed. If you are generating SQL strings on the fly without using parameters it is possible you will hit memory issues. We may convert the dictionaries to an LRU Cache.

I am not able to understand what the line in bold means. I am using SQL Server and c# client.

Can someone please give a sample of c# code that will create this memory issue. thank you

Answer 1

If you are generating SQL strings on the fly without using parameters it is possible you will hit memory issues.

You can do this:

cmd.CommandText = "SELECT email, passwd, login_id, full_name " + 
                  "FROM members " +
                  "WHERE email = '" + email + "'";

or you can do this:

string s = "SELECT email, passwd, login_id, full_name " + 
           "FROM members WHERE " +
           "email = @email";
SqlCommand cmd = new SqlCommand(s);
cmd.Parameters.Add("@email", email);

The latter is parameterized. It will be cached once. The former is not parameterized. It will be cached every time you write a query like it with a different value for email. This will explode your memory.

The latter is vastly superior. It avoids injection attacks. dapper can cache it once. SQL Server will compile the execution plan once and cache it.

You should (imperative) already be using parameterized queries. If you aren't, drop everything you are doing and make this an immediate priority.

Can someone please give a sample of c# code that will create this memory issue. thank you

Just do the former in a loop. Watch your memory grow. Do the latter in a loop. Watch your memory not grow.

Answer 2

If you do need to flush the cache, you can invoke SqlMapper.PurgeQueryCache().