(Sittin' on the) Dock of the Bay

To be continued ...

2010-01-10T11:19:00.000+01:00

This blog continues here.

Codeless two-way binding to a WPF DataGrid

2009-11-03T13:14:00.001+01:00

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 ). 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();
        }
    }
}

In WPF, SelectionChanged does not mean that the selection changed

2009-10-07T12:59:00.001+02:00

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.Content>

</TabItem.Content>

</TabItem>

</TabItem>

</TabControl>

</Grid>

</Window>

/// <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.Content>

<ListBox x:Name="ListBox1"

SelectionChanged="ListBox1_SelectionChanged" />

</TabItem.Content>

</TabItem>

/// <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).

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

2009-09-30T21:03:00.005+02:00

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

2009-09-21T17:10:00.002+02:00

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;

SQL Server 2008 Change Data Capture in 60 seconds

2009-08-28T14:44:00.004+02:00

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

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

2009-07-16T14:33:00.002+02:00

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.

Validating mutually dependent properties in WPF

2009-07-15T15:32:00.001+02:00

In WPF the Validate method of the ValidationRule class takes as parameter a single value provided by the GUI. This is fine if you want to do a typecheck or a rangecheck on an isolated field, but what if you want to validate two -or more- mutually dependent values (e.g. an end date versus a begin date) ?

Let's say you have a Person class that exposes DateOfBirth and DateOfDeath properties. In most religions these two DateTime properties have the canonical chronologic dependency between them. So if you type an illegal value in the date of death textbox, you would expect a warning like this:

Likewise if the death date was already filled and you're entering the date of birth, you would expect an appropriate warning:

Unfortunately there's nothing in WPF like the ASP.NET CompareValidator class, so you have to come up with some coding yourself. Writing a custom validation rule that compares two entered values is quite cumbersome (but still possible: you find an example here).

If you're developing against WPF v3.5 or higher, then there is an elegant alternative. First you make the class to which the GUI controls are bound implement the good old IDataErrorInfo interface. You're specifically interested in the indexer on property name. This is were you write the validation code. Here's an example of the Person class, implementing the interface:

//-----------------------------------------------------------------------

// <copyright file="Person.cs" company="DockOfTheBay">

//     http://www.dotbay.be

// </copyright>

// <summary>Defines the Person class.</summary>

//-----------------------------------------------------------------------

namespace DockOfTheBay

    using System;

    using System.ComponentModel; // Home of the IDataErrorInfo interface.

    /// <summary>

    /// Sample class with mutually dependent properties.

    /// </summary>

    public class Person : IDataErrorInfo

        /// <summary>

        /// Gets or sets the date of birth.

        /// </summary>

        public DateTime? DateOfBirth { get; set; }

        /// <summary>

        /// Gets or sets the date of death.

        /// </summary>

        public DateTime? DateOfDeath { get; set; }

        /// <summary>

        /// Gets an error message indicating what is wrong with this object.

        /// The default is an empty string ("").

        /// </summary>

        /// <remarks>

        /// Member of IDataErrorInfo.

        /// </remarks>

        public string Error

get

                return string.Empty;

        /// <summary>

        /// Gets the error message for the property with the given name.

        /// </summary>

        /// <param name="propertyName">

        /// The name of the property whose error message to get.

        /// </param>

        /// <returns>

        /// The error message for the property.

        /// The default is an empty string ("").

        /// </returns>

        /// <remarks>

        /// Member of IDataErrorInfo.

        /// </remarks>

        public string this[string propertyName]

get

                if (propertyName == "DateOfBirth")

                    DateTime birth = this.DateOfBirth ?? DateTime.MinValue;

                    DateTime death = this.DateOfDeath ?? DateTime.MaxValue;

                    if (birth > death)

                        return "This date lies after the date of death.";

                else if (propertyName == "DateOfDeath")

                    DateTime birth = this.DateOfBirth ?? DateTime.MinValue;

                    DateTime death = this.DateOfDeath ?? DateTime.MaxValue;

                    if (birth > death)

                        return "This date lies before the date of birth.";

                return string.Empty;

All you need to do to trigger this code from XAML is use the DataErrorValidationRule inside the Binding.ValidationRules, or alternatively use the Binding.ValidatesOnDataErrors property. Here's an example of the first approach; it's the content of the form that I used for the above screenshots:

<Grid Margin="4 3">

    <!-- Resources -->

    <Grid.Resources>

        <local:Person x:Key="Person" x:Name="Person" />

        <Style TargetType="TextBox">

            <Setter Property="Margin" Value="4 3" />

            <Style.Triggers>

                <Trigger Property="Validation.HasError" Value="true">

                    <Setter Property="ToolTip"

                            Value="{Binding RelativeSource={RelativeSource self},

                                            Path=(Validation.Errors)[0].ErrorContent}" />

                    <Setter Property="Foreground" Value="Red" />

                </Trigger>

            </Style.Triggers>

        </Style>

    </Grid.Resources>

    <!-- Binding Context -->

    <Grid.DataContext>

        <Binding Source="{StaticResource Person}"/>

    </Grid.DataContext>

    <!-- Structure -->

    <Grid.ColumnDefinitions>

        <ColumnDefinition Width="auto" />

        <ColumnDefinition Width="*" />

    </Grid.ColumnDefinitions>

    <Grid.RowDefinitions>

        <RowDefinition Height="auto" />

        <RowDefinition Height="auto" />

    </Grid.RowDefinitions>

    <!-- Content -->

    <Label Content="Date of birth:"

           Grid.Row="0" Grid.Column="0"

           HorizontalAlignment="Right" />

    <TextBox x:Name="BirthTextBox"

             Grid.Row="0" Grid.Column="1" >

        <Binding Path="DateOfBirth"

                 UpdateSourceTrigger="PropertyChanged"

                 TargetNullValue="{x:Static sys:String.Empty }" >

            <Binding.ValidationRules>

                <DataErrorValidationRule />

            </Binding.ValidationRules>

        </Binding>

    </TextBox>

    <Label Content="Date of death:"

           Grid.Row="1" Grid.Column="0"

           HorizontalAlignment="Right" />

    <TextBox x:Name="DeathTextBox"

             Grid.Row="1" Grid.Column="1">

        <Binding Path="DateOfDeath"

                 UpdateSourceTrigger="PropertyChanged"

                 TargetNullValue="{x:Static sys:String.Empty }" >

            <Binding.ValidationRules>

                <DataErrorValidationRule />

            </Binding.ValidationRules>

        </Binding>

    </TextBox>

