home rss

To be continued ...

This blog continues here.

Codeless two-way binding to a WPF DataGrid

WPF 4.0 will finally contain a DataGrid. If you can't wait for that one, then all you have to do is download the current release of the WPF Toolkit. The DataGrid control in this toolkit is considered as stable, so why not give it a test drive? Let's create a list  of Formula 1 Drivers and two-way data bind it to a WPF DataGrid. In our little object model -the ViewModel if you like- a Formula 1 Driver is associated to a Formula 1 Team:

Here's the startup code for the Formula 1 Driver class:

namespace U2UConsult.DockOfTheBay
{
    using System.Collections.Generic;

    /// <summary>
    /// A Formula1 Team.
    /// </summary>
    public class FormulaOneTeam
    {
        /// <summary>
        /// Gets the entire list of Formula1 teams.
        /// </summary>
        public static Dictionary<int, FormulaOneTeam> GetAll
        {
            get
            {
                return new Dictionary<int, FormulaOneTeam>()
                {
                    { 0, new FormulaOneTeam { TeamId = 0, Name = "Unknown" } },
                    { 1, new FormulaOneTeam { TeamId = 1, Name = "Nintendo" } },
                    { 2, new FormulaOneTeam { TeamId = 2, Name = "Top Gear" } },
                    { 3, new FormulaOneTeam { TeamId = 3, Name = "Wacky Races" } }
                };
            }
        }

        /// <summary>
        /// Gets or sets the id of the Formula1 team.
        /// </summary>
        public int TeamId { get; set; }

        /// <summary>
        /// Gets or sets the name of the Formula1 team.
        /// </summary>
        public string Name { get; set; }
    }
}

In a real life scenario a class that's involved in data binding should implement the INotifyPropertyChanged and IDataError interfaces. An example of the latter can be found in a previous article, an alternative for validating is the BindingGroup class, that I will discuss in a future article (no hyperlink yet Wink). In M-V-VM applications you will probably implement this behavior by inheriting from some ViewModel base class.

Here's the implementation of the Formula 1 Team class:

namespace U2UConsult.DockOfTheBay
{
    using System.Collections.Generic;

    /// <summary>
    /// A Formula1 Team.
    /// </summary>
    public class FormulaOneTeam
    {
        /// <summary>
        /// The list of all Formula 1 Teams.
        /// </summary>
        private static Dictionary<int, FormulaOneTeam> getAll;

        /// <summary>
        /// Initializes static members of the FormulaOneTeam class.
        /// </summary>
        static FormulaOneTeam()
        {
            getAll = new Dictionary<int, FormulaOneTeam>()
                {
                    { 0, new FormulaOneTeam { TeamId = 0, Name = "Unknown" } },
                    { 1, new FormulaOneTeam { TeamId = 1, Name = "Nintendo" } },
                    { 2, new FormulaOneTeam { TeamId = 2, Name = "Top Gear" } },
                    { 3, new FormulaOneTeam { TeamId = 3, Name = "Wacky Races" } }
                };
        }

        /// <summary>
        /// Gets the entire list of Formula 1 Teams.
        /// </summary>
        public static Dictionary<int, FormulaOneTeam> GetAll
        {
            get
            {
                return getAll;
            }
        }

        /// <summary>
        /// Gets or sets the id of the Formula 1 Team.
        /// </summary>
        public int TeamId { get; set; }

        /// <summary>
        /// Gets or sets the name of the Formula 1 Team.
        /// </summary>
        public string Name { get; set; }
    }
}

