Nicke Andersson

Creating more SQL Server 2005 date functions

TSQL is great, but there's a couple of date-related functions I almost always miss when I'm working with it, at least if I'm doing something related to reporting and BI. Many things need to be displayed per week or quarter, and especially weeks is something that SQL Server isn't very good at. DATEPART doesn't work correctly outside of the US when it comes to week/ww/wk -parts, because it doesn't support ISO 8601 correctly. Years ago I implemented a correct week function as a stored procedure, but now I wanted to do the same in C# in a user defined function and run it through SQL CRL integration.

The first problem I ran into was that the .net Framework claims to implement ISO 8601, but doesn't. Doh! I blogged about this in my last post, "GetWeekOfYear is not reliable in .NET". But with the help of a good post by Shawn Steele I was able to implement a working version.

Creating a user defined function in SQL Server 2005

In Visual Studio there's a project template called "SQL Server Project" that let's you create user defined functions among other things.

Once you've created a project based on that template (and specified a database connection) you can right-click on the project node in the Solution Explorer and select "Add" and then "User Defined Function...". Give it a filename (for example "MyDateFunctions.cs"), and click "Add". This gives you a sample function that in at least Visual Studio 2008 looks like this:

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6:  

   7: public partial class UserDefinedFunctions

   8: {

   9:     [Microsoft.SqlServer.Server.SqlFunction]

  10:     public static SqlString Function1()

  11:     {

  12:         // Put your code here

  13:         return new SqlString("Hello");

  14:     }

  15: };

If you now right-click on the project node in the Solution Explorer and click "Deploy" the code will be compiled, and the function will be deployed to the SQL Server and the database you specified in the database connection you provided when you created the project. If you then open the SQL Server Management Studio (or SQL Server Management Studio Express if you're using that) you can call the function in a query, for example like this: SELECT dbo.Function1()

   1: SELECT dbo.Function1()

Which should return "Hello".

Now, to do something more productive with this, here's the code for two date-related functions:

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6: using System.Globalization;

   7:  

   8: public partial class UserDefinedFunctions

   9: {

  10:     [Microsoft.SqlServer.Server.SqlFunction]

  11:     public static SqlInt32 Week(SqlDateTime date)

  12:     {

  13:         return new SqlInt32(GetIso8601WeekOfYear(date.Value));

  14:     }

  15:  

  16:     [Microsoft.SqlServer.Server.SqlFunction]

  17:     public static SqlString YearWeek(SqlDateTime date)

  18:     {

  19:         int weekNumber = GetIso8601WeekOfYear(date.Value);

  20:  

  21:         int year = date.Value.Year;

  22:  

  23:         if (weekNumber >= 53 && date.Value.Month == 1)

  24:             year -= 1;

  25:  

  26:         if (weekNumber == 1 && date.Value.Month == 12)

  27:             year += 1;

  28:  

  29:         string yearWeek = year.ToString() + '-' + string.Format("{0:D2}" ,weekNumber);

  30:  

  31:         return new SqlString(yearWeek);

  32:     }

  33:  

  34:  

  35:     // The code below was taken from Shawn Steele's blog post:

  36:     // http://blogs.msdn.com/shawnste/archive/2006/01/24/iso-8601-week-of-year-format-in-microsoft-net.aspx

  37:  

  38:     // This presumes that weeks start with Monday.

  39:     // Week 1 is the 1st week of the year with a Thursday in it.

  40:     private static int GetIso8601WeekOfYear(DateTime time)

  41:     {

  42:         // Need a calendar.  Culture's irrelevent since we specify start day of week

  43:         Calendar cal = CultureInfo.InvariantCulture.Calendar;

  44:  

  45:         // Seriously cheat.  If its Monday, Tuesday or Wednesday, then it'll 

  46:         // be the same week# as whatever Thursday, Friday or Saturday are,

  47:         // and we always get those right

  48:         DayOfWeek day = cal.GetDayOfWeek(time);

  49:         if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)

  50:         {

  51:             time = time.AddDays(3);

  52:         }

  53:  

  54:         // Return the week of our adjusted day

  55:         return cal.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

  56:     }

  57: };

