T-SQL condition short-circuiting does not work

This isn't really a WPF issue at all - it concerns SQL Server 2012.

I've always thought that SQL Server (T-SQL in general) uses short-circuiting when evaluating conditions. For example, if you have dates stored in YYYYMMDD format (because they come from an ancient HP-3000) and null dates are stored as '00000000' then you can search on the field like this...

DATE != '00000000' AND CAST(DATE AS DateTime) > '1/1/2015'

Even though you cannot cast '00000000' as a datetime this works because the invalid dates never get to the CAST function because of short-circuiting. This is the same as the VB.Net AndAlso operator.

An employee came to me today because he was getting an invalid cast exception using syntax like this. His SELECT statement was part of a cursor declaration which was inside a function. I extracted the SELECT statement and ran it in a new query and it ran fine. The problem only occurred when we ran it as part of a cursor declaration.

To test my theory that failure of short-circuiting was the cause of the problem I altered the SQL to look more like this...

CAST(CASE WHEN DATE='00000000' THEN '19000101' ELSE DATE END) > '1/1/2015'

The modified function works correctly which confirms my theory that you cannot rely on short-circuiting in SQL Server. Clearly the statement was being optimized differently when part of a cursor. Also, the function was working fine a few days ago so either we got a patch that broke it or perhaps the statistics changed enough to change the optimization plan for the statement.

I've done a little research on-line and I've found some authoritative blogs that state "SQL Server supports expression short circuiting but you cannot rely on the sequence the expressions will be evaluated." In effect this improves performance but you probably want to use a CASE statement if you have a condition like the one described in this blog.

e.OriginalSource in RoutedCommands is unreliable

I came across an interesting WPF bug (Framework 4.0) today that warrants documenting.

I wanted to have a RoutedCommand.Executed event handler reach through to the MenuItem that triggered the command and read the Tag property for some extra information because the CommandParameter was already occupied with something else. The idea was the the following XAML


<CommandBinding Command="Open" CanExecute="OpenCanExecute" Executed="OpenExecuted" />
.
.
.
<MenuItem Header="Edit" Command="Open" CommandParameter="Vendor.Maintenance" Tag="Edit"/>

would execute code that could use e.OriginalSource to get the value of Tag but from time to time the OriginalSource would be the textbox that had focus instead of the MenuItem that actually initiated the RoutedCommand. Clearly this is a bug in WPF

In the end I solved the problem by making the syntax of CommandParameter more flexible.

In the example above you can see the CommandParameter is in the format <string>.<string>. But a CommandParameter is an object so you can populate it with anything you want. I discovered a way to populate a CommandParameter with an array of strings declaratively in XAML.

Start by making sure you have the following two XMLNS declarations in your main tag.

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:sys="clr-namespace:System;assembly=mscorlib"

and now try the following XAML

<Menu IsMainMenu="true">

    <MenuItem Header="Edit" Command="Open">

        <MenuItem.CommandParameter>
            <x:ArrayExtension Type="sys:String">
                <sys:String>Vendor</sys:String>
                <sys:String>Maintenance</sys:String>
                <sys:String>Edit</sys:String>
            </x:ArrayExtension>
        </MenuItem.CommandParameter>
    </MenuItem>
</Menu>

Tooltips on DataGrid headers

