Database driven client side calculations in GridView

• Download Source - 137.4 KB

Introduction

This article talks about how to develop database driven client side calculations for a GridView. The calculation expressions are configurable in a SQL Server table. Based on the configurations, this code sample generates the necessary functions in JavaScript.

Background

A few weeks back, I was working on a project that requires a lot of calculations to be done in a GridView and these calculation expression needed to be changed very frequently, new calculations being injected for new data columns etc.

This can be done vey easily by changing the JavaScript and HTML, but business requirement was that this should be configurable, so whenever the calculation logic gets changes, we just need to change the configurable values.

To achieve the dynamic nature of calculations at client side, I have developed a sample ASP.NET application in C# using a SQL Server database. The DB script and the application sample is attached to this article.

Code Discussion

Let's first discuss the database. I have created a table Column_Mapping with columns GridID, ColumnID, ColumnName, ControlType, IsDynamic, CalculationExpression, and IsActive. CalculationExpression is the expression to calculate the value of ColumnName's control in the grid. GridID is used to identify the associated grid of the control.

CalculationExpression would be like {Qty}*{Rate} where Qty and Rate are control IDs in the grid. So, in calculationExpression, the control IDs should be within { and }.

The other table is Invoicedetails, having data on which we need to do calculations.

The CommonUI class contains the Setupgrid function that is the top level function to implement the functionality. To implement the calculation functionality in the grid, just call the Setupgrid function from the code-behind..

public class CommounUI
{
    public int GridViewID { get; set; }
    public GridView grdview { get; set; }
    private string GridViewCachKey { get; set; }

    public CommounUI(int GridViewID, GridView grd)
    {
        this.GridViewID = GridViewID;
        this.grdview = grd;
        GridViewCachKey =grd.Page.Title.Trim()+ grdview.ID;
    }
    
    public void SetupGrid()
    {
        //to include calculations.js in the calling page,
        //so no need to add reference in 
        //aspx page at design time
        IncludeExternalJsFile(); 
        //to create required calculation functions script 
        //and register the script in calling page
        RegisterJsCalculationFunctions();
        grdview.RowDataBound += 
           new GridViewRowEventHandler(grdview_RowDataBound);
    }

    public void RegisterJsCalculationFunctions()
    {
    
        //to create js calculations functions.
        CreateJsCalculationFunctions();
        Int16 i = 1;
        //jsFunctionsDict will hold the generated js functions
        Dictionary<string, ScriptContainer> jsFunctionsDict = 
           (Dictionary<string, ScriptContainer>)HttpRuntime.Cache[GridViewCachKey];
        foreach (ScriptContainer entry in jsFunctionsDict.Values)
        {
          grdview.Page.ClientScript.RegisterClientScriptBlock(this.GetType(), 
            "AutoGenFunction" + i.ToString()+grdview.ID , 
            entry.JSFunctionScript, true);
          i++;
        }
       
    }

    /// <summary>
    /// This function generates respective
    /// js functions based on the database entries.
    /// </summary>
    public void CreateJsCalculationFunctions()
    {

        Dictionary<string, ScriptContainer> jsFunctionsDict = 
           new Dictionary<string,ScriptContainer>();
        DataTable dt = new DataTable();
        dt = DataLayer.GetAllColumnsMapping(this.GridViewID);
        string ctrl;
        
        string[] controlsArrayTokens;
        string javascriptFunction = string.Empty;
        string CalculationExpression = string.Empty;
        
        foreach (DataRow row in dt.Rows)
        {
            if (row["CalculationExpression"] != DBNull.Value && 
                row["CalculationExpression"].ToString().Trim() != "")
            {
                ctrl = row["columnname"].ToString();
                CalculationExpression = row["CalculationExpression"].ToString();
                controlsArrayTokens = 
                  Calculations.GetControlsInCalculationExpression(CalculationExpression);

                //javascriptFunction variable will hold the js function name
                javascriptFunction = "AutoGeneratedCalculationsFor"  + grdview.ID+ ctrl;
                string functionscript = 
                  CreateCalculationFunction(javascriptFunction, CalculationExpression, ctrl);
                string javascriptAttributeString =  javascriptFunction + "(this);";
                jsFunctionsDict.Add(ctrl, new ScriptContainer { JSFunctionName = 
                  javascriptAttributeString, JSFunctionScript = functionscript, 
                  JSExpression = CalculationExpression });

            }
        }
        HttpRuntime.Cache[GridViewCachKey] = jsFunctionsDict;
    }