The first function, Week, returns the week number for a give date. The second function, YearWeek, returns a string in the format of CCYY-WW, for example "2007-51" for today. This is quite convenient when creating reports and graphs where stuff is grouped per week, because sorting and grouping on such a value is easy.

Once the code above is deployed to the server you can run queries like these:

   1: SELECT dbo.Week('2007-12-31')

   2: SELECT dbo.YearWeek('2007-12-22')

   3: SELECT dbo.YearWeek('2008-2-14')

   4: SELECT dbo.YearWeek('2007-12-31')

   5: SELECT dbo.YearWeek('2008-12-31')

   6: SELECT dbo.YearWeek('2009-12-31')

Which should give you the following result:

   1: 1

   2: 2007-51

   3: 2008-07

   4: 2008-01

   5: 2009-01

   6: 2009-53

So as you can see it gives the right week numbers for the last days of 2007, 2008 and 2009, which DATEPART can't do. In addition, if you use YearWeek, you get a nicely formatted string that you can use in reports and such.

GetWeekOfYear is not reliable in .NET

Microsoft doesn't know what week it is. Or at least they can't calculate it correctly. There's a nice standard, ISO 8601, for how to represent date and time. The Calendar and GregorianCalendar classes implement GetWeekOfYear incorectly, and return the incorrect week for some dates. For example, I realized that they claim that the week number for the date 2007-12-31 is 53, when in fact it is 1. After googling this for a while I found a great post from Shawn Steele that explains how to get around the behavior. Quote:

"A simple workaround to consistently get the ISO 8601 week is to realize that consecutive days Monday through Sunday in ISO 8601 weeks all have the same week #.  So Monday has the same week # as Thursday.  Since Thursday is the critical day for determining when the week starts each year my solution is to add 3 days if the day is Monday, Tuesday or Wednesday.  The adjusted days are still in the same week, and use values that GetWeekOfYear and ISO 8601 agree on."

He also posted a great code sample for this.

I ran into this while creating some date-related SQL CLR functions for use in SQL Server 2005. I'll post more about this is a while.

Update: I blogged some more about this, including sample code and how to use this stuff to create a set of date-related user defined functions in SQL Server 2005 in the post "Creating more SQL Server 2005 date functions".

A WPF desktop alert control

Desktop alerts are a great feature in many applications. Whenever something important (and perhaps urgent) happens a nice little window appears at the bottom right corner of the screen and tells you about it. Everyone is familiar with what they look like in Outlook:

Even though these little notification popup windows can be quite annoying if used incorrectly (I'm thinking Messenger and a multitude of alerts here), they can most certainly be a good tool for making sure users are made aware of pressing issues they should be reacting to.

A few years ago I created a custom control for showing alerts like this in Windows Forms. However, I did find that different applications needed different types of alerts, with different functionality in them. Satisfying this need in Windows Forms always meant doing a whole bunch of coding and deriving a new custom control from my initial one. Now when Visual Studio 2008 was released, with decent support for Windows Presentation Foundation, I figured it would be a good time to revisit the old control and see how it could be implemented in WPF.

Since the thinking around controls in WPF is quite different from Windows Forms, and there is such a strong emphasis on the separation of behavior and appearance. First of all, is it even necessary to create a special control, or would a control template applied to for example a window be sufficient?

After thinking about it I came to the conclusion that there's enough behavior associated with a desktop alert to warrant making a custom control. It needs to be shown only for a short while, unless the user activates it (then it should stay visible until closed by the user). There can also be several alerts visible at the same time, and then they should be laid out in such a way that they don't obscure each other. Trying to achieve this with just a repeatable template would be difficult, but it should be possible to standardize these through a custom control. Some of the thinking that went into this was outlined in my previous post, "Closing a WPF custom control from a storyboard". At the same time I decided to create a small sample of this, and that is what you're reading now. :) The code itself is downloadable, look at the bottom of this post for a link.

Creating a desktop alert sample

Different applications need different looking alerts. The one used by Outlook contains both information about the email (sender, subject and a snippet of the email body) as well as commands you can perform on the email (delete, flag as urgent and so forth). Another application might want to provide links that when clicked open up different views of data in the application, depending on what the alert was about. These all sound like things that would be easy to provide specifically for each application by just showing application-specific XAML content in the alert window.

The next challenge was which existing WPF control should I base my control on, and how the control should be invoked and used. I decided to base it on Window, and allow deriving from the control to create other alert windows with different content in them like this:

(I'm sorry about the uneven lines and shaky text in the image above, but I'm writing this on an airplane...)

DesktopAlertBase is a basic alert window, that you derive from to create specific alert window types. In the diagram above there are two sample derived classes, SimpleAlert (which has a title and a text body) and ImageAlert (which has a title and shows an image as its body). Not very realistic (or good) alerts, but they will have to do for this sample.

I decided to go for derived classes despite the fact that this complicates using the control. But in the end it is actually the derived classes that should be reused, and not the DesktopAlertBase. Its purpose is to simplify the development of desktop alerts, not to be the actual alert that is used in an application. The fact is that the reason for doing this is that I didn't figure out a way to solve this with a control that could be placed inside a Window, since it is the actual window that has the desktop alert behavior. I may change this completely later, and then I'll post a new sample. :)

