Building a custom section to manage external data
Rating: 5 / 5 stars - 8 vote(s).
I’m currently involved in a small project that requires a customer to manage non-umbraco data from within the umbraco backend. So I thought I’d share some thought and issues I had to overcome during my learning experience. First, let me state: this is not a tutorial on how to build a custom section in umbraco. Nope, that’s what umbraco.tv is for. Tim Geyssens from the umbraco HQ has knocked together a great series of videos on how to build custom sections in umbraco. In fact, most of my current knowledge on custom section comes from learning/watching/repeating what’s being said/coded in the series. One thing I missed though was ‘How do I build a custom tree that has multiple levels’. It may as well be a great tip on a next video in the series, but I couldn’t wait for this video, so I did some digging and after a 2 to 3 hrs of mocking up, I finally managed to knock together a nice multilevel custom tree for managing external data. For this article, I’ll be using the so well known Northwind database, as it’s nice for demo purposes because of its structure and prepopulation of data. Enough talk, let’s get into code.
First, a screenshot of the final result we’ll be working towards:
As you can see from the above screenshot, I’ve created a custom tree to represent customers, orders and order details (= products). All data is nicely grouped and sorted based on the first letter of the customer’s code. Also, we’ll show how to change the context menu items based on type of child node.
Let’s create the grouping structure first. So, fire up your vs.net and create a new class library project. I’ve called mine Northwind.Trees. Of course, you’re free to use whatever name you’d like. Add a new class file, and make it inherit from the BaseTree class as in following codesnippet:
1: namespace Northwind.Trees {
2:
3: public class LoadNorthwind : BaseTree {
4:
5: private static ISqlHelper sqlHelper;
6:
7: public LoadNorthwind(string application) : base(application) {}
8:
9: static LoadNorthwind() {
10: sqlHelper = DataLayerHelper.CreateSqlHelper(@"server=.\sqlexpress;database=Northwind;user id=northwind;password=scrambled");
11: }
12:
13: protected override void CreateRootNode(ref XmlTreeNode rootNode) {/*...*/}
14: protected override void CreateAllowedActions(ref List<umbraco.interfaces.IAction> actions) {/*...*/}
15: public override void RenderJS(ref StringBuilder Javascript) {/*...*/}
16:
17: public override void Render(ref XmlTree tree) {
18: var reader = sqlHelper.ExecuteReader(@"
19: SELECT DISTINCT(SUBSTRING([CustomerID], 1, 1)) AS CustomerLetter, COUNT([CustomerID]) As NumberOfCustomers
20: FROM [Northwind].[dbo].[Customers]
21: GROUP BY SUBSTRING([CustomerID], 1, 1)");
22:
23: while (reader.Read()) {
24: XmlTreeNode node = XmlTreeNode.Create(this);
25:
26: node.NodeID = reader.Get<string>("CustomerLetter");
27: node.Text = reader.Get<string>("CustomerLetter");
28: node.Icon = "letter.gif";
29: node.Action = string.Format("javascript:openCustomersPage('{0}');", node.NodeID);
30: node.Source = reader.Get<int>("NumberOfCustomers") > 0 ? GetTreeServiceUrl() : string.Empty;
31:
32: tree.Add(node);
33: }
34: }
35: }
36: }
I won’t be dicussing the three methods CreateRootNode(), CreateAllowedActions() and RenderJS().
Most important method is the Render() method, that’s where all the magic happens. Currently, this method executes a sql query on the Northwind database, returning letters and the number of customers that have a customer code that starts with that letter. In this case, the count won’t be that important as w'e’ll only return letters from the alphabet for which we have found a customer in the database whose name starts with that letter. It would become more significant if we’d return all letters from the alpabet, even those for which the count would be zero. Nevermind, we’ll leave it there!
As soon as we’ve returned the data from the database, we’ll be building a tree node for each letter returned from the query. Most important properties to set are the unique id for the node (.NodeId), the text to display in the tree (.Text) and the icon to be used.
Additionally, we’ve set the javascript function as the action for the node. Last and quite important property is the .Source property. Here’s where we decide whether we’d need or like to add another level in the tree. So, we perform a check on the number of customers whose name starts with that letter, and call the GetTreeServiceUrl() from the base class BaseTree. If you leave this property empty, this node will be a leaf node and have no child nodes. Don’t worry too much now about the url, we’ll talk about it in a bit.
After building the project, copying the dll to the /bin folder of our running umbraco instance, fire up a browser and navigate to the new section:
Cool, but what happens if you click on any node to expand. Our Render() method will be hit again, and our class object will hold info about the node being clicked. Exactly what we need. At this point, we want to add some child nodes to a ‘letter’ node as soon as such a node is clicked in the tree. A little change in the code to perform a check on whether we’ve clicked the root node (for which we’ve set the node id = –1 in the CreateRootNode() method) or a ‘letter’ node and add child nodes based on that info:
1: public override void Render(ref XmlTree tree) {
2: if (this.id == -1) {
3: var reader = sqlHelper.ExecuteReader(@"
4: SELECT DISTINCT(SUBSTRING([CustomerID], 1, 1)) AS CustomerLetter, COUNT([CustomerID]) As NumberOfCustomers
5: FROM [Northwind].[dbo].[Customers]
6: GROUP BY SUBSTRING([CustomerID], 1, 1)");
7:
8: while (reader.Read()) {
9: XmlTreeNode node = XmlTreeNode.Create(this);
10:
11: node.NodeID = reader.Get<string>("CustomerLetter");
12: node.Text = reader.Get<string>("CustomerLetter");
13: node.Icon = "letter.gif";
14: node.Action = string.Format("javascript:openCustomersPage('{0}');", node.NodeID);
15: node.Source = reader.Get<int>("NumberOfCustomers") > 0 ? GetTreeServiceUrl(node.NodeID) : string.Empty;
16:
17: tree.Add(node);
18: }
19: }
20: else {
21: var letterReader = sqlHelper.ExecuteReader(@"
22: SELECT C.[CustomerID], MIN(C.[CompanyName]) AS CustomerName, COUNT(O.[OrderID]) AS NumberOfOrders
23: FROM [Northwind].[dbo].[Customers] C
24: INNER JOIN [Northwind].[dbo].[Orders] O ON O.[CustomerID] = C.[CustomerID]
25: WHERE SUBSTRING(C.[CustomerID], 1, 1) = @CustomerLetter
26: GROUP BY C.[CustomerID]",
27: sqlHelper.CreateParameter("@CustomerLetter", node.id));
28:
29: while (letterReader.Read()) {
30: XmlTreeNode node = XmlTreeNode.Create(this);
31:
32: node.NodeID = letterReader.Get<string>("CustomerID");
33: node.Text = letterReader.Get<string>("CustomerName");
34: node.Icon = "customer.gif";
35: node.Action = string.Format("javascript:openCustomerPage('{0}');", node.NodeID);
36: node.Source = letterReader.Get<int>("NumberOfOrders") > 0 ? GetTreeServiceUrl(node.NodeID) : string.Empty;
37:
38: tree.Add(node);
39: }
40: }
41: }
First part of the code hasn’t changed, we’ve added the else branch which will build a subtree for a ‘letter’ node. First querying the database for all customers that start with the letter and build the child node tree. Query also returns info about the number of orders a specific customer has already made, as we want to add yet another level in the custom tree. Again, build the project, copy the dll, … (you know the drill) and browse the custom section and expand a first level node:
Oops, not quite what you’d expect huh? It seems as we' never hit the else branch. Bit strange, as we do set the node id correctly for each child node… Some investigation wouldn’t hurt now… Here’s where the GetTreeServiceUrl() comes in. I won’t go too much into detail, but that method actually builds the url that gets loaded whenever we hit a node. It forces a reload of that frame to build tree and subtree.
Here’s an example of such an url: /umbraco/tree.aspx?rnd=13f298a9-1690-4b8b-a51b-187f848e42d1&id=-1&treeType=northwindDb&nodeKey=A&contextMenu=true&isDialog=false
Important to us is the querystring parameter id. It seems to have a value of –1, regardless of the position in the tree, and we should focus on that part…. Ok, let’s digg into the code and found out what GetTreeServiceUrl() does in the background:
1: public virtual string GetTreeServiceUrl(object id) {
2: int nodeID;
3: TreeService treeSvc;
4: if (int.TryParse(id.ToString(), out nodeID))
5: // updated by NH to pass showcontextmenu, isdialog and dialogmode variables
6: treeSvc = new TreeService(nodeID, TreeAlias, this.ShowContextMenu, this.IsDialog, this.DialogMode, "");
7: else
8: treeSvc = new TreeService(-1, TreeAlias, this.ShowContextMenu, this.IsDialog, this.DialogMode, "", id.ToString());
9: return treeSvc.GetServiceUrl();
10: }
(Code taken from BaseTree class)
Code tries to convert the incoming id into an integer value. If that is successful, this id to build the tree url, otherwise –1 is used (Exactly what we’re experiencing now). I think we may have found what the problem is… we’re using
1: node.NodeID = reader.Get<string>("CustomerLetter");
to assign a string value to the node id, which will always silently fail on the GetTreeServiceUrl() method and assign –1 to the id querystring parameter. At current, we can’t use numeric value for the id as the Northwind database is not designed to work with numeric keys for the customers table. So we need a work around. Did someone say ‘umbraco isn’t flexible at all!’? Shame on you, on the contrary, umbraco core team has done such a great job and has provided properties to overcome this limitation.
According to the wiki, one may use the NodeKey property to deal with our specific situation.
After making some changes to the code, this is how our class looks like:
1: public override void Render(ref XmlTree tree) {
2: if (this.NodeKey == string.Empty) {
3: var reader = sqlHelper.ExecuteReader(@"
4: SELECT DISTINCT(SUBSTRING([CustomerID], 1, 1)) AS CustomerLetter, COUNT([CustomerID]) As NumberOfCustomers
5: FROM [Northwind].[dbo].[Customers]
6: GROUP BY SUBSTRING([CustomerID], 1, 1)");
7:
8: while (reader.Read()) {
9: XmlTreeNode node = XmlTreeNode.Create(this);
10:
11: node.NodeID = reader.Get<string>("CustomerLetter");
12: node.Text = reader.Get<string>("CustomerLetter");
13: node.Icon = "letter.gif";
14: node.Action = string.Format("javascript:openCustomersPage('{0}');", node.NodeID);
15:
16: TreeService treeService = new TreeService(-1, TreeAlias, ShowContextMenu, IsDialog, DialogMode, app, string.Format("NorthwindCustomerLetter-{0}", reader.Get<string>("CustomerLetter")));
17: node.Source = reader.Get<int>("NumberOfCustomers") > 0 ? treeService.GetServiceUrl() : string.Empty;
18:
19: tree.Add(node);
20: }
21: }
22: else {
23: var letterReader = sqlHelper.ExecuteReader(@"
24: SELECT C.[CustomerID], MIN(C.[CompanyName]) AS CustomerName, COUNT(O.[OrderID]) AS NumberOfOrders
25: FROM [Northwind].[dbo].[Customers] C
26: INNER JOIN [Northwind].[dbo].[Orders] O ON O.[CustomerID] = C.[CustomerID]
27: WHERE SUBSTRING(C.[CustomerID], 1, 1) = @CustomerLetter
28: GROUP BY C.[CustomerID]",
29: sqlHelper.CreateParameter("@CustomerLetter", this.NodeKey.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries)[1]));
30:
31: while (letterReader.Read()) {
32: XmlTreeNode node = XmlTreeNode.Create(this);
33:
34: node.NodeID = letterReader.Get<string>("CustomerID");
35: node.Text = letterReader.Get<string>("CustomerName");
36: node.Icon = "customer.gif";
37: node.Action = string.Format("javascript:openCustomerPage('{0}');", node.NodeID);
38:
39: TreeService treeService = new TreeService(-1, TreeAlias, ShowContextMenu, IsDialog, DialogMode, app, string.Format("NorthwindCustomer-{0}", letterReader.Get<string>("CustomerID")));
40: node.Source = letterReader.Get<int>("NumberOfOrders") > 0 ? treeService.GetServiceUrl() : string.Empty;
41:
42: tree.Add(node);
43: }
44: }
45: }
Code now builds the service url itself, using a TreeService helper class. Most importantly, we pass our own custom NodeKey value which we’ll use to check what type of node user clicks in the custom tree.
Our intermediate result:
Much better result. As from now on, we can have as many levels as we’d like, we just have to make sure we set a custom NodeKey which we check each time the Render() method gets hit. Following final code sample adds order and order details node to the custom tree:
1: public override void Render(ref XmlTree tree)
2: {
3: if (this.NodeKey == string.Empty)
4: {
5: //root node
6: var reader = sqlHelper.ExecuteReader(@"
7: SELECT DISTINCT(SUBSTRING([CustomerID], 1, 1)) AS CustomerLetter, COUNT([CustomerID]) As NumberOfCustomers
8: FROM [Northwind].[dbo].[Customers]
9: GROUP BY SUBSTRING([CustomerID], 1, 1)");
10:
11: while (reader.Read())
12: {
13: XmlTreeNode node = XmlTreeNode.Create(this);
14:
15: node.NodeID = reader.Get<string>("CustomerLetter");
16: node.Text = reader.Get<string>("CustomerLetter");
17: node.Icon = "letter.gif";
18: node.Action = string.Format("javascript:openCustomersPage('{0}');", node.NodeID);
19:
20: node.Menu.Clear();
21: node.Menu.AddRange(new List<IAction> { ActionNew.Instance, ActionRefresh.Instance });
22:
23: TreeService treeService = new TreeService(-1, TreeAlias, ShowContextMenu, IsDialog, DialogMode, app, string.Format("NorthwindCustomerLetter-{0}", reader.Get<string>("CustomerLetter")));
24:
25: node.Source = reader.Get<int>("NumberOfCustomers") > 0 ? treeService.GetServiceUrl() : string.Empty;
26:
27: tree.Add(node);
28: }
29: }
30: else
31: {
32: switch (this.NodeKey.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries)[0])
33: {
34: case "NorthwindCustomerLetter":
35:
36: var letterReader = sqlHelper.ExecuteReader(@"
37: SELECT C.[CustomerID], MIN(C.[CompanyName]) AS CustomerName, COUNT(O.[OrderID]) AS NumberOfOrders
38: FROM [Northwind].[dbo].[Customers] C
39: INNER JOIN [Northwind].[dbo].[Orders] O ON O.[CustomerID] = C.[CustomerID]
40: WHERE SUBSTRING(C.[CustomerID], 1, 1) = @CustomerLetter
41: GROUP BY C.[CustomerID]",
42: sqlHelper.CreateParameter("@CustomerLetter", this.NodeKey.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries)[1]));
43:
44: while (letterReader.Read())
45: {
46: XmlTreeNode node = XmlTreeNode.Create(this);
47:
48: node.NodeID = letterReader.Get<string>("CustomerID");
49: node.Text = letterReader.Get<string>("CustomerName");
50: node.Icon = "customer.gif";
51: node.Action = string.Format("javascript:openCustomerPage('{0}');", node.NodeID);
52:
53: node.Menu.Clear();
54: node.Menu.AddRange(new List<IAction> { ActionRefresh.Instance });
55:
56: TreeService treeService = new TreeService(-1, TreeAlias, ShowContextMenu, IsDialog, DialogMode, app, string.Format("NorthwindCustomer-{0}", letterReader.Get<string>("CustomerID")));
57:
58: node.Source = letterReader.Get<int>("NumberOfOrders") > 0 ? treeService.GetServiceUrl() : string.Empty;
59:
60: tree.Add(node);
61: }
62: break;
63: case "NorthwindCustomer":
64: var customerReader = sqlHelper.ExecuteReader(@"
65: SELECT O.[OrderID], COUNT(OD.[OrderID]) AS NumberOfProducts
66: FROM [Northwind].[dbo].[Orders] O
67: INNER JOIN [Northwind].[dbo].[Order Details] OD ON OD.[OrderID] = O.[OrderID]
68: WHERE O.[CustomerID] = @CustomerId
69: GROUP BY O.[OrderID]",
70: sqlHelper.CreateParameter("@CustomerId", this.NodeKey.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries)[1]));
71:
72: while (customerReader.Read())
73: {
74: XmlTreeNode node = XmlTreeNode.Create(this);
75:
76: node.NodeID = Convert.ToString(customerReader.Get<int>("OrderId"));
77: node.Text = node.NodeID;
78: node.Icon = "order.gif";
79: node.Action = string.Format("javascript:openOrderPage({0});", node.NodeID);
80:
81: node.Menu.Clear();
82: node.Menu.AddRange(new List<IAction> { ActionNew.Instance, ActionDelete.Instance, ContextMenuSeperator.Instance, ActionRefresh.Instance });
83:
84: TreeService treeService = new TreeService(-1, TreeAlias, ShowContextMenu, IsDialog, DialogMode, app, string.Format("NorthwindOrder-{0}", node.NodeID));
85:
86: node.Source = customerReader.Get<int>("NumberOfProducts") > 0 ? treeService.GetServiceUrl() : string.Empty;
87:
88: tree.Add(node);
89: }
90: break;
91: case "NorthwindOrder":
92: var orderReader = sqlHelper.ExecuteReader(@"
93: SELECT P.[ProductID], P.[ProductName]
94: FROM [Northwind].[dbo].[Order Details] OD
95: INNER JOIN [Northwind].[dbo].[Products] P ON OD.[ProductID] = P.[ProductID]
96: WHERE OD.[OrderID] = @OrderId",
97: sqlHelper.CreateParameter("@OrderId", this.NodeKey.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries)[1]));
98: while (orderReader.Read())
99: {
100: XmlTreeNode node = XmlTreeNode.Create(this);
101:
102: node.NodeID = Convert.ToString(orderReader.Get<int>("ProductID"));
103: node.Text = orderReader.Get<string>("ProductName");
104: node.Icon = "product.gif";
105: node.Action = string.Empty;
106:
107: node.Menu.Clear();
108: node.Menu.AddRange(new List<IAction> { ActionDelete.Instance, ContextMenuSeperator.Instance, ActionRefresh.Instance });
109:
110: node.Source = string.Empty;
111:
112: tree.Add(node);
113: }
114: break;
115: default:
116: break;
117: }
118: }
119: }
Above code gives us the result as can be seen in first screenshot. Some final remarks.
If you’d like to have different context menu items for each of the different type of nodes in your custom tree, use
1: node.Menu.Clear();
2: node.Menu.AddRange(new List<IAction> { ActionDelete.Instance, ContextMenuSeperator.Instance, ActionRefresh.Instance });
to build your own context menu.
In final project code, I’ve included yet another custom tree loading class, which creates another tree node in the custom Northwind section. Doing so will create an extra level but doesn’t affect the existing stuff. A screenshot after adding an extra tree node
Final result can be downloaded here. Leave your comments here. Hope you enjoyed this tutorial.