Introduction

I dislike Excel. A lot. It’s not that it isn’t a useful tool, it’s just that every time it crosses my path someone has tried to make it do something it really doesn’t want to do. Let’s face it, it’s not an RDBMS by any stretch of the imagination and it’s a long way from being a fully functional reporting engine. However, cross my path it does, and it’s likely to do so for the foreseeable future, so I might as well try and play nice with it.

As a follow up to Creating a Word Document with the Open XML SDK 2.0 I though I’d see how easy (or otherwise) it was to create a Workbook using similar tactics.

Creating the Console Application

I’ve added a second Console Application (imaginatively called WorkbookBuilder) to the Document Builder solution created last time round, and this time imported the following namespaces: –

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Firstly, add the following BuildWorkbook method to your new Program class.

private static void BuildWorkbook(string fileName)
{
    try
    {
        using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookPart = s.AddWorkbookPart();
            WorksheetPart worksheetPart = workbookPart.AddNewPart();
            string relId = workbookPart.GetIdOfPart(worksheetPart);
            Workbook workbook = new Workbook();
            FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();
            DateTime date = new DateTime(2009, 1, 1);
            int salesLastYear = 25185;
            int salesThisYear = 25348;
            for (UInt32 i = 1; i <= 52; i++)
            {
                Row contentRow = CreateContentRow(i, date, salesLastYear, salesThisYear);
                sheetData.AppendChild(contentRow);
                date = date.AddDays(7);
                salesLastYear += (int)(salesLastYear * 0.031);
                salesThisYear += (int)(salesThisYear * 0.027);
            }
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            s.WorkbookPart.Workbook = workbook;
            s.WorkbookPart.Workbook.Save();
            s.Close();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        Console.ReadLine();
    }
}

Second, add the CreateContentRow to the mix: –

private static Row CreateContentRow(UInt32 index, DateTime date, int salesLastYear, int salesThisYear)
{
    Row r = new Row { RowIndex = index };
    Cell cell1 = CreateTextCell("A", index, date.ToString());
    Cell cell2 = CreateNumberCell("B", index, salesLastYear);
    Cell cell3 = CreateNumberCell("C", index, salesThisYear);
    r.Append(cell1);
    r.Append(cell2);
    r.Append(cell3);
    return r;
}

Next, add the CreateTextCell and CreateNumberCell methods: –

private static Cell CreateTextCell(string header, UInt32 index, string text)
{
    Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
    InlineString istring = new InlineString();
    Text t = new Text { Text = text };
    istring.Append(t);
    c.Append(istring);
    return c;
}
 
private static Cell CreateNumberCell(string header, UInt32 index, int number)
{
    Cell c = new Cell { CellReference = header + index };
    CellValue v = new CellValue { Text = number.ToString() };
    c.Append(v);
    return c;
}

Finally, add the call to the BuildWorkbook method from the Main method: –

public static void Main(string[] args)
{
    BuildWorkbook(@"C:\Test.xlsx");
}

Hit F5 to run the application as before.

I’m sure you’ll agree that this is hardly the most involved of Workbooks but I have to admit I’m nonetheless impressed with the speed of execution of these applications. If you’ve ever had to use the Office Object Model you’ll know that just instantiating the Application Classes can be quite time consuming.

Score one for Excel? No, I don’t think so, this isn’t technically Excel!

References

Introduction

I’m always eager to find ways of making my life easier, so recently I’ve been searching for a method of creating a Microsoft Word document using purely managed code (none of that Object Model awfulness). I’d been playing around with version 1.0 of the Open XML SDK and while this works fine, it’s not strongly typed, so requires you to manipulate the XML directly.

Brian Jones has an excellent post about the SDK in his blog: – http://blogs.msdn.com/brian_jones/archive/2008/10/06/open-xml-format-sdk-2-0.aspx, which also includes an example of how to create a basic Word document in C#. This post hopes to demonstrate how to create a document that includes a header and an image.

If you’re following along with this, you’ll need the SDK if you don’t have it already, you can get it from: – http://go.microsoft.com/fwlink/?LinkId=127912.

Creating the Console Application

