• EasyUI笔记（六）数据表格

    前言
    用asp.net结合easyui做个一个数据网格的页面，包括对数据的增删改查，界面如下：
     一、UI界面
    先写UI界面，代码如下，记得引入相关js和css文件
    1. <body>  
    2.     <table id="tb1"></table>  
    3.     <div id="tb" style="padding:5px">  
    4.         <div style="margin-bottom:5px">  
    5.             <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-reload'" onclick="reload()">刷新</a>  
    6.             <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-add'" onclick="Add()">添加</a>
    7.             <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-edit'" onclick="edit()">修改</a>
    8.             <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-remove'" onclick="destroy()">删除</a>  
    9.         </div>  
    10.         <div>  
    11.             查询名称：<input type="text" id="Eq" name="Eq" style="width:150px"/>  
    12.             <select id="order" class="easyui-combobox" style="width:100px">  
    13.                 <option value="asc">升序</option>  
    14.                 <option value="desc">降序</option>  
    15.             </select>  
    16.             <a href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'" onclick="obj.search()">查询</a>  
    17.         </div>  
    19.     </div>  
    21.     <%-- 弹窗  Start--%>  
    22.      <div id="dlg" class="easyui-dialog" style="width:400px;height:320px;padding:10px 20px"  data-options="closed:true,buttons:'#dlg-buttons'">  
    23.      <div class="ftitle">设备信息</div>  
    24.      <form id="fm" method="post" novalidate="novalidate">  
    25.          <div class="fitem">  
    26.              <label>产品编号:</label>  
    27.              <input id="ProductID" name="ProductID" class="easyui-validatebox" data-options="required:false"/>  
    28.          </div>  
    29.          <div class="fitem">  
    30.              <label>产品名称:</label>  
    31.              <input id="Name" name="Name" class="easyui-validatebox" data-options="required:true"/>  
    32.          </div>  
    33.          <div class="fitem">  
    34.              <label>描述:</label>  
    35.              <input id="Description" name="Description" class="easyui-validatebox" data-options="required:true"/>  
    36.          </div>  
    37.          <div class="fitem">  
    38.              <label>种类:</label>  
    39.              <input id="Category" name="Category" class="easyui-validatebox" data-options="required:true"/>  
    40.          </div>  
    41.          <div class="fitem">  
    42.              <label>价格:</label>  
    43.              <input id="Price" name="Price" class="easyui-validatebox" data-options="required:true,validType:'intOrFloat'"/>  
    44.          </div>  
    45.      </form>  
    46.  </div>  
    47.  <div id="dlg-buttons">  
    48.      <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-ok'" onclick="save()" style="width:90px">Save</a>  
    49.      <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" onclick="javascript:$('#dlg').dialog('close')" style="width:90px">Cancel</a>  
    50.  </div>  
    51.  <%-- 弹窗  End --%> 
    52.     
    53. </body>
    二、JS加载
    关于datagrid的加载，我用js，要记住列名要和数据源对应
    1. $(function () {
    2.     $('#tb1').datagrid({
    3.         url: '../Handler/GetDataFormSql.ashx',
    4.         width: 1200,
    5.         title: '管理',
    6.         method: 'get',
    7.         toolbar: '#tb',
    8.         //rownumbers: true,
    9.         singleSelect:true,
    10.         columns: [[
    11.             { field: 'ProductID', title: '编号', width: 150 },
    12.             { field: 'Name', title: '名称', width: 150 },
    13.             { field: 'Description', title: '描述', width: 250 },
    14.             { field: 'Category', title: '种类', width: 150 },
    15.             { field: 'Price', title: '价格', width: 150 }
    16.         ]],
    17.         pagination: true,
    18.         pageSize: 20,
    19.         pageList: [20, 30 , 40]
    20.     });
    21. })
    三、一般处理程序
    GetDataFormSql.ashx 文件内容：
    1.  public void ProcessRequest(HttpContext context)
    2.         {
    3.             context.Response.ContentType = "text/plain";
    5.             int page = 1, rows = 10;
    7.             //EasyUI自带的两个参数rows与page ,表示当前页与行数  
    8.             if (context.Request.QueryString["rows"] != null)
    9.             {
    10.                 rows = int.Parse(context.Request.QueryString["rows"].ToString().Trim());
    11.             }
    12.             if (context.Request.QueryString["page"] != null)
    13.             {
    14.                 page = int.Parse(context.Request.QueryString["page"].ToString().Trim());
    15.             }
    17.             //查询分页 stratIndex endIndex  
    18.             int stratIndex, endIndex;
    19.             stratIndex = (page - 1) * rows + 1;
    20.             endIndex = page * rows;
    22.             //查询排序 key order  
    23.             string key = "", order = "desc";  
    24.             if (context.Request.QueryString["key"] != null)  
    25.             {  
    26.                 key = context.Request.QueryString["key"].ToString().Trim();  
    27.             }  
    28.             if (context.Request.QueryString["order"] != null)  
    29.             {  
    30.                 order = context.Request.QueryString["order"].ToString().Trim();  
    31.             }
    33.             //设置模糊查询
    34.             StringBuilder sqlWhere = new StringBuilder();
    35.             if (key != "") {
    36.                 sqlWhere.AppendFormat("upper(ProductID) like '%{0}%' or "+
    37.                                                             "upper(Name) like '%{0}%' or "+
    38.                                                             "upper(Description) like '%{0}%' or " +
    39.                                                             "upper(Category) like '%{0}%' or " +
    40.                                                             "upper(Price) like '%{0}%'",key);
    41.             }
    43.             //查询数据库  
    44.             DAL.SqlHelper sqlhelper = new DAL.SqlHelper();
    45.             //获取查询数据的行数  
    46.             int count = sqlhelper.EUGetRecordCount(sqlWhere.ToString());
    47.             //封装数据到dataset  
    48.             DataSet ds = sqlhelper.GetListByPagebykey(sqlWhere.ToString(), order, stratIndex, endIndex);
    49.             //将dataset转化为Json格式  
    50.             string strToJon = DAL.ToJson.DatasetJson(ds, count);
    51.             context.Response.Write(strToJon);
    52.             context.Response.End();  
    53.         }
    三、DAL数据访问层
    接下来就是数据访问层SqlHelper的代码
    1. public int EUGetRecordCount(string key)
    2.         {
    3.             StringBuilder strSql = new StringBuilder();
    4.             strSql.Append("select count(1) FROM Products ");
    5.             if (key.Trim() != "")
    6.             {
    7.                 strSql.Append("where " + key);
    8.             }
    9.             object obj = DBHelper.GetSingle(strSql.ToString());
    10.             if (obj == null)
    11.             {
    12.                 return 0;
    13.             }
    14.             else
    15.             {
    16.                 return Convert.ToInt32(obj);
    17.             }
    18.         }
    19. public DataSet GetListByPagebykey(string Key, string order, int startIndex, int endIndex)
    20.         {
    21.             StringBuilder strSql = new StringBuilder();
    22.             strSql.Append("SELECT * FROM ( ");
    23.             strSql.Append(" SELECT ROW_NUMBER() OVER (");
    24.             if (!string.IsNullOrEmpty(Key.Trim()))
    25.             {
    26.                 strSql.Append("order by T." + Key+" " + order);
    27.             }
    28.             else
    29.             {
    30.                 strSql.Append("order by T.ProductID asc");
    31.             }
    32.             strSql.Append(")AS Row, T.*  from  Products T ");
    33.             if (!string.IsNullOrEmpty(Key.Trim()))
    34.             {
    35.                 strSql.Append(" WHERE " + Key);
    36.             }
    37.             strSql.Append(" ) TT");
    38.             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
    39.             return DBHelper.Query(strSql.ToString());
    40.         }
    四、辅助方法
    还需要dataset转json的代码，其实这个可以从网上找到
    1. public class ToJson
    2.     {
    3.         /// <summary>
    4.         /// DataSet转换成Json格式  
    5.         /// </summary>  
    6.         /// <param name="ds">DataSet</param> 
    7.         /// <returns></returns>  
    8.         public static string DatasetJson(DataSet ds, int total = -1)
    9.         {
    10.             StringBuilder json = new StringBuilder();
    12.             foreach (DataTable dt in ds.Tables)
    13.             {
    14.                 json.Append("{"total":");
    15.                 if (total == -1)
    16.                 {
    17.                     json.Append(dt.Rows.Count);
    18.                 }
    19.                 else
    20.                 {
    21.                     json.Append(total);
    22.                 }
    23.                 json.Append(","rows":");
    24.                 json.Append(DataTableJson(dt));
    25.                 json.Append("]}");
    26.             } return json.ToString();
    27.         }
    28.         private static string DataTableJson(DataTable dt)
    29.         {
    30.             StringBuilder jsonBuilder = new StringBuilder();
    31.             jsonBuilder.Append("[");
    32.             for (int i = 0; i < dt.Rows.Count; i++)
    33.             {
    34.                 jsonBuilder.Append("{");
    35.                 for (int j = 0; j < dt.Columns.Count; j++)
    36.                 {
    37.                     jsonBuilder.Append(""");
    38.                     jsonBuilder.Append(dt.Columns[j].ColumnName);
    39.                     jsonBuilder.Append("":"");
    40.                     jsonBuilder.Append(dt.Rows[i][j].ToString());
    41.                     jsonBuilder.Append("",");
    42.                 }
    43.                 jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
    44.                 jsonBuilder.Append("},");
    45.             }
    46.             jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
    47.             return jsonBuilder.ToString();
    48.         }
    49.     }
    到这就大致实现了网格加载的功能，还剩四个增删改查的按钮功能，这里只列出增加数据的数据访问层代码，其中应用了SqlParameter参数化SQL，可以防止SQL注入
    1.  public int Add(Products model)
    2.         {
    3.             StringBuilder strSql = new StringBuilder();
    4.             strSql.Append("insert into Products(");
    5.             strSql.Append("Name,Description,Category,Price)");
    6.             strSql.Append(" values (");
    7.             strSql.Append("@Name,@Description,@Category,@Price)");
    8.             strSql.Append(";select @@IDENTITY");
    9.             SqlParameter[] parameters = {  
    10.                 new SqlParameter("@Name", SqlDbType.NVarChar,255),  
    11.                 new SqlParameter("@Description", SqlDbType.NVarChar,255),  
    12.                 new SqlParameter("@Category", SqlDbType.NVarChar,255),  
    13.                 new SqlParameter("@Price ", SqlDbType.Decimal,255),  
    14.         };
    15.             //parameters[0].Value = model.ProductID;
    16.             parameters[0].Value = model.Name;
    17.             parameters[1].Value = model.Description;
    18.             parameters[2].Value = model.Category;
    19.             parameters[3].Value = model.Price;
    21.             object obj = DBHelper.GetSingle(strSql.ToString(), parameters);
    22.             if (obj == null)
    23.             {
    24.                 return 0;
    25.             }
    26.             else
    27.             {
    28.                 return Convert.ToInt32(obj);
    29.             }
    30.         }
  • 相关阅读:
    计算公司下班时间（娱乐）
    设计模式-观察者模式
    vue 打包后白屏的问题
    HashSet实现原理
    LinkedList实现原理
    ArrayList的实现原理
    HashMap实现原理分析
    深入理解java动态代理机制
    Spring工作原理
    数据库SQL优化大总结之 百万级数据库优化方案
  • 原文地址：https://www.cnblogs.com/iwsx/p/7162012.html
Copyright © 2020-2023  润新知