</Grid>

Finding the parent of a process in C#

2009-07-09T09:24:00.004+02:00

If you want to find the parent process of a Windows process from .NET, there are a couple of alternatives. None of these alternatives is very straightforward, because this functionality is not a built into the System.Diagnostics.Process class. Well ... at least not by default. You can of course inject it via an extension method -say ParentProcess()- to enable calls like the following:

            // Find my own parent.

            Process myProcess = Process.GetCurrentProcess();

            Console.WriteLine(myProcess.ParentProcess().ProcessName);

            // Create a child, and find its parent.

            Process p = Process.Start("notepad.exe");

            Console.WriteLine(p.ParentProcess().ProcessName);

This code displays the parent of a test program, then starts a Notepad instance and requests its parent. The resulting console looks like this (with and without a debugger):

I've seen C# implementations that read performance counters or call WMI queries to get the parent process id, but I assume that the following extension method will outperform these:

//-----------------------------------------------------------------------

// <copyright file="ProcessExtensions.cs" company="DockOfTheBay">

//     http://www.dotbay.be

// </copyright>

// <summary>Defines the ProcessExtensions class.</summary>

//-----------------------------------------------------------------------

namespace DockOfTheBay

    using System;

    using System.Diagnostics;

    using System.Runtime.InteropServices;

    /// <summary>

    /// Extension Methods for the System.Diagnostics.Process Class.

    /// </summary>

    public static class ProcessExtensions

        /// <summary>

        /// Returns the Parent Process of a Process

        /// </summary>

        /// <param name="process">The Windows Process.</param>

        /// <returns>The Parent Process of the Process.</returns>

        public static Process ParentProcess(this Process process)

            int parentPid = 0;

            int processPid = process.Id;

            uint TH32CS_SNAPPROCESS = 2;

            // Take snapshot of processes

            IntPtr hSnapshot = CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0);

            if (hSnapshot == IntPtr.Zero)

                return null;

            PROCESSENTRY32 procInfo = new PROCESSENTRY32();

            procInfo.dwSize = (uint)Marshal.SizeOf(typeof(PROCESSENTRY32));

            // Read first

            if (Process32First(hSnapshot, ref procInfo) == false)

                return null;

            // Loop through the snapshot

do

                // If it's me, then ask for my parent.

                if (processPid == procInfo.th32ProcessID)

                    parentPid = (int)procInfo.th32ParentProcessID;

            while (parentPid == 0 && Process32Next(hSnapshot, ref procInfo)); // Read next

            if (parentPid > 0)

                return Process.GetProcessById(parentPid);

            else

                return null;

        /// <summary>

        /// Takes a snapshot of the specified processes, as well as the heaps,

        /// modules, and threads used by these processes.

        /// </summary>

        /// <param name="dwFlags">

        /// The portions of the system to be included in the snapshot.

        /// </param>

        /// <param name="th32ProcessID">

        /// The process identifier of the process to be included in the snapshot.

        /// </param>

        /// <returns>

        /// If the function succeeds, it returns an open handle to the specified snapshot.

        /// If the function fails, it returns INVALID_HANDLE_VALUE.

        /// </returns>

        [DllImport("kernel32.dll", SetLastError = true)]

        private static extern IntPtr CreateToolhelp32Snapshot(uint dwFlags, uint th32ProcessID);

        /// <summary>

        /// Retrieves information about the first process encountered in a system snapshot.

        /// </summary>

        /// <param name="hSnapshot">A handle to the snapshot.</param>

        /// <param name="lppe">A pointer to a PROCESSENTRY32 structure.</param>

        /// <returns>

        /// Returns TRUE if the first entry of the process list has been copied to the buffer.

        /// Returns FALSE otherwise.

        /// </returns>

        [DllImport("kernel32.dll")]

        private static extern bool Process32First(IntPtr hSnapshot, ref PROCESSENTRY32 lppe);

        /// <summary>

        /// Retrieves information about the next process recorded in a system snapshot.

        /// </summary>

        /// <param name="hSnapshot">A handle to the snapshot.</param>

        /// <param name="lppe">A pointer to a PROCESSENTRY32 structure.</param>

        /// <returns>

        /// Returns TRUE if the next entry of the process list has been copied to the buffer.

        /// Returns FALSE otherwise.</returns>

        [DllImport("kernel32.dll")]

        private static extern bool Process32Next(IntPtr hSnapshot, ref PROCESSENTRY32 lppe);

        /// <summary>

        /// Describes an entry from a list of the processes residing

        /// in the system address space when a snapshot was taken.

        /// </summary>

        [StructLayout(LayoutKind.Sequential)]

        private struct PROCESSENTRY32

            public uint dwSize;

            public uint cntUsage;

            public uint th32ProcessID;

            public IntPtr th32DefaultHeapID;

            public uint th32ModuleID;

            public uint cntThreads;

            public uint th32ParentProcessID;

            public int pcPriClassBase;

            public uint dwFlags;

            [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 260)]

            public string szExeFile;

GROC (Getting Rid Of Cursors) part three: Cursor Details

2009-07-01T14:00:00.002+02:00

This is the third part of the "GROC-files". I will show you how to enumerate the details about the cursors in the scope of a sql module. You can use this information to figure out if these cursors can or should be optimized.

If you already have a reference (e.g. via a variable or a parameter) to cursors in the current scope, then it suffices to call sp_describe_cursor, and probably sp_describe_cursor_columns and sp_describe_cursor_tables. If you don't have a reference (e.g. within a stored procedure that is called from the body of a cursor) then you can just call sp_cursor_list. Well ... 'just call' may be an understatement. All these system stored procedures use a cursor as OUTPUT variable, so there's some work involved. In a sense I find it cruel that in order to see details about cursors you are forced to create yet another cursor. But anyway, here's how you can find out if there are cursors hanging around in your current scope:

USE AdventureWorks

GO

/************************/

/* Using sp_cursor_list */

/************************/

-- More info about the returned columns:

-- http://msdn.microsoft.com/en-us/library/ms186256.aspx

-- Declare and open a keyset-driven cursor.

DECLARE c1 CURSOR GLOBAL DYNAMIC

FOR

  SELECT LastName

    FROM Person.Contact

   WHERE LastName LIKE 'S%'

ORDER BY LastName

OPEN c1

-- Declare a cursor variable to hold the cursor output variable

-- from sp_cursor_list.

DECLARE @Report CURSOR

-- Execute sp_cursor_list into the cursor variable.