Open up Visual Studio and create a new Console Application. In this demo I’ve called it DocumentBuilder. Next add a reference to the DocumentFormat.OpenXML and WindowsBase dlls.

In this example, to include the image, I’ve stored it as a Resource, so add a new Resources File to your project (I’ve called it DocumentResources.resx) and add your image, the image should get copied into a new Resources folder in your project.

Add these using statements to the Program.cs file: –

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using d = DocumentFormat.OpenXml.Drawing;

Now add the following constants to the Program class: –

private const double EMU_PER_PIXEL = 9525;
private const string GRAPHIC_DATA_URI = @"http://schemas.openxmlformats.org/drawingml/2006/picture";

To keep a bit of structure about the program I’ve implemented three methods, one to build the document itself, one to build the header and finally one to insert the image. Firstly, add the following code for the BuildDocument method: –

private static void BuildDocument(string fileName)
{
    using (WordprocessingDocument w = WordprocessingDocument.Create(fileName, WordprocessingDocumentType.Document))
    {
        MainDocumentPart mp = w.AddMainDocumentPart();
        Document d = new Document();
        Body b = new Body();
        Paragraph p = new Paragraph();
        Run r = new Run();
        Text t = new Text();
        t.Text = "This is some body text.";
        r.Append(t);
        p.Append(r);
        b.Append(p);
        HeaderPart hp = mp.AddNewPart<HeaderPart>();
        string headerRelationshipID = mp.GetIdOfPart(hp);
        SectionProperties sectPr = new SectionProperties();
        HeaderReference headerReference= newHeaderReference();
        headerReference.Id = headerRelationshipID;
        headerReference.Type = HeaderFooterValues.Default;
        sectPr.Append(headerReference);
        b.Append(sectPr);
        d.Append(b);
        hp.Header = BuildHeader(hp, "This is some header text.");
        hp.Header.Save();
        mp.Document = d;
        mp.Document.Save();
        w.Close();
    }
}

Next, add the following for the BuildHeader method: –

private static Header BuildHeader(HeaderPart hp, string title)
{
    // Add an ImagePart.
    ImagePart ip = hp.AddImagePart(ImagePartType.Jpeg);
    string imageRelationshipID = hp.GetIdOfPart(ip);
    using (Stream imgStream = ip.GetStream())
    {
        System.Drawing.Bitmap logo = DocumentResources.sw;
        logo.Save(imgStream, System.Drawing.Imaging.ImageFormat.Jpeg);
    }
    Header h = new Header();
    Paragraph p = new Paragraph();
    Run r = new Run();
    Drawing drawing = BuildImage(imageRelationshipID, "sw.gif", 48, 48);
    r.Append(drawing);
    p.Append(r);
    r = new Run();
    RunProperties rPr = new RunProperties();
    TabChar tab = new TabChar();
    Bold b = new Bold();
    Color color = new Color { Val = "006699" };
    FontSize sz = new FontSize { Val = 40 };
    Text t = new Text { Text = title };
    rPr.Append(b);
    rPr.Append(color);
    rPr.Append(sz);
    r.Append(rPr);
    r.Append(tab);
    r.Append(t);
    p.Append(r);
    h.Append(p);
    return h;
}

Lastly, add the BuildImage method: –

