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()
{
IncludeExternalJsFile();
RegisterJsCalculationFunctions();
grdview.RowDataBound +=
new GridViewRowEventHandler(grdview_RowDataBound);
}
public void RegisterJsCalculationFunctions()
{
CreateJsCalculationFunctions();
Int16 i = 1;
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++;
}
}
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 = "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;
}
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);
ScriptBldr.Append("var tr=getExpectedTR(obj);");
ScriptBldr.Append("\n");
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);
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)
{
BindCalculationsAttributes(e.Row);
}
}
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("}", "");
return ControlName;
}
public static string[] GetControlsInCalculationExpression(string Template)
{
Regex r = new Regex(@"\{(?<id>\w+(\.\w+)*)(?<param>:[^}]+)?\}");
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;
}
public static string[] GetControlNamesFromExpression(string Template)
{
Regex r = new Regex(@"\{(?<id>\w+(\.\w+)*)(?<param>:[^}]+)?\}");
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.