EXEC master.dbo.sp_cursor_list

  @cursor_return = @Report OUTPUT,

  @cursor_scope = 3 -- Local and Global

-- Fetch all the rows from the sp_cursor_list output cursor.

FETCH NEXT from @Report

WHILE (@@FETCH_STATUS <> -1)

BEGIN

   FETCH NEXT from @Report

END

-- Close and deallocate the cursor from sp_cursor_list.

CLOSE @Report

DEALLOCATE @Report

GO

-- Close and deallocate the original cursor.

CLOSE c1

DEALLOCATE c1

GO

The result of the call to sp_cursor_list is not very intuitive:

I can easily imagine that you would prefer an output like this:

You can achieve this by writing a wrapper procedure -or table valued function- around the system stored procedure. Here's an example:

/**********************************/

/*                                */

/* User-friendly Cursor List Demo */

/*                                */

/**********************************/

USE AdventureWorks

GO

/***************/

/* Preparation */

/***************/

CREATE SCHEMA DockOfTheBay

GO

/*******************************************/

/* User-friendly version of sp_cursor_list */

/*******************************************/

CREATE PROC DockOfTheBay.usp_cursor_list

AS

SET NOCOUNT ON

-- Build a table variable to hold the results from sp_cursor_list

DECLARE @Cursors TABLE

   reference_name char(30),

   cursor_name char(30),

   cursor_scope tinyint,

   [status] int,

   model tinyint,

   concurrency tinyint,

   scrollable tinyint,

   open_status tinyint,

   cursor_rows decimal(10,0),

   fetch_status smallint,

   column_count smallint,

   row_count decimal(10,0),

   last_operation tinyint,

   cursor_handle  int

-- Declare a cursor variable to hold the cursor output variable

-- from sp_cursor_list.

DECLARE @Report CURSOR

-- Declare variables to hold the fetched values from @Report

DECLARE

   @reference_name char(30),

   @cursor_name char(30),

   @cursor_scope tinyint,

   @status int,

   @model tinyint,

   @concurrency tinyint,

   @scrollable tinyint,

   @open_status tinyint,

   @cursor_rows decimal(10,0),

   @fetch_status smallint,

   @column_count smallint,

   @row_count decimal(10,0),

   @last_operation tinyint,

   @cursor_handle  int

-- Execute sp_cursor_list into the cursor variable.

EXEC master.dbo.sp_cursor_list

     @cursor_return = @Report OUTPUT,

     @cursor_scope = 3

-- Fetch the first row from the sp_cursor_list output cursor.

FETCH NEXT FROM @Report

INTO

   @reference_name,

   @cursor_name,

   @cursor_scope,

   @status,

   @model,

   @concurrency ,

   @scrollable ,

   @open_status,

   @cursor_rows,

   @fetch_status,

   @column_count,

   @row_count,

   @last_operation,

   @cursor_handle

WHILE (@@FETCH_STATUS = 0)

