Paul Kimmel's Blog

A customer posted a question to the forums, and I was asked to handle. Mehul Harry and the other guys have been pretty good to me on this front. They know I am busy with the book—Professional DevExpress ASP.NET Controls—and have been handling the.lion’s share of the forums work. However, the question was a LINQ question and I was happy to answer it. It’d probably be a waste to write a sample solution and share it with one person because as my associate Richard Morris put it: “Nice I just found a use for [“Converting SQL with Groups to XML Using LINQ and Functional Construction”] backing up data between XAF schema redesigns .... I love it when some seemingly random stuff precisely meets an unexpected requirement.” The web never sleeps, never runs out of space, the world is always awake somewhere and you never know what someone might need next.

The question posed was basically how do I get the ASPxTreeList to work with the LinqServerModeDataSource. The answer is that the LinqServerModeDataSource isn’t presently designed to work with the ASPxTreeList. That’s ok though. The ASPxTreeList has a virtual mode. Virtual mode is basically where the tree looks fully loaded, but child nodes aren’t actually loaded until the parent is expanded, and then the child nodes a repopulated on an Ajax call. This means that even huge data sets can effectively be presented to the end user because the nature of trees is that the end user is generally drilling down into a branch of the tree.

The key to all of this Ajax and virtual goodness is two event handlers: VirtualModeCreateChildren and VirtualModeNodeCreating. Simply implement VirtualModeCreateChildren and add child nodes based on the parent path and implement VirtualModeNodeCreating to fill in the details of each node. For example, if you have a column who’s field name is LastName then in VirtualModeNodeCreating you will want to set the LastName value for that node.

An example—see Figure 1—based on the AdventureWorks database is provided below. The example uses the self-referentiating table HumanResources.Employee (which has an EmployeeID and a ManagerID and the ManagerID refers to the employee’s manager, a row in the same table). The root node or nodes are all employees that have no manager, logically such a person is the CEO or top guy. Underneath the CEO are people who report to the CEO and so on. Roughly, to construct the example follow these basic steps (you can always copy and paste the code to fill in the blanks):

1. Create a Web site in Visual Studio
2. Drop an ASPxTreeList in the <div> tag of the default.aspx page
3. For the ASPxTreeList change SettingsBehavior.ProcessSelectionChangedOnServer to true
4. Click on the Columns property and use the Columns editor (see Figure 2) to add a column to the ASPxTreeList
5. Change the new column’s FieldName property to LastName
6. Click OK to close the columns editor form
7. Click on the ASPxTreeList’s Smart tags menu and select Edit Templates
8. Select DataCell in the Display drop down and add two ASPxLabels to the DataCell template (see Figure 3)
9. Using the Smart tags menu select Edit Bindings and define the binding statement for the Text property of each label—use Container.GetValue("EmployeeID") for the first label, and Container.GetValue("LastName") for the second label
10. Click the ASPxTreeList’s Smart tags menu and select End Template Editing
11. Define an event handler for each of the VirtualModeCreateChildren and VirtualModeNodeCreating events for the ASPxTreeList

That’s it. You can now use the code in Listings 1 and 2 to fill in the blanks.  A brief explanation of the code behind follows listing 2.

Figure 1: The solution based on the AdventureWorks Employee table, LINQ and the ASPxTreeList using virtual mode.

Figure 2: Define a column for the ASPxTreeList and set the FieldName to ‘LastName’.

Figure 3: Add two ASPxLabel controls to the DataCell template.

Figure 4: Define the ASPxLabel’s binding statement using Container.GetValue(“name”) where “name” is the node value to assign to this control.

Listing 1: The ASPX for the ASPxTreeList sample.

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register assembly="DevExpress.Web.ASPxTreeList.v9.1, Version=9.1.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxTreeList" tagprefix="dxwtl" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v9.1, Version=9.1.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    <dxwtl:ASPxTreeList ID="ASPxTreeList1" runat="server"
      onvirtualmodecreatechildren="ASPxTreeList1_VirtualModeCreateChildren"
      onvirtualmodenodecreating="ASPxTreeList1_VirtualModeNodeCreating"
      AutoGenerateColumns="False">
      <SettingsBehavior ProcessSelectionChangedOnServer="True" />
      <Templates>
        <DataCell>
          <dxe:ASPxLabel ID="ASPxLabel1" runat="server"
  Text='<%# Container.GetValue("EmployeeID") %>'>
          </dxe:ASPxLabel>
          <dxe:ASPxLabel ID="ASPxLabel2" runat="server"
            Text='<%# Container.GetValue("LastName") %>'>
          </dxe:ASPxLabel>
        </DataCell>
      </Templates>
      <Columns>
        <dxwtl:TreeListTextColumn FieldName="LastName" VisibleIndex="0">
        </dxwtl:TreeListTextColumn>
      </Columns>
    </dxwtl:ASPxTreeList>
    </form>
