Anything related to the .NET framework
04 Sep 2009

ASP.NET MVC Cheat Sheets

My latest batch of cheat sheets is out on DRP which are focused on ASP.NET MVC. So what is in this set:

ASP.NET MVC View Cheat Sheet

This focuses on the HTML Helpers, URL Helpers and so on that you would use within your views.


ASP.NET MVC Controller Cheat Sheet

This focuses on what you return from your controller and how to use them and it also includes a lot of information on the MVC specific attributes.


ASP.NET MVC Framework Cheat Sheet

This focuses on the rest of MVC like routing, folder structure, execution pipeline etc… and some info on where you can get more info (is that meta info?).


ASP.NET MVC Proven Practises Cheat Sheet

This contains ten key learnings that every ASP.NET MVC developer should know - it also includes links to the experts in this field where you can get a ton more information on those key learning's.


What are the links in the poster?

Think before you data bind
    Full URL:

Keep the controller thin
    Full URL:

Create UrlHelper extensions
    Full URL:

Keep the controller HTTP free
    Full URL:

Use the OutputCache attribute
    Full URL:

Plan your routes
    Full URL:

Split your view into multiple view controls
    Full URL:

Separation of Concerns (1)
    Full URL:

Separation of Concerns (2)
    Full URL:

The basics of security still apply
    Full URL:

Decorate your actions with AcceptVerb
    Full URL:

19 Aug 2009

Reading and writing to Excel 2007 or Excel 2010 from C# - Part IV: Putting it together

[Note: See the series index for a list of all parts in this series.]


In part III we looked the interesting part of Excel, Shared Strings, which is just a central store for unique values that the actual spreadsheet cells can map to. Now how do we take that data and combine it with the sheet to get the values?

What makes up a sheet?

First lets look at what a sheet looks like in the package:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<worksheet xmlns="" xmlns:r="" xmlns:mc="" mc:Ignorable="x14ac" xmlns:x14ac="">
<dimension ref="A1:A4" /> 
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="A5" sqref="A5" /> 
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25" /> 
<row r="1" spans="1:1" x14ac:dyDescent="0.25">
<c r="A1" t="s">
<row r="2" spans="1:1" x14ac:dyDescent="0.25">
<c r="A2" t="s">
<row r="3" spans="1:1" x14ac:dyDescent="0.25">
<c r="A3" t="s">
<row r="4" spans="1:1" x14ac:dyDescent="0.25">
<c r="A4" t="s">
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> 

Well there is a lot to understand in the XML, but for now we care about the <row> (which is the rows in our speadsheet) and within that the cells which the first one looks like:

<c r="A1" t="s">

First that t=”s” attribute is very important, it tells us the value is stored in the shared strings. Then the index to the shared string is in the v node, in this example it is index 0. It is also important to note the r attribute for both rows and cells contains the position in the sheet.

As an aside what would this look like if we didn’t use shared strings?

<c r="A1">

The v node contains the actual value now and we no longer have the t attribute on the c node.

The foundation code for parsing the data

Now that we understand the structure and we have this Dictionary<int,string> which contains the shared strings we can combine them - but first we need a class to store the data in, then we need to get to the right worksheet part and a way to parse the column and row info, once we have that we can parse the data.

Before we read the data, we need a simple class to put the info into:

public class Cell
    public Cell(string column, int row, string data)
        this.Column = column;
        this.Row = row;
        this.Data = data;

    public override string ToString()
       return string.Format("{0}:{1} - {2}", Row, Column, Data);

    public string Column { get; set; }
    public int Row { get; set; }
    public string Data { get; set; }

How do we find the right worksheet? In the same way as we did get the shared strings in part II.

private static XElement GetWorksheet(int worksheetID, PackagePartCollection allParts)
   PackagePart worksheetPart = (from part in allParts
                                 where part.Uri.OriginalString.Equals(String.Format("/xl/worksheets/sheet{0}.xml", worksheetID))
                                 select part).Single();

    return XElement.Load(XmlReader.Create(worksheetPart.GetStream()));

How do we know the column and row? Well the c node has that in the r attribute. We’ll pull that data out as part of getting the data, we just need a small helper function which tells us were the column part ends and the row part begins. Thankfully that is easy since rows are always numbers and columns always letters. The function looks like this:

private static int IndexOfNumber(string value)
    for (int counter = 0; counter < value.Length; counter++)
        if (char.IsNumber(value[counter]))
            return counter;
    return 0;

Finally - we get the data!