private static Drawing BuildImage(string imageRelationshipID, string imageName,
    int pixelWidth, int pixelHeight)
{
    int emuWidth = (int)(pixelWidth * EMU_PER_PIXEL);
    int emuHeight = (int)(pixelHeight * EMU_PER_PIXEL);
    Drawing drawing = new Drawing();
    d.Wordprocessing.Inline inline = new d.Wordprocessing.Inline { DistanceFromTop = 0, DistanceFromBottom = 0, DistanceFromLeft = 0, DistanceFromRight = 0 };
    d.Wordprocessing.Anchor anchor = new d.Wordprocessing.Anchor();
    d.Wordprocessing.SimplePosition simplePos = new d.Wordprocessing.SimplePosition { X = 0, Y = 0 };
    d.Wordprocessing.Extent extent = new d.Wordprocessing.Extent { Cx = emuWidth, Cy = emuHeight };
    d.Wordprocessing.DocProperties docPr = new d.Wordprocessing.DocProperties { Id = 1, Name = imageName };
    d.Graphic graphic = new d.Graphic();
    // We don’t have to hard code a URI anywhere else in the document but if we don’t do it here 
    // we end up with a corrupt document.
    d.GraphicData graphicData = new d.GraphicData { Uri = GRAPHIC_DATA_URI };
    d.Pictures.Picture pic = new d.Pictures.Picture();
    d.Pictures.NonVisualPictureProperties nvPicPr = new d.Pictures.NonVisualPictureProperties();
    d.Pictures.NonVisualDrawingProperties cNvPr = new d.Pictures.NonVisualDrawingProperties { Id = 2, Name = imageName };
    d.Pictures.NonVisualPictureDrawingProperties cNvPicPr = new d.Pictures.NonVisualPictureDrawingProperties();
    d.Pictures.BlipFill blipFill = new d.Pictures.BlipFill();
    d.Blip blip = new d.Blip { Embed = imageRelationshipID };
    d.Stretch stretch = new d.Stretch();
    d.FillRectangle fillRect = new d.FillRectangle();
    d.Pictures.ShapeProperties spPr = new d.Pictures.ShapeProperties();
    d.Transform2D xfrm = new d.Transform2D();
    d.Offset off = new d.Offset { X = 0, Y = 0 };
    d.Extents ext = new d.Extents { Cx = emuWidth, Cy = emuHeight };
    d.PresetGeometry prstGeom = new d.PresetGeometry { Preset = d.ShapeTypeValues.Rectangle };
    d.AdjustValueList avLst = new d.AdjustValueList();
    xfrm.Append(off);
    xfrm.Append(ext);
    prstGeom.Append(avLst);
    stretch.Append(fillRect);
    spPr.Append(xfrm);
    spPr.Append(prstGeom);
    blipFill.Append(blip);
    blipFill.Append(stretch);
    nvPicPr.Append(cNvPr);
    nvPicPr.Append(cNvPicPr);
    pic.Append(nvPicPr);
    pic.Append(blipFill);
    pic.Append(spPr);
    graphicData.Append(pic);
    graphic.Append(graphicData);
    inline.Append(extent);
    inline.Append(docPr);
    inline.Append(graphic);
    drawing.Append(inline);
    return drawing;
}

All that remains is to call the BuildDocument from the Main method: –

public static void Main(string[] args)
{
    BuildDocument(@"C:\TempTest.docx");
}

Press F5 to run your app and you should get your Word document. If you’re using your own image in the header you’ll notice that it’s a bit mis-shapen (unless you happen to have picked a 48 x 48 pixel image). To correct this just change the pixelHeight and pixelWidth parameters passed into the BuildImage method to suit.

References

Introduction

If, like me, you have a bizarre fascination with maps you can’t have failed to notice that Google Maps are appearing on an ever-increasing number of websites, some for valid and useful reasons, some not so much. Now, if you head over to https://maps.google.com/ and zoom in on a major US city you might notice that there is a Traffic button at the top right of the map. Click this and you’ll get traffic data overlaid on the map, looking a bit like Figure 1 below.


Figure 1: New York Traffic

Now if you zoom in somewhere in the UK you will get this traffic info, unless you’re here in Scotland, but the overlays seem a bit off to me and I’ve yet to see any icons that will display message windows with more information. I’ve no doubt that Google will address these issues in due course, but in the meantime there is a way of getting detailed traffic info (albeit without the nice coloured overlays) onto a Google Map, namely TPEG.

What’s TPEG?

TPEG stands for Transport Protocol Experts Group and they have agreed standards for Binary and XML transmission of Traffic and Travel Information (TTI). For more information have a look at the TPEG links in the References section at the end of this post. In the UK, Auntie Beeb is helpfully publishing TPEG data in both Binary and XML formats (and you thought they were all about scamming phone voters). The XML document for road traffic information lives at http://www.bbc.co.uk/travelnews/tpeg/en/local/rtm/rtm_tpeg.xml and there’s a corresponding public transport document at http://www.bbc.co.uk/travelnews/tpeg/en/pti/pti_tpeg.xml. In the next few steps I’ll demonstrate how to display a subset of this data on a very simple ASP.NET website using Google Maps.