</body>
</html>

Listing 2: The code behind for the ASPxTreeList demo.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    public DataTable EmployeesTable
    {
      get
      {
        const string key = "EMPLOYEES_TABLE";
          if(Session[key] == null)
          Session[key] = GetTable();

        return (DataTable)Session[key];
      }
    }

    /// <summary>
    /// you could do this at the application level.
    /// </summary>
    public DataTable GetTable()
    {
      string connectionString =
        @"Data Source=WYOMING\SQLEXPRESS;" +
        "Initial Catalog=AdventureWorks;Integrated Security=True";

      string sql = "SELECT * FROM HumanResources.Employee E " +
          "INNER JOIN Person.Contact C ON E.ContactID = C.ContactID";

      using(SqlConnection connection = new SqlConnection(connectionString))
      {
        connection.Open();
        SqlCommand command =
          new SqlCommand(sql, connection);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataTable table = new DataTable();
        adapter.Fill(table);
        return table;
      }
    }

    protected void ASPxTreeList1_VirtualModeCreateChildren(object sender, DevExpress.Web.ASPxTreeList.TreeListVirtualModeCreateChildrenEventArgs e)
    {
      ASPxTreeList1.SettingsBehavior.AutoExpandAllNodes = false;
      // for the first one or root nodes
      int? key = null;
      e.Children = new List<DataRow>();

      if(e.NodeObject != null)
      {
        key = ((DataRow)e.NodeObject).Field<int?>("EmployeeID");
      }

      var data = from emp in EmployeesTable.AsEnumerable()
                 where emp.Field<int?>("ManagerID") == key
                 select emp;

      Array.ForEach<DataRow>(data.ToArray(), one=>e.Children.Add(one));

    }

    protected void ASPxTreeList1_VirtualModeNodeCreating(object sender, DevExpress.Web.ASPxTreeList.TreeListVirtualModeNodeCreatingEventArgs e)
    {
      DataRow row = (DataRow)e.NodeObject;
      e.NodeKeyValue = row.Field<int>("EmployeeID").ToString();
      e.IsLeaf = GetIsLeaf(row);
      e.SetNodeValue("EmployeeID", row.Field<int>("EmployeeID"));
      e.SetNodeValue("LastName", row.Field<string>("LastName"));
    }

    private bool GetIsLeaf(DataRow row)
    {
      return (from emp in EmployeesTable.AsEnumerable()
              where emp.Field<int?>("ManagerID")
                != row.Field<int>("EmployeeID")
              select emp).Count() == 0;
    }
}

Once you know the basic trick—implement the two aforementioned event handlers—using ASPxTreeList in virtual mode is straight forward. In Listing 2 there is a helper method that returns the AdventureWorks Employee table using ADO.NET. How you actually get the data or whether you store it or query it each time is irrelevant. In the example, the Employee table is created using a lazy load and stored in Session. The important methods are VirtualModeCreateChildren and VirtualModeNodeCreating.

In VirtualModeCreateChlidren a place to store data is create and assigned to TreeListVirtualModeCreateChildrenEventArgs.Children. Leaving this property unassigned means you can assign the kind of data structure you want. Next, the argument NodeObject property is checked. If its null then you are working with the root. In the listing, if its not null, the key is retrieved from the NodeObject. You know NodeObject is a DataRow because that’s what the code will be assigning to each node (see the end of the function). The key represents the manager and it is used in the where clause of the LINQ query to retrieve just this Employees subordinates—that is, the managed employees. Finally, the Array.ForEach adds each DataRow to the Children property. (Of course, that is a goofy statement, oops. List<T> has an AddRange method and that can be called passing the entire set of data. The reason I didn’t do that is because Children is defined as an IList. List<T> implements IList, but IList doesn’t require AddRange. Because Intellisense only showed Children.Add I ended with an Array.ForEach instead of an AddRange.)

For every item added in VirtualModeCreateChildren VirtualModeNodeCreating will be called. Implement an event handler here to flesh out the node’s properties. In the event handler the NodeObejct is the DataRow for the node. Assign the key; it has to be unique. Since you are dealing with a primary key in the Employee table you can safely use the EmployeeID. Next, set IsLeaf. You can set IsLeaf to false to always show an expand and collapse icon. In Listing 2 another LINQ query is used to see if an employee is a manager—GetIsLeaf. Finally, set the values that you need displayed in the tree node. You can have as many as you like.

That’s pretty much all there is to it. When you write this code a dozen times in a couple of weeks it will only take you a couple of minutes (after the practice). Of course, you can just defined the query with something like a SqlDataSource set the KeyFieldName and ParentFieldName for the ASPxTreeList and let her fly. Large data sets might be a problem with that approach. The key is knowing a couple of approaches to solving a problem and then use the one that causes you the least amount of pain.