I have a DataGridTextColumn which has a dynamically generated tooltip on each cell (binding the cell's tooltip via a multi-value converter) but I want a static tooltip on the column header that explains what the user is looking at. It turns out to be quite easy once you know how to do it.

Here is the original DataGridTextColumn definition...

<DataGridTextColumn Header="Total Pending" Width="60" Binding="{Binding Path=TotalQuantityPending, StringFormat=0.00;;#}" ElementStyle="{StaticResource TextBlockRightAlign}" IsReadOnly="true">
    <DataGridTextColumn.CellStyle>
        <Style TargetType="DataGridCell" BasedOn="{StaticResource DataGridCell}">
            <Setter Property="ToolTip">
                <Setter.Value>
                    <MultiBinding Converter="{StaticResource ConversionConverter}">
                        <Binding Path="UORCode"/>
                        <Binding Path="UOICode"/>
                        <Binding Path="Conversion"/>
                        <Binding Path="TotalQuantityReturned"/>
                    </MultiBinding>
                </Setter.Value>
            </Setter>
        </Style>
    </DataGridTextColumn.CellStyle>
</DataGridTextColumn>

Now to add a static tooltip to the header we take advantage of the fact that the header can be any content control. So we make it a textblock with it's own tooltip. Simple when you think of it.

<DataGridTextColumn Width="60" Binding="{Binding Path=TotalQuantityPending, StringFormat=0.00;;#}" ElementStyle="{StaticResource TextBlockRightAlign}" IsReadOnly="true">
    <DataGridTextColumn.Header>
        <TextBlock Text="Total Pending" ToolTip="Total received on this line on all Pending receipts."></TextBlock>
    </DataGridTextColumn.Header>
    <DataGridTextColumn.CellStyle>
        <Style TargetType="DataGridCell" BasedOn="{StaticResource DataGridCell}">
            <Setter Property="ToolTip">
                <Setter.Value>
                    <MultiBinding Converter="{StaticResource ConversionConverter}">
                        <Binding Path="UORCode"/>
                        <Binding Path="UOICode"/>
                        <Binding Path="Conversion"/>
                        <Binding Path="TotalQuantityReturned"/>
                    </MultiBinding>
                </Setter.Value>
            </Setter>
        </Style>
    </DataGridTextColumn.CellStyle>
</DataGridTextColumn>

Daisy-chaining converters

For WPF 4.0. Credit to Gareth Evans.

I have a converter that returns true if the current user has the specified security code. I have another converter that returns Visible if I pass in a true value otherwise Collapsed. I have a menu control that I want to only show if the user has a specified security code. I wish I could join my two converters so I didn't have to write a third one. Well it turns out I can...

Let's start with the two converters I have already written. Note I removed error handling for simplicity.

<ValueConversion(GetType(String), GetType(Boolean))>
Public Class SecurityToBooleanConverter
    Implements IValueConverter

    Public Function Convert(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.Convert

        Return Application.Current.Properties.User_SecurityCodes.ContainsKey(parameter) 
    End Function

    Public Function ConvertBack(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.ConvertBack
        Throw New System.NotImplementedException("SecurityConverter")
    End Function
End Class

<ValueConversion(GetType(Boolean), GetType(Visibility))>

Public Class BooleanToVisibilityConverter

    Implements IValueConverter

    Public Function Convert(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.Convert

        Return IIf(CBool(value), Visibility.Visible, Visibility.Collapsed)

    End Function

    Public Function ConvertBack(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.ConvertBack

        Throw New NotImplementedException

    End Function

End Class

In my resources I have created StaticResources like this...

    <local:SecurityToBooleanConverter x:Key="SecurityToBooleanConverter"/>

    <local:BooleanToVisibilityConverter x:Key="BooleanToVisibilityConverter"/>

Now here is the clever bit. In my list of converter classes I add the following generic code. This is the new class that will allow me to string as many converters together as I need.

Public Class ValueConverterGroup

    Inherits Collections.Generic.List(Of IValueConverter)

    Implements IValueConverter

    Public Function Convert(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.Convert

        Return Me.Aggregate(value, Function(current, converter) converter.Convert(current, targetType, parameter, culture))

    End Function

    Public Function ConvertBack(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.ConvertBack

        Throw New NotImplementedException()

    End Function

End Class

Now I add a resource that describes how I want to string the converters together. In my example I want to convert from a security code to a boolean and then to a visibility. The new resource to do this looks like this. It could be defined globally in the Application.XAML or on a single page's XAML.

    <local:ValueConverterGroup x:Key="SecurityToVisibilityConverter">

        <local:SecurityToBooleanConverter></local:SecurityToBooleanConverter>

        <local:BooleanToVisibilityConverter></local:BooleanToVisibilityConverter>

    </local:ValueConverterGroup>

When InitializeComponent finds this resource it creates an instance of the ValueConverterGroup and populates the list of converters with a SecurityToBooleanConverter and a BooleanToVisibilityConverter. The ValidConverterGroup has a very clever use of Aggregate that essentially calls each converter in turn passing the value output from the previous converter and with the parameter and culture that was passed to the ValueConverterGroup.

The binding of my control's visibility looks like this...

    <MenuItem Header="Requisitions" Visibility="{Binding Converter={StaticResource SecurityToVisibilityConverter}, ConverterParameter='Requisition'}"/>

The only drawback I see with this solution is that you cannot pass different parameters to different steps in the conversion.

Binding properties of DataGridColumns

For WPF 4.0 or later

Similar to my earlier blog entry on binding the ItemsSource of a DataGridComboBoxColumn, it is also difficult to bind other properties of all flavors of DataGridColumns. I recently wanted to be able to disable a DataGridColumn when a property was set in the window's DataContext. I was using MVVM at the time but this solution works more generically.

I Googled the problem and found several possible solutions, but the one that caught my eye was to create an invisible ContentControl and create a static resource that referenced it. You set the DataContext of the FrameworkElement to the View-Model class (or the code behind) and then reference the static resource in the dependency object's binding. Once you have defined the Framework Element and the StaticResource once on a page, you can use it in multiple places on that page.

There are other techniques that solve the problem solution wide, but they require a lot more up-front work.

I'll offer my solution using a property in the code-behind. MVVM programmers will know how to tweak it for their needs.

Consider the following window that has a data grid with one column. There is also a CheckBox that can be checked to put the grid's Name column into read-only mode. Here is the XAML and code behind.

<Window x:Class="BindingDataGridColumn.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525"
        DataContext="{Binding RelativeSource={RelativeSource Self}}">
    <Grid>
        <CheckBox Content="Is ReadOnly?" IsChecked="{Binding IsReadOnly}"></CheckBox>
    <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Names}" Margin="0,40,0,0">
        <DataGrid.Columns>
            <DataGridTextColumn Header="Name" Binding="{Binding Path=.}" IsReadOnly="{Binding IsReadOnly}"></DataGridTextColumn>
        </DataGrid.Columns>
    </DataGrid>
    </Grid>
</Window>
----------------------------------------------------------------------------
using System;
using System.Windows;
using System.Collections.ObjectModel;
using System.ComponentModel;

namespace BindingDataGridColumn
{
    public partial class MainWindow : Window, INotifyPropertyChanged
            {
        public ObservableCollection<String> Names { get; set; }

        private Boolean _IsReadOnly = false;
        public Boolean IsReadOnly
        {
            get { return _IsReadOnly; }
            set 
            {
                if (value != _IsReadOnly)
                {
                    _IsReadOnly = value;
                    NotifyPropertyChanged("IsReadOnly");
                }
            }
        }

        public MainWindow()
        {
            Names = new ObservableCollection<String> { "Bob", "Mike" };
            InitializeComponent();
        }

        public event PropertyChangedEventHandler PropertyChanged;
        private void NotifyPropertyChanged(String PropertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(PropertyName));
        }
    }
}

When we run this application we see we can modify the names even if we check the Is Readonly CheckBox. Also, when we look at the Output window we see the familiar message...

System.Windows.Data Error: 2 : Cannot find governing FrameworkElement or FrameworkContentElement for target element. BindingExpression:Path=IsReadOnly; DataItem=null; target element is 'DataGridTextColumn' (HashCode=45828630); target property is 'IsReadOnly' (type 'Boolean')

To solve this problem we need to do three things...

1. Add a resource to the Window that has the same data context as the window...
   <FrameworkElement x:Key="ProxyElement" DataContext="{Binding}"/>
2. Add a ContentControl and make it invisible. Its content will be the FrameworkElement we just defined, so its data context will be the same as the window...
   <ContentControl Visibility="Collapsed" Content="{StaticResource ProxyElement}"/>
3. Use the new ProxyElement as the source in the DataGridColumn's binding. You can re-use the ProxyElement on all DataGridColumns that need it.
   Source={StaticResource ProxyElement}

So now the XAML looks like this. Note there are no code changes required...

<Window x:Class="BindingDataGridColumn.MainWindow"

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

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

        Title="MainWindow" Height="350" Width="525"

        DataContext="{Binding RelativeSource={RelativeSource Self}}">

    <Window.Resources>

        <FrameworkElement x:Key="ProxyElement" DataContext="{Binding}"/>

    </Window.Resources>

    <Grid>

        <ContentControl Visibility="Collapsed" Content="{StaticResource ProxyElement}"/>

        <CheckBox Content="Is ReadOnly?" IsChecked="{Binding Path=IsReadOnly}"></CheckBox>

    <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Names}" Margin="0,40,0,0">

        <DataGrid.Columns>

                <DataGridTextColumn Header="Name" Binding="{Binding Path=.}" IsReadOnly="{Binding Path=DataContext.IsReadOnly, Source={StaticResource ProxyElement}}"></DataGridTextColumn>

        </DataGrid.Columns>

    </DataGrid>

    </Grid>

</Window>

Note we had to change the Binding Path because the Framework Element does not have an IsReadOnly property, but its DataContext does.

Running Report Manager on newly installed Reporting Services

Sometime Microsoft seems intent on pissing off as many people as possible Their attitude towards this particular problem is exceptionally arrogant and dismissive even for them.

I just installed SQL Server 2014 Reporting Services on my personal development computer and, after configuring it, I went to Report Manager by launching IE and going to http://localhost/Reports. The browser prompted me to authenticate, which I did, and got an error message saying I don't have the required access rights.

This is what I had to do to grant myself (an administrator) rights to use Report Manager in sixteen easy steps.

1. Enable the built-in administrator account
    Run CMD as administrator and enter "NET USER administrator /active:yes"

2. Pray you remember the administrator password

3. Switch user to the administrator

4. Start IE as administrator and go to "localhost/Reports"

5. Click on Site Settings in the top right corner

6. Click on Security in the left column

7. Click [New Role Assignment]

8. Type my domain\user name and check all the checkboxes

9 . Click [OK]

10. Click on Home in the top right corner

11. Click [Folder Settings]

12. Click [New Role Assignment]

13. Type my domain\user name and check all the checkboxes

14. Click [OK]

15. Close the browser and switch back to my user

16. Prevent IE from re-authenticating when you go to Report Manager
     Tools -> Internet Options -> Security -> Local Intranet -> Sites -> Advanced
     Add http://localhost to the list of Intranet sites

Now Microsoft's attitude is that they granted access to the administrator account and that's good enough. It isn't. How hard would it have been to grant site and root folder access rights to the account that installed Reporting Services? This would have solved all these issues and would not have caused a security issue because the installing account can administer reporting services anyway - just not report manager.

Sometimes I find myself becoming confrontational with my users when I think they're being lazy or unreasonable. When I feel this coming on - I really must say to myself "Don't be Microsoft!"

Putting a DatePicker in a datagrid

This is such a common requirement I'm surprised Microsoft hasn't added a DataGridDatePickerColumn but they haven't so every time we want a DatePicker in a DataGrid we have to go through the convolutions below...

<DataGrid ItemsSource="{Binding Path=Events}" AutoGenerateColumns="False">
    <DataGrid.Columns>
        <DataGridTemplateColumn Header="Event Date" Width="200">
            <DataGridTemplateColumn.CellTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding Path=EventDate,StringFormat=MM/dd/yyyy}"></TextBlock>
                </DataTemplate>
            </DataGridTemplateColumn.CellTemplate>
            <DataGridTemplateColumn.CellEditingTemplate>
                <DataTemplate>
                    <DatePicker SelectedDate="{Binding Path=EventDate}"></DatePicker>
                </DataTemplate>
            </DataGridTemplateColumn.CellEditingTemplate>
        </DataGridTemplateColumn>
    </DataGrid.Columns>
</DataGrid>