We got the worksheet, then we got the cells using LINQ to XML and then we looped over them in a foreach loop. We then got the location from the r attribute - split it into columns and rows using our helper function and then grabbed the index, which we then go to the shared strings object and retrieve the value. The following code puts all those bits together and should go in your main method:

    List<Cell> parsedCells = new List<Cell>();

    XElement worksheetElement = GetWorksheet(1, allParts);

    IEnumerable<XElement> cells = from c in worksheetElement.Descendants(ExcelNamespaces.excelNamespace + "c")
                                  select c;

    foreach (XElement cell in cells)
        string cellPosition = cell.Attribute("r").Value;
        int index = IndexOfNumber(cellPosition);
        string column = cellPosition.Substring(0, index);
        int row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));
        int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.excelNamespace +  "v").Single().Value);

        parsedCells.Add(new Cell(column, row, sharedStrings[valueIndex]));

And finally we get a list back with  all the data in a sheet!

29 Jul 2009

Reading and writing to Excel 2007 or Excel 2010 from C# - Part III: Shared Strings

[Note: See the series index for a list of all parts in this series.]


Excel’s file format is an interesting one compared to the rest of the Office Suite in that it can store data in two places where most others store the data in a single place. The reason Excel supports this is for good performance while keeping the size of the file small. To illustrate the scenario lets pretend we had a single sheet with some info in it:


Now for each cell we need to process the value and the total size would be 32 characters of data. However with a shared strings model we get something that looks like this:


The result is the same however we are processing values once and the size is less, in this example 24 characters.

The Excel format is pliable, in that it will let you do either way. Note the Excel client will always use the shared strings method, so for reading you should support it. This brings up an interesting scenario, say you are filling a spreadsheet using direct input and then you open it in Excel, what happens? Well Excel identifies the structure, remaps it automatically and then when the user wishes to close (regardless if they have made a change or not) will prompt them to save the file.

The element we loaded at the end of part 2 is that shared strings file, which in the archive is \xl\sharedstrings.xml. If we look at it, it looks something similar to this:

Each <t> node is a value and it corresponds to a value in the sheet which we will parse later. The sheet will have a value in it, which is the key to the item in the share string. The key is an zero based index. So in the above example the first <t> node (Some) will be stored as 0, the second (Data) will be 1 and so on. The code to parse it which I wrote looks like this:
private static void ParseSharedStrings(XElement SharedStringsElement, Dictionary<int, string>sharedStrings)
    IEnumerable<XElement> sharedStringsElements = from s in SharedStringsElement.Descendants(ExcelNamespaces.excelNamespace + "t")
                                                  select s;

    int Counter = 0;
    foreach (XElement sharedString in sharedStringsElements)
        sharedStrings.Add(Counter, sharedString.Value);

Using this I am parsing the node and putting the results into a Dictionary<int,string>.

25 Jul 2009

Reading and Writing to Excel 2007 or Excel 2010 from C# - Part II: Basics

[Note: See the series index for a list of all parts in this series.]


To get support for the technologies we will use in this we need to add a few assembly references to our solution:

  • WindowsBase.dll
  • System.Xml
  • System.Xml.Linq
  • System.Core

Next make sure you have the following namespaces added to your using/imports:

  • System.IO.Packaging: This provides the functionality to open the files.
  • System.Xml
  • System.Xml.Linq
  • System.Linq
  • System.IO

Right next there is a XML namespace (not to be confused with .NET code name spaces) we need to use for most of our queries: and there is a second one we will use seldom So I dumped this into a nice static class as follows:

namespace XlsxWriter
    using System.Xml.Linq;

    internal static class ExcelNamespaces
        internal static XNamespace excelNamespace = XNamespace.Get("");
        internal static XNamespace excelRelationshipsNamepace = XNamespace.Get("");

Next we need to create an instance of the System.IO.Packaging.Package class (from WindowsBase.dll) and instantiate it by calling the static method Open.

 Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);

Note: It is at this point that the file is opened, this is important since Excel will LOCK an open file. This is an important issue to be aware of because when you open a file that is locked a lovely exception is thrown. To correct that you must make sure to call the close method on the package, for example:


When you open the XLSX file manually, the first file you’ll see is the [Content_Types].xml file which is a manifest of all the files in the ZIP archive. What is nice with using Packaging is that you can call the GetParts method to get a collection of Parts, which are actually just the files within the XLSX file.

The contents of the XLSX if renamed to a ZIP file and opened.
The various files listed in the [Content_Types].xml file.

What we will use during this is the ContentType parameter to filter the parts to the specific item we want to work with. The second image above to identify the value for the ContentType. For example the ContentType for a worksheet is: application/vnd.openxmlformats-officedocument.speadsheetml.worksheet+xml.