The ideal collection type for complex data binding (that's binding one control to a collection of objects) is ObservableCollection(T). ObservableCollection(T) is to WPF what BindingList(T) is to WinForms. ObservableCollection(T) only implements the INotifyCollectionChanged interface. This is sufficient to do complex data binding -at least for WPF's ItemControl subclasses like ListView, ComboBox and DataGrid. In a WinForms application the ObservableCollection(T) class loses all its magic. To continue with the sample, add to the Formula 1 Driver class a method that returns such a collection:

/// <summary>
/// Gets a two-way bindable list of Formula 1 drivers.
/// </summary>
public static ObservableCollection<FormulaOneDriver> GetAll
{
    get
    {
        ObservableCollection<FormulaOneDriver> drivers =
            new ObservableCollection<FormulaOneDriver>()
            {
                new FormulaOneDriver(){ Name = "Super Mario",
                                        TeamId = 1,
                                        PolePositions = 2 },
                new FormulaOneDriver(){ Name = "The Stig",
                                        TeamId = 2,
                                        PolePositions = 20,
                                        LatestVictory = DateTime.Today },
                new FormulaOneDriver(){ Name = "Dick Dastardley",
                                        TeamId = 3,
                                        PolePositions = 0 },
                new FormulaOneDriver(){ Name = "Luigi",
                                        TeamId = 1,
                                        PolePositions = 2 }
            };

        return drivers;
    }
}

If you use this collection as ItemSource of the DataGrid, then the result should look like this:


The DataGrid has a couple of intuitive properties, as you can see in its XAML definition:

<toolkit:DataGrid
   x:Name="DriversDataGrid"
   ItemsSource="{Binding Source={x:Static local:FormulaOneDriver.GetAll}}"
   AutoGenerateColumns="False"
   CanUserAddRows="True"
   CanUserDeleteRows="True"
   CanUserSortColumns="True"
   CanUserReorderColumns="True"
   AlternatingRowBackground="WhiteSmoke"
   RowHeaderWidth="16"
   Grid.Column="0" Grid.Row="0"
   HorizontalAlignment="Center"
   VerticalAlignment="Center">
    <!-- toolkit:DataGrid.Columns [...] -->
</toolkit:DataGrid>

The DataGrid can be populated with four column types:
  • DataGridTextColumn,
  • DataGridCheckBoxColumn,
  • DataGridComboBoxColumn,
  • DataGridHyperlinkColumn, and
  • DataGridTemplateColumn.
This is an example of a DataGridTextColumn:
<toolkit:DataGridTextColumn
   Header="Name"
   Binding="{Binding Name}"
   CanUserReorder="True"
   IsReadOnly="False"
   CanUserSort="True"
   SortMemberPath="Name"/>

Let's look for an excuse to use a DataGridComboBoxColumn. It's not a good idea to confront end users with technical keys, so instead of showing the team's identity we'll display its name. When the grid cell is in edit mode, we let the user select from a ComboBox:

The embedded ComboBox is bound to a Dictionary, its citizens have a Key and a Value property that you should respectively bind to SelectedValuePath and DisplayMemberPath:
<toolkit:DataGridComboBoxColumn
   x:Name="TeamsCombo"
   Header="Team"
   ItemsSource="{Binding Source={x:Static local:FormulaOneTeam.GetAll}}"
   SelectedValueBinding="{Binding TeamId}"
   SelectedValuePath="Key"
   DisplayMemberPath="Value.Name"
   SortMemberPath="Team.Name" />

Make sure to specify the correct SortMemberPath. Users can sort the rows by clicking on the column header(s). The 'Team' column is bound to the TeamId, but it displays the team's name, so it should sort by name. The following screenshot shows that the grid is sorted on the Team column (notice the sort icon in the column header):

The LatestVictory property of the Formula 1 Driver is of the type DateTime. In edit mode, it makes sense to  use a DatePicker control to let the user select the date. This control can also be found in the WPF Toolkit. Here's how it can be used in a DataGridTemplateColumn:
<toolkit:DataGridTemplateColumn
   Header="Latest Victory"
   CanUserSort="True"
   SortMemberPath="LatestVictory">
    <toolkit:DataGridTemplateColumn.CellTemplate >
        <DataTemplate >
            <toolkit:DatePicker
               SelectedDate="{Binding LatestVictory}"
               BorderThickness="0"/>
        </DataTemplate >
    </toolkit:DataGridTemplateColumn.CellTemplate >
</toolkit:DataGridTemplateColumn>

Just like any WPF control the DataPicker is über-stylable. I don't like the default GUI settings for it, so I tweaked the GUI a little bit by hiding its border and giving the embedded TextBox a transparent backcolor. You have access to this via a Style. That's also the place to override the default watermark (through the Text property):
<Style TargetType="{x:Type toolkit:DatePickerTextBox}">
    <Setter Property="Text" Value="None" />
    <Setter Property="Background" Value="#00000000" />
</Style>

Here's how the DatePicker looks like when it's expanded:

When inserting is allowed, then the DataGrid displays an empty row at the bottom. Unfortunately there seems to be no way to display the intuitive asterisk in its row header (look here for my solution). When you start editing this row, the default constructor of the bound type is called to initialize the cells. Here's how the one for the Formula 1 driver looks like:
/// <summary>
/// Initializes a new instance of the FormulaOneDriver class.
/// </summary>
public FormulaOneDriver()
{
    this.Name = "<Name>";
}


For completeness' sake, here's the full XAML for the sample form:
<Window
   x:Class="U2UConsult.DockOfTheBay.DataGridSampleWindow"
   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   xmlns:local="clr-namespace:U2UConsult.DockOfTheBay"
   xmlns:toolkit="http://schemas.microsoft.com/wpf/2008/toolkit"
   Title="WPF DataGrid Sample"
   SizeToContent="WidthAndHeight"
   Icon="/DataGridSample;component/dotbay.png" >
    <Window.Resources>
        <Style TargetType="{x:Type toolkit:DatePickerTextBox}">
            <Setter Property="Text" Value="None" />
            <Setter Property="Background" Value="#00000000" />
        </Style>
    </Window.Resources>
    <Grid>
        <toolkit:DataGrid
           x:Name="DriversDataGrid"
           ItemsSource="{Binding Source={x:Static local:FormulaOneDriver.GetAll}}"
           AutoGenerateColumns="False"
           CanUserAddRows="True"
           CanUserDeleteRows="True"
           CanUserSortColumns="True"
           CanUserReorderColumns="True"
           AlternatingRowBackground="WhiteSmoke"
           RowHeaderWidth="16"
           Grid.Column="0" Grid.Row="0"
           HorizontalAlignment="Center"
           VerticalAlignment="Center">
            <toolkit:DataGrid.Columns>
                <toolkit:DataGridTextColumn
                   Header="Name"
                   Binding="{Binding Name}"
                   CanUserReorder="True"
                   IsReadOnly="False"
                   CanUserSort="True"
                   SortMemberPath="Name"/>
                <toolkit:DataGridComboBoxColumn
                   x:Name="TeamsCombo"
                   Header="Team"
                   ItemsSource="{Binding Source={x:Static local:FormulaOneTeam.GetAll}}"
                   SelectedValueBinding="{Binding TeamId}"
                   SelectedValuePath="Key"
                   DisplayMemberPath="Value.Name"
                   SortMemberPath="Team.Name" />
                <toolkit:DataGridTextColumn
                   Header="Pole Positions"
                   Binding="{Binding PolePositions}"
                   CanUserSort="True" />
                <toolkit:DataGridTemplateColumn
                   Header="Latest Victory"
                   CanUserSort="True"
                   SortMemberPath="LatestVictory">
                    <toolkit:DataGridTemplateColumn.CellTemplate >
                        <DataTemplate >
                            <toolkit:DatePicker
                               SelectedDate="{Binding LatestVictory}"
                               BorderThickness="0"/>
                        </DataTemplate >
                    </toolkit:DataGridTemplateColumn.CellTemplate >
                </toolkit:DataGridTemplateColumn>
            </toolkit:DataGrid.Columns>
        </toolkit:DataGrid>
    </Grid>
</Window>

And here's the C# code:
namespace U2UConsult.DockOfTheBay
{
    using System.Windows;

    /// <summary>
    /// Two-way binding to a Data Grid Sample.
    /// </summary>
    /// <remarks>Not much to see here: it's all in the XAML.</remarks>
    public partial class DataGridSampleWindow : Window
    {
        /// <summary>
        /// Initializes a new instance of the DataGridSampleWindow class.
        /// </summary>
        public DataGridSampleWindow()
        {
            InitializeComponent();
        }
    }
}
Labels:

In WPF, SelectionChanged does not mean that the selection changed

Windows Presentation Foundation's Routed Events can lead to unexpected or at least nonintuitive behavior when using TabControls that contain ListViews and/or ComboBoxes. A routed event generally bubbles from the control that raised it, up the whole element tree until the root. On its way up it invokes handlers on multiple listeners. This makes a lot of sense in the ButtonBase.Click Event: if a button is clicked, then its containing element is also clicked.

By design, the Selector.SelectionChanged Event is such a routed event. TabItem, ListBox, and ComboBox all inherit from Selector, so if you put them in a hierarchy they will register on each other's events. A ComboBox that appears via a template in a ListBox will raise the SelectionChanged event of that ListBox - even if the user didn't select a new ListBoxItem. If you put that ListBox in a TabControl, then the SelectionChanged on that TabControl will also be fired - even if the user didn't select a new TabItem.

Enough talking: let's build a small demo. First we build a Window with a TabControl that has a ComboBox in its first TabItem:

XAML

<Window x:Class="DockOfTheBay.SelectorSampleWindow"
       xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
       xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
       Title="Selector sample" Height="200" Width="400" >
    <Grid>
        <TabControl
           x:Name="MainTabControl"
           SelectionChanged="MainTabControl_SelectionChanged" >
            <TabItem Header="Courses" >
                <TabItem.Content>
                    <ListBox x:Name="ListBox1" />
                </TabItem.Content>
            </TabItem>
            <TabItem Header="Classrooms" >
            </TabItem>
        </TabControl>
    </Grid>
</Window>

C#

/// <summary>
/// The selection in the main tab control was changed.
/// </summary>
/// <param name="sender">Sender of the event: the Main Tab.</param>
/// <param name="e">Event arguments.</param>
private void MainTabControl_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    // First Tab
    if (this.MainTabControl.SelectedIndex == 0)
    {
        // (Re-)Populate ListBox
        this.ListBox1.Items.Clear();
        this.ListBox1.Items.Add("Building Windows Applications with WPF, LINQ and WCF");
        this.ListBox1.Items.Add("Building Cloud based Enterprise Applications for Windows Azure");
        this.ListBox1.Items.Add("Designing Data Warehouses using Dimensional Modeling");
        this.ListBox1.Items.Add("Upgrade to SharePoint 2010");
    }
}



