Wednesday, 22 April 2009

SqlCacheDependency with ASP.Net

ASP.Net websites drawing their data and content from databases tend to be relatively inefficient in terms of their data access strategy – regularly fetching and re-fetching data on each page that hasn’t change between requests. Often this isn’t a problem, but as traffic scales it becomes more and more important to consider an effective caching strategy.

The converse issue of course is returning data that is out of date –time based caches work well, except for the poor person updating the content of the site who is wondering why his or her changes can’t be viewed.

ASP.Net provides a useful technique where the expiry of the cache can be tied to a table (or tables) in the database – meaning that the cache is only flushed when the data changes (or the application is restarted of course).

This post is intended to detail the steps required to set up and use this on SQL 2005 (the method has changed from previous versions of SQL server where a poll based mechanism was used).

Configure Database

Run the following statement on your database:


Because this requires an exclusive lock on the database, you may find it takes a very long time. To close existing sessions and run at once, use this:


You can check the result with this statement - should return 1 if all is well.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'mydb'

Setting up Notifications

Before any SQL queries are made, the notification listener needs to be started. This creates and keeps a connection to the database, through which notification updates are received. The best place for this is in Application_OnStart in the global.ascx file:
void Application_Start(object sender, EventArgs e)

This process requires a number of permissions to view system tables and set up procedures, therefore it needs to be run with appropriate database permissions to do this. Normally you would want to configure your website to run using a database login with minimal permissions (e.g. just SELECTS and EXECUTES), so this may need to be changed to use a different user account for this one process.

In web.config, you need to add a section like the following within <system.web>:

<sqlcachedependency enabled="true">
<add name="mydb"

Using the Cache

The way I tend to use the cache is in my business logic layer (BLL – a layer in between the presentation layer and the data access layer (DAL) where the database specific code is held).

By adding an extra parameter to my factory methods (methods that instantiate typed objects or collections) I can select whether to use caching or not via a configuration file. If that is set, a cache key is defined – basically a string made up of other parameters that would change the generated SQL or stored procedure call and hence the output.

I look up this cache key, if there’s something found, it’s returned. If not, I go to the database to retrieve the data for populating the object or collection, store the result in the cache and return it.


public static Language GetLanguage(int intID, bool blnUseCaching)
Language objLanguage;
string strCacheKey = String.Format("Language_{0}", intID);
if (blnUseCaching && System.Web.HttpContext.Current != null && System.Web.HttpContext.Current.Cache[strCacheKey] != null)
objLanguage = (Language)System.Web.HttpContext.Current.Cache[strCacheKey];
objLanguage = DataAccess.LanguageMethods.GetLanguage(intID, "");
if (blnUseCaching)
System.Web.HttpContext.Current.Cache.Insert(strCacheKey, objLanguage, DataAccess.LanguageMethods.GetCacheDependency());
return objLanguage;

In DAL (DataAccess.LanguageMethods)

public static SqlCacheDependency GetCacheDependency()
SqlCacheDependency dep = null;

dep = new SqlCacheDependency("app", "dbo.tblLanguages");
catch (TableNotEnabledForNotificationException)
SqlCacheDependencyAdmin.EnableTableForNotifications(DataAccessUtilities.ConnectionString(), "dbo.tblLanguages");
dep = new SqlCacheDependency("app", "dbo.tblLanguages");

return dep;

public static Language GetLanguage(int intID, string strURLFolder)
IDataReader dr = null;
Language objLanguage = null;

Object[] arrParamValues = new Object[1];
arrParamValues[0] = intID;
dr = DataAccessUtilities.GetDataReaderFromProcedure(DataAccessUtilities.ConnectionString(), "spGetLanguage", arrParamValues);
if (dr.Read())
objLanguage = new Language(dr.GetByte(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4));
catch (Exception ex)
throw (ex);
return objLanguage;

Creating a Dependency on More Than One Table

In many, perhaps most, situations database queries involve more than one table – and hence it wouldn’t be necessary to tie the cache to each. SQLCacheDependencies can only be associated with a single table however.

The key here is to make use of the AggregateCacheDependency – a mechanism for combining several other types of dependency to a single one, such that a change in any of the dependent tables or files will cause the cache to be flushed.
dep1 = new SqlCacheDependency ("app", "tblUsers"),
dep2 = new SqlCacheDependency ("app", "tblRoles"),

AggregateCacheDependency depAgg = new AggregateCacheDependency ();
depAgg.Add (dep1, dep2);

Cache.Insert ("Users", data, depAgg);

For more details head to MSDN

For details on how to use this technique with older versions of SQL server again there is information at MSDN and ASP.Net Resources

Thursday, 2 April 2009

Coding HTML Emails

Today had to spend some time HTML coding up some email newsletters. There's a certain guilty pleasure in this... as for a while you have to forget about web standards, and (as Prince almost said) code like it's 1999.

Due to the variability in rendering HTML in all the various email and webmail clients, this means using tables, font tags etc.

So here's my checklist of coding issues that I try to follow when presented with an HTML email design to code up:
  • Ask the designer to avoid background images - particularly with light coloured text overlayed. Reason being if the background isn't displayed, the text will be illegible. So use solid colours. Failing that, I make sure I put a background colour on as well.
  • Use tables for layout.
  • Don't use any external CSS - only inline.
  • Even then, use alternate means to be sure what you require will come out. For example if I need text at a particular size, I'll put <span> tags inside <font> tags, e.g.

    <font face="arial">
    <span style="font-size: 32px">
    Big text
