GridView 中的数据库驱动的客户端计算

• 下载源码 - 137.4 KB

引言

本文讨论了如何为 GridView 开发数据库驱动的客户端计算。 计算表达式可在 SQL Server 表中配置。 基于这些配置，此代码示例在 JavaScript 中生成必要的函数。

背景

几周前，我正在做一个项目，需要在 GridView 中完成大量计算，并且这些计算表达式需要经常更改，还要为新的数据列注入新的计算等等。

这可以通过更改 JavaScript 和 HTML 轻松完成，但业务需求是它应该是可配置的，因此每当计算逻辑发生变化时，我们只需要更改可配置的值即可。

为了实现客户端计算的动态特性，我使用 SQL Server 数据库开发了一个 C# 中的 ASP.NET 示例应用程序。 数据库脚本和应用程序示例已附加到本文中。

代码讨论

首先让我们讨论数据库。 我创建了一个表 Column_Mapping，其中包含列 GridID, ColumnID, ColumnName, ControlType, IsDynamic, CalculationExpression 和 IsActive。 CalculationExpression 是计算网格中 ColumnName 控件值的表达式。 GridID 用于标识控件的关联网格。

CalculationExpression 应该像 {Qty}*{Rate}，其中 Qty 和 Rate 是网格中的控件 ID。 因此，在 calculationExpression 中，控件 ID 应该在 { 和 } 之间。

另一个表是 Invoicedetails，其中包含我们需要进行计算的数据。

CommonUI 类包含 Setupgrid 函数，该函数是实现该功能的最顶层函数。 要在网格中实现计算功能，只需从代码隐藏中调用 Setupgrid 函数。

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 生成所需的函数脚本并将属性添加到相应的函数。 ScriptContainer 类包含生成的函数名称和脚本。 getElementinTableRow 和 getExpected 在 calculation.js 文件中定义，通过调用 IncludeExternalJsFile 将其包含在 ASPX 页面中。 BindCalculationsAttributes 函数调用 GridView 的 RowDatabound 事件，以将 onblur 属性添加到已映射的列。

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;
        }
    }

在这里，我通过应用 Regex 类进行正则表达式来提取控件的名称。

让我们看看 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];
    }
}

GetAllColumnsMapping 函数返回特定网格的所有列映射。 在这里，我使用 Microsoft Data Applications 块与数据库交互。 请根据您的 SQL Server 名称和其他设置更改 web.config 中的数据库连接字符串。

GridView 标记

<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>

在这里，我们将文本框放在模板字段中，并且文本框 ID 与计算表达式中的标记相同，例如 amount 字段的计算表达式是 {Qty}*{Rate}。 然后，我们应该有一个 ID 为 Amount 的控件，一个 ID 为 Qty 的控件，以及一个 ID 为 Rate 的控件。

关注点

此代码示例假设您正在创建没有下划线的控件 ID； 为此，您需要进行一个小代码更改。