【原创】翻页asp.net实现方法 - jQuery MiniUI

/// <summary>
/// 翻页查询目标表数据
/// </summary>
/// <param name="table">表名（支持构造表）</param>
/// <param name="key">主键</param>
/// <param name="flds">查询字段</param>
/// <param name="where">筛选（需要加where）</param>
/// <param name="sort">排序字段</param>
/// <param name="order">排序类型</param>
/// <param name="index">页码（从一开始）</param>
/// <param name="size">页大小</param>
/// <returns>数据集</returns>
public Hashtable GetDataSet(string table,string key,string flds,string where,string sort,string order,int index,int size)
{
string sql = "";
string orderby = "";
if (!string.IsNullOrEmpty(sort))
{
if (order != "desc") order = "asc";
orderby = " order by " + sort + " " + order;
}
else
{
if (order != "desc") order = "asc";
orderby = " order by " + key + " " + order;
}
sql = string.Format(@"select {0} from {1} where {3} in(
select top ({5}) {3} from(select row_number()over({4}) as rownum,{3} from {1} {2}) as Tb
where rownum>(({6}-1)*{5})) {4}
select count(*) as total from {1} {2}",flds,table,where,key,orderby,size,index);
DataSet ds = Sqlhelper.GetDataSet(Sqlhelper.connectionString, CommandType.Text, sql);
if (ds != null)
{
if (ds.Tables.Count == 2)
{
DataTable dt = ds.Tables[0];
ArrayList data = this.DataTable2ArrayList(dt);
Hashtable result = new Hashtable();
result["data"] = data;
result["total"] = ds.Tables[1].Rows[0][0].ToString();
return result;
}
}
return null;
}
private ArrayList DataTable2ArrayList(DataTable data)
{
ArrayList array = new ArrayList();
for (int i = 0; i < data.Rows.Count; i++)
{
DataRow row = data.Rows[i];
Hashtable record = new Hashtable();
for (int j = 0; j < data.Columns.Count; j++)
{
object cellValue = row[j];
if (cellValue.GetType() == typeof(DBNull))
{
cellValue = null;
}
record[data.Columns[j].ColumnName] = cellValue;
}
array.Add(record);
}
return array;
}

复制代码