• 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>
    18. </div>
    19. <%-- 弹窗 Start--%>
    20. <div id="dlg" class="easyui-dialog" style="width:400px;height:320px;padding:10px 20px" data-options="closed:true,buttons:'#dlg-buttons'">
    21. <div class="ftitle">设备信息</div>
    22. <form id="fm" method="post" novalidate="novalidate">
    23. <div class="fitem">
    24. <label>产品编号:</label>
    25. <input id="ProductID" name="ProductID" class="easyui-validatebox" data-options="required:false"/>
    26. </div>
    27. <div class="fitem">
    28. <label>产品名称:</label>
    29. <input id="Name" name="Name" class="easyui-validatebox" data-options="required:true"/>
    30. </div>
    31. <div class="fitem">
    32. <label>描述:</label>
    33. <input id="Description" name="Description" class="easyui-validatebox" data-options="required:true"/>
    34. </div>
    35. <div class="fitem">
    36. <label>种类:</label>
    37. <input id="Category" name="Category" class="easyui-validatebox" data-options="required:true"/>
    38. </div>
    39. <div class="fitem">
    40. <label>价格:</label>
    41. <input id="Price" name="Price" class="easyui-validatebox" data-options="required:true,validType:'intOrFloat'"/>
    42. </div>
    43. </form>
    44. </div>
    45. <div id="dlg-buttons">
    46. <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-ok'" onclick="save()" style="width:90px">Save</a>
    47. <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" onclick="javascript:$('#dlg').dialog('close')" style="width:90px">Cancel</a>
    48. </div>
    49. <%-- 弹窗 End --%>
    50. </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";
    4. int page = 1, rows = 10;
    5. //EasyUI自带的两个参数rows与page ,表示当前页与行数
    6. if (context.Request.QueryString["rows"] != null)
    7. {
    8. rows = int.Parse(context.Request.QueryString["rows"].ToString().Trim());
    9. }
    10. if (context.Request.QueryString["page"] != null)
    11. {
    12. page = int.Parse(context.Request.QueryString["page"].ToString().Trim());
    13. }
    14. //查询分页 stratIndex endIndex
    15. int stratIndex, endIndex;
    16. stratIndex = (page - 1) * rows + 1;
    17. endIndex = page * rows;
    18. //查询排序 key order
    19. string key = "", order = "desc";
    20. if (context.Request.QueryString["key"] != null)
    21. {
    22. key = context.Request.QueryString["key"].ToString().Trim();
    23. }
    24. if (context.Request.QueryString["order"] != null)
    25. {
    26. order = context.Request.QueryString["order"].ToString().Trim();
    27. }
    28. //设置模糊查询
    29. StringBuilder sqlWhere = new StringBuilder();
    30. if (key != "") {
    31. sqlWhere.AppendFormat("upper(ProductID) like '%{0}%' or "+
    32. "upper(Name) like '%{0}%' or "+
    33. "upper(Description) like '%{0}%' or " +
    34. "upper(Category) like '%{0}%' or " +
    35. "upper(Price) like '%{0}%'",key);
    36. }
    37. //查询数据库
    38. DAL.SqlHelper sqlhelper = new DAL.SqlHelper();
    39. //获取查询数据的行数
    40. int count = sqlhelper.EUGetRecordCount(sqlWhere.ToString());
    41. //封装数据到dataset
    42. DataSet ds = sqlhelper.GetListByPagebykey(sqlWhere.ToString(), order, stratIndex, endIndex);
    43. //将dataset转化为Json格式
    44. string strToJon = DAL.ToJson.DatasetJson(ds, count);
    45. context.Response.Write(strToJon);
    46. context.Response.End();
    47. }
    三、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();
    11. foreach (DataTable dt in ds.Tables)
    12. {
    13. json.Append("{"total":");
    14. if (total == -1)
    15. {
    16. json.Append(dt.Rows.Count);
    17. }
    18. else
    19. {
    20. json.Append(total);
    21. }
    22. json.Append(","rows":");
    23. json.Append(DataTableJson(dt));
    24. json.Append("]}");
    25. } return json.ToString();
    26. }
    27. private static string DataTableJson(DataTable dt)
    28. {
    29. StringBuilder jsonBuilder = new StringBuilder();
    30. jsonBuilder.Append("[");
    31. for (int i = 0; i < dt.Rows.Count; i++)
    32. {
    33. jsonBuilder.Append("{");
    34. for (int j = 0; j < dt.Columns.Count; j++)
    35. {
    36. jsonBuilder.Append(""");
    37. jsonBuilder.Append(dt.Columns[j].ColumnName);
    38. jsonBuilder.Append("":"");
    39. jsonBuilder.Append(dt.Rows[i][j].ToString());
    40. jsonBuilder.Append("",");
    41. }
    42. jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
    43. jsonBuilder.Append("},");
    44. }
    45. jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
    46. return jsonBuilder.ToString();
    47. }
    48. }
    到这就大致实现了网格加载的功能,还剩四个增删改查的按钮功能,这里只列出增加数据的数据访问层代码,其中应用了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;
    20. object obj = DBHelper.GetSingle(strSql.ToString(), parameters);
    21. if (obj == null)
    22. {
    23. return 0;
    24. }
    25. else
    26. {
    27. return Convert.ToInt32(obj);
    28. }
    29. }

  • 相关阅读:
    关于jQuery方法解析(一)append-参数设置问题
    CSS动画
    关于html CSS 绝对相对布局问题
    Chrome自带的开发者工具使用方法教程
    web常见漏洞及防范方法
    前端性能优化 Web前端应该从哪些方面来优化网站?
    属性的特征描述可以分为两类:数据属性和访问器属性
    iScroll.js的用法
    百度前端学院在线学习参考资料
    GET和POST的区别,何时使用POST?
  • 原文地址:https://www.cnblogs.com/iwsx/p/7162012.html
Copyright © 2020-2023  润新知