In Visual Studio I set up a project structure like this:

The solution is split in two projects. The first, DesktopAlert, contains the reusable custom control and its default WPF template. It handles the fading in and out of the alert window, closes the alert when it's been up for a while, and also allows the user to close the alert window.

The second project, SandboxApplication, is a demo application that contains two derived classes (ImageAlert and SimpleAlert) and a little application that launches these.

The DesktopAlertBase class is quite simple. It contains a couple of defaults for the alerts, and the necessary behavior related to opening and closing the alerts. The code for it looks like this:

   1: public abstract class DesktopAlertBase : Window

   2: {

   3:     private DoubleAnimation _fadeInAnimation;

   4:     private DoubleAnimation _fadeOutAnimation;

   5:     private DispatcherTimer _activeTimer;

   6:  

   7:     static DesktopAlertBase()

   8:     {

   9:         DefaultStyleKeyProperty.OverrideMetadata(typeof(DesktopAlertBase), new FrameworkPropertyMetadata(typeof(DesktopAlertBase)));

  10:     }

  11:  

  12:     // We need to add a public constructor so that we can set up our

  13:     // new window properly

  14:     public DesktopAlertBase()

  15:     {

  16:         Visibility = Visibility.Visible;

  17:  

  18:         // Let's set up a dummy window size

  19:         Width = 350;

  20:         Height = 75;

  21:  

  22:         // Set some default properties for the alerts.

  23:         // These can be changed by the derived alerts.

  24:         ShowInTaskbar = false;

  25:         WindowStyle = WindowStyle.None;

  26:         ResizeMode = ResizeMode.NoResize;

  27:         Topmost = true;

  28:         AllowsTransparency = true;

  29:         Opacity = 0.8;

  30:         BorderThickness = new Thickness(1);

  31:         BorderBrush = Brushes.Black;

  32:         Background = Brushes.White;

  33:  

  34:         // Set up the fade in and fade out animations

  35:         _fadeInAnimation = new DoubleAnimation();

  36:         _fadeInAnimation.From = 0;

  37:         _fadeInAnimation.To = 0.8;

  38:         _fadeInAnimation.Duration = new Duration(TimeSpan.Parse("0:0:1.5"));

  39:  

  40:         // For the fade out we omit the from, so that it can be smoothly initiated

  41:         // from a fade in that gets interrupted when the user wants to close the window

  42:         _fadeOutAnimation = new DoubleAnimation();

  43:         _fadeOutAnimation.To = 0;

  44:         _fadeOutAnimation.Duration = new Duration(TimeSpan.Parse("0:0:1.5"));

  45:  

  46:         Loaded += new RoutedEventHandler(DesktopAlertBase_Loaded);

  47:     }

  48:  

  49:     // When the template is applied to the control, look for a button called "PART_CloseButton".

  50:     // If such a button exists, hook up an event handler so that the alert can be closed when

  51:     // the user clicks the button.

  52:     public override void OnApplyTemplate()

  53:     {

  54:         ButtonBase closeButton = Template.FindName("PART_CloseButton", this) as ButtonBase;

  55:         if (closeButton != null)

  56:             closeButton.Click += new RoutedEventHandler(closeButton_Click);

  57:     }

  58:  

  59:     // In the Loaded-event handler we need to figure out where to place the alert window.

  60:     // Currently they are all placed in the bottom right corner, and can not take into

  61:     // account other currently open alert windows.

  62:     void DesktopAlertBase_Loaded(object sender, RoutedEventArgs e)

  63:     {

  64:         // Figure out where to place the window based on the current screen resolution

  65:         Rect workAreaRectangle = System.Windows.SystemParameters.WorkArea;

  66:         Left = workAreaRectangle.Right - Width - BorderThickness.Right;

  67:         Top = workAreaRectangle.Bottom - Height - BorderThickness.Bottom;

  68:  

  69:         _fadeInAnimation.Completed += new EventHandler(_fadeInAnimation_Completed);

  70:  

  71:         // Start the fade in animation

  72:         BeginAnimation(DesktopAlertBase.OpacityProperty, _fadeInAnimation);

  73:     }

  74:  

  75:     // When the fade in animation is completed, start another timer that fires an event when the

  76:     // window has been visible for 10 seconds

  77:     void _fadeInAnimation_Completed(object sender, EventArgs e)

  78:     {

  79:         _activeTimer = new DispatcherTimer();

  80:         _activeTimer.Interval = TimeSpan.Parse("0:0:10");

  81:  

  82:         // Attach an anonymous method to the timer so that we can start fading out the alert

  83:         // when the timer is done.

  84:         _activeTimer.Tick += delegate(object obj, EventArgs ea) { FadeOut(); };

  85:  

  86:         _activeTimer.Start();

  87:     }

  88:  

  89:     // Set up the fade out animation, and hook up an event handler to fire when it is completed.

  90:     private void FadeOut()

  91:     {

  92:         // Attach an anonymous method to the Completed-event of the fade out animation

  93:         // so that we can close the alert window when the animation is done.

  94:         _fadeOutAnimation.Completed += delegate(object sender, EventArgs e) { Close(); };

  95:  

  96:         BeginAnimation(DesktopAlertBase.OpacityProperty, _fadeOutAnimation, HandoffBehavior.SnapshotAndReplace);

  97:     }

  98:  

  99:     // If the user clicks the close button, stop the timer that counts how long the alert

 100:     // window has been open, and start the fading out of the window.

 101:     void closeButton_Click(object sender, RoutedEventArgs e)

 102:     {

 103:         _activeTimer.Stop();

 104:         FadeOut();

 105:     }

The template for the control (in Generic.xaml) is quite simple. It contains a template for the close button, and a template for the actual control. The control has a title area, and a content area in which the content of the derived class is displayed.

   1: <ResourceDictionary

   2:     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

   3:     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

   4:     xmlns:local="clr-namespace:DesktopAlert">

   5:  

   6:     <ControlTemplate x:Key="CloseButtonTemplate" TargetType="{x:Type Button}">

   7:         <Grid Background="Transparent">

   8:             <Viewbox>

   9:                 <ContentPresenter/>

  10:             </Viewbox>

  11:         </Grid>

  12:         <ControlTemplate.Triggers>

  13:             <Trigger Property="IsMouseOver" Value="True">

  14:                 <Setter Property="BitmapEffect">

  15:                     <Setter.Value>

  16:                         <OuterGlowBitmapEffect GlowSize="12" GlowColor="White"/>

  17:                     </Setter.Value>

  18:                 </Setter>

  19:             </Trigger>

  20:             <Trigger Property="IsPressed" Value="True">

  21:                 <Setter Property="BitmapEffect">

  22:                     <Setter.Value>

  23:                         <OuterGlowBitmapEffect GlowSize="12" GlowColor="DarkGray"/>

  24:                     </Setter.Value>

  25:                 </Setter>

  26:             </Trigger>

  27:         </ControlTemplate.Triggers>

  28:     </ControlTemplate>

  29:     

  30:     <Style TargetType="{x:Type local:DesktopAlertBase}">

  31:         <Setter Property="Template">

  32:             <Setter.Value>

  33:                 <ControlTemplate TargetType="{x:Type local:DesktopAlertBase}">

  34:                     <Border Background="{TemplateBinding Background}"

  35:                             BorderBrush="{TemplateBinding BorderBrush}"

  36:                             BorderThickness="{TemplateBinding BorderThickness}"

  37:                             SnapsToDevicePixels="True">

  38:                         <Grid>

  39:                             <Grid.RowDefinitions>

  40:                                 <RowDefinition Height="20"/>

  41:                                 <RowDefinition/>

  42:                             </Grid.RowDefinitions>

  43:                             <Grid Grid.Row="0" Background="LightGray">

  44:                                 <Grid>

  45:                                     <Grid.ColumnDefinitions>

  46:                                         <ColumnDefinition/>

  47:                                         <ColumnDefinition Width="20"/>

  48:                                     </Grid.ColumnDefinitions>

  49:                                     <TextBlock Grid.Column="0" Margin="5, 0, 0, 0" FontWeight="Bold" FontSize="12" 

  50:                                                Opacity="1.0" Text="{TemplateBinding Title}"/>

  51:                                     <Button x:Name="PART_CloseButton" 

  52:                                                 Grid.Column="1"

  53:                                                 HorizontalAlignment="Right" VerticalAlignment="Top"

  54:                                                 Template="{StaticResource CloseButtonTemplate}"

  55:                                                 Width="18" Height="18" 

  56:                                                 FontWeight="Bold"

  57:                                                 Foreground="#FF222222"

  58:                                                 >x</Button>

  59:                                 </Grid>

  60:                             </Grid>

  61:                             <ContentControl Grid.Row="1" Content="{TemplateBinding Content}"/>

  62:                         </Grid>

  63:                     </Border>

  64:                 </ControlTemplate>

  65:             </Setter.Value>

  66:         </Setter>

  67:     </Style>

  68: </ResourceDictionary>

The derived classes contain very little code. The SimpleAlert type code behind implements a property called Message that contains the body of the alert. The XAML for SimpleAlert looks like this:

   1: <da:DesktopAlertBase x:Class="SandboxApplication.Alerts.SimpleAlert"

   2:     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

   3:     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

   4:     xmlns:da="clr-namespace:DesktopAlert;assembly=DesktopAlert"

   5:     Title="SimpleAlert">

   6:     <Grid>

   7:         <TextBlock FontSize="12" Padding="5" TextWrapping="Wrap"

   8:                    Text="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type Window}}, Path=Message}"/>

   9:     </Grid>

  10: </da:DesktopAlertBase>

As you can see, all it does is display a TextBlock that is data bound to the Message property. And why a relative binding? The truth is I was too tired to figure out another way of doing it at the time. :)

