c# 怼给前端layui 数据库命令查询 分页方法

 public class LayUiTableInfo
    {
        public int code { get; set; }
        public string msg { get; set; }
        public int count { get; set; }
        public DataTable data { get; set; }
    }

第二个方法是网上找的, 修改了一下,可以直接碰到复杂一点的联查 ,还有前端的搜索,总之就是拼接好sql语句 方法2 效率高于是就用了方法2

 /// <summary>
        /// 分页查询函数
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="Params">参数</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="order">排序字段</param>
        /// <param name="sort">排序放</param>
        /// <returns>备注：查询效率偏低</returns>
        public DataTable Pagination(string strSql, List<SqlParameter> Params, int pageSize, int pageIndex, string order, string sort)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            cmd.Connection = conn;
            string sqlFinal = string.Format(@"WITH tmp2 as( SELECT ROW_NUMBER() OVER(ORDER BY {1} {2})
                   AS rownum,* from ({0}) tmp1)
                   select  (select count(*) from tmp2)total,* from tmp2 where tmp2.rownum BETWEEN
                   @startRow and @endRow", strSql, order, sort);

            Params.Add(new SqlParameter("@startRow", (pageIndex - 1) * pageSize + 1) { SqlDbType = SqlDbType.Int });
            Params.Add(new SqlParameter("@endRow", pageIndex * pageSize) { SqlDbType = SqlDbType.Int });
            cmd.CommandText = sqlFinal;
            for (int i = 0; i < Params.Count; i++)
            {
                cmd.Parameters.Add(Params[i]);
            }
            ada.Fill(dt);
            return dt;
        }
      

        /// <summary>
        /// 分页查询函数
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="Params">参数</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="order">排序字段</param>
        /// <param name="sort">排序放</param>
        /// <returns>备注：查询效率高，但是会建临时表</returns>
        public static DataTable Pagination2(string strSql, int pageSize, int pageIndex, string order, string sort)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            cmd.Connection = conn;
            string sqlFinal = string.Format(@"SELECT ROW_NUMBER() OVER(ORDER BY {1} {2})
                   AS rownum,* into #tmp2 from ({0}) tmp1;
                   select  (select count(*) from #tmp2)total,* from #tmp2 where #tmp2.rownum BETWEEN
                   @startRow and @endRow", strSql, order, sort);
            List<SqlParameter> Params = new List<SqlParameter>();
            Params.Add(new SqlParameter("@startRow", (pageIndex - 1) * pageSize + 1) { SqlDbType = SqlDbType.Int });
            Params.Add(new SqlParameter("@endRow", pageIndex * pageSize) { SqlDbType = SqlDbType.Int });
            cmd.CommandText = sqlFinal;
            for (int i = 0; i < Params.Count; i++)
            {
                cmd.Parameters.Add(Params[i]);
            }
            ada.Fill(dt);
            return dt;
        }

三,引用config

 

  static string connStr = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ToString();

四  控制器端 加搜索,功能

   [HttpGet]
        public IHttpActionResult TerminalList()
        {
            object rt = null;
            //if (AuthenticationHelper.IsAuthenticated())
            //{
            try
            {
                int page = QueryHelper.GetInteger("page", 1);
                //每页条数
                int limit = QueryHelper.GetInteger("limit", 10);
                //排序字段
                string field = QueryHelper.GetString("field", string.Empty);
                //排序方式
                string order = QueryHelper.GetString("order", string.Empty);
                //返回列名
                string columns = "*";
                //表名
                string tablename = "AutoWscl_Terminal";
                string orderby = "TerminalID";
                string where = " 1=1  ";
                string terminalName = QueryHelper.GetString("terminalName", string.Empty);
                string portType = QueryHelper.GetString("portType", string.Empty);
                string messageFlag = QueryHelper.GetString("messageFlag", string.Empty);
                int isMaster = QueryHelper.GetInteger("isMaster", -1);
                //设备名
                if (!string.IsNullOrEmpty(terminalName))
                {
                    where += " and terminalName like N'%" + SqlHelper.EscapeLikeText(terminalName) + "%'";
                }
                //设备类型
                if (!string.IsNullOrEmpty(portType))
                {
                    where += " and portType='" + SqlHelper.EscapeQuotes(portType) + "'";
                }
                //设备标识
                if (!string.IsNullOrEmpty(messageFlag))
                {
                    where += " and  MessageFlag like N'%" + SqlHelper.EscapeLikeText(SqlHelper.EscapeQuotes(messageFlag)) + "%'";
                }
                //是否是主设备
                if (isMaster != -1)
                {
                    where += " and isMaster=" +isMaster;
                }

                if (!string.IsNullOrEmpty(order) && !string.IsNullOrEmpty(field))
                {
                    orderby = field + " " + order;
                }
                rt =new TerminalService().GetTableJson(tablename, columns, where, orderby, page, limit);
            }
            catch (Exception ex)
            {
                rt = ex.Message;
            }
            //}
            //else 
            //{
            //    rt = "请重新登陆！";
            //}
            return Json(rt);
        }

五 前端 layui

Html Body: <div class="layui-form">
    <!--搜索条-->
    <div class="layui-form-item">
        <div class="layui-inline">
            <label class="layui-form-label">设备管理</label>
            <div class="layui-input-inline">
                <input type="text" autocomplete="off" id="terminalName" required  lay-verify="required" placeholder="请设备名称" autocomplete="off" class="layui-input">
            </div>
            <div class="layui-input-inline">
                <select id="isMaster" width="20px">
                    <option value='' disabled selected style='display:none;'>是否是主设备</option>
                    <option value='1'>是</option>
                    <option value='0'>否</option>
                </select>
            </div>
        </div>
        <!--   <div class="layui-inline">
               <label class="layui-form-label">活动状态</label>
               <div class="layui-input-inline">
                   <select id="Status" lay-verify="required">
                       <option value="">全部</option>
                       <option value="0">未启用</option>
                       <option value="1">启用</option>
                   </select>
               </div>
           </div>-->
        <div class="layui-inline">
            <button class="layui-btn" data-type="chaxun" id="chaxun">查询</button>
        </div>
    </div><!--搜索条结束-->
    <div>

        <button class="layui-btn" data-type="addNew" id="addNew">添加新地址</button>
    </div>
    <table class="layui-hide" id="LAY_table_user" lay-filter="user"></table>
</div>

Html Footer: <script src="xxxxy/jquery-2.2.2.min.js"></script>
<script type="text/html" id="barDemo">
    <a class="layui-btn layui-btn-xs" lay-event="edit">编辑</a>
    <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>
</script>
<script>
    layui.use('table', function(){
        var table = layui.table;
        var $ = layui.$;//等同于jquery

        //方法级渲染
        table.render({
            elem: '#LAY_table_user'//table元素的ID
            ,id: 'testReload'//容器的ID
            ,url: '/xxxxrminalList'
            ,height:$(window).height()/2+80
            , cols: [[
              xxx
                { field: "terminalCMD", title: "设备命令", width: 120 },
                { field: "messageFlag", title: "设备标识", width: 200 },
                { field: "parentlocation", title: "位置", width: 200 },
                { field: "LocationName", title: "采集点", width: 200 },
                { title: "操作", width: 180, align: "center", fixed: "right", templet: "#barDemo"}
            ]]
            ,page: true
            ,limits: [10,20,30]
            ,limit: 10
            ,where: {
                terminalName:$('#terminalName').val(),
                isMaster:$('#isMaster').val()
            }
        });

        //监听工具条
        table.on('tool(user)', function(obj){
            var data = obj.data;
            if(obj.event === 'edit'){
                window.location.href="/Mxxxxl/edit.aspx?TerminalID="+data.TerminalID;
            }
            else if(obj.event === 'del'){
                layer.confirm('确定要删除该地址?', function(index){
                    //do something
                    deleteActive(data.TerminalID,table);
                    layer.close(index);
                });
            }
         
        });
        //监听工具条结束

        //监听排序
        table.on('sort(user)', function(obj){ //注：tool是工具条事件名，test是table原始容器的属性 lay-filter="对应的值"
            table.reload('testReload', {//刷新列表
                initSort: obj //记录初始排序，如果不设的话，将无法标记表头的排序状态。 layui 2.1.1 新增参数
                ,where: { //请求参数
                    field: obj.field //排序字段
                    ,order: obj.type //排序方式
                }
            });
        });
        //监听排序结束

        //查询
        $("#chaxun").click(function(){
            table.reload('testReload', {//刷新列表
                where: {
                    terminalName:$('#terminalName').val(),
                    isMaster:$('#isMaster').val()
                },page: {
                    curr: 1 //重新从第 1 页开始
                }
            });
        })
        $("#addNew").click(function(){
            window.location.href="/Managexx.aspx";
        })

    });


    function deleteActive(TerminalID,table){
        var indexload = layer.load(1, {
            shade: [0.3,'#000'],
            success: function(layero, indexload){
                $.ajax({
                    type: 'Get',
                    async: false,
                    dataType: 'json',
                    url: '/xxal/DelTerminal',
                    data: { TerminalID: TerminalID},//$("form").serialize(),
                    /* processData: false,  // 告诉jQuery不要去处理发送的数据
                     contentType: false,  // 告诉jQuery不要去设置Content-Type请求头*/
                    success: function (data) {
                        if(data.res == "ok"){
                            layer.alert("删除成功!", function(index){
                                layer.close(index);
                                layer.close(indexload);
                                table.reload('testReload', {
                                    where: {
                                        terminalName:$('#terminalName').val()
                                        ,Status:$('#Status').val()
                                    }
                                });
                            });
                        }
                        else{
                            layer.alert(data.res, function(index){
                                layer.close(index);
                                layer.close(indexload);
                                table.reload('testReload', {
                                    where: {
                                        terminalName:$('#terminalName').val()
                                        , isMaster:$('#isMaster').val()
                                    }
                                });
                            });
                        }
                    },
                    error:function(e){
                        layer.alert("删除失败，请重试!", function(index){
                            //do something
                            layer.close(index);
                            layer.close(indexload);
                            table.reload('testReload', {
                                where: {
                                    terminalName:$('#terminalName').val()
                                    , isMaster:$('#isMaster').val()
                                }
                            });
                        });
                    }
                });
            }
        });

    }

  

</script>

service 端就是些sql调用分页方法的 忽略