Calculate Age from Date Of Birth in SSIS

Just responded to an interesting question about working out someone's age from their date of birth in an SSIS derived column transform. My first reaction was to use a script task (I've done it so often in C# that I can do it without thinking). But I thought it'd be nice to work out how to do it in a devired column. So here it is:


YEAR(GETDATE()) - YEAR([dobcolumn]) -
(SIGN
  (DATEPART ("dy", [dobcolumn]) -
   DATEPART ("dy", GETDATE()))
     == 1 ? 1 : 0)


The magic is all in the last part there. Translated into english, it says "subtract the day of the year of the dobcolum and the day of the year of today's date. If that result is positive (i.e. the dob is later in the year than today), then subtract 1 from the total". The ternary operator was the magic bit.

StringFormats in Binding Expressions

Do I have a bee in my bonnet about Culture settings? Maybe I do, but in this case it's a little annoying and it's to do with String.Format (quite possibly my favourite method in the whole of the .NET framework). So in WPF, if I do something like:

MessageBox.Show(string.Format("Price: {0:C}", 123.45));

I get a little message box showing me "Price: £123.45" (because my regional settings are en-GB, you see).

If, however, I use a binding expression like this one:

Binding="{Binding Path=Price, StringFormat={}{0:C}}"

It doesn't matter what my regional settings are, it'll always show up as USD. Now I can kind of see why this might be - the binding expression is stored in an xml file and of course xml files can have language settings, but it does smack to me of a bit of a miss, a dare-I-say-it, "design feature". After a bit of digging around, notably this forum discussion, I came up with the following, less coding solution. Place the following code in your App.xaml code-behind:

protected override void OnStartup(StartupEventArgs e)
{
  FrameworkElement.LanguageProperty.OverrideMetadata(
    typeof(FrameworkElement),
    new FrameworkPropertyMetadata(
      XmlLanguage.GetLanguage(
        CultureInfo.CurrentCulture.IetfLanguageTag)));

  base.OnStartup(e);
}

Clearly, this will only work if you want EVERY element to use your local settings.

Regular expression for changing DateTime.Parse(mm/dd/yyyy) into new DateTime(yyyy, mm, dd)

In my previous post I bemoaned my lack of Regex skills and the fact that the VS2010 Training Kit startdates were fixed to use DateTime.Parse() rather than new DateTime(). I always use new DateTime() because it removes the mm/dd versus dd/mm problem. I've revisited the problem, in part to try and improve my Regex skills, and here is my solution.

Pattern:

DateTime\.Parse\("(\d{1,2})/(\d{1,2})/(\d{4})"\)

Replace:

new DateTime($3, $1, $2)

Good news / bad news time. That will work programmatically using the .NET Framework's Regex.Replace() method, BUT NOT in find / replace. Curses! The curlies get read as a "tag" by the find/replace tool, by which they mean capture group. Also, "\d" doesn't appear to be recognised and needs to be replaced with ":d". So that means I need to ask it to find one or two digits, then one or two digits, then four digits. I can use a hat "^" to specify a count. Finally, rather than using the "$" to place a capture group in the replace, it wants a "\". So that leaves my pattern looking like this:

DateTime\.Parse\("{(:d|:d^2)}/{(:d|:d^2)}/{:d^4}"\)

and leaves my replace looking like this:

new DateTime(\3, \1, \2)

Phew! I'm going for a lie down...

VS2010 Training Kit - US-centric hardcoded dates

Finally got my virtual machine with Windows 7 and VS2010 beta sorted so I started working through the Training Kit, wanting to mess with the Parallel stuff. Ran the first code snippet and BANG! I'm getting a parse exception. Turns out the startdates for the Employees are being created using (e.g.):

DateTime.Parse("5/16/2001")

Which is fine in the States but no use on my en-GB machine. So my first instinct was to run a regular expression to turn them all into (e.g.):

new DateTime(2001, 5, 16)

But then I realised that my RegEx skills are not good enough and I wanted to get the job done before my train home finally arrived. After umming and ahhing for a few moments I remembered that the problem was my culture settings. So I thought I have two options - either change my culture settings (BOO!) or change the constructor's culture settings.

So I stuck this at the start of the EmployeeList constructor:

var ct = Thread.CurrentThread;
var origCulture = ct.CurrentCulture;
ct.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");

And this at the end:


ct.CurrentCulture = origCulture;

Yay!

Launcher Window WPF UserControl

I've been prepping a WPF course this week and in the course of that prep I've been putting together a bunch of demos, frequently using more than one window for each demo. To make it easier to run those demos, I found myself creating a "Launcher Window" - basically a "switchboard" style window - with a bunch of buttons on it, one for each window in the project.


After I'd done it twice, I suddenly realised that I was duplicating my efforts and my rule of thumb is, "if you do it more than once, create a class to do it for you."
So here's what I came up with. A LauncherControl, which uses reflection to get a list of windows in the calling assembly and then adds a button for each one to the control.


As I can't upload files, I'll just post the markup and code and you can build one yourself.

1. Create a WPF User Control Library. Call it what you like. Mine's called Widgets.


2. Delete the UserControl1 and add a new UserControl called LauncherControl.


3. Change the root layout into a StackPanel, or whatever takes your fancy and call it controlRoot.


