鉴于最近有人询问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的自定义条件查询就实现了。