Switching from one tab to another behaves nicely. Unfortunately, clicking in the ListBox now also triggers the SelectionChanged event from the TabControl itself, resulting in unexpected behavior:



Before diving into solutions, let's make it worse by implementing a very popular pattern. When the user navigates to a new TabItem, we populate a ListBox, and programatically select its first item. In theory there's nothing wrong with this, in practice it creates an infinite loop (well, it's not really infinite: it stops when you're out of stack space):



We can solve this by letting the child controls prevent the event from propagating -via the Handled property- like this:

XAML

<TabItem Header="Courses" >
    <TabItem.Content>
        <ListBox x:Name="ListBox1"
                SelectionChanged="ListBox1_SelectionChanged" />
    </TabItem.Content>
</TabItem>

C#

/// <summary>
/// The selection in the listbox was changed.
/// </summary>
/// <param name="sender">Sender of the event: the ListBox.</param>
/// <param name="e">Event arguments.</param>
private void ListBox1_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    // Stop the event from bubbling.
    e.Handled = true;
}

Of course, not all child controls will have an event handler or even need one (e.g. a ComboBox bound to a property of a Business Entity). Moreover, the parent control can not depend on the implementation of his children. Fortunately the parent control can decide to ignore all bubbled events from child controls like this:

C#

/// <summary>
/// The selection in the main tab control was changed.
/// </summary>
/// <param name="sender">Sender of the event: the Main Tab.</param>
/// <param name="e">Event arguments.</param>
private void MainTabControl_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    // Ignore Routed Events from children
    if (e.OriginalSource == this.MainTabControl)
    {
        // First Tab
        if (this.MainTabControl.SelectedIndex == 0)
        {
            // (Re-)Populate ListBox
            this.ListBox1.Items.Clear();
            this.ListBox1.Items.Add("Building Windows Applications with WPF, LINQ and WCF");
            this.ListBox1.Items.Add("Building Cloud based Enterprise Applications for Windows Azure");
            this.ListBox1.Items.Add("Designing Data Warehouses using Dimensional Modeling");
            this.ListBox1.Items.Add("Upgrade to SharePoint 2010");

            // Select first item (no more 'Kaboom')
            this.ListBox1.SelectedIndex = 0;
        }
    }
}