BEGIN

   -- Insert the values into the temp table

   INSERT INTO @Cursors

   VALUES (

           @reference_name,

           @cursor_name,

           @cursor_scope,

           @status,

           @model,

           @concurrency ,

           @scrollable ,

           @open_status,

           @cursor_rows,

           @fetch_status,

           @column_count,

           @row_count,

           @last_operation,

           @cursor_handle

   -- Fetch subsequent rows from @Report

   FETCH NEXT FROM @Report

   INTO

      @reference_name,

      @cursor_name,

      @cursor_scope,

      @status,

      @model,

      @concurrency ,

      @scrollable ,

      @open_status,

      @cursor_rows,

      @fetch_status,

      @column_count,

      @row_count,

      @last_operation,

      @cursor_handle

END

-- Close and deallocate the cursor from sp_cursor_list.

CLOSE @Report

DEALLOCATE @Report

-- Return the information from the table variable, translated into readable descriptions

IF (SELECT count(*) FROM @Cursors) > 0

  SELECT

   [Cursor Reference] = reference_name,

   [Declared Name] = cursor_name,

   [Scope] = CASE cursor_scope

     WHEN 1 THEN 'local'

     ELSE 'global'

   END,

   [Status] = CASE [status]

     WHEN 1  THEN 'open'

     WHEN 0  THEN 'no rows'

     WHEN -1 THEN 'closed'

     WHEN -2 THEN 'no reference'

     WHEN -3 THEN 'does not exist'

   END,

   [Model] =  CASE model

     WHEN 1 THEN 'static'

     WHEN 2 THEN 'keyset'

     WHEN 3 THEN 'dynamic'

     WHEN 4 THEN 'fast forward'

   END,

   [Locking] = CASE concurrency

     WHEN 1 THEN 'read only'

     WHEN 2 THEN 'scroll locks'

     WHEN 3 THEN 'optimistic'

   END,

   [Scrolling] = CASE scrollable

     WHEN 0 THEN 'forward only'

     WHEN 1 THEN 'scrollable'

   END,

   [Open] = CASE open_status

     WHEN 0 THEN 'no'

     WHEN 1 THEN 'yes'

   END,

   [Qualifying Rows] =  cursor_rows,

   [Fetch Status] = CASE fetch_status

     WHEN 0  THEN 'successful'

     WHEN -1 THEN 'out of bounds'

     WHEN -2 THEN 'missing row'

     WHEN -9 THEN 'no fetch done'

   END,

   [Columns] = column_count,

   [Rows for last operation] = row_count,

   [Last operation] = CASE last_operation

     WHEN 0 THEN 'none'

     WHEN 1 THEN 'OPEN'

     WHEN 2 THEN 'FETCH'

     WHEN 3 THEN 'INSERT'

     WHEN 4 THEN 'UPDATE'

     WHEN 5 THEN 'DELETE'

     WHEN 6 THEN 'CLOSE'

     WHEN 7 THEN 'DEALLOCATE'

   END,

   [Handle] = cursor_handle

  FROM @Cursors

GO

/*********/

/* Usage */

/*********/

-- Declare and open a keyset-driven cursor.

DECLARE c1 CURSOR DYNAMIC GLOBAL

FOR

  SELECT LastName

    FROM Person.Contact

   WHERE LastName LIKE 'S%'

ORDER BY LastName

OPEN c1

-- Call the user-friendly version.

exec DockOfTheBay.usp_cursor_list

-- Close and deallocate the original cursor.

CLOSE c1

DEALLOCATE c1

GO

/***********/

/* Cleanup */

/***********/

DROP PROC DockOfTheBay.usp_cursor_list

DROP SCHEMA DockOfTheBay

GO

DB2JAVIT RC 9505, SQL5005C System Error, and failing vsai installation

2009-06-30T16:24:00.002+02:00

If your favorite search engine brought you to this article, then you must be a .NET developer who installed IBM DB2 (Express) on a Windows Vista Box ... and then everything went wrong.

Here are a couple of symptoms that I experienced over the last couple of weeks, and how I solved them:

If on every reboot after installation you get a SQL5005C System Error, then you should add your user account to the local DB2ADMNS or to the local DB2USERS group. These groups are created during the DB2 installation, but left empty.
If some of the tools (like the Control Center) return a "DB2JAVIT : RC = 9505" error -or don't seem to do anything at all- then you should right click on the launch shortcut and select "Run as administrator".
If you downloaded the IBM Database Addins for Visual Studio (from the file 'db2exc_vsai_952_WIN_x86.zip'), and the installer repeatedly fails with "a major error occured" without any further explanation or log file, then you should download the whole server: yes, the more than half a GigaByte IBM DB2 9.7 Data Server 90-days Trial ('v9.7_nt32_server.zip'). Unzip the Server/image/VSAI/Windows subfolder and install the Visual Studio Add-ins from there. This one successfully upgraded both my Visual Studio.NET installations (2005 & 2008).

SQL Server Urban Legend: You can not rollback a TRUNCATE statement

2009-06-16T21:31:00.002+02:00

It's truly amazing how some really silly beliefs on SQL Server keep on coming back in discussions. Everybody continues to believe them, yet it's so easy to proof they're wrong. One of these 'urban legends' is the following: You can not rollback a TRUNCATE statement.

Every urban legend has a little grain of truth: indeed, one of the reasons why a TRUNCATE is faster than a DELETE has to do with the transaction log. That's the file containing all information to rollback your transactions properly. While a DELETE statement sends all the deleted records one-by-one to the transaction log, the TRUNCATE only saves the references to the deallocated pages. That's a lot less data, but still more than enough information for restoring the data when a rollback is encountered.

[For the sake of completeness: other reasons why a TRUNCATE is faster than a DELETE include bypassing constraints and triggers.]

Anyway, here's a little script to debunk the myth:

/****************************************************************/

/*              Microsoft SQL Server Urban Legends              */

/****************************************************************/

/*                                                              */

/* Urban Legend: "A TRUNCATE statement can not be rolled back." */

/*                                                              */

/****************************************************************/

IF OBJECT_ID('tempdb..#ProductTotals','U') IS NOT NULL

  DROP TABLE #ProductTotals

GO

CREATE TABLE #ProductTotals

   ProductID INT

  ,Revenue   MONEY

  INSERT

    INTO #ProductTotals (ProductID, Revenue)

  SELECT ProductID, SUM(UnitPrice * OrderQty)

    FROM AdventureWorks.Sales.SalesOrderDetail

GROUP BY ProductID

BEGIN TRANSACTION

    TRUNCATE TABLE #ProductTotals

    -- DELETE FROM #ProductTotals   /* Same result, but slower. */

ROLLBACK TRANSACTION

-- IF > 0 THEN BUSTED

SELECT COUNT(*) AS [Number of rows]

  FROM #ProductTotals

/****************************************************************/

/* Status: BUSTED!!!                                            */

/****************************************************************/

GROC (Getting Rid Of Cursors) part two: Cursor Resources

2009-06-09T11:22:00.003+02:00

This is the second part of the "GROC-files". I will focus on the resource consumption of cursors, especially tempdb space and locks. The variable that holds the cursor, and the FETCH INTO-variables also take some space, but I'm going to ignore that here. The following T-SQL script contains switches for most of the relevant factors:

cursor type,
cursor locking behavior,
locking hints in the query,
transaction isolation level, and
cursor close on commit setting.

If you run the script, it will return a resultset containing the resource consumption per step, like this:

Here are a couple of observations from several runs with different settings:

Static and keyset cursors can consume a considerable amount of tempdb memory.
Dynamic cursors don't use tempdb.
In higher transaction isolation levels, keyset cursors can take a lot of locks, and for a long period.
In higher transaction isolation levels, static cursors can take a lot of locks, and for a long period. [They copy everything to the tempdb and lock it in the user db !].
FOR UPDATE apparently has no impact on resources.
Cursor owned locks only appear with the SCROLL_LOCKS setting. All other locks belong to the transaction.
If the connection option to keep cursors open on a commit is set on, all locks will be kept across a commit or rollback operation.
A WITH (NOLOCK) hint avoids transaction as well as cursor owned locks. [So it actually overrides the SCROLL_LOCKS setting, which might no be what you wanted !]

Anyway, some of these observations confirm (again) that cursors should be avoided in most scenarios: they use more resources than necessary and are not easily controllable.

Some final advice:

Select as few as possible rows and columns in the cursor resultset (this advice actually applies to all possible resultsets in batches/transactions),
whenever possible, prefer dynamic cursors over static ones, and
explicitly set CURSOR_CLOSE_ON_COMMIT to ON in your session.

Here's the full test script:

/*************************/

/*                       */

/* Cursor Resources Demo */

/*                       */

/*************************/

USE AdventureWorks

GO

/***************/

/* Preparation */

/***************/

CREATE SCHEMA DockOfTheBay

GO

CREATE FUNCTION DockOfTheBay.ufn_TempDb_Internal_Objects

    @Session_id AS int

RETURNS int

AS

BEGIN

--

    -- Returns the number of KBytes allocated to tempdb internal objects for a session

--

    DECLARE @Result int

    -- rem: Caching and Deferred Drop operations may influence counters

    SELECT @Result = SUM((internal_objects_alloc_page_count - internal_objects_dealloc_page_count)) * 8

      FROM  tempdb.sys.dm_db_task_space_usage

     WHERE  session_id = @Session_id

       AND database_id = db_id('tempdb')

    RETURN @Result

END

GO

CREATE FUNCTION DockOfTheBay.ufn_Locks

    @Session_id AS int

RETURNS NVARCHAR(200)

AS

BEGIN

--

    -- Returns the number of locks per type and owner for a session, as a comma-separated list

--

    DECLARE @Result NVARCHAR(200)

    ;WITH Locks AS

        SELECT resource_type AS [Type]

              ,CONVERT(NVARCHAR(03), COUNT(*)) AS [Number]

              ,request_owner_type AS [Owner]

          FROM sys.dm_tran_locks

         WHERE request_session_id = @Session_id

           AND resource_type IN ('ROW', 'PAGE', 'RID', 'KEY')

         GROUP BY resource_type, request_owner_type

    SELECT @Result = COALESCE(@Result + ', ', '') + [Owner] + ' ' + [Type] + ' lock(s): ' + [Number]

    FROM Locks

    RETURN @Result

END

GO

/*************/

/* Execution */

/*************/

/* Switch option ON and OFF: */

SET CURSOR_CLOSE_ON_COMMIT /* ON */ OFF

/* Uncomment ONE of the following options at a time: */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

-- Table Variable to hold the measurement results

DECLARE @Measurements TABLE

    [Step] NVARCHAR(50)

   ,[TempDb KBytes] INT

   ,[Locks] NVARCHAR(200)

INSERT @Measurements

SELECT 'Transaction started'

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid)

      ,DockOfTheBay.ufn_Locks(@@spid)

