Click here to Skip to main content
Click here to Skip to main content

How to Handle Recursive Relationship in ASP.NET with SQL Server Database

, 18 Mar 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Build web component based on recursive data

AuthMatrixScreenShot.JPG


Download AuthMatrix.zip - 2.53 MB

I was involved in the development of a Corporation Authorization Matrix Management System which maintains the information about who would approve certain IT system access to internal or external end users for specific organizational unit(s). It was aimed to help enforcing the compliance requirements of Sarbanes-Oxley Act or Bill-C198. During the design phase, I thought about using a table structure with recursive relationship to tackle the requirement of unlimited level of organization/system hierarchy and dynamically changing system configuration menu layout.

The data structure design would present a challenge to the implementation of the web presentation layer, since:

1. At design time, you don’t know what the actual number and level of the organizational units and system configuration menu will be (Since the company I am with is constantly in acquisition mode.), and all the relevant change management and reporting has to be accomplished through the web interface.
2. The regional summary report function will not be that straight forward to implement because the management want to drill down to all levels of the system configuration menu and view corresponding table of authorizers for all the branches in a specific administrative region or area.

Thanks to my current employer, I was given the permission to publish the primitive code base of this project which was developed in Visual Studio 2005 with DotNet Framework 2.0. This release is not of production quality but still present all the major functionalities. Although it was implemented with the AJAX enabled NetAdvantage web control library (You need at least to install the trial version of NetAdvantage Component library ASP.NET 2008 Vol 1 from http://www.infragistics.com/downloads/default.aspx to run this project), it does illustrate some generic idea on how to deal with recursive relationship in ASP.NET 2.0 application in the sense that you can somehow switch to another web control library like ‘Telerik’ (from http://www.telerik.com/ which I had tried myself), or even use your own web controls for the front end without changing the basic design.

The little algorithm contained in the ‘InitSummaryObjects1.cs ‘ file is used to populate a multi-level table titles by figuring out proper "Column Span" and "Row Span" from the recursively related data in the database. I believe you can even apply this algorithm in a JEE application with JSF or Tapestry as front end to achieve flexible dimension based data reporting feature. So I hope in a certain degree this demo code base can demonstrate a general idea and methodology in coping with enterprise web 2.0 applications utilizing recursive table structure at the backend.

The actual data shown in the RegionSummary.aspx page is cached in the Application context, so when the user clicks on the radio button to drill down to different reporting level, the response time is almost instantaneous if you have decent IIS server performance and network connection.

Since I am using Transact SQL stored procedure heavily at database tier, the backend database is not easy to be migrated to different platforms, such as MySql, Oracle or DB2, but it is still possible.

You will probably need a working Active Directory instance to test the complete function set, since AD is required for form based authentication and search for potential authorizers. The package does provide a kind of workaround if you don’t have AD setup but still want to see most of the features. If you want to totally decouple with AD, you will have to substitute the existing ‘membershipADProvider’ with some other ones in the ‘web.conf’ file and implement your own lookup function for potential authorizers and backup authorizers in ‘EditAuthMatrix.aspx.cs’ file and ‘EditBackupAuth.aspx.cs’ file. As for the concept for the ‘backup authorizer’, is just someone to whom an existing authorizer will delegate certain area of responsibilities for a period of time, so that backup authorizer can still grant certain system access to end users during the absence of a primary authorizer.

To meet auditing requirements, all the major modifying activities of the matrix will be recorded into a report table based on which you can further generate all kinds of web reports for historical tracking, this is realized by using table trigger.

Please see the readme.doc file under the web site root for further installation tips. Two demo database files are provided under the App_Data directory, they can be attached to a Sql Server 2000 or 2005 Enterprise Edition or even a Sql Server 2005 Express Edition.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Thomas Yan

Canada Canada
Using Java since 1998, I do like DotNet a lot

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 18 Mar 2008
Article Copyright 2008 by Thomas Yan
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid