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

///

/// 数据库连接字符串 /// sql语句 /// 参数

/// 每页条数 /// 页码 /// 排序字段 /// 排序放 /// 备注：查询效率偏低

public DataTable Pagination(string strSql, List 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; }

///

/// 数据库连接字符串 /// sql语句 /// 参数

/// 每页条数 /// 页码 /// 排序字段 /// 排序放

/// 备注：查询效率⾼，但是会建临时表

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 Params = new List();

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:

Html Footer:

service 端就是些sql调⽤分页⽅法的 忽略