-- Dummy variable for the FETCH operation

DECLARE @temp SQL_VARIANT

-- Declare and open a keyset-driven cursor.

DECLARE c1 CURSOR

    /* Please uncomment ONE of the following options at a time: */

    -- DYNAMIC

    DYNAMIC SCROLL_LOCKS

    -- KEYSET

    -- STATIC

FOR

    SELECT *

      FROM Production.ProductModel

      /* Uncomment to see the impact: */

      -- WITH (NOLOCK)

     WHERE ProductModelID > 1

     /* Uncomment to see the impact: */

     -- FOR UPDATE

OPEN c1

INSERT @Measurements

SELECT 'After Open' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

FETCH NEXT FROM c1 INTO @temp, @temp, @temp, @temp, @temp, @temp

INSERT @Measurements

SELECT 'After Fetch' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

COMMIT TRANSACTION

INSERT @Measurements

SELECT 'After Commit' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

-- Close and deallocate the cursor.

CLOSE c1

DEALLOCATE c1

INSERT @Measurements

SELECT 'After Deallocate' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

-- Display results

SELECT * FROM @Measurements

GO

/***********/

/* Cleanup */

/***********/

DROP FUNCTION DockOfTheBay.ufn_TempDb_Internal_Objects

DROP FUNCTION DockOfTheBay.ufn_Locks

DROP SCHEMA DockOfTheBay

GO

Rendering WPF Visuals to Bitmap

2009-06-05T12:51:00.003+02:00

Sometimes we need to convert a WPF Window or Control (or any other type of Visual) to a plain old GDI+ Bitmap. In WinForms, creating a Bitmap of a Window or Control is quite complex. In WPF it's a piece of cake, at least if you're comfortable with the citizens of the System.Windows.Media.Imaging namespace, like RenderTargetBitmap, and the range of BitmapEncoders. I created a little helper class with some extension methods to Visual to facilitate the process. The class comes with a PngBitmap method that returns a Bitmap (PNG format), and a BitmapSource method for data binding, e.g. to a WPF Image control.

This is a screenshot of a small sample. The textbox and ellipse in the upper part of the window are original WPF Controls. The lower part of the window is a bitmap of the upper part, displayed in an Image control:

The Window has the following XAML content:

    <StackPanel>

        <TextBlock

            Margin="10"

            Text="Dock of the Bay" />

        <Ellipse

            Margin="10"

            Height="50" Width="50"

            Fill="DeepSkyBlue" Stroke="MidnightBlue"

            HorizontalAlignment="Left" />

        <Image

            x:Name="ScreenShotImage" />

    </StackPanel>

The screenshot is taken, and displayed in the Image control with the following one-liner:

   this.ScreenShotImage.Source = this.BitmapSource();

Here's the whole extension class:

//-----------------------------------------------------------------------

// <copyright file="VisualExtensions.cs" company="DockOfTheBay">

//     http://www.dotbay.be

// </copyright>

// <summary>Defines the VisualExtensions class.</summary>

//-----------------------------------------------------------------------

namespace DockOfTheBay

    using System;

    using System.Drawing;           // Need reference to System.Drawing.dll

    using System.Windows;

    using System.Windows.Interop;   // Need reference to PresentationCore.dll

    using System.Windows.Media;

    using System.Windows.Media.Imaging;

    /// <summary>

    /// Extension Methods for the System.Windows.Media.Visual Class

    /// </summary>

    public static class VisualExtensions

        /// <summary>

        /// Returns the contents of a WPF Visual as a Bitmap in PNG format.

        /// </summary>

        /// <param name="visual">A WPF Visual.</param>

        /// <returns>A GDI+ System.Drawing.Bitmap.</returns>

        public static Bitmap PngBitmap(this Visual visual)

            // Get height and width

            int height = (int)(double)visual.GetValue(FrameworkElement.ActualWidthProperty);

            int width = (int)(double)visual.GetValue(FrameworkElement.ActualHeightProperty);

            // Render

            RenderTargetBitmap rtb =

                new RenderTargetBitmap(

                    height,

                    width,

96,

96,

                    PixelFormats.Default);

            rtb.Render(visual);

            // Encode

            PngBitmapEncoder encoder = new PngBitmapEncoder();

            encoder.Frames.Add(BitmapFrame.Create(rtb));

            System.IO.MemoryStream stream = new System.IO.MemoryStream();

            encoder.Save(stream);

            // Create Bitmap

            Bitmap bmp = new Bitmap(stream);

            stream.Close();

            return bmp;

        /// <summary>

        /// Returns the contents of a WPF Visual as a BitmapSource, e.g.

        /// for binding to an Image control.

        /// </summary>

        /// <param name="visual">A WPF Visual.</param>

        /// <returns>A set of pixels.</returns>

        public static BitmapSource BitmapSource(this Visual visual)

            Bitmap bmp = visual.PngBitmap();

            IntPtr hBitmap = bmp.GetHbitmap();

            BitmapSizeOptions sizeOptions = BitmapSizeOptions.FromEmptyOptions();

            return Imaging.CreateBitmapSourceFromHBitmap(

                                hBitmap,

                                IntPtr.Zero,

                                Int32Rect.Empty,

                                sizeOptions);

By the way, if you prefer to screen capture to XPS instead of to a Bitmap, then I suggest this article.

Screen capturing a WPF form

2009-06-03T08:59:00.005+02:00