Let's have a look at what the alerts actually like. Here's the SimpleAlert:

Not very pretty yet, but we'll get to that in another post. And how much code was needed to launch the alert? This much:

   1: SimpleAlert simpleAlert = new SimpleAlert();

   2: simpleAlert.Title = "My alert has a sample title";

   3: simpleAlert.Message = "This is my message! If it's long enough it will wrap. Is it long enough now?";

The ImageAlert is equally simple, so I won't post the code here. You can download it and have a look if you want.

Summing it all up

The code in this sample is by no means perfect. It's intended for exploring this way of implementing a desktop alert.

At this point I'm still not convinced that the approach outlined here is the right one, but at least it seems good enough for me now. I'll have to work on it a bit more, and try it out in a real project, before I know for sure. I'll also try to put together some proper alerts and make some nice designs for them to see what this will look like in real life. Also, this implementation doesn't yet support simultaneously appearing windows (they appear on top of each other), I hope to have time to fix that in a few days.

Please download the source code for this sample and have a look if you're interested. Please note that it comes with no warranties at all. If you like it (or do something with it), please let me know. You need Visual Studio 2008 to compile the sample (but compiled executables are included in the zip).

Closing a WPF custom control from a storyboard

While working on a custom control for Windows Presentation Foundation I came across a scenario where I needed the control (which was derived from Window) to fade in when opened, and fade out when a close-button is clicked.