In practice this means that you should program this check on OriginalSource not only in every TabControl (because its TabItems can contain ListBoxes and ComboBoxes), but also in every ListBox (because its template can contain ComboBoxes).
Labels:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

This error occurs very often after publishing an Oracle-consuming Web Site, WCF Service, or SSRS Report to the production environment. Your first reaction should be to check if indeed an Oracle client ìs installed on the machine. Microsoft's System.Data.OracleClient is just not enough.

If an Oracle client was installed and the error still occurs, then you should check its ACLs. Your service might be running under an account that does not have file permissions to the Oracle client runtime. You can solve this issue by making sure that the content of the ORACLE_HOME directory is visible to all technical and impersonated users on the machine - in reality that simply boils down to all Authenticated Users.

Here's the recipe:
  • Log on to the server as a user with Administrator privileges.
  • Start Window Explorer and navigate to the ORACLE_HOME folder, most probably C:\oracle\version.
  • Rightclick and select "Properties" on the ORACLE_HOME folder.
  • Click the “Security” tab of the “Properties” window.
  • Select “Authenticated Users” item in the “Group or User names” list.
  • Uncheck and then recheck the “Read and Execute” box in the “Permissions” list under the “Allow” column.
  • Click the “Advanced” button in the “Permission Entries” to verify that “Authenticated Users” are listed with permission = “Read & Execute”, and make sure that "Apply To" shows “This folder, subfolders and files”.
  • Click “OK” buttons to close all of the security properties windows. Be patient, it may take Windows a couple of seconds to complete the modifications.
  • Restart IIS and try the application. If the problem persists, then reboot (security information is almost always cached somewhere...).

Accessing controls in a WPF ItemTemplate

Programmatically accessing a control in an ItemTemplate is not so straightforward. We clearly need to call the FindName method, but not before we first find the receiver of that call...

Let's say we have a listbox databound to a list of Book instances. The Book class looks like this:

namespace DockOfTheBay
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
 
    /// <summary>
    /// Sample book class.
    /// </summary>
    public class Book
    {
        /// <summary>
        /// Gets or sets the title of the book.
        /// </summary>
        public string Title { get; set; }
 
        /// <summary>
        /// Get or sets a value indicating whether the book is still on sale.
        /// </summary>
        public bool Discontinued { get; set; }
    }
}

Books are displayed via an itemtemplate that contains a checkbox bound to the Discontinued property. For one or another reason, we want to programmatically enable or disable that checkbox. For demo purposes, let's implement a button that toggles the IsEnabled property of all checkboxes.

This is the XAML for the demo form:

<Window x:Class="DockOfTheBay.Window1"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:DockOfTheBay"
        Title="ItemTemplate Access" 
        Height="160" Width="200" 
        ResizeMode="NoResize">
 
    <Window.Resources>
        <DataTemplate x:Key="BookTemplate" DataType="{x:Type local:Book}">
            <Grid>
                <Grid.RowDefinitions>
                    <RowDefinition Height="auto"/>
                </Grid.RowDefinitions>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="auto" SharedSizeGroup="Title" />
                    <ColumnDefinition Width="auto" SharedSizeGroup="Discontinued" />
                </Grid.ColumnDefinitions>
                <TextBlock Text="{Binding Path=Title}" 
                           Grid.Row="0" Grid.Column="0" Margin="2"/>
                <CheckBox x:Name="DiscontinuedCheckBox"
                          IsChecked="{Binding Discontinued}" 
                          IsEnabled="False" 
                          Grid.Row="0" Grid.Column="1"
                          Margin="2"/>
            </Grid>
        </DataTemplate>
    </Window.Resources>
 
    <Grid Grid.IsSharedSizeScope="True">
        <Grid.RowDefinitions>
            <RowDefinition Height="auto"/>
            <RowDefinition Height="auto" />
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="*"/>
        </Grid.ColumnDefinitions>
        <ListBox x:Name="BookListBox"
                 ItemTemplate="{StaticResource BookTemplate}" 
                 Grid.Row="0" Grid.Column="0" 
                 HorizontalAlignment="Center" 
                 Margin="4" BorderThickness="0" />
        <Button x:Name="ToggleButton" 
                Click="ToggleButton_Click" 
                Content="Toggle Checkbox Access"
                Grid.Row="1" Grid.Column="0" 
                HorizontalAlignment="Center"
                Margin="4" />
    </Grid>
</Window>


The form looks like this:


Let's implement the button's event handler. We have to iterate through the listbox's items. Thanks to data binding, these items are all Book -not ListBoxItem- instances. We gain access to the surrounding user interface element through a ItemContainerGenerator.ContainerFromItem call. The Content property of that listBoxItem refers back to the Book instance, so in order to access the child controls we have to follow another path: that of its ContentPresenter. We find it by walking through the VisualTree, looking for an instance of the appropriate type. The next extension method can be very useful here:

namespace DockOfTheBay
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Windows;
    using System.Windows.Media;
 
    /// <summary>
    /// Extension methods to the DependencyObject class.
    /// </summary>
    public static class DependencyObjectExtensions
    {
        /// <summary>
        /// Find a child of a specific type in the Visual Tree.
        /// </summary>
        public static T FindVisualChild<T>(this DependencyObject obj) where T : DependencyObject
        {
            for (int i = 0; i < VisualTreeHelper.GetChildrenCount(obj); i++)
            {
                DependencyObject child = VisualTreeHelper.GetChild(obj, i);
                if (child != null && child is T)
                {
                    return (T)child;
                }
                else
                {
                    T grandChild = child.FindVisualChild<T>();
                    if (grandChild != null)
                    {
                        return grandChild;
                    }
                }
            }
 
            return null;
        }
    }
}

Eventually, the ContentTemplate of that presenter is the DataTemplate instance to which we'll send the FindName call. Here's the C# for the entire form:

namespace DockOfTheBay
{
    using System.Collections.Generic;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Documents;
 
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class Window1 : Window
    {
        /// <summary>
        /// Initializes a new instance of the Window1 class.
        /// </summary>
        public Window1()
        {
            InitializeComponent();
            this.FillListBox();
        }
 
        /// <summary>
        /// Fills the listbox with some sample books.
        /// </summary>
        private void FillListBox()
        {
            List<Book> books = new List<Book>();
            books.Add(new Book { Title = "WPF Unleashed ", Discontinued = false });
            books.Add(new Book { Title = "WinForms in action", Discontinued = true });
            books.Add(new Book { Title = "The iBook for Dummies", Discontinued = false });
            books.Add(new Book { Title = "Essential WPF", Discontinued = false });
            this.BookListBox.ItemsSource = books;
        }
 
        /// <summary>
        /// Toggle the access to the CheckBox.
        /// </summary>
        /// <param name="sender">Sender of the event (button).</param>
        /// <param name="e">Event arguments.</param>
        private void ToggleButton_Click(object sender, RoutedEventArgs e)
        {
            // Iterate through Books
            foreach (var item in this.BookListBox.Items)
            {
                // Get the ListBoxItem around the Book
                ListBoxItem listBoxItem =
                    this.BookListBox.ItemContainerGenerator.ContainerFromItem(item) as ListBoxItem;
 
                // Get the ContentPresenter
                ContentPresenter presenter = listBoxItem.FindVisualChild<ContentPresenter>();
 
                // Get the Template instance
                DataTemplate template = presenter.ContentTemplate;
 
                // Find the CheckBox within the Template
                CheckBox checkBox = template.FindName("DiscontinuedCheckBox", presenter) as CheckBox;
                checkBox.IsEnabled = !checkBox.IsEnabled;
            }
        }
    }
}
Labels:

SQL Server 2008 Change Data Capture in 60 seconds

SQL Server 2008 Change Data Capture (CDC) is a management feature that allows you to track changes to the structure and content of tables in a database. Changes as a result of DML and DDL statements are registered asynchronously, so CDC is a welcome alternative to synchronous tracking features like table, database, and server triggers.

Let's start with the creation of a demo database with a table:

/********************************************/
/*                                          */
/* SQL Server 2008 Change Data Capture Demo */
/*                                          */
/********************************************/
 
/***********************************************************/
/* Preparation: Create a database and a table to play with */
/***********************************************************/
 
USE [master]
GO
 
IF EXISTS (SELECT name FROM sys.databases 
           WHERE name = N'DockOfTheBay')
DROP DATABASE [DockOfTheBay]
GO
 
CREATE DATABASE DockOfTheBay 
GO
 
USE [DockOfTheBay]
GO
 
CREATE TABLE [dbo].[TrackedTable]
(
    [ID] [int] NOT NULL,
    [Name] [varchar](100) NULL,
 
    CONSTRAINT [PK_TrackedTable] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)
GO

Before you can use it on a table, CDC needs to be enabled at database level: 

/**********************************/
/* Enabling CDC at database level */
/**********************************/
 
/* Check if CDC is enabled at DB level */
SELECT is_cdc_enabled 
  FROM sys.databases 
 WHERE [name] = 'DockOfTheBay'
 
/* Activate CDC at DB level */
EXEC sys.sp_cdc_enable_db
GO

As a result of this activation, a schema with the name of cdc is generated and populated with some system tables:



When CDC is enabled for the database, you can enable it for each table that you want to track. Here's how this goes:

/*******************************/
/* Enabling CDC at table level */
/*******************************/
 
/* Activate CDC at table level */
EXEC sys.sp_cdc_enable_table 
     @source_schema = 'dbo', 
       @source_name = 'TrackedTable',
         @role_name = 'CDC_Admin'
GO
 
/* Check if CDC is enabled at table level */
SELECT is_tracked_by_cdc 
  FROM sys.tables 
 WHERE [name] = 'TrackedTable'

The system tables in the cdc schema get populated with metadata about the tracking: they will hold tables and columns that are being tracked:

/* Check tracking meta data */
SELECT *
  FROM cdc.change_tables
 
SELECT *
  FROM cdc.captured_columns
GO



Using CDC requires the SQL Server Agent to be running, since he will run two jobs that control the log readers:



The engine is running now, so let's give CDC a test drive. I'm first going to add a column to the tracked table. Shortly after that -remember, it's not synchronous- the ddl_history table in the cdc schema should reflect the change:

/***************/
/* Tracing DML */
/***************/
 
/* Modify Table Structure */
ALTER TABLE dbo.TrackedTable
ADD [Description] VARCHAR(255)
GO
 
/* Check (Please give the SQL Agent Job some slack) */
SELECT * 
  FROM cdc.ddl_history
GO



In SQL Server 2005, you can get the same result with event notifications, but that's a little bit more cumbersome (note: this is an understatement).

Let's see what happens if we apply some DML statements against the table. For each tracked table, a mirror table is created that holds the changes:

/***************/
/* Tracing DML */
/***************/
 
/* Modify Table Contents */
INSERT INTO [DockOfTheBay].[dbo].[TrackedTable]
     VALUES (1, 'X', 'X'),(2, 'Y', 'Y')
 
/* Check Table */
SELECT *
  FROM cdc.dbo_TrackedTable_CT
 
/* More DML */
UPDATE dbo.TrackedTable
   SET [Name] = 'Z'
 WHERE ID = 1
 
 DELETE dbo.TrackedTable
  WHERE ID = 2
 
 UPDATE dbo.TrackedTable
    SET [Description] = 'Ignored :-('
 GO
 
/* Check Table */
SELECT *
  FROM cdc.dbo_TrackedTable_CT


As you can see, the Description column that was added to the table, is not tracked. I'll solve that in a minute.

The __$operation column identifies the DML operation (insert, before update, after update, delete, or merge):

/* Check Table 2 */
SELECT CASE __$operation 
            WHEN 1 THEN 'Delete'
            WHEN 2 THEN 'Insert'
            WHEN 3 THEN 'Before Update'
            WHEN 4 THEN 'After Update'
            WHEN 5 THEN 'Merge' 
       END AS Operation,
       ID,
       [Name]
  FROM cdc.dbo_TrackedTable_CT