In this article I explained how a form with a DocumentViewer control can be used to render, save, and print official documents - like contracts and registration forms. I'm using that same form now behind a Print Screen button or command, accessible from the application's main toolbar. It allows the user to generate a screenshot of the application's main window. Here's what you can type in the button_clicked or the command_executed code:

        /// <summary>

        /// Opens a ReportWindow with a screenshot of the whole main window and its contents.

        /// </summary>

        /// <param name="sender">The (toolbar) button that was clicked.</param>

        /// <param name="e">The Event Args.</param>

        private void PrintScreenButton_Click(object sender, RoutedEventArgs e)

            XpsWysiwyg reportWindow = new XpsWysiwyg();

            reportWindow.Title = "Schermafdruk"; // That's just Dutch for 'ScreenShot' ;-)

            reportWindow.WindowState = WindowState.Normal;

            reportWindow.ElementToRender = this; // The Main Window

            reportWindow.documentViewer1.FitToMaxPagesAcross(1); // Zoom to whole Page

            reportWindow.ShowDialog();

Here's how it looks like in a real application:

Finding the owner of a Process in C#

2009-06-02T10:22:00.002+02:00

The System.Diagnostics.Process class provides lots of information about any running process, but unfortunately not its owner's identity. We need to invoke unmanaged code to get that. Every process executed on behalf of a user has a copy of its access token. That token is returned by the OpenProcessToken function, and can then be used in a WindowsIdentity constructor. I created a wrapper around this functionality as an extension method to the Process class. This allows you to get the associated Windows Identity of a Process by simply calling aProcess.WindowsIdentity.

Here's how you use the new method:

            ////

            // 'WindowsIdentity' Extension Method Demo:

            //   Enumerate all running processes with their associated Windows Identity

            ////

            foreach (var p in Process.GetProcesses())

                string processName;

try

                    processName = p.WindowsIdentity().Name;

                catch (Exception ex)

                    processName = ex.Message; // Probably "Access is denied"

                Console.WriteLine(p.ProcessName + " (" + processName + ")");

Here's the corresponding class:

//-----------------------------------------------------------------------

// <copyright file="ProcessExtensions.cs" company="DockOfTheBay">

//     http://www.dotbay.be

// </copyright>

// <summary>Defines the ProcessExtensions class.</summary>

//-----------------------------------------------------------------------

namespace DockOfTheBay

    using System;

    using System.Diagnostics;

    using System.Runtime.InteropServices;

    using System.Security.Principal;

    /// <summary>

    /// Extension Methods for the System.Diagnostics.Process Class.

    /// </summary>

    public static class ProcessExtensions

        /// <summary>

        /// Required to query an access token.

        /// </summary>

        private static uint TOKEN_QUERY = 0x0008;

        /// <summary>

        /// Returns the WindowsIdentity associated to a Process

        /// </summary>

        /// <param name="process">The Windows Process.</param>

        /// <returns>The WindowsIdentity of the Process.</returns>

        /// <remarks>Be prepared for 'Access Denied' Exceptions</remarks>

        public static WindowsIdentity WindowsIdentity(this Process process)

            IntPtr ph = IntPtr.Zero;

            WindowsIdentity wi = null;

try

                OpenProcessToken(process.Handle, TOKEN_QUERY, out ph);

                wi = new WindowsIdentity(ph);

            catch (Exception)

                throw;

            finally

                if (ph != IntPtr.Zero)

                    CloseHandle(ph);

            return wi;

        [DllImport("advapi32.dll", SetLastError = true)]

        private static extern bool OpenProcessToken(IntPtr processHandle, uint desiredAccess, out IntPtr tokenHandle);

        [DllImport("kernel32.dll", SetLastError = true)]

        [return: MarshalAs(UnmanagedType.Bool)]

        private static extern bool CloseHandle(IntPtr hObject);

GROC (Getting Rid Of Cursors) part one: Cursor Lifecycle

2009-05-28T13:54:00.002+02:00

This is the first of the GROC-files: a list of articles about TSQL server-side cursors and how to get rid of them.

In the late '80s cursors were added to the ANSI SQL standard, basically to allow the Cobol/PL1/IMSDB developers of that time (like me) to program against a relational database using the same constructs as in their structured programs and hierarchical databases: DO WHILE and FOR NEXT loops. So in a sense you can consider them as a backward compatibility feature: they are there for your development comfort, not for performance reasons. Indeed in most -but not all!- cases cursor based logic will be outperformed by well written Relational Algebra.

In SQL Server cursors are implemented as a native data type, so they can be referred to by variables of this type, or returned as an output parameter of a stored procedure.

In this article I'll focus on the life cycle of cursors. Cursors live (and hence consume resources) from the moment that they are declared, until they are deallocated - explicitly by you, or automatically by SQL Server. To find out whether or not there are cursors hanging around, you can monitor SQL Server's Performance Counters from the category SQLServer:Cursor Manager. You can access these counters from TSQL:

/***************************************/

/* Cursor-related Performance Counters */

/***************************************/

SELECT DISTINCT *

  FROM sys.dm_os_performance_counters

 WHERE [object_name] LIKE 'SQLServer:Cursor%'

GO

I created a small function on top of some the performance counters, and called it a couple of times during the lifecycle of a pretty trivial cursor. Run the script, modify it (see included comments) and run it again. The script proves that cursors can remain alive (and consume resources) a lot longer than you might expect, because:

a global cursor survives batches, and
a cursor is not really deallocated if there are variables referencing it.

So here is some advice to keep the lifetime of a cursor as short as possible:

Open any cursor as late as possible,
close and deallocate any cursor as soon as possible,
explicitly use LOCAL for a locally scoped cursor - otherwise you depend on the CURSOR_DEFAULT database setting, and
be careful when referring to cursors via variables.

Here we go with the full test script:

/*************************/

/*                       */

/* Cursor Lifecycle Demo */

/*                       */

/*************************/

/***************/

/* Preparation */

/***************/

ALTER DATABASE AdventureWorks SET CURSOR_DEFAULT LOCAL -- or GLOBAL

GO

USE AdventureWorks

GO

CREATE SCHEMA DockOfTheBay

GO

CREATE FUNCTION DockOfTheBay.ufn_CursorsInScope

    @Step AS NVARCHAR(25)

RETURNS TABLE

AS

RETURN

SELECT @Step AS [Step], instance_name AS [Cursor Type], cntr_value AS [Number]

  FROM sys.dm_os_performance_counters

 WHERE counter_name = 'Active cursors'

   AND instance_name LIKE 'TSQL%Cursor%'

GO

/*************/

/* Execution */

/*************/

-- Declare variables for each field

DECLARE @Name nvarchar(50)

-- Show that cursor is not yet there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Start')

-- Declare the cursor:

--   name, scrolling behavior, scope, and

--   the query that produces the resultset

