c# 怼给前端layui 数据库命令查询 分页方法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了c# 怼给前端layui 数据库命令查询 分页方法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8509字，纯文字阅读大概需要13分钟。
内容图文
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调用分页方法的 忽略
内容总结
以上是互联网集市为您收集整理的c# 怼给前端layui 数据库命令查询 分页方法全部内容，希望文章能够帮你解决c# 怼给前端layui 数据库命令查询 分页方法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】