4. Tell the controlRoot panel to handle all Click events for all its buttons (that'll save me from having to manually hook up event handlers for every button I create later as I would in WinForms)


<UserControl x:Class="Widgets.LauncherControl"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
<StackPanel Name="controlRoot" Button.Click="ButtonClick">

</StackPanel>
</UserControl>


5. Switch to LauncherControl.xaml.cs and code it up

What we need to do in here is to get the UserControl to look at all the types in the assembly that's using the control and find all of the ones that derive from Window. I'm also checking to ensure that if I have a window in the calling assembly called Launcher, it gets ignored (v2.0 - have a DP called StringsToIgnore or something).

Took me a little while to figure out which method of Assembly to call - my first stab was GetCallingAssembly, but that ended up being mscorlib.dll, so GetEntryAssembly gives me the assembly that contains the app's entry point. Obvious really...
Having done that, I'm going to create a new btn with a name of the window class's name, and display some nice(ish) content on the button using a string extension method (InjectSpaces) which is defined further down. (v2.0 - instantiate each Window class and ask it for its Title property). Finally, I'm going to add that button to the controlRoot panel:


public LauncherControl()
{
    InitializeComponent();
    Assembly assy = Assembly.GetEntryAssembly();
    var qry = from t in assy.GetTypes()
        where t.BaseType == typeof(Window) &&
            !t.Name.Contains("Launcher")
        orderby t.Name
        select t;
    foreach (var item in qry)
    {
        Button btn = new Button();
        btn.Name = item.Name;
        btn.Margin = new Thickness(5);
        btn.Content = item.Name.InjectSpaces();
        controlRoot.Children.Add(btn);
    }
}


OK, now we can implement the ButtonClick event handler. What we need to do here is grab the Source of the RoutedEvent (which will be whatever button was clicked to raise the event), find out its name, which you will recall is the same as the name of the Window class, get that Type from the EntryAssembly and use Activator to instantiate it.


Finally, call its ShowDialog() method.
(v2.0 - tweak performance by making the EntryAssembly static)


private void ButtonClick(object sender,
    RoutedEventArgs e)
{
    Button source = e.Source as Button;
    if (source == null)
    {
        return;
    }
    Type t = Assembly.GetEntryAssembly().GetTypes()
        .First(bType => bType.Name == source.Name);
    Window w = Activator.CreateInstance(t) as Window;
    w.ShowDialog();
}



6. And here's that string extension method to prettify the window's name:
   

   
   static class StringExtensions
   {
       public static string InjectSpaces(
           this string stringToFormat)
       {
           StringBuilder sb = new StringBuilder();
           char[] chars = stringToFormat.ToCharArray();
           for (int i = 0; i < chars.Length; i++)
           {
               if (i == 0)
               {
                   sb.Append(chars[i]);
               }
               else if (char.IsUpper(chars[i]))
               {
                   sb.AppendFormat(" {0}", chars[i]);
               }
               else
               {
                   sb.Append(chars[i]);
               }
           }
           return sb.ToString();
       }
   }
   

   
   So now I can just add a reference to Widgets.dll to every project I want to use the launcher in, and have the startupuri be a window called LauncherWindow, which contains that control.
   
   
   But then I thought, hang on, there's some more duplication there. Let's take this one step further and have the LauncherWindow contained in the Widgets lib, so I can just set that as the StartupUri and cut out the middleman if I want to. So here goes:
   
   
   
7. Add a Window to the Widgets library, called LauncherWindow. It should look like this:

<Window x:Class="Widgets.LauncherWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="clr-namespace:Widgets"
Title="LauncherWindow" Height="300" Width="300">
<StackPanel>
<local:LauncherControl/>
</StackPanel>
</Window>
OK, so we've got the Window hosting the UserControl. I felt this was an important way to do it, rather than re-jigging the UserControl as Window, because consumers get a choice of how to use it - they might want a switchboard form with other stuff on it, or just the basic switchboard. Choice is all important!


Finally, I had to work out how to get a client App to use the LauncherWindow for it's StartupUri. I won't share how many attempts I made before finally succumbing to rtfming; suffice to say that I checked out MSDN and found the solution - pack uris! Following is the setting for StartupUri if you've called your widgets project Widgets and your switchboard window LauncherWindow. I'm sure you're clever enough to figure out the differences if you didn't...



8. 
   StartupUri="pack://application:,,,/Widgets;component/LauncherWindow.xaml"
   

Not bad eh? There may be one or two problems with this code which I may or may not come back and correct. But that should do it for now...

switch statement auto-fill for enums in C#

Just a handy-dandy little tip for working with enums and switch statements in Visual Studio.

Let's say I have an enum defined somewhere:

public enum Genders { Female, Male, Neuter }

If I then declare a variable of the enumerated type:

...
Genders g;
...

And then create a switch statement using the switch code snippet (i.e. type the word “switch” and then press tab) I get the following:

switch (switch_on)
{
  default:
}

and the word “switch_on” will be selected.

Change that switch_on to your enum variable’s name, in this case g, and then press the down-arrow cursor key (other keystrokes might work, but I know that down-arrow definitely does) and VS will automagically fill in the switch statement with the values:

switch (g)
{
  case Genders.Female:
    break;
  case Genders.Male:
    break;
  case Genders.Neuter:
    break;
  default:
    break;
}

Cool, eh?