Simulating the Earth Tones Palette in SQL Reporting Services

For a task at work I had to create a table in an SQL Server Reporting Services report that simulated the colours used in the Earth Tones colour palette. I tried to find some documentation that would tell me, and then tried to find out programmatically, finally after a bit of number crunching and research I worked it out manually.

So you don't have to - here is a handy SQL query that returns the colours in order

SELECT 1 As [Index], 'DarkOrange' AS Colour UNION
SELECT 2, 'DarkGoldenRod' UNION
SELECT 3, '#C04000' UNION -- Mahogany
SELECT 4, 'OliveDrab' UNION
SELECT 5, 'Peru' UNION
SELECT 6, '#C0C000' UNION -- DarkYellow
SELECT 7, 'ForestGreen' UNION
SELECT 8, 'Chocolate' UNION
SELECT 9, 'Olive' UNION
SELECT 10, 'LightSeaGreen' UNION
SELECT 11, 'SandyBrown' UNION
SELECT 12, '#00C000' UNION -- Dark Green
SELECT 13, 'DarkSeaGreen' UNION
SELECT 14, 'FireBrick' UNION
SELECT 15, 'SaddleBrown' UNION
SELECT 16, '#C04000' -- DarkRed

Stop a Number Being Multiplied by 100 When the '%' Custom Number Format Character is Used

When formatting a number with the % character the value is multiplied by 100 before it is displayed. This is not always the desired behaviour as percentage values are often stored as integers. I.E 15% might be stored in a database as 15. The easiest way to get the character to work in this way is simply to place a single quote character ' before the percentage sign. A quick example follows.

static void Main(string[] args)
{
    var value = 15;
    Console.WriteLine("{0:#,##0.00%}", value);
    Console.WriteLine("{0:#,##0.00'%}", value);
    Console.ReadLine();
}

Gives the output:

1,500.00%
15.00%

Concatenate Values in a Column in a Child Table so they Appear as one Value when Joined to the Parent Table

Here is some code that might make your SQL Server reporting queries a bit simpler. The solution given here allows this task to be completed uning a join rather than involving variables and function calls.

First I'll set up some sample data

SET NOCOUNT ON

CREATE TABLE Family
(
    FamilyID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    FamilyName VARCHAR(20) NOT NULL
)

CREATE TABLE FamilyMember
(
    MemberID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    FamilyID INT NOT NULL REFERENCES Family (FamilyID),
    MemberName VARCHAR(20) NOT NULL
)

DECLARE @LastID INT

INSERT INTO Family(FamilyName) VALUES ('Brunger')
SET @LastID = @@IDENTITY
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'David')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Katherine')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Jack')

INSERT INTO Family(FamilyName) VALUES ('Bailey')
SET @LastID = @@IDENTITY
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Elizabeth')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Chris')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Lucy')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'James')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Matty')

SELECT * FROM Family
SELECT * FROM FamilyMember

Now comes the clever query using the FOR XML clause to do the concatenation for us, but ensuring that no XML tags are inserted into the resultant string. The CROSS APPLY clause calls the enclosed "function" for each row in the result set.

SELECT
    FamilyName,
    -- 1 is subtracted from the length of Members rather
    -- than 2 as the LEN() function ignores the training
    -- whitespace
    LEFT(Members, LEN(Members) - 1) AS Members
FROM
    Family CROSS APPLY
    (
        SELECT
            -- No column alias such as [text()] or "*"
            -- is necessary here as the concatenation
            -- operation means that the the column is
            -- already nameless
            MemberName + ', '
        FROM
            FamilyMember
        WHERE
            FamilyMember.FamilyID = Family.FamilyID
        -- The empty string here means that now 
        -- tag is generated for each row
        FOR XML PATH('')
    ) AS Data(Members)

Now tidy up.

DROP TABLE FamilyMember
DROP TABLE Family

This gives the following output:

FamilyID    FamilyName
----------- --------------------
1           Brunger
2           Bailey

MemberID    FamilyID    MemberName
----------- ----------- --------------------
1           1           David
2           1           Katherine
3           1           Jack
4           2           Elizabeth
5           2           Chris
6           2           Lucy
7           2           James
8           2           Matty

FamilyName           Members
-------------------- --------------------------------------
Brunger              David, Katherine, Jack
Bailey               Elizabeth, Chris, Lucy, James, Matty

URL Rewriting for Apache/PHP

