• Download sample - 9.1 MB

Introduction 

While in recent development activity we have come across an RFC where our client wants to show counts against each menu item and in almost real time off course. Internal discussion leads to have some sort of framework that should allow managing change easy and quickly, that is

• Attaching count to a new menu should not require changes at all application layers
• Detaching a count from menu should not require any effort at all
• Bug fixes in this area should not require a complete release
• Changes in the count logic should be done in isolation without modifying any existing layers

We have googled and could not found much help which leads to a decision to build our own framework and help others by writing this article in case they like to develop similar feature. 

Solution

Based on above requirements we have sketched a framework that will do the job. The pseudo solution could be

1. Application should request an AJAX call to the server every n minutes lets say 
2. The server should return some sort of XML which contains numbers against each menu item
3. Application process the xml by iterating the nodes. If it found a node that has count value it will update the tree node

If we closely look at the above approach we can observe that solution has three layers. Lets dissect each layer.

Database Layer

We thought that database should hold the key to this framework. As standard business application menu mostly render from permission tables where each user’s permissions are stored and upon login the user’s permission loads and rendered via menu dynamically. So we see an obvious change in this structure, if somehow we could attach some code to each permission/menu our problem will get solved.

Let’s assume we have following table structure that store permissions

At database layer the most granular code that runs is functions. If we somehow attach a function at each menu than our most of the problems gets solved. SQL Functions are isolated independent components that are highly maintainable so we have decided to use functions. The modified table structure would be like:

This gives us significant benefits to complete our requirements. Now we can

• Write distinct business to each items to calculate count
• Isolate the bug as we know that it has to be the function since that is calculating the count
• Bug fixing is easy, we only need to patch the function and deploy that function only
• Similarly changes are easy to do on each individual menu
• Performance won’t be compromise as we are using SQL Functions which are compiled in nature
• Detaching count to a menu item is now a piece of cake
• Attaching count to a new menu has now become very easy as we only need to create new function and simply associate it with the menu. This task even can be done at production site.

Parameters to these functions are critical as these parameters will act as context to these function.  They will help them decide from where they have invoked, how they have invoked and/or do they require any special logic. For example LoginId can be tricky here as the count might depend upon individual user business logic. In case if a single function has been decided as silver bullet for all menu items, in that case function id would be a critical parameter for a function in deciding from where it has been invoked.

All we now need a mechanism to load the menu items of an individual user, execute all the functions associated with the menu, store the result in XML and pass to application and we are all done.

XML

Instead of returning result in tabular format we choose to return as XML. The reason is that XML represent hierarchal structure (Menu for example) in much better way if compared to other formats. Secondly it would be handy at client side to iterate xml as it almost represent the same tree structure. We decided to generate XML at Database layer because of good xml support at SQL Server but its matter of choice, one can generate the same XML at Business Layer as well.

<r>
   <n t="0_1" v="24">
      <n t="0_2" v="58">
         <n t="0_3" v="74"/>
     </n>
  </n>
</r>

If we closely look at the xml we can see the efforts that have been put to decrease the xml size. This is being done to reduce the data size floating from server to browser every N Minutes. n represent here a menu node. an inner n means a menu inside a menu and so on. t represent the menu key that will be use to precisely identify the tree menu node. v represent a value that we will be associated with the menu tree node. An empty v means no value which naturally means should not be updated.

Dynmic SQL

Attaching SQL functions to individual menu item we think is an elegant idea as it provides enormous maintenance flexibility, modifications, new additions, removal, bug fixing, business changes can be done very quickly without impacting the whole system. But the challenge here is how to develop function execution mechanism without causing significant performance impact. One idea was to write some iterative logic which in turn execute the function and store result at some place. We have improved this idea by eliminating the iteration and built a dynamic sql like this

SELECT <HardcodeValue1>, <HardcodedValue2>, dbo.get_xxxx(HardcodeValue1, HardcodeValue2,..)

UNION ALL

SELECT <HardcodeValue1>, <HardcodedValue2>, dbo.get_xxxx(HardcodeValue1, HardcodeValue2,..)

....

Above approach will give us a single SQL that will be executed only once and will return result in a tabular structure. This tabular strucure now has number against each menu item. A simple temporary table can now hold these values as shown below. This temporary table is now of great help as through this we can update countvalues against each menu item in one go that is without any iterative loops.

Hirarical XML Generation

Yes, SQL Server has great native xml support. But in our case there is a challenge. since our menu structure is hierarchal and our stated XML is also nested in nature hence we required a mechanism to have nested elements in our XML which is not directly supported by SQL SERVER. This required us to built a function specifically to do this job. We devised following mechanism for generative nested elements in  our xml

SELECT 
   isnull(Unique_id,'') as 't',
   isnull(n.value,'') as 'v',
   CASE WHEN IsNull(ParentFunctionId,0)=@FunctionId 
        THEN dbo.DocGet_XMLNode(@MENUTBL, FunctionId) 
   END
FROM     @MENUTBL as n 
WHERE IsNull(ParentFunctionId,0)=@FunctionId
FOR XML AUTO, TYPE