DECLARE c1 CURSOR READ_ONLY LOCAL /* change to 'GLOBAL', or remove, and compare results */

FOR

SELECT [Name]

  FROM Production.Product

-- Show that cursor is already there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Declared')

-- Open cursor:

--    populates the resultset

OPEN c1

-- Show that cursor is already there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Opened')

-- Fetch Cursor:

--    reads the next row of the resultset into variables

FETCH NEXT FROM c1 -- Some types also allow 'FIRST', 'LAST' and/or 'PREVIOUS'

INTO @Name

-- Loop through resultset

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Body of the cursor

    PRINT @Name -- Away to the Messages Pane

    -- Fetch Cursor:

    --    reads the next row of the resultset into variables

    FETCH NEXT FROM c1

    INTO @Name

END

-- Show that cursor is still there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Fetched')

-- GO

-- Uncomment the 'GO' statement on the previous line, and compare results:

--    at the end of a batch a local cursor is cleaned up,

--    but a global cursor continues to live.

-- Close cursor:

--    releases the result set and the locks

CLOSE c1

BEGIN TRY

    OPEN c1 -- Only successful if still in scope: local and in the batch

    SELECT 'LOOK: ' AS [ ], 'The cursor was reopened' As [Message]

    CLOSE c1

END TRY

BEGIN CATCH

    SELECT 'OOPS: ' AS [ ], ERROR_MESSAGE() As [Message]

END CATCH

-- Show cursor is still there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Closed')

-- Create a reference in a variable

DECLARE @c2 CURSOR

SET @c2 = c1

-- Deallocate cursor:

--    removes the reference and -if it was the last one- all remaining resources

DEALLOCATE c1

-- Show cursor is still there, even after deallocation

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Deallocated')

-- Try to reopen

BEGIN TRY

    OPEN @c2

    SELECT 'LOOK: ' AS [ ], 'I reopened a deallocated cursor' As [Message]

    CLOSE @c2

END TRY

BEGIN CATCH

    SELECT 'OOPS: ' AS [ ], ERROR_MESSAGE() As [Message]

END CATCH

-- Deallocate cursor:

--    removes the reference and -if it was the last one- all remaining resources

DEALLOCATE @c2

-- Show cursor is really gone now

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Deallocated - last ref')

GO

/***********/

/* Cleanup */

/***********/

DROP FUNCTION DockOfTheBay.ufn_CursorsInScope

DROP SCHEMA DockOfTheBay

GO

Loading a User Profile from C#

2009-05-20T22:43:00.002+02:00

Lightweight Impersonation using the LogonUser function may be nice for accessing a file or a database. It changes the credentials, but not the profile associated with the process. If you want access the impersonated account's mapped network drives, printers, environment variables and special folders (e.g. DeskTop, My Documents, and Application Data Folders) then you need to load its profile.

Changing the profile associated with a process is not obvious. The win32 APIs contain functions to do that: theoretically you could -after the impersonation for the current process started- respectively call ImpersonateLoggedOnUser to put the security context on the thread, LoadUserProfile to load the User Hive and CreateEnvironmentBlock to get the environment variables for the specified user. Unfortunately you need to have an unrealistic set of permissions to make this work (other than to a specific account for a Windows Service, I would be reluctant to assign 'Administrator' as well as 'Part of the Operating System' privileges).

So when you want to load a specific account's user profile, it makes probably more sense to simply start another process under that account. The ProcessStartInfo structure -used as parameter of .NET's Process.Start() method- has all the necessary attributes to make sure the account's profile is loaded.

Here's some sample code; it's the Main method of a console application that restarts itself under another account:

    ////

    // Process Restart Demo: Restart process under another account

    ////

    Console.Write("User Name: ");

    Console.WriteLine(WindowsIdentity.GetCurrent().Name);

    Console.Write("Application Directory: ");

    Console.WriteLine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.LocalApplicationData));

    // Create Impersonation Object

    Impersonation impersonation = new Impersonation("YourDomain", "Test123", "Test123");

    // Ask for Restart

    Console.ForegroundColor = ConsoleColor.Cyan;

    Console.Write("\nReady to restart (Y/N): ");

    ConsoleKeyInfo key = Console.ReadKey();

    if ((key.KeyChar == 'y') || (key.KeyChar == 'Y'))

        // Start Process

try

            impersonation.StartProcess(Assembly.GetExecutingAssembly().Location);

        catch (Exception ex)

            // Oops

            Console.ForegroundColor = ConsoleColor.Red;

            Console.WriteLine("\n\n" + ex.Message);

            Console.ReadKey();

Here's the full Impersonation class:

//-----------------------------------------------------------------------

// <copyright file="Impersonation.cs" company="DockOfTheBay">

//     http://www.dotbay.be

// </copyright>

// <summary>Defines the Impersonation class.</summary>

//-----------------------------------------------------------------------

