Introduction

This is something I've been meaning to write a wee article about for some time. I finally found the necessary time/care factor to bash away on the keyboard to look somewhat knowledgeable and thus I bring you:

"WPF – table self referencing child parent bindy treeview cleverness"

Or – how to get a treeview to correctly display a self referencing table whose rows have a parent child relationship.

Background

Sometimes you have hierarchical information described in a database table where items reference other items as parents, and sometimes you want to display the data in a treeview control. This article shows you how to do it in a very simple fashion in WPF.

Please note, to keep the code simple I've not added exception handling, error catching and the like.

Our Data

First off, let's have our data.

As you can see I have created a faux management structure for a pretend company. Each element has an ID. It also may (or may not) have a parent. And that parent is identified by a parent_element_id (that is, the ID of the parent). It should be fairly obvious we are describing a reporting structure. We can see, for example, that Chris the Tea boy reports to the lowly Maunder coder, who in turn reports to Manager 3.

Cool, that’s looking all very tree like and I can tell you're getting excited. Except perhaps if you're a tea boy.

Its XAML time

Fire up your trusted Visual Studio – I'm using Visual Studio 2010. Open up the WPF solution available as a download with this article – I've called it WpfTreeviewStuff because I'm not particularly imaginative.

Now, have a look at the MainWindow.xaml file.

The XAML for the window that contains the embedded treeview looks like this;

WTF? WPF

The XAML tells WPF that we have a treeview and it is going to have some data bound (attached) to it. This bit:

ItemsSource="{Binding}"

creates a default binding object for use later on.

In other words - the list of items the tree view displays (that is, the itemsource) will be connected up to the treeview sometime in the future.

Hierarchical data display

The next interesting part of this isn’t just the pretty colours, it’s the HierarchicalDataTemplate tag. Usually with a treeview, we want to display a collection of data that has some sort of logical hierarchy. To do this in XAML, we use the HierarchicalDataTemplate tag.

In our case, the collection of data is described by "rsParentChild" – which in this case is (as you will see) is a data relation object. The treeview can populate itself with the data bound to it from the object called rsParentChild.

What to display

The actual text item we want to show is called element_name, yes it is the same name as the column in the database. The TextBlock tag tells the treeview what to actually display on the screen, i.e., a TextBlock. You can have quite a bit of fun with this as you can do petty much whatever you want.

Code time

Right, if you're fairly new to this WPF malarky, none of that makes much sense, but then again it possibly might. Now, head on over to the code behind (to keep this dead simple, we're an MVVM-free zone today).

And in the constructor, you will see the following:

public partial class MainWindow : Window
{
    string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename={0}\" + 
           @"treeviewdata.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
    SqlConnection conn = new SqlConnection();

    public MainWindow()
    {
        InitializeComponent();

        //get the folder where i put the mdf file.
        string baseDir = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
        string whereTheWildFileLives = Directory.GetParent(baseDir).ToString() ;
                
        whereTheWildFileLives = Directory.GetParent(whereTheWildFileLives).ToString();
    
        //build out connection string
        connString = string.Format(connString, whereTheWildFileLives);

        conn.ConnectionString = connString;
        conn.Open();
        // select the data from our table and fill our dataset                                       
        String sql = "select element_name,element_id,parent_element_id from sexytimetable";
        DataSet myGroovySet = new DataSet();
    
        SqlDataAdapter adpt = new SqlDataAdapter(sql, conn);
        adpt.Fill(myGroovySet);

        myGroovySet.Relations.Add("rsParentChild",
                    myGroovySet.Tables[0].Columns["element_id"],
                    myGroovySet.Tables[0].Columns["parent_element_id"]);
                
        //get the default view and then filter based on the parent element id being null
        DataView vw = myGroovySet.Tables[0].DefaultView;
        vw.RowFilter = "parent_element_id IS NULL"; //try commenting me out later
        treeView1.DataContext = vw;
        conn.Close();
    }
}

The first piece of code that is interesting is the following:

String sql = "select element_name, element_id, parent_element_id from sexytimetable";
DataSet myGroovySet = new DataSet();
            
SqlDataAdapter adpt = new SqlDataAdapter(sql, conn);
adpt.Fill(myGroovySet);

Now we could have been clever about it and created an entity model, but this article isn’t about that sort of technology. And I had a hell of a time trying to set my Visual Studio up to use entity models with a big Oracle database. So I don’t like them (with Oracle databases) and for simplicity, I stuck with the code you see.

The next section of code is where the interesting stuff happens:

myGroovySet.Relations.Add("rsParentChild",
            myGroovySet.Tables[0].Columns["element_id"],
            myGroovySet.Tables[0].Columns["parent_element_id"]);

The code shows a relation being created (called "rsParentChild") and added to the dataset, the relation is between the two columns element_id and parent_element_id. This relationship is what will be displayed in the treeview.

Before we display our data, we need to filter the dataset to focus on the "topmost" elements first, i.e., the ones without any parents. We can do this by the creating a dataview object and filtering based on rows having a null parent_element_id.

DataView vw = myGroovySet.Tables[0].DefaultView;
vw.RowFilter = "parent_element_id IS NULL";
treeView1.DataContext = vw;

When we run the code, we get a nicely populated treeview that looks like, as we can see the "staff" are reporting correctly to their "managers".

happy treeview

With all the nodes expanded, it looks like the following (yes, even Chris the tea-boy is reporting correctly):

happier treeview

So what happens if we don't add the row filter "parent_element_id IS NULL"?

This is what happens, and it is not what we want (for obvious reasons):

unhappyrelations

That's about it, we're done like a one pint Maunder. Thanks for reading and I hope you enjoyed the show.

Other references

Last but certainly not least, very cool Josh Smith:

Josh Smith on WPF

Displaying self referencing hierarchical data in a treeview