I have posted a new build with some enhancements and bug fixes. New features have been added to the "TreeNode Factory" AddOn (be sure to reimport the new version of this add-on into your repository).
Note that you should be familiar with this AddOn before reading this. If this is not the case, you can take a look at the documentation:
<OlyMars directory>\AddOns\TreeNodeFactory\Documentation.mht
Let's say that we have the following scenario:
- We would like to add a parent node named "Categories"
- We would like to add under this parent node all the categories of the database. Each time the user selects one of these nodes, he should see in the status bar the number of products of that category
- We would like to add under each category node all the related products. We would to see the name of the product, the price of that product and the current stock number. Furthermore, each time the product current stock is less than 50, we would like the node to be red; for stock between 50 and 300, we would like it to be orange. For stock above 300, the node should be green. Each time the user selects a product, he should see in the status bar the name of the main supplier of this product. Finally, we do not want to add all the products which price is between 1000 and 1100
To be able to handle this scenario, we will use the new features provided in the "TreeNode Factory" AddOn:
- A new StaticCustomTreeNode class
- Two new events : BeforeInsert and AfterInsert
- The ability to build dynamically both the Id and/or the Display to be used for each node by using the FormatIdDisplay event
- The ContextOfUse has been expanded from the string type to the more general object type allowing you to provide an enumeration instead of a string for example
- A Columns array is now available for each StoredProcedureTreeNode. You can retrieve back all the columns for each row returned by the stored procedure call
Here is an example on how to reuse those new features (you can download this code):
[Database]-----------------------------------------------------------------------------------------
CREATE TABLE [Category] (
[CategoryID] uniqueidentifier,
[CategoryName] [varchar] (255),
CONSTRAINT [PK_Category] PRIMARY KEY ([CategoryID])
)
CREATE TABLE [Supplier] (
[SupplierID] uniqueidentifier,
[SupplierName] [varchar] (255),
CONSTRAINT [PK_Supplier] PRIMARY KEY ([SupplierID])
)
CREATE TABLE [Product] (
[ProductID] uniqueidentifier,
[ProductName] [varchar] (255),
[ProductCategoryID] [uniqueidentifier],
[ProductPrice] [money],
[ProductCurrentStock] [int],
[ProductMainSupplierID] [uniqueidentifier],
CONSTRAINT [PK_Product] PRIMARY KEY ([ProductID]),
CONSTRAINT [FK_Product_Category] FOREIGN KEY ([ProductCategoryID]) REFERENCES [Category] ([CategoryID]),
CONSTRAINT [FK_Product_Supplier] FOREIGN KEY ([ProductMainSupplierID]) REFERENCES [Supplier] ([SupplierID])
)
CREATE Procedure [spS_xTNF_Categories] As
Select
CategoryID
, CategoryName
, (Select IsNull(Count(*), 0) From Product Where ProductCategoryID = CategoryID) As ProductsCount
From Category Order By CategoryName Asc
Return(@@RowCount)
CREATE Procedure [spS_xTNF_Products] (@CategoryID uniqueidentifier) As
Select
ProductID
, ProductName
, ProductPrice
, ProductCurrentStock
, SupplierName
From Product Left Outer Join Supplier On ProductMainSupplierID = SupplierID
Where ProductCategoryID = @CategoryID Order By ProductName Asc
[C#.NET]-----------------------------------------------------------------------------------------
using System.Data.SqlTypes;
using SPs = DemoTNF.DataClasses.StoredProcedures;
using DemoTNF.Windows.TreeNodeFactory;
using SP_TNF = DemoTNF.Windows.TreeNodeFactory.StoredProcedureTreeNodeFactory;
public FormStart() {
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
Init();
}
private void Init() {
// Let's decide once for good how to connect to the back end database
SP_TNF.SetUpConnection(string.Empty);
// We want to handle the following three events:
// FormatIdDisplay, BeforeInsert and AfterInsert
SP_TNF.FormatIdDisplay += new FormatIdDisplayHandler(SP_TNF_FormatIdDisplay);
SP_TNF.BeforeInsert += new BeforeInsertHandler(SP_TNF_BeforeInsert);
SP_TNF.AfterInsert += new AfterInsertHandler(SP_TNF_AfterInsert);
// Let's create a Parent TreeNode to hold all the categories
// Let's use a StaticCustomTreeNode for this
StaticCustomTreeNode categories = new StaticCustomTreeNode(
MyContextOfUse.Categories
, null
, "Categories"
, true);
treeView1.Nodes.Add(categories);
}
private enum MyContextOfUse {
Categories, Category, Product
}
private void treeView1_BeforeExpand(object sender, System.Windows.Forms.TreeViewCancelEventArgs e) {
// Let's get the node being expanded
ITreeNodeFactoryCustomTreeNode currentNode =
e.Node as ITreeNodeFactoryCustomTreeNode;
// If this node is not a node coming from the TreeNodeFactory
// or if this node is already UpToDate, do nothing
if (currentNode == null || currentNode.IsUpToDate) return;
// Depending on the context of use of the current node,
// let's do the right thing
switch ((MyContextOfUse)currentNode.ContextOfUse) {
// If we are expanding the categories node, let's
// fill this node with all the categories. We are going
// to call spS_xTNF_Categories stored procedure for this
case MyContextOfUse.Categories:
SP_TNF.Fill_spS_xTNF_Categories(
currentNode.Nodes // this is the Nodes collection to fill
, MyContextOfUse.Category // this is the context of use of the nodes being added
, true // yes I want to purge the Nodes collection
, true // yes I want to add a sub node to all the added nodes
, SPs.spS_xTNF_Categories.Resultset1.Fields.Column_CategoryID.ColumnIndex // this is the column index of the primary key
, SPs.spS_xTNF_Categories.Resultset1.Fields.Column_CategoryName.ColumnIndex // this is the index of the column to be use for display
, true // I want to get track of all columns for each row
);
break;
// If we are expanding a category node, let's
// fill this node with all the related products. We are going
// to call spS_xTNF_Products stored procedure for this
case MyContextOfUse.Category:
SP_TNF.Fill_spS_xTNF_Products(
currentNode.Nodes // this is the Nodes collection to fill
, MyContextOfUse.Product // this is the context of use of the nodes being added
, true // yes I want to purge the Nodes collection
, false // no I do not want to add a sub node to the added nodes
, (Guid)currentNode.Id // This is the value to use for the @CategoryID stored procedure parameter
, SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductID.ColumnIndex // this is the column index of the primary key
, -1 // We do not want to use one specific column for the display but rather be called in the FormatdDisplay event to format this display
, true // I want to get track of all columns for each row
);
break;
}
// Let's specify that this node is UpToDate now
currentNode.IsUpToDate = true;
}
private void SP_TNF_FormatIdDisplay(object sender, StoredProcedureFactoryFormatIdDisplayEventArgs e) {
switch ((MyContextOfUse)e.Node.ContextOfUse) {
// Let's decide how to format the display for our products
// All the row columns are available via e.Node.Columns[]
case MyContextOfUse.Product:
SqlString productName = (SqlString)e.Node.Columns[SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductName.ColumnIndex];
SqlMoney productPrice = (SqlMoney)e.Node.Columns[SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductPrice.ColumnIndex];
SqlInt32 productCurrentStock = (SqlInt32)e.Node.Columns[SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductCurrentStock.ColumnIndex];
e.Display = string.Format("{0} ({1:c}, {2:### ##0} in stock)", productName.Value, productPrice.Value, productCurrentStock.Value);
break;
}
}
private void SP_TNF_BeforeInsert(object sender, StoredProcedureFactoryBeforeInsertEventArgs e) {
switch ((MyContextOfUse)e.Node.ContextOfUse) {
// We don't want to see all products which prices are greater
// than 1 000 and less than 1 100
case MyContextOfUse.Product:
SqlMoney productPrice = (SqlMoney)e.Node.Columns[SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductPrice.ColumnIndex];
e.Cancel = (productPrice.Value >= 1000m && productPrice.Value < 1100m);
break;
}
}
private void SP_TNF_AfterInsert(object sender, StoredProcedureFactoryAfterInsertEventArgs e) {
switch ((MyContextOfUse)e.Node.ContextOfUse) {
// We would like to set some color to the products nodes
// Red for the products which current stock is less than 50
// Orange for the products which current stock is greater than 50 and less than 300
// Green for the products which current stock is greater than 300
case MyContextOfUse.Product:
SqlInt32 productCurrentStock = (SqlInt32)e.Node.Columns[SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductCurrentStock.ColumnIndex];
if (productCurrentStock.Value < 50) {
e.Node.ForeColor = Color.Red;
}
else if (
productCurrentStock.Value >= 50
&& productCurrentStock.Value < 300
) {
e.Node.ForeColor = Color.Orange;
}
else {
e.Node.ForeColor = Color.Green;
}
break;
}
}
private void treeView1_AfterSelect(object sender, System.Windows.Forms.TreeViewEventArgs e) {
StoredProcedureCustomTreeNode currentNode = e.Node as StoredProcedureCustomTreeNode;
if (currentNode == null) return;
switch ((MyContextOfUse)currentNode.ContextOfUse) {
case MyContextOfUse.Category:
SqlInt32 productsCount = (SqlInt32)currentNode.Columns[SPs.spS_xTNF_Categories.Resultset1.Fields.Column_ProductsCount.ColumnIndex];
statusBar1.Text = string.Format("This category has {0} products", productsCount.Value);
break;
case MyContextOfUse.Product:
SqlString supplierName = (SqlString)currentNode.Columns[SPs.spS_xTNF_Products.Resultset1.Fields.Column_SupplierName.ColumnIndex];
statusBar1.Text = supplierName.IsNull ? "No supplier" : string.Format("Main supplier for this product is: {0}", supplierName.Value);
break;
}
}
[VB.NET]-----------------------------------------------------------------------------------------
Imports System.Data.SqlTypes
Imports SPs = DemoTNF.DataClasses.StoredProcedures
Imports DemoTNF.Windows.TreeNodeFactory
Imports SP_TNF = DemoTNF.Windows.TreeNodeFactory.StoredProcedureTreeNodeFactory
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
Init()
End Sub
Private Enum MyContextOfUse
Categories
Category
Product
End Enum
Private Sub Init()
' Let's decide once for good how to connect to the back end database
SP_TNF.SetUpConnection(String.Empty)
' We want to handle the following three events:
' FormatIdDisplay, BeforeInsert and AfterInsert
AddHandler SP_TNF.FormatIdDisplay, New FormatIdDisplayHandler(AddressOf SP_TNF_FormatIdDisplay)
AddHandler SP_TNF.BeforeInsert, New BeforeInsertHandler(AddressOf SP_TNF_BeforeInsert)
AddHandler SP_TNF.AfterInsert, New AfterInsertHandler(AddressOf SP_TNF_AfterInsert)
' Let's create a Parent TreeNode to hold all the categories
' Let's use a StaticCustomTreeNode for this
Dim categories As New StaticCustomTreeNode( _
MyContextOfUse.Categories _
, Nothing _
, "Categories" _
, True)
TreeView1.Nodes.Add(categories)
End Sub
Private Sub SP_TNF_FormatIdDisplay(ByVal sender As Object, ByVal e As StoredProcedureFactoryFormatIdDisplayEventArgs)
' Let's decide how to format the display for our products
' All the row columns are available via e.Node.Columns[]
Select Case CType(e.Node.ContextOfUse, MyContextOfUse)
Case MyContextOfUse.Product
Dim productName As SqlString = CType(e.Node.Columns(SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductName.ColumnIndex), SqlString)
Dim productPrice As SqlMoney = CType(e.Node.Columns(SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductPrice.ColumnIndex), SqlMoney)
Dim productCurrentStock As SqlInt32 = CType(e.Node.Columns(SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductCurrentStock.ColumnIndex), SqlInt32)
e.Display = String.Format("{0} ({1:c}, {2:### ##0} in stock)", productName.Value, productPrice.Value, productCurrentStock.Value)
End Select
End Sub
Private Sub SP_TNF_BeforeInsert(ByVal sender As Object, ByVal e As StoredProcedureFactoryBeforeInsertEventArgs)
' We don't want to see all products which prices are greater
' than 1 000 and less than 1 100
Select Case CType(e.Node.ContextOfUse, MyContextOfUse)
Case MyContextOfUse.Product
Dim productPrice As SqlMoney = CType(e.Node.Columns(SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductPrice.ColumnIndex), SqlMoney)
e.Cancel = productPrice.Value >= 1000 And productPrice.Value < 1100
End Select
End Sub
Private Sub SP_TNF_AfterInsert(ByVal sender As Object, ByVal e As StoredProcedureFactoryAfterInsertEventArgs)
' We would like to set some color to the products nodes
' Red for the products which current stock is less than 50
' Orange for the products which current stock is greater than 50 and less than 300
' Green for the products which current stock is greater than 300
Select Case CType(e.Node.ContextOfUse, MyContextOfUse)
Case MyContextOfUse.Product
Dim productCurrentStock As SqlInt32 = CType(e.Node.Columns(SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductCurrentStock.ColumnIndex), SqlInt32)
If productCurrentStock.Value < 50 Then
e.Node.ForeColor = Color.Red
ElseIf productCurrentStock.Value >= 50 And productCurrentStock.Value < 300 Then
e.Node.ForeColor = Color.Orange
Else
e.Node.ForeColor = Color.Green
End If
End Select
End Sub
Private Sub TreeView1_BeforeExpand(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewCancelEventArgs) Handles TreeView1.BeforeExpand
' Let's get the node being expanded
Dim currentNode As ITreeNodeFactoryCustomTreeNode = _
CType(e.Node, ITreeNodeFactoryCustomTreeNode)
' If this node is not a node coming from the TreeNodeFactory
' or if this node is already UpToDate, do nothing
If currentNode Is Nothing Or currentNode.IsUpToDate Then Return
' Depending on the context of use of the current node,
' let's do the right thing
Select Case CType(currentNode.ContextOfUse, MyContextOfUse)
' If we are expanding the categories node, let's
' fill this node with all the categories. We are going
' to call spS_xTNF_Categories stored procedure for this
Case MyContextOfUse.Categories
SP_TNF.Fill_spS_xTNF_Categories( _
currentNode.Nodes _
, MyContextOfUse.Category _
, True _
, True _
, SPs.spS_xTNF_Categories.Resultset1.Fields.Column_CategoryID.ColumnIndex _
, SPs.spS_xTNF_Categories.Resultset1.Fields.Column_CategoryName.ColumnIndex _
, True _
)
' currentNode.Nodes: this is the Nodes collection to fill
' MyContextOfUse.Category: this is the context of use of the nodes being added
' true: yes I want to purge the Nodes collection
' true: yes I want to add a sub node to all the added nodes
' SPs.spS_xTNF_Categories.Resultset1.Fields.Column_CategoryID.ColumnIndex: this is the column index of the primary key
' SPs.spS_xTNF_Categories.Resultset1.Fields.Column_CategoryName.ColumnIndex: this is the index of the column to be use for display
' true: I want to get track of all columns for each row
' If we are expanding a category node, let's
' fill this node with all the related products. We are going
' to call spS_xTNF_Products stored procedure for this
Case MyContextOfUse.Category
SP_TNF.Fill_spS_xTNF_Products( _
currentNode.Nodes _
, MyContextOfUse.Product _
, True _
, False _
, New SqlGuid(CType(currentNode.Id, Guid)) _
, SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductID.ColumnIndex _
, -1 _
, True _
)
' currentNode.Nodes: this is the Nodes collection to fill
' MyContextOfUse.Product: this is the context of use of the nodes being added
' true: yes I want to purge the Nodes collection
' false: no I do not want to add a sub node to the added nodes
' (Guid)currentNode.Id: This is the value to use for the @CategoryID stored procedure parameter
' SPs.spS_xTNF_Products.Resultset1.Fields.Column_ProductID.ColumnIndex: this is the column index of the primary key
' -1: We do not want to use one specific column for the display but rather be called in the FormatdDisplay event to format this display
' true: I want to get track of all columns for each row
End Select
' Let's specify that this node is UpToDate now
currentNode.IsUpToDate = True
End Sub
Private Sub TreeView1_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect
Dim currentNode As StoredProcedureCustomTreeNode
Try
currentNode = CType(e.Node, StoredProcedureCustomTreeNode)
Catch
End Try
If currentNode Is Nothing Then Return
Select Case CType(currentNode.ContextOfUse, MyContextOfUse)
Case MyContextOfUse.Category
Dim productsCount As SqlInt32 = CType(currentNode.Columns(SPs.spS_xTNF_Categories.Resultset1.Fields.Column_ProductsCount.ColumnIndex), SqlInt32)
StatusBar1.Text = String.Format("This category has {0} products", productsCount.Value)
Case MyContextOfUse.Product
Dim supplierName As SqlString = CType(currentNode.Columns(SPs.spS_xTNF_Products.Resultset1.Fields.Column_SupplierName.ColumnIndex), SqlString)
StatusBar1.Text = IIf(supplierName.IsNull, "No supplier", String.Format("Main supplier for this product is: {0}", supplierName.Value))
End Select
End Sub