    /// <summary>
    /// adds the onblur attribute in respective controls of the gridview
    /// </summary>
    /// <param name="GridRow"></param>
    public void BindCalculationsAttributes(GridViewRow GridRow)
    {
        string javascriptAttributeString;
        Dictionary<string, ScriptContainer> jsFunctionsDict = 
          (Dictionary<string, ScriptContainer>)HttpRuntime.Cache[GridViewCachKey];
       foreach (ScriptContainer entry in jsFunctionsDict.Values)
       {
           foreach (string ctrlName in 
              Calculations.GetControlNamesFromExpression(entry.JSExpression))
           {
               javascriptAttributeString = entry.JSFunctionName;
               Control expCtrl = GridRow.FindControl(ctrlName);
               if (expCtrl != null)
               {
                   if (expCtrl is TextBox)
                   {
                       if (((TextBox)expCtrl).Attributes["onblur"] == null)
                           ((TextBox)expCtrl).Attributes.Add("onblur", 
                             javascriptAttributeString);
                       else
                           ((TextBox)expCtrl).Attributes["onblur"] = 
                            ((TextBox)expCtrl).Attributes["onblur"].ToString() + 
                              javascriptAttributeString;
                   }
                   else if (expCtrl is Label)
                   {
                       ((Label)expCtrl).Attributes.Add("onblur", 
                                                       javascriptAttributeString);
                   }
                   else if (expCtrl is DropDownList)
                   {
                       ((DropDownList)expCtrl).Attributes.Add("onblur", 
                                               javascriptAttributeString);
                   }
               }
           }
       }
    }
                
    private string CreateCalculationFunction(string FunctionName, 
                   string CalculatinExpression, string DestControlID)
    {
        string[] controlsArrayTokens;
        StringBuilder ScriptBldr = new StringBuilder();
        ScriptBldr.Append("\n");
        ScriptBldr.Append("function " + FunctionName + "(obj)");
        ScriptBldr.Append("\n");
        ScriptBldr.Append("{");
        ScriptBldr.Append("\n");
        controlsArrayTokens = 
          Calculations.GetControlsInCalculationExpression(CalculatinExpression);
        //getExpectedTR is defined in calculations.js and return tr of passed object
        ScriptBldr.Append("var tr=getExpectedTR(obj);");
        ScriptBldr.Append("\n");
        //namingcontaner is hierarchical unique id, 
        //for example if control is grdview_ctl02_rate
        //we are extracting grdview_ctl02_
        ScriptBldr.Append("var namingContainer=
           obj.id.substring(0,obj.id.lastIndexOf('_')+1);");
        ScriptBldr.Append("\n");
        foreach (string token in controlsArrayTokens)
        {
            string ctrlName = Calculations.GetcontrolNameFromToken(token);
            //getElementinTableRow is defined in calculation.js, 
            //it's like getelementById but this 
            //function searches control in tr only, so more optimized
            ScriptBldr.Append("var " + ctrlName + "=getElementinTableRow(tr, 
              namingContainer+" + "'" +ctrlName + 
              "'" + ").value;");
            ScriptBldr.Append("\n");
        }
        if (DestControlID != null)
        {
            CalculatinExpression = 
              CalculatinExpression.Replace("{", "").Replace("}", "");
            ScriptBldr.Append("var calculationExpression='" + 
                              CalculatinExpression + "';");
            ScriptBldr.Append("\n");
            ScriptBldr.Append("var destcontrolid=namingContainer+'" + 
                              DestControlID + "';");
            ScriptBldr.Append("\n");
        }
                   
        ScriptBldr.Append("var result =eval(calculationExpression);");
        ScriptBldr.Append("if(isNaN(result)){result=0};");
        ScriptBldr.Append("getElementinTableRow(tr, 
                          destcontrolid).value =result;");
        ScriptBldr.Append("\n");
        ScriptBldr.Append("}");
        ScriptBldr.Append("\n");

        return ScriptBldr.ToString();

    }

    private void grdview_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //adds onblur attribute in respective controls
            BindCalculationsAttributes(e.Row);
        }
    }

    /// <summary>
    /// includes calculation.js calling page at runtime. 
    /// </summary>
    private void IncludeExternalJsFile()
    {
        grdview.Page.ClientScript.RegisterClientScriptInclude(
          "ScriptforCalculations", "Calculations.js");
    }
}

RegisterJsCalculationFunctions generates the required function scripts and adds attributes to the respective functions. The ScriptContainer class contains the generated function name and the script. The getElementinTableRow and getExpected are defined in the calculation.js file, which gets included in the ASPX page by calling IncludeExternalJsFile. The BindCalculationsAttributes function calls the GridView's RowDatabound event to add onblur attributes to the mapped columns.

public class ScriptContainer
{
    public string JSFunctionName { get; set; }
    public string JSFunctionScript { get; set; }
    public string JSExpression{ get; set; }
}
    
public class Calculations
{

    public static string GetcontrolNameFromToken(string token)
    {
       string ControlName = token.ToString().Replace("{", "");
        ControlName = ControlName.Replace("}", "");
        //m.Value = ControlName;
        return ControlName;

    }

