Today I was challenged by a coworker to find out which tables in our database require SQL Cache Dependencies in our Application. His use case involved automated testing, and wanted a better alternative to updating a CSV containing the table names. Since he has access to the application’s assembly, I had a bright idea to use reflection to solve the world’s most miniscule problem.
In order to support such a query against the assembly, I needed some way to indicate that a class or business object in the assembly uses SQL Cache Dependency. I came up with a new attribute, SqlCacheAttribute:
public class SqlCacheAttribute : Attribute { }
Now, I can decorate business objects that represent tables (Custom Collections, Repositories, etc.) with this new attribute where appropriate:
internal class CustomersRepository { }
[SqlCache]
internal class DepartmentsRepository { }
[SqlCache]
internal class ItemsRepository { }
internal class OrdersRepository { }
[SqlCache]
internal class SuppliersRepository { }
Notice that I have even marked my repositories as internal. I have done this to demonstrate that table names for SQL Cache can be exposed while the classes remain local to the assembly. With these attributes in place, I can now reflect against the assembly to query these names.
I created a static class called Helpers to put all of my utility methods. The first helper method that I wrote was for getting a reference to the current assembly. I wasn’t sure how to easily pull this off, but StackOverflow had an answer (the accepted answer wasn’t exactly what I was looking for).
/// <summary>
/// Returns the current Assembly
/// </summary>
/// <returns></returns>
static Assembly GetAssembly()
{
string loc = (typeof(SqlCacheAttribute)).Assembly.FullName;
return Assembly.Load(loc);
}
Yeah, I’m cheating a little here. GetAssembly knows to use SqlCacheAttribute to find the correct Assembly. There is probably a better way, but this worked great. Now that I have access to this assembly, I am able to perform the actual reflection to solve our problem. Calling GetTypes() on the Assembly is helpful, but I could surely benefit from a way to detect if a specific type has any specific attribute types on it.
/// <summary>
/// Returns true if the type has any attributes of the generic type
/// </summary>
/// <typeparam name="TAttribute"></typeparam>
/// <param name="type"></param>
/// <returns></returns>
public static bool AnyAttributes<TAttribute>(this Type type) where TAttribute : Attribute
{
return type.GetCustomAttributes(false).OfType<TAttribute>().Any();
}
This is a generic extension method that allows Type objects to easily decide whether it has attributes of the generic type. TAttribute has a clause that requires it to derive from System.Attribute. Okay, so quick recap. I can now 1) reference the assembly, 2) test types for a certain attribute. Now consider the following code:
/// <summary>
/// Gets an array of Table Names.
/// </summary>
/// <returns></returns>
public static string[] GetTablesForSqlCache()
{
var names = from type in GetAssembly().GetTypes()
where type.AnyAttributes<SqlCacheAttribute>()
select type.Name;
return names.ToArray();
}
The GetTablesForSqlCache method uses LINQ to Obejcts to query the Types in the Assembly, and filtering where the types have AnyAttribute of type SqlCacheAttribute. Pretty intuitive, right? There is a problem with this functionality, though. It’s highly unlikely that my table names will be ItemsRepository or DepartmentsRepository. I need to provide a way of manipulating these names as I query them:
/// <summary>
/// Gets an array of Table Names.
/// </summary>
/// <param name="manipulate">
/// <remarks>
/// Allows the consumer to manipulate the name.
/// </remarks>
/// </param>
/// <returns></returns>
public static string[] GetTablesForSqlCache(Func<string, string> manipulate)
{
var names = from type in GetAssembly().GetTypes()
where type.AnyAttributes<SqlCacheAttribute>()
select manipulate(type.Name);
return names.ToArray();
}
The revised GetTablesForSqlCache function takes a delegate of type Func. The delegate is called during the select portion of the query. If the table names are the same as the objects decorated, I can offer a parameterless overload.
/// <summary>
/// Gets an array of Table Names
/// </summary>
/// <returns></returns>
public static string[] GetTablesForSqlCache()
{
return GetTablesForSqlCache(p => p);
}
This completes all of the functionality required to query the assembly for SQL Cache table names. In my solution I have added a new console application for testing this new code. The console application has a reference to my class library project.
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("----- Unchanged Class Names -----");
foreach (string s in Helpers.GetTablesForSqlCache())
Console.WriteLine(s);
Console.WriteLine("----- Manipulated Class Names ----");
foreach (string s in Helpers.GetTablesForSqlCache(Manipulate))
Console.WriteLine(s);
Console.ReadKey();
}
static string Manipulate(string input)
{
int index = input.IndexOf("Repository");
return input.Substring(0, index);
}
}
}
This code demonstrates the use of both overloads of the GetTablesForSqlCache method. Since one of the overloads takes Func, the Manipulate method fits the criteria. Running this code will generate the following output:
Notice that only the classes marked with the [SqlCache] attribute have been returned by this code. This strategy could have a number of other uses, such as creating a static method in the assembly for writing out a batch file that makes calls to aspnet_regsql.exe.