Here's some links to others offering tips on this subject:

Wednesday, 1 April 2009

ASP.Net MVC Resources

I’ve been working recently on a project using the ASP.Net MVC framework – Microsoft’s implementation of this pattern as an alternative means of presentation from Web forms. There are a number of basic tutorials on the web, but it’s taken a bit of digging to make progress beyond the relatively simple examples.

As with any new technology, you get stuck on things that should be simple, but having worked through these issues am enjoying using the framework, and have started to use it for an, albeit small, production project for a client.

The project follows the standard list, create, edit, delete application type that many of the tutorials follow, so it seemed a good fit.

In this post I’ll pull together some of the best resources I found that helped me on my way to developing this application, along with some tips on how certain problems were solved.


Firstly the application was based on the excellent tutorial Stephen Walther has released – this demonstrates the use of views and controllers, and extends the model to provide data access and validation, via clear use of interfaces and design patterns to adhere to the loosely coupled best practices in this area. He also illustrates how MVC supports unit testing – one of its major advantages over web forms.

Taking Things Further...

Editing Complex Properties
One of the first hurdles I had to get over was the editing of a record that has a field selected from a drop-down list (as opposed to simply entering the value in a text box that most of the simple tutorials demonstrate – these work “out of the box” if you make sure you use a typed view and ensure the names of your input fields match the names of your class properties).

There are three discrete challenges here – one is passing the data to the view that populates the selection list, another is pre-selecting the current value and finally we need to get the updated value to pass up to the data access layer.

The following controller logic passes additional data to the view for creating and editing a list of users for an application; in this case a list of user "roles" to be selected in a drop-down list. The Create action passes the list, the Edit the list within an additional parameter indicating the current selection:

public ActionResult Create()
return View();

public ActionResult Create([Bind(Exclude = "ID")] User objUserToCreate)
//Get selected Role object and attach to created User
objUserToCreate.Role = mobjService.GetRole(byte.Parse(Request["Roles"]));

//Create user record - redirect to list if succcesful or redisplay form if not
if (mobjService.CreateUser(objUserToCreate))
return RedirectToAction("Index");
return View("Create");

public ActionResult Edit(int id)
User objUser = mobjService.GetUser(id); //retrieves User object
return View(objUser);

public ActionResult Edit(User objUserToEdit)
//Note - the following does not work when using Entity Framework
//it marks the object as "Added" rather than "Detached"
//and leads to problems when calling ApplyPropertyValues
//objUserToEdit.Role = mobjService.GetRole(byte.Parse(Request["Roles"]));

objUserToEdit.Role = new Role();
objUserToEdit.Role.ID = byte.Parse(Request["Roles"];

if (mobjService.EditUser(objUserToEdit))
return RedirectToAction("Index");
return View("Edit", objUserToEdit);

private void GetRoleListForViewData()
ViewData["Roles"] = new SelectList(mobjService.ListRoles(), "ID", "Name");

private void GetRoleListForViewData(int intSelectedID)
ViewData["Roles"] = new SelectList(mobjService.ListRoles(), "ID", "Name", intSelectedID);

Useful Code Samples

Martijn Boland provides an excellent sample project that can simply be referenced in order to provide a paging mechanic.

C# Language Extensions to Support LINQ

On the final day of Devweek this year I attended a full-day workshop on LINQ. It was initially billed as a day of “Entity Framework and LINQ”, but annoying the first bit of the title was dropped at some point before the day! Nonetheless it was a good presentation of what LINQ is, what language extension have been developed to support it, and how it is used in LINQ to Objects, LINQ to XML and LINQ to Entities. The talk was given by Niels Berglund.

In order to support LINQ, a number of amends and enhancements were made to the .Net compiler and the VB.Net and C# languages – I found it worth getting my head around these, so that the LINQ language introduced later appeared less like “magic”.

Automatic Properties

Traditionally properties would be created in a class with a public accessor and a private backing store:

private string mstrFirstName;

public string FirstName
get { return mstrFirstName; }
set { mstrFirstName = value; }

At the compiled code level this hasn’t changed, but in C# as a productivity enhancement you can now simply do this:

public string FirstName { get; set; }

Object Initialisers

Again as an alternate syntax, objects can be initialised in a single statement:

User objUser = new User { ID = 1, FirstName = “Fred”, LastName = “Bloggs” }

Type inference with var

For local types, you can leave the complier to work out what type you are assigning to a variable. Note this is not the same as VB’s variant, or an untyped variable found in an interpreted language like VBScript – it will be typed in the compiled code, and the compiler will complain if you try to subsequently assign a string to a variable initialised with an integer for example.

var i = 1;

Personally, I would only use this when the occasion warranted it – e.g. from LINQ queries – as I feel this reduces code readability.

Extension Methods
Can be used to extend classes that may be sealed or for which you may not have source code available. Really they are just static methods that appear to be instance methods.

e.g. adding an extension method to the string class:

static class MyExtensions
public static string MyExtension(this string s)
return DoSomething(s);
Anonymous Methods
Allow you to define a method in-line. Normally when you use delegates you pass an existing method - using anonymous method you can define it in the same line as the call.

Lambda Expressions
Lambda expressions are another way of writing anonymous methods (inline functions):

Array.Sort (nums, delegate(int a, int b) {return a.CompareTo(b); } );

Array.Sort (nums, {a, b} => a.CompareTo(b) );