Jquery EasyUI datagrid数据库分页-CSDN博客

本文介绍了一个具体的前端到后端再到数据库的请求处理流程。前端通过EasyUI插件Datagrid展示从服务器获取的项目信息，并实现了双击引用功能；后端接收并解析前端传递的参数，构造SQL查询语句返回给前端；数据库则负责执行具体的数据查询。

1.前端请求：

    function loadRefProj(searchInput) {
        $('#projreftable').datagrid({
            url: 'DataServer/SilentContractProjectBatchHandler.ashx',
            columns: [[
                { field: "Wbs号", title: "项目号", align: "center", width: 200 },
                { field: "Wbs名称", title: "项目名称", align: "center", width: 450},
                {
                    title: '', field: "Ref", align: "center", width: 100, formatter: function (value, rec, index) {
                        return '<button  onclick="sureRefProj(\'' + index + '\');$(this).parent().click();return false;">引用</button>';
                    }
                }
            ]],
            pageSize: 10,
            pagination: true,
            pageList: [10, 20, 30, 40, 50],
            queryParams: { "action": "queryRefProj", "searchInput": searchInput },
            onDblClickRow: function (index, row) {
                sureRefProj(index);
            }
        });
        getPage($('#projreftable'));
    }

function getPage($table) {
    var pager = $table.datagrid("getPager");
    pager.pagination({
        beforePageText: '第',
        afterPageText: '页 共{pages}页',
        displayMsg: '共{total}条', //当前显示{from}-{to}条
    });
}

2.后台接收请求：

　　　　　　　　case "queryRefProj":
                    {
                        string searchInput = context.Request.Params["searchInput"] ?? "";
                        int page = context.Request.Form["page"] != "" ? Convert.ToInt32(context.Request.Form["page"]) : 0;
                        int size = context.Request.Form["rows"] != "" ? Convert.ToInt32(context.Request.Form["rows"]) : 0;
                        context.Response.Write(QueryRefProj(searchInput, page, size));
                    }

string QueryRefProj(string searchInput, int page, int size)
{
    DataTable refProjDt = dal.GetRefProj(page,size,searchInput);
    if (refProjDt != null && refProjDt.Rows.Count > 0)
    {
        int count = dal.GetRefProjCount(searchInput);
        StringBuilder sbJson = new StringBuilder();
        sbJson.Append("{\"rows\":");
        sbJson.Append(JsonConvert.SerializeObject(refProjDt, Newtonsoft.Json.Formatting.Indented));
        sbJson.Append(",\"total\":");
        sbJson.Append(count + "}");
        return sbJson.ToString();
    }
    return "[]";
}

3.数据库请求语句：

SELECT TOP @size Wbs号,Wbs名称 FROM (SELECT TOP @size*@page Wbs号,Wbs名称 FROM Wbs结构表 @condition ORDER BY Wbs号) DERIVEDTBL ORDER BY Wbs号 DESC

        public DataTable GetRefProj(int page, int size, string queryInput = null)
        {
            string strSql = "SELECT TOP {0} Wbs号,Wbs名称 FROM (SELECT TOP {1} Wbs号,Wbs名称 FROM Wbs结构表 {2} ORDER BY Wbs号) DERIVEDTBL ORDER BY Wbs号 DESC ";
            if (!string.IsNullOrWhiteSpace(queryInput))
            {
                queryInput = string.Format(" where Wbs号 like '%{0}%' or Wbs名称 like '%{0}%'", queryInput);
            }
            strSql = string.Format(strSql, size, page * size, queryInput ?? "");
            return DaoService.GetDBDataSet(strSql, null).Tables[0];
        }

        public int GetRefProjCount(string queryInput = null)
        {
            string strSql = SqlUtil.GetSelSql("COUNT(*)", "Wbs结构表");
            queryInput = queryInput ?? "";
            strSql = string.Format(strSql, string.Format(" where Wbs号 like '%{0}%' or Wbs名称 like '%{0}%'", queryInput));
            return Convert.ToInt32(DaoService.ExecuteScalar(CommandType.Text, strSql, null));
        }