229 lines
7.9 KiB
C#
229 lines
7.9 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Reflection;
|
|||
|
|
using System.Linq.Expressions;
|
|||
|
|
|
|||
|
|
namespace Mtxfw.Utility
|
|||
|
|
{
|
|||
|
|
public static class PagingHelper
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取分页SQL语句，默认row_number为关健字，所有表不允许使用该字段名
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="_recordCount">记录总数</param>
|
|||
|
|
/// <param name="_pageSize">每页记录数</param>
|
|||
|
|
/// <param name="_pageIndex">当前页数</param>
|
|||
|
|
/// <param name="_safeSql">SQL查询语句</param>
|
|||
|
|
/// <param name="_orderField">排序字段，多个则用“,”隔开</param>
|
|||
|
|
/// <returns>分页SQL语句</returns>
|
|||
|
|
public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _safeSql, string _orderField)
|
|||
|
|
{
|
|||
|
|
//计算总页数
|
|||
|
|
_pageSize = _pageSize == 0 ? _recordCount : _pageSize;
|
|||
|
|
int pageCount = (_recordCount + _pageSize - 1) / _pageSize;
|
|||
|
|
|
|||
|
|
//检查当前页数
|
|||
|
|
if (_pageIndex < 1)
|
|||
|
|
{
|
|||
|
|
_pageIndex = 1;
|
|||
|
|
}
|
|||
|
|
else if (_pageIndex > pageCount)
|
|||
|
|
{
|
|||
|
|
_pageIndex = pageCount;
|
|||
|
|
}
|
|||
|
|
//拼接SQL字符串，加上ROW_NUMBER函数进行分页
|
|||
|
|
StringBuilder newSafeSql = new StringBuilder();
|
|||
|
|
newSafeSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,", _orderField);
|
|||
|
|
newSafeSql.Append(_safeSql.Substring(_safeSql.ToUpper().IndexOf("SELECT") + 6));
|
|||
|
|
|
|||
|
|
//拼接成最终的SQL语句
|
|||
|
|
StringBuilder sbSql = new StringBuilder();
|
|||
|
|
sbSql.Append("SELECT * FROM (");
|
|||
|
|
sbSql.Append(newSafeSql.ToString());
|
|||
|
|
sbSql.Append(") AS T");
|
|||
|
|
sbSql.AppendFormat(" WHERE row_number between {0} and {1}", ((_pageIndex - 1) * _pageSize) + 1, _pageIndex * _pageSize);
|
|||
|
|
|
|||
|
|
return sbSql.ToString();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取记录总数SQL语句
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="_safeSql">SQL查询语句</param>
|
|||
|
|
/// <returns>记录总数SQL语句</returns>
|
|||
|
|
public static string CreateCountingSql(string _safeSql)
|
|||
|
|
{
|
|||
|
|
return string.Format(" SELECT COUNT(1) AS RecordCount FROM ({0}) AS T ", _safeSql);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static class PageHelper
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 按页码分页
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="objects">要分页的数据集合</param>
|
|||
|
|
/// <param name="pageIndex">页码</param>
|
|||
|
|
/// <param name="pageSize">页大小</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static object PageByIndex(this IEnumerable<object> objects, int pageIndex, int pageSize)
|
|||
|
|
{
|
|||
|
|
return objects.Skip(pageIndex * pageSize).Take(pageSize);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取当前项在列表中的位置
|
|||
|
|
/// </summary>
|
|||
|
|
/// <typeparam name="T"></typeparam>
|
|||
|
|
/// <param name="list"></param>
|
|||
|
|
/// <param name="item"></param>
|
|||
|
|
/// <param name="property"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int GetPageIndex<T>(object[] list, object item, string property = "ID")
|
|||
|
|
{
|
|||
|
|
PropertyInfo ptyIdInfo = typeof(T).GetProperty(property);
|
|||
|
|
var index = 0;
|
|||
|
|
for (int i = 0; i < list.Length; i++)
|
|||
|
|
{
|
|||
|
|
var objid = ptyIdInfo.GetValue(list[i], null);
|
|||
|
|
if (Equals(objid, item))
|
|||
|
|
{
|
|||
|
|
index = i;
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return index;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 按lastid分页
|
|||
|
|
/// </summary>
|
|||
|
|
/// <typeparam name="T"></typeparam>
|
|||
|
|
/// <param name="objects">要分页的数据集合</param>
|
|||
|
|
/// <param name="item">分页项值</param>
|
|||
|
|
/// <param name="property">分页项</param>
|
|||
|
|
/// <param name="iswilful">分页项是最后一个还是任意中间一个</param>
|
|||
|
|
/// <param name="pageSize">页大小</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static object PageByLastId<T>(this IEnumerable<object> objects, object item, bool iswilful = false, int pageSize = 0, string property = "ID")
|
|||
|
|
{
|
|||
|
|
if (pageSize == 0) pageSize = 10;
|
|||
|
|
if (item == null || item.ToString() == "")
|
|||
|
|
{
|
|||
|
|
return objects.PageByIndex(0, pageSize);
|
|||
|
|
}
|
|||
|
|
var list = objects as object[] ?? objects.ToArray();
|
|||
|
|
var index = GetPageIndex<T>(list, item, property);
|
|||
|
|
if (iswilful)
|
|||
|
|
{
|
|||
|
|
return list.Skip(index + 1).Take(pageSize); //取任意ID后面pageSize条
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
if ((index + 1) % pageSize == 0)
|
|||
|
|
{
|
|||
|
|
var pageIndex = index / pageSize + 1;
|
|||
|
|
return PageByIndex(list, pageIndex, pageSize);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return new List<T>();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class QueryParameters
|
|||
|
|
{
|
|||
|
|
public string order { get; set; }
|
|||
|
|
public int page { get; set; }
|
|||
|
|
public int rows { get; set; }
|
|||
|
|
public string sort { get; set; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class DataGrid<T>
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 默认构造函数
|
|||
|
|
/// </summary>
|
|||
|
|
public DataGrid() { }
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 构造函数
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="rows">表格数据</param>
|
|||
|
|
public DataGrid(List<T> rows)
|
|||
|
|
{
|
|||
|
|
this.rows = rows;
|
|||
|
|
}
|
|||
|
|
private List<T> _rows = new List<T>();
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 表格数据
|
|||
|
|
/// </summary>
|
|||
|
|
public List<T> rows
|
|||
|
|
{
|
|||
|
|
get { return _rows; }
|
|||
|
|
set
|
|||
|
|
{
|
|||
|
|
if (value != null)
|
|||
|
|
{
|
|||
|
|
_rows = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 总记录数
|
|||
|
|
/// </summary>
|
|||
|
|
public int total { get; set; }
|
|||
|
|
|
|||
|
|
public static DataGrid<T> QueryWithParameters(IEnumerable<T> queryable, QueryParameters queryParameters)
|
|||
|
|
{
|
|||
|
|
if (queryable == null)
|
|||
|
|
{
|
|||
|
|
return new DataGrid<T>();
|
|||
|
|
}
|
|||
|
|
queryParameters = queryParameters ?? new QueryParameters();
|
|||
|
|
IQueryable<T> source = Queryable.AsQueryable<T>(queryable);
|
|||
|
|
|
|||
|
|
var page = queryParameters.page;
|
|||
|
|
var rows = queryParameters.rows;
|
|||
|
|
var sort = queryParameters.sort;
|
|||
|
|
var order = queryParameters.order;
|
|||
|
|
|
|||
|
|
int num = source.Count();
|
|||
|
|
|
|||
|
|
int count = (page - 1) * rows;
|
|||
|
|
|
|||
|
|
IQueryable<T> s = source;
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrWhiteSpace(sort))
|
|||
|
|
{
|
|||
|
|
var sortExpression = Expression.Parameter(source.ElementType);
|
|||
|
|
var selector = Expression.Lambda(Expression.PropertyOrField(sortExpression, sort), sortExpression);
|
|||
|
|
if (order.ToLower() == "asc")
|
|||
|
|
{
|
|||
|
|
s = (IQueryable<T>)source.Provider.CreateQuery(Expression.Call(typeof(Queryable), "OrderBy", new Type[] { source.ElementType, selector.Body.Type }, source.Expression, selector));
|
|||
|
|
}
|
|||
|
|
else if (order.ToLower() == "desc")
|
|||
|
|
{
|
|||
|
|
s = (IQueryable<T>)source.Provider.CreateQuery(Expression.Call(typeof(Queryable), "OrderByDescending", new Type[] { source.ElementType, selector.Body.Type }, source.Expression, selector));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//方法1
|
|||
|
|
s = count == 0 ? s.Take(rows) : s.Skip(count).Take(rows);
|
|||
|
|
//方法2
|
|||
|
|
s = count == 0 ? Queryable.Take<T>(s, rows) : Queryable.Take<T>(Queryable.Skip<T>(s, count), rows);
|
|||
|
|
|
|||
|
|
return new DataGrid<T>()
|
|||
|
|
{
|
|||
|
|
total = num,
|
|||
|
|
rows = s.ToList()
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|