Let's walk the whole mile and reverse engineer the contents of the tracking table back to DML:

/* Regenerate DML */
SELECT CASE __$operation 
            WHEN 1 THEN 
                'DELETE FROM TrackedTable' + 
                ' WHERE id = ' + CONVERT(VARCHAR(20), id) +
                ' AND [Name] = ''' + [Name] + ''''
            WHEN 2 THEN 
                'INSERT INTO TrackedTable' +
                '(id, [Name]) VALUES (' +
                CONVERT(VARCHAR(20),id) + ', ''' +
                [Name] + '''' + ')'
            WHEN 3 THEN 
                'DELETE FROM TrackedTable'+ 
                ' WHERE id = ' + CONVERT(VARCHAR(20), id) +
                ' AND [Name] = ''' + [Name] + ''''
            WHEN 4 THEN 
                'INSERT INTO TrackedTable' +
                '(id, [Name]) VALUES (' +
                CONVERT(VARCHAR(20),ID) + ', ''' +
                [Name] + '''' + ')'
            WHEN 5 THEN 
                'MERGE ...' 
       END AS [Regenerated DML]
  FROM cdc.dbo_TrackedTable_CT
 ORDER BY __$start_lsn



Nice, isn't it? Well, unfortunately some of the changes were not registered, since CDC only tracks the columns that existed when the tracking was initiated for the table. It is unaware of my Description column, so let's bring that column in the scope:

/********************************/
/* Changing tracking properties */
/********************************/
 
/* Disable CDC at table level             */
/* WARNING: this drops the tracking table */
EXEC sys.sp_cdc_disable_table
        @source_schema = 'dbo', 
          @source_name = 'TrackedTable',
     @capture_instance = 'dbo_TrackedTable'
GO
 
 /* Table is gone ... */
SELECT *
  FROM cdc.dbo_TrackedTable_CT
 
/* Re-enable with a specific column list */
EXEC sys.sp_cdc_enable_table 
            @source_schema = 'dbo', 
              @source_name = 'TrackedTable',
                @role_name = 'CDC_Admin',
     @captured_column_list = 'ID, Name, Description' 
GO
 
/* Check columns */
SELECT *
  FROM cdc.captured_columns
 
/* Table is back */
SELECT *
  FROM cdc.dbo_TrackedTable_CT

And finally, the cleanup:

/***********/
/* Cleanup */
/***********/
 
/* Disable CDC */
EXEC sys.sp_cdc_disable_db
GO
 
/* SQL Agent Jobs are gone ... */
SELECT *
  FROM msdb.dbo.sysjobs
 WHERE [name] LIKE 'cdc.%'
 
USE [master]
GO
 
/* Drop db */
DROP DATABASE DockOfTheBay
GO
Labels:

GROC (Getting Rid Of Cursors) part four: the T-SQL Pivot operator

This is part four of the GROC-files. I guess it's time to discover some syntactical constructions that make the use of cursors (and also temporary tables) obsolete. One of these constructions was added in SQL server 2005: the PIVOT operator. This operator allows you to write crosstab or matrix queries in a more-or-less relational way.

Let's say we have a relatively normalized data model that has a DailySales table or a view like the following:

  SELECT * 
    FROM DailySales
ORDER BY ProductName, [DayofWeek]



In most cases our business analysts/managers/sales people would like this information to be presented in a less normalized structure, like this:



Each value in the original DayOfWeek column is presented as a column name in the new resultset. The content of that column is the SUM of OrderQuantity after a GROUP BY ProductName is applied. I can imagine that a large number of database developers would attack this problem by creating a table variable -or worse: a temporary table- and then run through the DailySales table with a cursor to do INSERTs and UPDATEs. Well, this huge overkill is not necessary: the PIVOT statement can take care of everything. Here's the T-SQL statement that generates the pivoted result:

SELECT ProductName, 
       Monday, 
       Tuesday, 
       Wednesday, 
       Thursday,
       Friday, 
       Saturday, 
       Sunday
  FROM DailySales
 PIVOT (
          SUM(OrderQuantity) 
          FOR [DayOfWeek] IN 
          (
             [Monday], [Tuesday], [Wednesday], [Thursday],[Friday], [Saturday], [Sunday]
          )
       ) As pvt

The only disadvantage is that the list of columns should be enumerated in your query, but with some dynamic SQL you can get around this.

By the way, there is not much magic involved under the hood. The Pivot statement is just syntactic sugar for CASE constructions. The following query uses more traditional T-SQL, but returns exactly the same result, with the same performance (in SQL Server 2008 even with the exact same query plan steps):

  SELECT ProductName,
         SUM(CASE WHEN [DayOfWeek] = 'Monday' THEN OrderQuantity END) AS Monday,
         SUM(CASE WHEN [DayOfWeek] = 'Tuesday' THEN OrderQuantity END) AS Tuesday,
         SUM(CASE WHEN [DayOfWeek] = 'Wednesday' THEN OrderQuantity END) AS Wednesday,
         SUM(CASE WHEN [DayOfWeek] = 'Thursday' THEN OrderQuantity END) AS Thursday,
         SUM(CASE WHEN [DayOfWeek] = 'Friday' THEN OrderQuantity END) AS Friday,
         SUM(CASE WHEN [DayOfWeek] = 'Saturday' THEN OrderQuantity END) AS Saturday,
         SUM(CASE WHEN [DayOfWeek] = 'Sunday' THEN OrderQuantity END) AS Sunday         
    FROM DailySales
GROUP BY ProductName

If you want to testdrive the queries, then here's the full script:

/*********************************************/
/*                                           */
/* SQL Server 2005/2008 Pivot Statement Demo */
/*                                           */
/*********************************************/
 
-- If you use the SQL2005 AdventureWorksDW 
-- then replace DimDate by DimTime         
--          and DateKey by TimeKey         
 
USE AdventureWorksDW2008
GO
 
/*********************************/
/* What we have in the database  */
/* A 3rd Normal Form(-ish) model */
/*********************************/
 
;WITH DailySales AS
(
    SELECT p.EnglishProductName AS ProductName, 
           SUM(f.OrderQuantity) AS OrderQuantity, 
           d.EnglishDayNameOfWeek AS [DayofWeek] 
      FROM FactInternetSales f 
INNER JOIN DimProduct p 
        ON f.ProductKey = f.ProductKey 
INNER JOIN DimDate d 
        ON f.OrderDateKey = d.DateKey 
  GROUP BY p.EnglishProductName, 
           d.EnglishDayNameOfWeek, 
           d.DayNumberOfWeek
)
  SELECT * 
    FROM DailySales
ORDER BY ProductName, [DayofWeek]
 
/*********************************/
/* What our boss wants to see    */
/* A heavily denormalized report */
/*********************************/
 
;WITH DailySales AS
(
    SELECT p.EnglishProductName AS ProductName, 
           SUM(f.OrderQuantity) AS OrderQuantity, 
           d.EnglishDayNameOfWeek AS [DayofWeek] 
      FROM FactInternetSales f 
INNER JOIN DimProduct p 
        ON f.ProductKey = f.ProductKey 
INNER JOIN DimDate d 
        ON f.OrderDateKey = d.DateKey 
  GROUP BY p.EnglishProductName, 
           d.EnglishDayNameOfWeek, 
           d.DayNumberOfWeek
)
SELECT ProductName, 
       Monday, 
       Tuesday, 
       Wednesday, 
       Thursday,
       Friday, 
       Saturday, 
       Sunday
  FROM DailySales
 PIVOT (
          SUM(OrderQuantity) 
          FOR [DayOfWeek] IN 
          (
             [Monday], [Tuesday], [Wednesday], [Thursday],[Friday], [Saturday], [Sunday]
          )
       ) As pvt
 
/***************************************/       
/* What SQL Server does under the hood */
/* A Select Case T-SQL statement       */
/***************************************/
 
;WITH DailySales AS
(
    SELECT p.EnglishProductName AS ProductName, 
           SUM(f.OrderQuantity) AS OrderQuantity, 
           d.EnglishDayNameOfWeek AS [DayofWeek] 
      FROM FactInternetSales f 
INNER JOIN DimProduct p 
        ON f.ProductKey = f.ProductKey 
INNER JOIN DimDate d 
        ON f.OrderDateKey = d.DateKey 
  GROUP BY p.EnglishProductName, 
           d.EnglishDayNameOfWeek, 
           d.DayNumberOfWeek
)
  SELECT ProductName,
         SUM(CASE WHEN [DayOfWeek] = 'Monday' THEN OrderQuantity END) AS Monday,
         SUM(CASE WHEN [DayOfWeek] = 'Tuesday' THEN OrderQuantity END) AS Tuesday,
         SUM(CASE WHEN [DayOfWeek] = 'Wednesday' THEN OrderQuantity END) AS Wednesday,
         SUM(CASE WHEN [DayOfWeek] = 'Thursday' THEN OrderQuantity END) AS Thursday,
         SUM(CASE WHEN [DayOfWeek] = 'Friday' THEN OrderQuantity END) AS Friday,
         SUM(CASE WHEN [DayOfWeek] = 'Saturday' THEN OrderQuantity END) AS Saturday,
         SUM(CASE WHEN [DayOfWeek] = 'Sunday' THEN OrderQuantity END) AS Sunday         
    FROM DailySales
GROUP BY ProductName


In this article Devin Knight explains how to pivot the same result set, using SQL Server Integration Services.
Labels:
Subscribe to: Posts (Atom)