Images, BLOBs and other general database madness...

I've been playing around with storing image/media data in the database for the Beavervision Scoreboard - partly so I could just ship a whole database backup around without having to remember a whole heap of additional content files, and partly to test out BLOB handling through ADO.NET Entity Frameworks.

Firstly, file upload and reading/writing to the database. A doddle, read the incoming file into a byte array and stick it into the db object e.g:

HttpPostedFileBase v_HPFB = (HttpPostedFileBase)Request.Files[v_File];

if (v_HPFB.ContentLength != 0)
{
tbl_Media v_Media = new tbl_Media();
byte[] v_Buffer = new byte[v_HPFB.ContentLength];
v_HPFB.InputStream.Read(v_Buffer, 0, v_HPFB.ContentLength);
v_Media.value = v_Buffer;
}

Obviously for larger files you might want to stream rather than reading all into memory.

And then equally reading out from the column is also a doddle as its exposed as the byte array - obviously take care including the table holding the BLOB as you don't really want it to come back every time you do a simple query. I created a separate media table (cunningly called tbl_Media (which has just an identity column and a value image column - as above), and then have link tables for each area of the system that can have media, and only go to the BLOB on actual request for it.

So then we need to get this image out and use it somehow. I cracked out MVC and found the FileContentResult and FileStreamResult which you can attach to an action to essentially return non-HTML-view data. Thus to fetch an item out of the media table by ID I created a generic action, e.g

public FileContentResult Get(int id)
{
BeavervisionEntities v_Ent = new BeavervisionEntities();
tbl_Media v_Media = (from m in v_Ent.tbl_Media.Include("tbl_MediaType") where m.id == id select m).First();
return File(v_Media.value, v_Media.tbl_MediaType.type);
}

The Media Type table being the associated mime-type of the media stored in a normalised fashion.

Of course, once you wander into processing the BLOB, rather that just serving it up from a static location, you can start to muck around with it mid-stream...

So (hang on to your hats now), I know that I have a list of countries taking part, they all have their own flags, and I want to put them on different parts of the site in different sizes (e.g. a small one for a list, a mid-size one for view)... so I'll create a number of facade actions in the controller to neatly wrap a more complex action, and just upload and store a high-quality large transparent PNG (which I use as the source for all my images anyway), I then knocked-up a quick static html page to call these, and the output looks like:

• A is the original image as from the /Media/Get/1 source.
• B is the same image, unprocessed from /Media/Flag/Albania - querying through to find the relevant country name and pulling the associated flag image back.
• C is through the /Media/SmallFlag/Albania facade action to provide a 50x28 PNG.
• D is through the /Media/GetFlag/Albania?height=84&width=150 action, which outputs an 150x84 PNG (the facade in C is actually passing through to this action).
• E are two re-encoded images (JPEG & GIF respectively) used by appending &format=image/jpeg (or image/gif) to the GetFlag action.

All very nice and pretty (and pretty ugly too), and quick for me using the site on my own - but what can we do to try and reduce server & database overhead? How about some output caching? Well MVC nicely supports this with OutputCache attribute, so before the Flag & SmallFlag actions I told it to cache (for 2 weeks) with:

[OutputCache(Duration = 1209600, NoStore = false, Location = System.Web.UI.OutputCacheLocation.Any, VaryByParam = "id")]

Now to test this, I decided to upload a flag for a different country - but wouldn't you know it, me being the dunce who wants to test caching I deliberately uploaded the wrong image and bring up the static page designed to query it (i.e. changing Albania to Andorra), duh me. OK, so then I bin that from the database and upload the correct image - then let's see what came out for that:

So, using the same lettering from above I told the actions called in B & C to cache with the wrong image, and lo it doth return the cached image, whereas A, D & E haven't been told to cache and do return the correct image.

Neat.

Now I can abuse this to within an inch of its life.