Creating the Website

I’m going to be using Visual Studio 2008 for this but 2005 will do just the same. Open up Visual Studio and create a new Web Site project. In addition to the App_Data folder that’s created for you, add an App_Code and a Bin folder to the project. Firstly, download the zip file from Subgurim.NET which wraps the functionality of a Google Map into an ASP.NET web control and extract the GMaps.dll into the Websites Bin folder.

Next, get yourself a copy of the road traffic information XML file (just download it from the link above) and copy it into the App_Data folder.

Now, to make working with the data a little bit easier, we’ll create a schema definition for the XML (we can’t download one because it’s based on a DTD currently) and then use xsd.exe to get ourselves a class file. Open up the rtm_tpeg file in Visual Studio, go to the XML menu and select Create Schema. When you get the resulting rtm_tpeg.xsd file on the screen save that into the App_Data folder too.

Open the Visual Studio Command Prompt, navigate to your App_Data folder, and execute the following command to create a C# class in your App_Code folder: xsd.exe rtm_tpeg.xsd /c /language:cs /o:../App_Code. If you’re working in VB.NET simply change the language switch to /language:vb. The structure of your Website should new resemble Figure 2.


Figure 2: Website Structure

Now, we can add the map control to our web page and start adding information windows to it. Open up your default.aspx page and edit it so that it resembles the following: –

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="default.aspx.cs" Inherits="_default" %>
<%@ Register Assembly="GMaps" Namespace="Subgurim.Controles" TagPrefix="gmap" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml">
    <head runat="server">
        <title>TPEG and Google Maps</title>
        <style type="text/css">
        html, body
        {
            margin: 0px 0px 0px 0px;
            padding: 0px 0px 0px 0px;
            height: 100%;
        }
        v:*
        {
            behavior: url(#default#VML);
        }
        </style>
    </head>
    <body>
        <form id="mapForm" runat="server">
            <gmap:GMap ID="map" runat="server" Width="100%" Height="100%" />
        </form>
    </body>
</html>

Open the default.aspx.cs and modify it to look like: –

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Schema;
using System.Xml.Serialization;
using Subgurim.Controles;
 
public partial class _default : System.Web.UI.Page
{
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            map.setCenter(new GLatLng(55.864536, -4.263103));
            map.GZoom = 12;
            map.addControl(new GControl(GControl.preBuilt.SmallMapControl));
            string uri = “http://www.bbc.co.uk/travelnews/tpeg/en/local/rtm/rtm_tpeg.xml”;
            BindData(uri);
        }
    }
 
    private void BindData(string uri)
    {
        XmlDocument xml = new XmlDocument();
        xml.Load(uri);
        XmlNodeReader nr = new XmlNodeReader(xml.DocumentElement);
        XmlSerializer xs = new XmlSerializer(typeof(tpeg_document));
        tpeg_document tpeg = (tpeg_document)xs.Deserialize(nr);
        for (int i = 0; i < tpeg.Items.Length; i++)
        {
            if (tpeg.Items[i] is tpeg_message)
            {
                tpeg_message message = tpeg.Items[i] as tpeg_message;
                if (message.Item is road_traffic_message)
                {
                    road_traffic_message rtmessage = message.Item as road_traffic_message;
                    for (int j = 0; j < rtmessage.Items.Length; j++)
                    {
                        if (rtmessage.Items[j] is location_container)
                        {
                            location_container loc = rtmessage.Items[j] as location_container;
                            for (int k = 0; k < loc.Items.Length; k++)
                            {
                                if (loc.Items[k] is location_coordinates)
                                {
                                    location_coordinates coords = loc.Items[k] as location_coordinates;
                                    for (int l = 0; l < coords.Items.Length; l++)
                                    {
                                        if (coords.Items[l] is WGS84)
                                        {
                                            WGS84 latLong = coords.Items[l] as WGS84;
                                            double latitude = double.Parse(latLong.latitude);
                                            double longitude = double.Parse(latLong.longitude);
                                            StringBuilder sb = new StringBuilder();
                                            sb.Append(<p>);
                                            sb.Append(message.summary[0].Value);
                                            sb.Append(</p>);
                                            GMarker marker = new GMarker(new GLatLng(latitude, longitude));
                                            map.addInfoWindow(new GInfoWindow(marker, sb.ToString(), false));
                                            map.addGMarker(marker);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Briefly, we set centre the map on Glasgow, set the zoom level and add the small map control. Set the uri parameter to the XML TPEG feed (if you find that this takes a while to download you can always use the saved version in your App_Data folder). The BindData() method de-serialises the XML into our tpeg_document class and then starts to loop through the road traffic messages in the document. Without going into the gory details of the TPEG schema, the message contains a summary, which we write to the infoWindow, and location co-ordinates which allow us to add the marker icon in the right place.

Hit the F5 key to run the application, allowing Visual Studio to modify your web.config file if you like, and marvel (if you’re easily impressed) at live traffic information on a Google Map.


Figure 3: Glasgow Traffic

What Next

You’ll notice on the figure above that you often get two markers closely spaced on the same stretch of road. These tend to be the start and end points of heavy congestion and Google would display these as coloured overlays on the map. We can render a polyline on top of the map given a start and end point but the problem with that is it will be a straight line and won’t follow the road (probably). If you can find out how to overlay a line following the road then you’d be getting pretty close to what Google can do.

References

Maps

TPEG

Introduction

I seem to be putting an ever increasing amount of appSettings keys into my web and app.config files these days, and often these keys take the form of a list of settings that can be grouped together and apply to a single object/class. For example, we had an application that generates a report containing Excel chart objects and the colour palettes for these charts need to be user, or at least client, configurable. To achieve this with appSettings keys only we’d end up with a list resembling the following: –

<add key="chart9index1" value="Blue1"/>
<add key="chart9index2" value="Orange1"/>
<add key="chart9index3" value="Green1"/>
<add key="chart9index4" value="Pink1"/>
<add key="chart9index5" value="Blue3"/>
<add key="chart9index6" value="Orange3"/>

These settings define the colours used for a single chart in the report, so we’d need a list like this for each chart in the application. The first issue with this approach is that the list gets unwieldy pretty quickly; imagine if the application had twenty or so charts (which it does). Secondly, we’re tied to the one key/one value structure, suppose we also wanted to define what the red, green and blue components were of the colour Blue1. We would need something along the lines of: –

<add key="Blue1R" value="63"/>
<add key="Blue1G" value="166"/>
<add key="Blue1B" value="204"/>

Subsequently, we’d then need to write the code to read these values and associate them with the colour Blue1. Wouldn’t it be nicer if we could write something like: –

<add key="Blue1" R="63" G="166" B="204"/>

Well, you can’t, at least, not in the appSettings section of your config file. You can however create your own configuration section and do exactly this.

The configSections element

<configuration>
  <configSections>
    <sectionGroup name="colors">
      <section name="custom" type="StuartWhiteford.Reporting.Custom, StuartWhiteford.Reporting"/>
      <section name="palettes" type="StuartWhiteford.Reporting.ReportConsole.Palettes, StuartWhiteford.Reporting.ReportConsole"/>
    </sectionGroup>
  </configSections>
  ...
</configuration>

Within our main configuration element we’ve added a configSections element. This will contain the definitions for any custom configuration sections that we want to include. Within this element we have a sectionGroup element and named it colors. The sectionGroup isn’t a requirement, but it does give us a way of organising our sections logically.

The ConfigurationSection class

Delving deeper, we get to the section elements, and you’ll notice that these elements have a type attribute. This identifies the classes that we will de-serialise our subsequent configuration entries into. With hindsight giving a class the name Custom is a bit daft, it tells you precisely nothing about it. However, the code for this class is as follows: –

public class Custom : ConfigurationSection
{
    public static Custom GetConfig()
    {
        return ConfigurationManager.GetSection("colors/custom") as Custom;
    }
 
    [ConfigurationProperty("customColors")]
    public CustomColorCollection CustomColors 
    {
        get
        {
            return this["customColors"] as CustomColorCollection;
        }
    }
}

Not much to it. Firstly, we’ve inherited from the ConfigurationSection class, which provides the mechanisms for serialising and de-serialising to and from our config file. Secondly we require a way to access this data in code, so we have the GetConfig method that returns an object of type Custom, de-serialised from the information contained in the section with the name custom that resides in the sectionGroup colors. Generically we’d call ConfigurationManager.GetSection(“<sectionGroup name>/<section name>”) as <Class> omitting the sectionGroup if necessary.

Lastly, we define what we can put into our custom section. In this case we have a customColors element which de-serialises as a CustomColorCollection object, shown below: –

public class CustomColorCollection : ConfigurationElementCollection
{
 
    public CustomColor this[int index]
    {
        get
        {
            return base.BaseGet(index) as CustomColor;
        }
        set
        {
            if (base.BaseGet(index) != null)
            {
                base.BaseRemoveAt(index);
            }
            this.BaseAdd(index, value);
        }
    }
 
    public CustomColor this[object name]
    {
        get
        {
            return base.BaseGet(name) as CustomColor;
        }
    }
 
    protected override ConfigurationElement CreateNewElement()
    {
        return new CustomColor();
    }
 
    protected override object GetElementKey(ConfigurationElement element)
    {
        return ((CustomColor)element).Name;
    }
 
}

Since this is a collection object, the two public properties allow us to retrieve a CustomColor object either by its index or name. The CreateNewElement method returns a new instance of the CustomColor class and finally the GetElementKey method identifies which property of our CustomColor class we will use as the key in the collection, in this case we’ll use the Name. The CustomColor class is as follows: –

public class CustomColor : ConfigurationElement
{
 
    [ConfigurationProperty("name", IsRequired = true)]
    public string Name
    {
        get
        {
            return this["name"] as string;
        }
    }
 
    [ConfigurationProperty("R", IsRequired = true)]
    public int R
    {
        get
        {
            return (int)this["R"];
        }
    }
 
    [ConfigurationProperty("G", IsRequired = true)]
    public int G
    {
        get
        {
            return (int)this["G"];
        }
    }
 
    [ConfigurationProperty("B", IsRequired = true)]
    public int B
    {
        get
        {
            return (int)this["B"];
        }
    }
 
    public Color ToColor()
    {
        return Color.FromArgb(this.R, this.G, this.B);
    }
 
    public int ToColorRef()
    {
        return (this.R + (this.G << 8) + (this.B << 16));
    }
 
}

We have four properties: Name, R, G and B, all hopefully self-explanatory. We also have two methods that aren’t exposed in the XML.ToColor(), which returns a System.Drawing.Color object consisting of the defined red, green and blue values and ToColorRef(), which returns an integer that Excel understands as a colour.

The final piece of the puzzle to make this all work is the section in the config file that will describe our custom colours: –

<colors>
  <custom>
    <customColors>
      <add name="Blue1" R="63" G="166" B="204"/>
      <add name="Blue2" R="108" G="188" B="216"/>
      <add name="Blue3" R="155" G="210" B="229"/>
      <add name="Blue4" R="210" G="234" B="242"/>
      <add name="Orange1" R="240" G="106" B="0"/>
      <add name="Orange2" R="243" G="152" B="68"/>
      ...
    </customColors>
  </custom> 
</colors>

Now, in our application code, we can call CustomColorCollection _colors = Custom.GetConfig().CustomColors; and we’ve loaded all our stunning custom colours into a single object. No more messing around with ConfigurationManager.AppSettings[] calls and string manipulation. If we need the Blue2 Color object all we need to do is call _colors[“Blue2”].ToColor();

Conclusion

Granted, this is a fair amount of work for something so simple, but hopefully it has shown how you can store application configuration data, that you might have previously used a database for, in its proper place.

Introduction

There’s nothing particularly original about creating a PRODUCT function in SQL Server using the CLR. Follow this link to see how to implement one. Recently, however, I had the need to return an annualised product of a set of values. For example, consider the following table of quarterly percentage return figures: –

Date Return
30/06/2006 5.5 %
30/09/2006 7.0 %
31/12/2006 3.1 %
31/03/2007 1.8 %
30/06/2007 1.0 %
30/09/2007 2.2 %
31/12/2007 0.8 %
31/03/2008 6.5 %

The percentage return over the period as a whole would be: –

(((1.055 * 1.07 * 1.031 * 1.018 * 1.01 * 1.022 * 1.008 * 1.065) – 1) * 100) = 31.29 %

and to calculate the annual return we need to raise this result to the power of ½ (if we had three years of data then we’d use ⅓).

31.29 % ^ 0.5 = 14.58 %

Now the T-SQL to do this isn’t particularly difficult, but it can get pretty untidy, so what follows is a CLR implementation written in C#.

Aggregate functions are defined as structs. The struct should be marked as serializable so that SQL Server 2005 can store a representation of the aggregate on disk and the SqlUserDefinedAggregate attribute indicates that this is a user-defined aggregate. The Format.Native property tells the Framework to handle the serialization process using a simple algorithm and it works only with value types which is why the aggregate is defined as a struct. Within our struct, we have defined four private variables: –

  • _product: holds the running total of the product of all the values currently aggregated.
  • _annualisedProduct: takes the value of the _product variable and, if the _count of values is greater than the defined _num value, raises the value to the power of the _num divided by the _count.
  • _num: is the number of periods in one year. For this function (quarterly) the value is 4, to implement a monthly variation the value would be 12.
  • _count: stores the running count of the values in the set.

Within the struct, four methods need to be implemented: –

Init()
The Init() method initializes any variables or resources required and is called once when the aggregate function is invoked. Our Init() method set the _product variable to 1, _num to 4 and _count to 0.
Accumulate()
The Accumulate() method contains the implementation of the aggregate function and is called once for each row. Our implementation increments the _count variable, calculates the current _product and then determines the _annualisedProduct value.
Merge()
If the column to aggregate contains a large number of rows, SQL Server may split the rows into groups, aggregate each group and then merge the results of each group.
Terminate()
The Terminate() function is called to free any resources used and returns the result of the function, the _annualisedProduct.

Below is the final code for the struct: –

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 300, IsNullIfEmpty = true)]
public struct AnnualisedProductQuarterlyData : IBinarySerialize
{ 
 
    private Decimal _product;
    private Decimal _annualisedProduct;
    private Int32 _num;
    private Int32 _count;
 
    public void Init()
    {
        _product = 1;
        _num = 4;
        _count = 0;
    }
 
    public void Accumulate(SqlDecimal value)
    {
        _count++;
        _product = _product * (Decimal)value;
        if (_count > _num)
        {
            _annualisedProduct = (Decimal)Math.Pow((Double)_product, (Double)(_num / _count));
        }
        else
        {
            _annualisedProduct = _product;
        }
    }
 
    public void Merge(AnnualisedProductQuarterlyData Group)
    {
        Accumulate(Group.Terminate());
    }
 
    public SqlDecimal Terminate()
    {
        return _annualisedProduct;
    }
 
#region IBinarySerialize Members
 
    public void Read(System.IO.BinaryReader r)
    {
        _annualisedProduct = r.ReadDecimal();
    }
 
    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(_annualisedProduct);
    }
 
#endregion
 
}

The T-SQL to register the assembly and functions in your database is as follows: –

CREATE ASSEMBLY [StuartWhiteford] 
FROM C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\StuartWhiteford.SQLServer.dll'
WITH PERMISSION_SET = SAFE
 
CREATE AGGREGATE [dbo].[Product] (@input decimal(38,10))
RETURNS decimal(38,10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.Product];
 
CREATE AGGREGATE [dbo].[AnnualisedProductQuarterlyData] (@input decimal(38, 10))
RETURNS decimal(38, 10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.AnnualisedProductQuarterlyData]
 
CREATE AGGREGATE [dbo].[AnnualisedProductMonthlyData] (@input decimal(38, 10))
RETURNS decimal(38, 10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.AnnualisedProductMonthlyData]