Once we have all the parts of the XLSX file we can navigate through it to get the bits we need to read the content, which involves two steps:

  1. Finding the shared strings part. This is another XML file which allows for strings of values to shared between worksheets. This is optional for writing, to use but does save space and speed up loading. For reading values it is required as Excel will use it.
  2. Finding the worksheet that we want to read from, this is a separate part from the shared strings.

Lets start with reading the shared strings part, this will be basis for reading any part later in series. What we need to do is get the first PackagePart with the type: application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml

PackagePart sharedStringsPart = (from part in allParts
    where part.ContentType.Equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml")
    select part).Single();

Now we need to get the XML content out of the PackagePart, which is easy with the GetStream method, which we load into an XmlReader so that it can be loaded into a XElement. This is a bit convoluted but it is just one line to get it from one type to another and the benefits of using LINQ to XML are worth it:

XElement sharedStringsElement = XElement.Load(XmlReader.Create(sharedStringsPart.GetStream()));

Now we have the ability to work with the XElement and do some real work. In the next parts, we’ll look at what we can do with it and how to get from a single part to an actual sheet.

25 Jul 2009

Gallery2 + C# - Beta 2 Available

A few weeks back I posted beta 2 of the gallery 2 .net toolkit where I have done considerable more work on it than I ever expected I would. Lots of need bits of code and features available. What’s in it now:

There are four items currently available:

  • (Tool) For people just wanting to export all their images out of Gallery2, there is g2Export which is a command line tool to export images.
  • (Tool) For people wanting to get information out of Gallery2 into a sane format, there is g2 Album Management which is an Excel 2007 add-in to export information about albums and images to Excel.
  • (API) For developers wanting to write their own tools or integrations, there is the SADev.Gallery2.Protocol which wraps the Gallery2 remote API. Please see the What you should know? page for information on using the API.
  • (Source) Lastly for developers needing some help, there is the source code for the the g2 Export Tool and the g2 Album Management Excel Add-in
Here is a screen shot of g2 Album Management in action:

Here is a screen shot of g2Export in action:

If you are interested in how much of the Gallery2 API is catered for, it’s most of it (the file upload parts are the only major outstanding ones). The key thing to note on the table is the tested column. While the code is written, it may not be tested and may not work at all. I have found the documentation is not 100% in line with the actual gallery2 code so something it needs considerable rework for it to actually work.

API Call Basic Request Basic Response Tested Advanced Request Advanced Response
login done done done done done
fetch-albums done done done done done
fetch-albums-prune done done done done done
add-item (upload)   done     done
add-item (url) done done   done done
album-properties done done done done done
new-album done done   done done
fetch-album-images done done done done done
move-album done done   done done
increment-view-count done done   done done
image-properties done done done done done
no-op done done done done done
25 Jul 2009

Proven Source Control Practises Poster

Proven Practises Poster

Maybe one of the toughest things in software development to get right all the time: source control. Well now with this nice bright A3 poster printed on your wall (or maybe above the monitor of the guy who breaks the builds daily) you’ll never go wrong again.

It covers 17 proven practises broken into 5 key areas:

Things YOU should do

  • Keep up to date
  • Be light and quick with checkouts
  • Don’t check in unneeded binaries
  • Working folders should be disposable
  • Use undo/revert sparingly


  • Plan your branching
  • Own the merge
  • Look after branches


  • Useful & meaningful check in messages
  • Don’t use the audit trial for blame


  • Don’t break the build
  • Separate your repo
  • Don’t forget to shelve
  • Use labels


  • Try concurrent access
  • Don’t be afraid of branching concepts
  • Automerge for checkout only
    25 Jul 2009

    ADO.NET Data Services Cheat Sheet (WCF Data Service)

    ADO.NET Data Services (WCF Data Services) Cheat Sheet

    Above is a screen shot of an A3 cheat sheet I created for ADO.NET Data Services (version 1). The poster covers the filters, methods and gives plenty of examples in a nice bright poster.

    Direct Download

    25 Jun 2009

    Gallery2 + C#

    Gallery2 is a web based PHP gallery system with a remote API for doing many things. I have been using it for a while, but have decided to change and so I wanted to export my images, which is harder than it sounds. To actually get this done I ended up writing a basic wrapper for the Gallery2 remote API and implementing a small console application to do the export.

    If you are interested in the wrapper or the tool itself, I have setup a CodePlex project for it where you can download those:

    The reason it is there, is because I have decided to open source it because it is useful to people besides me and I have gotten what I need from it, so I doubt I’ll spend much time getting it feature complete. This way someone else can get the tool (if that is all they need) or get the source and add to it.


    Screen shot of the tool running.