namespace DockOfTheBay

    using System;

    using System.Diagnostics;

    using System.IO;

    using System.Runtime.InteropServices;

    using System.Security;

    using System.Security.Principal;

    /// <summary>

    /// Facilitates impersonation of a Windows User.

    /// </summary>

    public class Impersonation

        /// <summary>

        /// The User Id.

        /// </summary>

        private string userName = string.Empty;

        /// <summary>

        /// The Domain.

        /// </summary>

        private string domain = string.Empty;

        /// <summary>

        /// The Password.

        /// </summary>

        private SecureString password;

        /// <summary>

        /// Windows Token.

        /// </summary>

        private IntPtr tokenHandle = new IntPtr(0);

        /// <summary>

        /// The impersonated User.

        /// </summary>

        private WindowsImpersonationContext impersonatedUser;

        /// <summary>

        /// Initializes a new instance of the Impersonation class.

        /// </summary>

        /// <param name="domainName">Domain name of the impersonated user.</param>

        /// <param name="userName">Name of the impersonated user.</param>

        /// <param name="password">Password of the impersonated user.</param>

        /// <remarks>

        /// Uses the unmanaged LogonUser function to get the user token for

        /// the specified user, domain, and password.

        /// </remarks>

        public Impersonation(string domainName, string userName, string password)

            // Fill private field for later use

            this.domain = domainName;

            this.userName = userName;

            this.password = new SecureString();

            char[] passwordChars = password.ToCharArray();

            foreach (char c in passwordChars)

                this.password.AppendChar(c);

            // Use the standard logon provider.

            const int LOGON32_PROVIDER_DEFAULT = 0;

            // Create a primary token.

            const int LOGON32_LOGON_INTERACTIVE = 2;

            this.tokenHandle = IntPtr.Zero;

            // Call LogonUser to obtain a handle to an access token.

            bool returnValue = LogonUser(

                                userName,

                                domainName,

                                password,

                                LOGON32_LOGON_INTERACTIVE,

                                LOGON32_PROVIDER_DEFAULT,

                                ref this.tokenHandle);

            if (false == returnValue)

                // Something went wrong.

                int ret = Marshal.GetLastWin32Error();

                throw new System.ComponentModel.Win32Exception(ret);

        /// <summary>

        /// Starts the impersonation.

        /// </summary>

        public void Impersonate()

            // Create Identity.

            WindowsIdentity newId = new WindowsIdentity(this.tokenHandle);

            // Start impersonating.

            this.impersonatedUser = newId.Impersonate();

        /// <summary>

        /// Stops the impersonation and releases security token.

        /// </summary>

        public void Revert()

            // Stop impersonating.

            if (this.impersonatedUser != null)

                this.impersonatedUser.Undo();

            // Release the token.

            if (this.tokenHandle != IntPtr.Zero)

                CloseHandle(this.tokenHandle);

        /// <summary>

        /// Spawns a process under a specific account.

        /// </summary>

        /// <param name="fullPath">Full path to the executable.</param>

        /// <remarks>There's no need to call 'Impersonate' first.</remarks>

        public void StartProcess(string fullPath)

            ProcessStartInfo info = new ProcessStartInfo();

            // File properties

            info.FileName = Path.GetFileName(fullPath);

            info.WorkingDirectory = Path.GetDirectoryName(fullPath);

            // User properties

            info.Domain = this.domain;

            info.UserName = this.userName;

            info.Password = this.password;

            info.LoadUserProfile = true; // Optional, but makes sense ...

            info.UseShellExecute = false; // Must be 'false' when 'UserName' is filled

            // Kick Off

            Process.Start(info);

        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]

        private static extern bool CloseHandle(IntPtr handle);

        [DllImport("advapi32.dll", SetLastError = true)]

        private static extern bool LogonUser(

                string lpszUsername,

                string lpszDomain,

                string lpszPassword,

                int dwLogonType,

                int dwLogonProvider,

                ref IntPtr phToken);

Showing non-selectable items in a WPF ComboBox

2009-05-19T12:43:00.001+02:00

This is a demo about adding non-selectable items (like titles or category names) and hyperlinks to a WPF ComboBox.

One way of dealing with large comboboxes is filtering. Another technique is proposing only the most popular items in the default list, and provide a hyperlink to the full list:

If the hyperlink is clicked, the ComboBox is entirely populated, but by adding non-selectable items you can still make the distinction:

Here's the full code for the demo:

XAML

<Window x:Class="DockOfTheBay.SuggestionComboBoxSample"

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

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

        xmlns:local="clr-namespace:DockOfTheBay"

        Title="Suggestion ComboBox"

        Height="120" Width="300">

    <StackPanel Orientation="Horizontal"

                VerticalAlignment="Top"

                Margin="10 10">

        <TextBlock Text="Select: "

                   Padding="4 3"/>

        <ComboBox

            x:Name="SuggestionComboBox1"

            Padding="4 3"

            MinWidth="200"/>

    </StackPanel>

</Window>

namespace DockOfTheBay

    using System;

    using System.Collections.Generic;

    using System.Windows;

    using System.Windows.Controls;

    using System.Windows.Documents;

    using System.Windows.Media;

    /// <summary>

    /// Demonstration of using non-selectable items and hyperlinks in a

    /// WPF ComboBox.

    /// </summary>

    public partial class SuggestionComboBoxSample : Window

        /// <summary>

        /// The hyperlink displayed after the the ComboBox's Suggestions.

        /// </summary>

        private ComboBoxItem linkItem;

        /// <summary>

        /// Initializes a new instance of the SuggestionComboBoxSample class.

        /// </summary>

        public SuggestionComboBoxSample()

            InitializeComponent();

            // Add an item that looks like a title

            ComboBoxItem item = new ComboBoxItem();

            item.Content = "Most Frequent";

            item.IsEnabled = false;

            item.Background = Brushes.Blue;

            item.Foreground = Brushes.White;

            SuggestionComboBox1.Items.Add(item);

            // Add suggestions

            foreach (var name in this.Suggestions())

                SuggestionComboBox1.Items.Add(name);

            // Don't add content directly, like the following:

            // SuggestionComboBox1.Items.Add(new Separator());

            // It will not display properly when databinding is used.

            item = new ComboBoxItem();

            item.Content = new Separator();

            SuggestionComboBox1.Items.Add(item);

            Hyperlink link = new Hyperlink();

            // 'RequestNavigate' is not fired, so use 'Click' instead

            link.Click += this.Link_Click;

            link.Inlines.Add(new Run("Show Everything"));

            item = new ComboBoxItem();

            item.Content = link;

            this.linkItem = item;

            SuggestionComboBox1.Items.Add(item);

        /// <summary>

        /// Fetches the remaining items, and adds them to the ComboBox.

        /// </summary>

        /// <param name="sender">The HyperLink.</param>

        /// <param name="e">The Event Arg.</param>

        private void Link_Click(object sender, RoutedEventArgs e)

            // Remove Hyperlink

            SuggestionComboBox1.Items.Remove(this.linkItem);

            // Add an item that looks like a title

            ComboBoxItem item = new ComboBoxItem();

            item.Content = "The Rest";

            item.IsEnabled = false;

            item.Background = Brushes.Blue;

            item.Foreground = Brushes.White;

            SuggestionComboBox1.Items.Add(item);

            // Add suggestions

            foreach (var name in this.Rest())

                SuggestionComboBox1.Items.Add(name);

        /// <summary>

        /// The Suggested Items.

        /// </summary>

        /// <returns>A list of Suggestions.</returns>

        private List<string> Suggestions()

            List<string> names = new List<string>();

            names.Add("WPF rocks");

            names.Add("WCF rocks");

            names.Add("XAML is fun");

            return names;

        /// <summary>

        /// The non-Suggested Items.

        /// </summary>

        /// <returns>A list of non-Suggestions.</returns>

        private List<string> Rest()

            List<string> names = new List<string>();

            names.Add("WPF rules");

            names.Add("WCF rules");

            names.Add("WinForms not");

            return names;

Here's how the control looks like in a real-life application for cancer registration. It presents a list of the most probable histologic diagnoses, based on key words in the tumor location: