鉴于最近有人询问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>其实很简单，在queryParams属性里绑定表单元素的值，最后刷新表格即可。
后台代码
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的自定义条件查询就实现了。