Here's a good short introduction to URL rewriting in Apache, and how to make use of it in a PHP application.
http://www.phpaddiction.com/tags/category/url-routing/

Find the Row of a Control in a DataGrid in its Click Event

The Click Event in DataGrids has reared its ugly head again. A colleague of mine has spent hours searching the internet, and there doesn't seem to be a solution to this one. Using the control's ClientID or UniqueID properties will not work as the row does not have an id. The simple solution is use the control's NamingContainer property. If you go back through the hierarchy you can also get to the containing grid. To make life easier I wrote an extension method that also performs the cast to the desired container.

public static class ControlExtensions
{
    public static TControl GetNamingContainer<TControl>(this Control control) where TControl : Control
    {
        var namingContainer = control.NamingContainer;
        if (namingContainer == null)
        {
            return null;
        }
        if (namingContainer is TControl)
        {
            return (TControl)namingContainer;
        }
        return GetNamingContainer(namingContainer);
    }
}

Get a control's row by calling the extension method.

protected void LatestCheckBox_CheckedChanged(object sender, EventArgs e)
{
    var checkBox = (CheckBox)sender;
    var row = checkBox.GetNamingContainer<DataGridItem>();

    var control = row.FindControl("TestLabel");
    if ((control != null) && (control is Label))
    {
        var label = (Label)control;
        label.Text = checkBox.Checked ? "Checked" : "Not Checked";
    }
}

Generic Singleton

I've no idea why a generic singleton class is not available in the .Net framework, so I had to write my own. Here's the result.

public class Singleton<T> where T : Singleton<T>
{
    private static object instanceLock = new object();
    private static T value;

    public static T Value { get { return GetValue(); } }

    private static T GetValue()
    {
        lock (instanceLock)
        {
            if (value == null)
            {
                value = (T)Activator.CreateInstance(typeof(T), true);
            }
        }
        return value;
    }
}

To use it define a sealed subclass with a private constructor and away you go. For example

public sealed class ContextSingleton : Singleton<ContextSingleton>
{
    private ContextSingleton() { }
}

If you have got a few extra minutes, it might be worth checking to ensure that the subclass is sealed and has no public constructors at runtime in the GetValue() method.

Building Expression Trees

Don't do what I did and implement an Expression Tree Visitor by hand. I was creating an application that used LINQ to SQL to connect to a database, but I wanted to customise the generated SQL queries. Use this very useful bit of code provided on the MSDN website:
http://msdn.microsoft.com/en-us/library/bb882521.aspx

Listing All Controls on a Form and Flattening Hierarchies

For an application I'm developing it is necessary to enumerate my way through all the controls of a form. Using the Controls property only gives the immediate children, so it must be called recursively in order to retrieve all the controls. This started me thinking. Wouldn't it be easier to have a function that could flatten out any hierarchy so it could be easily enumerated?

At first I thought I'd just used IEnumerable.SelectMany, but again I fell foul of the deferred execution. Also some nasty jiggery-pokery would have been necessary to ensure I just didn't end up enumerating only the controls at the bottom of the hierarchy.

My solution is this simple method:

IEnumerable<T> Flatten<T>(T parent, Func<T, IEnumerable<T>> childFunction)
{
    yield return parent;
    foreach (T t in childFunction(parent))
    {
        foreach (T u in Flatten(t, childFunction))
        {
            yield return u;
        }
    }
}

To call this method so it pops up a message box containing the names of all the controls on a form (including the form itself) add a method to the form similar to this:

private void button1_Click(object sender, EventArgs e)
{
    var controls = Flatten<Control>(this, c => c.Controls.Cast<Control>());
    var builder = new StringBuilder();
    controls.ForEach(c => builder.AppendLine(c.Name));
    MessageBox.Show(builder.ToString());
}

For an explanation of the ForEach method see my previous post.

Perform an Action for each value in a Collection

I really don't know why there is no ForEach method in LINQ. If you want to perform an action on every item in a collection you have to use the TAccumulate Aggregate<TSource, TAccumulate>(TAccumulate, Func<TAccumulate, TSource TAccumulate>) method in order to avoid deferred execution, but calling it with a simple action leads to unnecessary ugly and hard to understand code.

To solve this issue just create a new extension method that wraps the call Aggregate call:

public static class IEnumerableExtensions
{
    public static void ForEach<TSource>(this IEnumerable<TSource> source, Action<TSource> action)
    {
        source.Aggregate<TSource, object>(null, (dummy, item) => { action(item); return null; });
    }
}