鉴于最近有人询问Bootstrap-Table的自定义条件查询如何实现,今天特在此说明。首先展示一下效果:
默认查询
条件查询
测试数据
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title>bootstrap-table条件查询</title> <!-- bootstrap --> <link href="lib/bootstrap/css/bootstrap.min.css" rel="stylesheet" /> <script src="lib/bootstrap/js/jquery-3.4.1.min.js"></script> <script src="lib/bootstrap/js/bootstrap.min.js"></script> <!-- bootstrap-table --> <link href="lib/bootstrap-table/bootstrap-table.min.css" rel="stylesheet" /> <script src="lib/bootstrap-table/bootstrap-table.min.js"></script> <script src="lib/bootstrap-table/locale/bootstrap-table-zh-CN.min.js"></script> </head> <body> <div style="margin-left:200px;margin-top:100px;width:1000px;"> <div class="panel panel-primary"> <div class="panel-heading"> <h3 class="panel-title">条件查询</h3> </div> <div class="panel-body"> <div style="display:inline-block;"> <div style="float:left;padding:6px;"> <span>姓名:</span> </div> <div style="float:left;"> <input id="name" class="form-control" style="width:200px;" placeholder="请输入姓名" /> </div> <div style="float:left;padding:6px;"> <span>性别:</span> </div> <div style="float:left;"> <select id="gender" class="form-control" style="width:200px;"> <option value="">请选择</option> <option value="男">男</option> <option value="女">女</option> </select> </div> <div style="float:left;margin-left:20px;"> <button id="query" class="btn btn-primary">查询</button> </div> </div> </div> </div> <table id="table"></table> </div> <script> $(document).ready(function () { $('#table').bootstrapTable({ url: "ashx/GetRecordsHandler.ashx", // URL method: "post", // 请求类型 contentType: "application/x-www-form-urlencoded", // post请求必须要有,否则后台接受不到参数 sidePagination: "server", // 设置在服务端还是客户端分页 showRefresh: false, // 是否刷新按钮 sortStable: true, // 是否支持排序 cache: false, // 是否使用缓存 pagination: true, // 是否显示分页 search: false, // 是否有搜索框 clickToSelect: true, // 是否点击选中行 pageNumber: 1, // 首页页码,默认为1 pageSize: 5, // 页面数据条数 pageList: [5, 10, 20, 30], queryParamsType: "", queryParams: function (params) { return { pageSize: params.pageSize, // 每页记录条数 pageNumber: params.pageNumber, // 当前页索引 name: $('#name').val(), // 姓名 gender: $('#gender').val() // 性别 }; }, columns: [{ field: "select", title: "全选", align: "center", halign: "center", checkbox: true, }, { field: 'Id', title: '编号', align: "center", halign: "center", sortable: true }, { field: 'Name', title: '姓名', align: "center", halign: "center" }, { field: 'Gender', title: '性别', align: "center", halign: "center" }, { field: 'Age', title: '年龄', align: "center", halign: "center" }] }) // 查询按钮 $('#query').click(function () { $('#table').bootstrapTable('refresh', { pageNumber: 1 }); }); }); </script> </body> </html>
后端代码
using Newtonsoft.Json; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Text; using System.Web; namespace WebApplication1.ashx { /// <summary> /// GetRecordsHandler 的摘要说明 /// </summary> public class GetRecordsHandler : IHttpHandler { /// <summary> /// 连接字符串 /// </summary> private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; // 获取分页参数 int pageSize = int.Parse(context.Request["pageSize"].ToString()); int pageNumber = int.Parse(context.Request["pageNumber"].ToString()); // 获取自定义参数 string name = context.Request["name"].ToString(); string gender = context.Request["gender"].ToString(); // 记录总数 int total = GetRecordsCount(name, gender); DataTable dataTable = GetRecords(pageSize, pageNumber, name, gender); // 格式化数据 var data = new { total = total, rows = dataTable }; context.Response.Write(JsonConvert.SerializeObject(data)); } public bool IsReusable { get { return false; } } // 获取记录总数 private int GetRecordsCount(string name, string gender) { // 查询语句 StringBuilder sql = new StringBuilder("select count(*) from [TPerson] "); if (!string.IsNullOrWhiteSpace(name) && !string.IsNullOrWhiteSpace(gender)) { sql.Append("where Name=@Name and Gender=@Gender"); } else if (!string.IsNullOrWhiteSpace(name) && string.IsNullOrWhiteSpace(gender)) { sql.Append("where Name=@Name"); } else if (string.IsNullOrWhiteSpace(name) && !string.IsNullOrWhiteSpace(gender)) { sql.Append("where Gender=@Gender"); } else { sql.Append(""); } // 查询参数 List<SqlParameter> parameters = new List<SqlParameter>(); if (!string.IsNullOrWhiteSpace(name)) { parameters.Add(new SqlParameter("@Name", name)); } if (!string.IsNullOrWhiteSpace(gender)) { parameters.Add(new SqlParameter("@Gender", gender)); } // 查询总数 using (SqlConnection connection = new SqlConnection(ConnectionString)) { SqlCommand command = new SqlCommand(sql.ToString(), connection); if (parameters.Count > 0) { command.Parameters.AddRange(parameters.ToArray()); } try { connection.Open(); object obj = command.ExecuteScalar(); command.Parameters.Clear(); return Convert.ToInt32(obj); } catch { command.Parameters.Clear(); return -1; } } } // 分页查询数据 private DataTable GetRecords(int pageSize, int pageNumber, string name, string gender) { // 查询语句 StringBuilder sql = new StringBuilder("select * from(select row_number() over(order by Id) as RowId, * from TPerson "); if (!string.IsNullOrWhiteSpace(name) && !string.IsNullOrWhiteSpace(gender)) { sql.Append("where Name=@Name and Gender=@Gender) "); } else if (!string.IsNullOrWhiteSpace(name) && string.IsNullOrWhiteSpace(gender)) { sql.Append("where Name=@Name) "); } else if (string.IsNullOrWhiteSpace(name) && !string.IsNullOrWhiteSpace(gender)) { sql.Append("where Gender=@Gender) "); } else { sql.Append(") "); } sql.Append("as b where b.RowId between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize order by Id"); // 查询参数 List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@pageSize", pageSize)); parameters.Add(new SqlParameter("@pageNumber", pageNumber)); if (!string.IsNullOrWhiteSpace(name)) { parameters.Add(new SqlParameter("@Name", name)); } if (!string.IsNullOrWhiteSpace(gender)) { parameters.Add(new SqlParameter("@Gender", gender)); } // 查询数据 DataTable dataTable = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), ConnectionString)) { adapter.SelectCommand.Parameters.AddRange(parameters.ToArray()); adapter.Fill(dataTable); adapter.SelectCommand.Parameters.Clear(); } return dataTable; } } }
到此为止,Bootstrap-Table的自定义条件查询就实现了。