Business Layer

The sample provided does not require an isolated business layer. Since we have use ASP.NET MVC for the sample so a controller can be treated as business layer. The responsibility of this layer now is to invoke the Database Components, get the required xml and return the result back to application. It has all being done using Controller Actions. Similarly for illustrative purpose simple ADO.NET is used for Database access. One thing that we would like to mention here is that this layer can also be used for generating the required XML (Which is currently being done at database layer). This is a matter of choice now. We used database because of its great support in XML. A SELECT FROM FOR XML AUTO is a powerful method that generates XML fast, since a mechanism is already present that's why we did not re-engineer it.

Presentation Layer

ASP.NET MVC is great in building cool web applications, it has tight integration with jQuery and easily support third party tools. We have used DEVEXPRESS Tree control MVC Extension for menu generation. jQuery is used for AJAX calls and client side tree update.

Using the code

As already stated above that the key to all this framework is at the database layer. DocGet_MenuXML stored procedure is the Heart as it holds all menu structure, build the dynamic sql for executing the associated functions and finally generate the XML in required format. Now lets dissect the DocGet_MenuXML Stored Procedure.

DocGet_MenuXML

The Stored Procedure begins by declaring variables. One of the important variable is @MENUTBL as this table will hold the required menu structure to be returned plus value field which will store the number associated with the menu item. We need to remember here that a menu structure is dependent upon user roles and rights. So it is necessary to get only those menu items for which a user has rights. In addition as the time pass menu items also become obsolete and become in active. We also need to filter those as well. This @MENUTBL do all these jobs.

Below excerpt from the SP shows how all active functions has been retrieved and stored in the @MNUTBL. 

WITH MENUHIRARCHY (FunctionParentId, FunctionId, MenuDisplayText, Level, countfunction)
AS
(
    SELECT e.functionparentid, e.FunctionId, e.MenuDisplayText, e.Level, e.countfunction
    FROM  SEC_FUNCTION AS e
    WHERE 1=1
    and     e.functionparentid is null
    UNION ALL
    SELECT e.functionparentid, e.FunctionId, e.MenuDisplayText,e.Level, e.countfunction
    FROM SEC_FUNCTION AS e
    INNER JOIN MENUHIRARCHY M on M.functionId = e.functionparentId
    WHERE 1=1
    AND     e.functionIsActive=1
    AND     e.functionIsActive=1
)
INSERT INTO @MENUTBL
(
        UNIQUE_ID,
        PARENTFUNCTIONID,
        FUNCTIONID,
        Level,
        MenuDisplayText,
        countfunction
)
SELECT  DISTINCT '0_'+CAST(FunctionId as varchar(10)),FunctionParentId, FunctionId, Level, MenuDisplayText, countfunction
FROM    MENUHIRARCHY;

As you can see a unique Id is being generated by pre-appending 0 in the functionid. This is just to show you that a custom unique Id can also be generated in this manner. 

The next step now is to built the SQL Query dynamically as briefly explained above. Below excerpt from SP shows how dynamic SQL query has been built. The one important thing to understand here is the where clause. Do understand that it is not necessary that each menu item must have a count function associated with it because business can ask count menu items to specific menus only. The where clause filtering all those menu items that do not have associated count function. Also take a closer look of how we passed the parameters to the function. The one limitation here is that parameter set is constant to all functions, this limitation can be avoided by storing all the parameters against each function in a table and their resolution mechanism (from where we get the values to these parameters) but this is off topic here.

SELECT @tsql = COALESCE(@tsql + '  UNION ALL ', 'INSERT INTO ##LocalTempMenuTable(ProjectID, FunctionID, value) ') + 'SELECT ' + cast(ProjectID as varchar(10)) + ',' + cast(FunctionID as varchar(10)) + ',' + Replace(Replace(Replace(CountFunction, '{0}', ProjectID), '{1}', FUNCTIONID), '{2}', @p_ContactID)
FROM   @MENUTBL
WHERE CountFunction IS NOT NULL;

Execution step of @TSQL is easy. The sql will be executed and the result will be stored in temporary table.

EXEC SP_EXECUTESQL @TSQL;

Now we need updated our value->@MENUTBL, its a peice of cake now as we just need a join to update all functions value to their associated menu items. See below how we did it

UPDATE    @MENUTBL
SET        value=CAST(m.value as varchar(20))
FROM    @MENUTBL a
INNER JOIN ##LocalTempMenuTable m on a.functionId = m.functionId and a.ProjectId = m.ProjectId;

The last step now it generate XML, we have used FOR XML AUTO, TYPE for generative XML in the required format. DocGet_XMLNode is used for creating nested elements because our xml is nested in nature.

SET @xml =  
(
    SELECT
    IsNull(Unique_id,'') as 't',
    Isnull(n.value,'') as 'v',
    dbo.DocGet_XMLNode(@MENUTBL, FunctionId)
    FROM   @MENUTBL n
    where    1=1
    AND        Level = 1
    FOR XML AUTO, TYPE
);
    
