Hierarchyid Tutorial with Ironspeed (running sample here).
Introduction.
The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. Read more...
As a structured account identifier, “1.01.01.0001”, that shows the hierarchy position of the account, hierarchyid field stores these information in a much more efficient way, and without the structured identifier levels limitations.
This new data type has new functions like <hierarchyid field>.GetLevel() and <hierarchyid field>.GetAncestor(1).
This tutorial introduces simple operations that will move any node (and descendants) to any position in hierarchy, except the unique root node. The following commands are implemented through new table buttons:
· Insertion: always as the last son of the selected parent; only one level 0 record is allowed.
· Promote – selected record grandparent becomes its parent.
· Demote – previous sibling becomes new parent of selected record.
· MoveUp – changes position with previous sibling.
· MoveDown – changes position with next sibling.
· Expand and Collapse – shows/hides selected record descendants.
Tutorial Environment.
With Ironspeed 5.2.1 or 6.2.1 hierarchy operations will be implemented in a ShowTablePage. Additional resources used:
· Account table described below.
· Code Customizations Templates (vb.net). Download... Unzip to C:\Program Files\Iron Speed\Designer v5.2.1\Code Customizations\vb.
· cv.dll that must be included in project’s bin folder. Download... to application bin folder.
Tutorial steps:
· Download code customization templates.
· Create database and Account Table (SQL Server 2008) using script in Data Base and Table Creation.
· Ironspeed application creation (vb.net, .net framework 3.5, inline); choose only ShowTable, AddRecord and EditRecord pages (warning: do not select _hid field in showTable; this will result in run time error!). Follow steps described in Ironspeed Project and Code Customizations.
· Include cv.dll in application bin folder.
· With Visual Studio 2008 add Microsoft.SqlServer.Types.dll and cv.dll as references.
Data Base.
· Hierarchy will be implemented in Account table as a general ledger chart of accounts.
· Primary key is the account field.
· The hierarchyid field is named _hid (standard name expected by code customization templates).
· The _niv Field is a calculated Field, also mandatory.
· Parent field is an example of new hierarchyid functions. Although a foreign key candidate, Sql Server does not allow this declarative referential integrity for non deterministic calculated field. We will use Ironspeed virtual key as a workaround.
Data Base and Table Creation
CREATE TABLE [dbo].[Account](
[Account] [bigint] IDENTITY(1,1) NOT NULL,
[AccountName] [nvarchar] (150) NOT NULL,
[_hid] [hierarchyid] NOT NULL,
[_niv] AS ([_hid].[GetLevel]()),
hidStr as ([_hid].ToString()),
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[Account] ASC
) WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY],
CONSTRAINT [IX_Account] UNIQUE NONCLUSTERED
(
[_hid] ASC
) WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Function [dbo].[AccountParent](@hid as hierarchyid)
returns bigint
as
begin
declare @parentHid as hierarchyid, @parent as bigint
set @parentHid=@hid.GetAncestor(1)
set @parent=(select Account from Account where _hid=@parentHid)
return @parent
end
GO
Alter Table dbo.Account
Add [Parent] as (dbo.AccountParent([_hid]))
GO
hidStr field was included to show the hierarchy string representation.
Ironspeed Project.
In Application Wizard select Account table of hid database, and only ShowTable, AddRecord and EditRecord pages for generation.
Choose vb.net, .Net framework 3.5 and inline sql.
In ShowTable:
· Include Account, _niv, hidStr and Parent fields;
· Leave only Add record, Refresh data and Reset filters Table Buttons;
· Remove Show record button at row level.
· Change the default generated filter for accountName to bind to _niv field and "less than or equal" condition (at properties).
Now add a new image button at a new column to the right of the Add record table button. Name it "AccountPromoteButton", use "../images/ButtonBarPrevious.gif" as icon and choose custom bind (properties).
To the right of the promote button repeat the process including "AccountDemoteButton" (icon "../images/ButtonBarNext.gif", custom command).
To the right of the demote button repeat the process including "AccountUpButton" (choose an icon representing up arrow , custom command).
To the right of the up button repeat the process including "AccountDownButton" (choose an icon representing down arrow, custom command).
Include a new column to the right of delte button. Add two new image buttons (same cell): "AccountExpandButton"and "AccountCollapseButton".
Associate "../images/DialogHeaderExpand.gif" and "../images/DialogHeaderCollapse.gif" to "AccountExpandButton"and "AccountCollapseButton".
Assure that the names conform to the pattern described. The Code Customization Wizard work depends on these patterns.
Apply the following attributes to the TD tag of AccountNAme field: Id="IndentedField" runat="server"
Optionally create a virtual foreign key in parent field referencing field account of Account table.
Your ShowTablePage must look like:
Warning (Version 6.2 only). You must change manually the Account.xml (App_Code\Data Access Layer folder), column _hid, the value of element columnType from String to Binary and the value of element columnDBType from hierarchyid to varbinary. This must be done again after da base scan.
Original Account.xml File Modified.
Code Customizations.
In Code Customization Wizard select in Hierarchy, 1-DataTablePreparation and choose the options for every variable.
Select ShowAccountTablePage.aspx and repeat the process for 2-ShowTablePreparation.
Now select AddAccountRecordPage and repeat the process for 3-AddRecordPreparation.
Application Generation and Test.
With Visual Studio 2008 add Microsoft.SqlServer.Types.dll and cv.dll as references. Now, in Ironspeed generate and test the sample application.
Sample application looks like (before "account receivables" collapse)
Account receivable collapsed and prepared for move up
After moving and expanded. Note the new string representation (Hid Str) for moved record and descendants.
