public DataTable PagingTable(IDbHandle.IDbop dbop, string sql, string sqlOrder, int pagingsize, int pagingnumber, out int rowcount)
{
rowcount = dbop.GetSingleInt(string.Format("SELECT COUNT(*) FROM({0}) tmp_tb_pagingt", sql));
if (rowcount == 0)
{
return(new DataTable());
}
string pagingSql;
if (rowcount <= pagingsize)
{
// 不接受 PagingNumber 参数 直接查询
pagingSql = string.Format("{0} {1}", sql, sqlOrder);
}
else
{
// sql 异常处理
if (!string.IsNullOrEmpty(sqlOrder) && sqlOrder.IndexOf('.') >= 0)
{
// 剔除字段前缀
sqlOrder = Regex.Replace(sqlOrder, "\\s+\\w+[.]", " ");
sqlOrder = Regex.Replace(sqlOrder, "[,]\\w+[.]", ",");
}
pagingSql =
@"WITH tmp_tb_paging AS
(SELECT *, ROW_NUMBER() OVER ({0})as t_rownumber FROM ({1}) t)
SELECT * FROM tmp_tb_paging WHERE t_rownumber between {2} and {3}";
pagingSql = string.Format(pagingSql,
sqlOrder,
sql,
(pagingnumber - 1) * pagingsize + 1,
pagingnumber * pagingsize);
}
return(dbop.GetDataTable(pagingSql));
}
/// <summary>
/// 获取分页表格 和 分页导航链接
/// </summary>
/// <param name="dbop"></param>
/// <param name="sql">sql语句 不含排序</param>
/// <param name="sqlOrder">排序sql</param>
protected DataTable PagingTableAndObj(IDbHandle.IDbop dbop, string sql, string sqlOrder)
{
DataTable r = PagingTable(dbop, sql, sqlOrder);
PagingObj = new PagingHelperObj(RowCount, PagingSize, PagingParamsStr);
return(r);
}
protected void Application_Start(object sender, EventArgs e)
{
// 初始化数据库操作
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
Dbop = new DbHandle.OperateSqlServer(connstring);
ModelDbopBase.Dbop = Dbop;
}
/// <summary>
/// 简单分页 获取sql行数和页码数据集
/// </summary>
/// <param name="dbop"></param>
/// <param name="sql"></param>
/// <param name="sqlOrder"></param>
/// <param name="pagingsize"></param>
/// <param name="pagingnumber"></param>
protected PagingHelperBase PagingSingle(IDbHandle.IDbop dbop, string sql, string sqlOrder, int pagingsize, int pagingnumber)
{
return(new PagingHelperBase(PagingTable(dbop, sql, sqlOrder, pagingsize, pagingnumber, out RowCount), RowCount));
}
/// <summary>
/// 分页数据
/// </summary>
/// <param name="dbop"></param>
/// <param name="sql">sql语句 不含排序</param>
/// <param name="sqlOrder">排序sql</param>
protected DataTable PagingTable(IDbHandle.IDbop dbop, string sql, string sqlOrder)
{
return(PagingTable(dbop, sql, sqlOrder, PagingSize, PagingNumber, out RowCount));
}