SET @p_Outstring = '<r>'+CONVERT(VARCHAR(max), @xml, 1)+'</r>';

DocGet_MenuXML ends here. Now let's see how we created the function get_GetAreaCount which is returning number count for Area menu item. The function is just a sample and does not do anything in real, it is just to show you how you can build complex functions on your own. get_GetAreaCount is simply returning a random value here.

ALTER FUNCTION [dbo].[get_GetAreaCount](@p_Param1 int, @p_Param2 int,  @p_Param3 int)
RETURNS INT
AS
BEGIN

    declare @Count INT = NULL  
    
 --   SELECT  @Count = COUNT(DISTINCT FUNCTIONID)
    --FROM    SEC_FUNCTION 
 --   WHERE    Functionid = @p_Param2
    SELECT @Count = cast(rndResult * 100 as int)
    FROM RandomView
    
    return  @Count
END

HomeController->GetServerResponse

Application initiates AJAX call to HomeController->GetServerResponse action. Below is the excerpt of the code fragment and the code is self explanatory. It simply create a database connection, create a command object and execute the DocGet_MenuXML SP and return the content back to application.

public ActionResult GetServerResponse()
{
    SqlConnection connection = null;
    try
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        string outXMLString = string.Empty;

        connection = new SqlConnection(connectionString);

        using (SqlCommand cmd = new SqlCommand("DocGet_MenuXML", connection))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@p_ContactID", System.Data.SqlDbType.Int));
            cmd.Parameters["@p_ContactID"].Direction = System.Data.ParameterDirection.Input;
            cmd.Parameters["@p_ContactID"].Value = 1;
            SqlParameter outparam = cmd.Parameters.Add(new SqlParameter("@p_Outstring", System.Data.SqlDbType.VarChar, 8000));
            outparam.Direction = System.Data.ParameterDirection.Output;

            connection.Open();
            cmd.ExecuteNonQuery();
            connection.Close();

            outXMLString = Convert.ToString(cmd.Parameters["@p_Outstring"].Value);
        }

        ViewBag.MenuXML = outXMLString;
        return Content(outXMLString);
    }
    catch (System.Data.DataException e)
    {
        if (connection != null)
            if (connection.State != System.Data.ConnectionState.Closed)
                connection.Close();
        throw new Exception(e.Message);
    }
}

_Layout.cshtml

this is core part of presentation layer, it first set timer hook for every N seconds/minutes and invoke getCountStatus() function. 

window.setInterval(function () {
    getCountStatus();
}, 1000 * 10 * 1.25);          //where X is your every X minutes

getCountFunction invoke AJAX call, receive the menuXML string, parse the xml string into XML object. Upon successfully parsing it gets the root node of our xml which is "r" if remember and pass it to MenuIterator function.

var actionUrlGetServerResponse = '@Url.Action("GetServerResponse", "Home")';
function getCountStatus() {
    $.get(actionUrlGetServerResponse, function (projectTransmittalStatus) {
        var menuXML = '@ViewBag.MenuXML';

        $xmlDoc = $($.parseXML(projectTransmittalStatus));
        var rootNode = $xmlDoc.find("*").eq(0);
        MenuIterator($(rootNode));
    });
}

MenuIterator is an iterator in nature. It finds all n nodes in the xml. For each xml node it gets the key by looking at t attribute. using this key it search for DevExpress tree control and find the corresponding node. Upon successfully identifying the node application uses regual expression to identify any number already present in the tree node text. There is tricky because in correctly handling may result concatenation of "()" on every AJAX Call. We used regular expression to identify that menu text has already been updated or not. If it has been updated then we updated only number in the menu text. If it is not then we update the text like this Area (9)

function MenuIterator($currentXMLNode) {
    if ($currentXMLNode != null) {

        $currentXMLNode.find('n').each(function (childXMLNode) { //'n'
            if (childXMLNode != null) {
                var menuText = $(this).attr('t');
                var node = tvFunction.GetNodeByName(menuText);
                if (node != null) {
                    //console.log("INFO", "node Value..." + node.GetText);
                    var nodeTotal = $(this).attr('v');
                    console.log("INFO", "nodeTotal" + nodeTotal);
                    if (nodeTotal != '') {
                        var re = new RegExp(/\d+/g);
                        var nodeText = node.GetText();
                        console.log("INFO", "nodeText = " + nodeText);
                        var m = re.exec(nodeText);
                        if (m != null) {
                            nodeText = nodeText.replace(re, nodeTotal);
                            console.log("INFO", "Matched" + nodeText);
                        }
                        else {
                            nodeText = nodeText + '(' + nodeTotal + ')';
                            console.log("INFO", "Not Matched Value..." + nodeText);
                        }
                        node.SetText(nodeText);
                    }
                }
                else {
                    //console.log("INFO", "node Value NULL " + menuText);
                }

            }
        });
    }
}

Conslusion

The approach formulated in this article not only help building applications which have similar requirements but also can be used in other areas where code need to be binded dynamically

History

First version - 0.1 - 20-Jul-2014