    /// <summary>
    /// Returns collection of tokens from calculation expression
    /// </summary>
    /// <param name="Template"></param>
    /// <returns></returns>
    public static string[] GetControlsInCalculationExpression(string Template)
    {

        Regex r = new Regex(@"\{(?<id>\w+(\.\w+)*)(?<param>:[^}]+)?\}");
            // Use the Regex to find all the placeholders
            MatchCollection mc = r.Matches(Template);
           string ControlName;
           string[] ControlNames=new string[mc.Count];
            Int16 i=0;
            foreach (Match m in mc)
            {
                ControlName = m.ToString();
                ControlNames[i] = ControlName;
                i++;
            }

            return ControlNames;
        }

        /// <summary>
        /// Returns collection of control names from calculation expression
        /// </summary>
        /// <param name="Template"></param>
        /// <returns></returns>
        public static string[] GetControlNamesFromExpression(string Template)
        {
            //regular expression to extract token from the calculation expression
            Regex r = new Regex(@"\{(?<id>\w+(\.\w+)*)(?<param>:[^}]+)?\}");
            // Use the Regex to find all the placeholders
            MatchCollection mc = r.Matches(Template);
            string ControlName;
            string[] ControlNames = new string[mc.Count];
            Int16 i = 0;
            foreach (Match m in mc)
            {
                ControlName = m.ToString();
                ControlName = m.ToString().Replace("{", "");
                ControlName = ControlName.Replace("}", "");
                ControlNames[i] = ControlName;
                i++;
            }

            return ControlNames;
        }
    }

Here, I am extracting the control's name by applying the Regex class for Regular Expressions.

Let's look at DataLayer:

public class DataLayer
{
    public DataTable GetGridData(int GridId)
    {
      
        DataSet ds = new DataSet();
        Database db = DatabaseFactory.CreateDatabase("DBDrivenGrid");

        DbCommand cmd = db.GetSqlStringCommand("select * from invoicedetails");
        
        ds = db.ExecuteDataSet(cmd);
        return ds.Tables[0];
          
    }

    public DataTable GetGridProperties(int GridId)
    {

        DataSet ds = new DataSet();
        Database db = DatabaseFactory.CreateDatabase("DBDrivenGrid");
        DbCommand cmd = db.GetSqlStringCommand("select * from invoicedetails");
        ds = db.ExecuteDataSet(cmd);
        return ds.Tables[0];

    }

    public static DataTable GetDynamicColumnsMapping(int GridId)
    {
        DataSet ds = new DataSet();
        Database db = DatabaseFactory.CreateDatabase("DBDrivenGrid");
        DbCommand cmd = db.GetSqlStringCommand("select * from " + 
                  "Column_mapping where isdynamic=1 and Gridid=" + GridId.ToString());
        ds = db.ExecuteDataSet(cmd);
        return ds.Tables[0];
    }

    public static DataTable GetAllColumnsMapping(int GridId)
    {
        DataSet ds = new DataSet();
        Database db = DatabaseFactory.CreateDatabase("DBDrivenGrid");
        DbCommand cmd = db.GetSqlStringCommand("select * " + 
                  "from Column_mapping where Gridid=" + GridId.ToString());
        ds = db.ExecuteDataSet(cmd);
        return ds.Tables[0];
    }
}

The GetAllColumnsMapping function returns all the column mapping for a particular grid. Here, I am using the Microsoft Data Applications block to interact with the database. Please change the database connection string in web.config as per your SQL Server name and other settings.

The GridView Markup

<asp:GridView ID="grdview" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="Item id">
            <ItemTemplate>
                <asp:TextBox ID="itemid" runat="server" 
                  DataColumn="itemid" 
                  Text='<%# DataBinder.Eval(Container.DataItem,"itemid") %>' />
            </ItemTemplate>
        </asp:TemplateField>
       <asp:TemplateField HeaderText="qty">
            <ItemTemplate>
                <asp:TextBox ID="qty" runat="server" 
                  DataColumn="qty" 
                  Text='<%# DataBinder.Eval(Container.DataItem,"qty") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="rate">
            <ItemTemplate>
                <asp:TextBox ID="rate" runat="server" 
                  DataColumn="rate" 
                  Text='<%# DataBinder.Eval(Container.DataItem,"rate") %>' />
            </ItemTemplate>
        </asp:TemplateField>
         <asp:TemplateField HeaderText="Amount">
            <ItemTemplate>
                <asp:TextBox ID="amount" runat="server" 
                  DataColumn="amount" 
                  Text='<%# DataBinder.Eval(Container.DataItem,"amount") %>' />
            </ItemTemplate> 
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Freight">
            <ItemTemplate>
                <asp:TextBox ID="Freight" runat="server" 
                  DataColumn="amount" 
                  Text='<%# DataBinder.Eval(Container.DataItem,"freight") %>' />
            </ItemTemplate> 
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Here, we are putting the textbox in the template fields and the textbox ID is the same as per the tokens in the calculation expression, like the calculation expression of the amount field is {Qty}*{Rate}. Then, we should have a control with ID Amount, one with ID Qty, and one with ID Rate.

Points of Interest

This code sample assumes that you are creating the control IDs without underscores; for that, you need to do a small code change.