I'd written a control that did this in Windows Forms a while back (I was now porting parts of it to WPF), using timers and stuff like this. But since WPF contains such a lot of cool stuff for animations and storyboards I figured I should be able to do this declaratively in XAML.

In the template for the control, in the Triggers-section, I added the included XAML below. I hooked up the fade in animation to the Loaded-event on the window, and the fade out to the Click-event on the button the user presses to close the window.

   1: <ControlTemplate.Triggers>

   2:     <EventTrigger RoutedEvent="Window.Loaded">

   3:         <EventTrigger.Actions>

   4:             <BeginStoryboard>

   5:                 <Storyboard TargetProperty="Opacity">

   6:                     <DoubleAnimation From="0" To="0.8" Duration="0:0:2"/>

   7:                 </Storyboard>

   8:             </BeginStoryboard>

   9:         </EventTrigger.Actions>

  10:     </EventTrigger>

  11:     <EventTrigger RoutedEvent="ButtonBase.Click" SourceName="PART_CloseButton">

  12:         <EventTrigger.Actions>

  13:             <BeginStoryboard>

  14:                 <Storyboard x:Name="PART_CloseAnimation" TargetProperty="Opacity">

  15:                     <DoubleAnimation From="0.8" To="0" Duration="0:0:2"/>

  16:                 </Storyboard>

  17:             </BeginStoryboard>

  18:         </EventTrigger.Actions>

  19:     </EventTrigger>

  20: </ControlTemplate.Triggers>

This worked fine. The window faded in when opened, and faded out when closed. But since all the fade out actually did was hide the window by making it opaque I wanted to come up with a way to actually get rid of the window (actually call Close on it).

The Storyboard has a "Completed"-event, and that seemed like the logical place to call an event handler. In the OnApplyTemplate-method of my window I was already doing some stuff where I was hooking up events to elements in the template, so that seemed like a logical place to hook up events to the storyboard as well.

It turns out that i couldn't get access to "PART_CloseAnimation" and hook up the event handler. I tried Template.FindName, I tried looking in Template.Resources, and I did the same on my window. I couldn't get to the storyboard. The reason turned out to be quite simple. When a ContentTemplate is loaded it becomes sealed, and can not be changed. Therefore you can't change it afterwards, and do things like hook up event handlers to it.

I couldn't come up with a way to get around this declaratively, so I ended up doing the fade in and fade out procedurally instead. It's dead easy to create the animation in code, for example like this:

   1: _fadeInAnimation = new DoubleAnimation();

   2: _fadeInAnimation.From = 0;

   3: _fadeInAnimation.To = 0.8;

   4: _fadeInAnimation.Duration = new Duration(TimeSpan.Parse("0:0:2"));

   5: BeginAnimation(MyWindow.OpacityProperty, _fadeInAnimation);

Of course, this is not as nice as doing it declaratively in the template, as this aspect of the behavior of my control can no longer be changed through skins and templates. But I guess I have to live with that.

By the way, the stuff above was done in Visual Studio 2008 and on version 3.5 of the .NET Framework. Just in case you try it on 3.0 and it happens to work differently.

PDC 08 has been announced

Wow, it seems that there will be a PDC next year. Microsoft has the details here. This is great news, as it was a bit of a disappointment that PDC 07 was canceled.

And what will we see at PDC 08? Oslo-related stuff is probably not a bad guess.