求一个高效的分页类
我只需要调用这个类就行了
如：GetAll(int startRecord, int pageSize, string str)
第几页，每页大小，sql语句（如：select * from table）
不用public int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable);方法，要高效的只读指定记录
[解决办法]
#region DataTable 分页
/// <summary>
/// 通用分页查询 DataTable
/// </summary>
/// <param name="pageSize">分页大小</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="TableName">查询表名</param>
/// <param name="ColumnName">查询字段，逗号分割</param>
/// <param name="WhereStr">查询条件，不带where</param>
/// <param name="SelectOrder">排序字段</param>
/// <param name="OrderType">排序类型，0降序 1升序</param>
/// <returns></returns>
public static DataTable getDataTable(int pageSize, int pageIndex, string TableName, string ColumnName, string WhereStr, string SelectOrder, int OrderType)
{
string @TemStr;
string @TemOrder;
int @Pages = pageSize * (pageIndex - 1);
if (OrderType != 0)//判断排序
{
@TemStr = ">(SELECT MAX";
@TemOrder = " ORDER BY " + SelectOrder + " ASC";
}
else
{
@TemStr = "<(SELECT MIN";
@TemOrder = " ORDER BY " + SelectOrder + " DESC";
}
StringBuilder SqlStr = new StringBuilder();
if (pageIndex == 1) //当页码为第一页时
{
if (WhereStr == "") //判断是否有条件查询
{
SqlStr.Append("SELECT TOP " + pageSize + " " + ColumnName + " FROM " + TableName + " " + @TemOrder);
}
else
{
SqlStr.Append("SELECT TOP " + pageSize + " " + ColumnName + " FROM " + TableName);
SqlStr.Append(" WHERE " + WhereStr + " " + @TemOrder);
}
}
else
{
if (WhereStr == "") //判断是否有条件查询
{
SqlStr.Append("SELECT TOP " + pageSize + " " + ColumnName + " FROM " + TableName + " WHERE ");
SqlStr.Append(SelectOrder + " " + @TemStr + "(" + SelectOrder + ") FROM (SELECT TOP " + @Pages + " ");
SqlStr.Append(SelectOrder + " FROM " + TableName + " " + @TemOrder + ")as t)" + @TemOrder);
}
else
{
SqlStr.Append("SELECT TOP " + pageSize + " " + ColumnName + " FROM " + TableName + " WHERE " + SelectOrder + " ");
SqlStr.Append(@TemStr + "(" + SelectOrder + ") FROM (SELECT TOP " + @Pages + " " + SelectOrder + " FROM ");
SqlStr.Append(TableName + " WHERE " + WhereStr + " " + @TemOrder + ")as t) and " + WhereStr + " " + @TemOrder);
}
}
return DAL.SQLHelper.ExecuteDataTable(SqlStr.ToString());
}
#endregion
#region 获取查询记录总数
/// <summary>
/// 获取查询记录总数
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="WhereStr">查询条件，可为空</param>
/// <returns></returns>
public static int getCount(string TableName, string WhereStr)
{
StringBuilder SqlStr = new StringBuilder();
SqlStr.Append("");
if (WhereStr!="")
{
SqlStr.Append("SELECT count(*) FROM ");
SqlStr.Append(TableName + " WHERE " + WhereStr);
}
else
{
SqlStr.Append("SELECT count(*) FROM " + TableName);
}
return DAL.SQLHelper.ExecuteScalarInt(SqlStr.ToString());
}
/// <param name="orderString">查询字符串中的order部分，需包含order关键字，且不能省略asc、desc关键字。</param>
public static DataTable GetDataByAspNetPager
(
Page page,
AspNetPager aspNetPager,
string selectString,
string fromString,
string whereString,
string orderString
)
{
//第一次载入页面时，获取分页数
if (!page.IsPostBack)
{
aspNetPager.RecordCount = int.Parse(DB.GetTable("select count(id) " + fromString + " " + whereString).Rows[0][0].ToString());
}
string n = "0";
string sql = string.Empty;
string OppositeOrderString = string.Empty;
orderString = orderString.Replace(";", "");
if (aspNetPager.PageSize < 1) { aspNetPager.PageSize = 1; }
if (aspNetPager.UrlPaging)
{
aspNetPager.CurrentPageIndex = int.Parse(CheckString(page.Request.QueryString["page"], "1"));
}
//获取第一页
if (aspNetPager.CurrentPageIndex == 1)
{
n = aspNetPager.PageSize.ToString();
selectString = selectString.Replace("select", "select top " + n);
sql = selectString + " " + fromString + " " + whereString + " " + orderString + ";";
}
//获取前半部分的某一页
else if (aspNetPager.CurrentPageIndex <= aspNetPager.PageCount / 2)
{
OppositeOrderString = orderString.ToLower();
OppositeOrderString = OppositeOrderString.Replace("asc", "temp");
OppositeOrderString = OppositeOrderString.Replace("desc", "asc");
OppositeOrderString = OppositeOrderString.Replace("temp", "desc");
n = (aspNetPager.PageSize * aspNetPager.CurrentPageIndex).ToString();
selectString = selectString.Replace("select", "select top " + n);
sql = selectString + " " + fromString + " " + whereString + " " + orderString;
sql = "select top " + aspNetPager.PageSize.ToString() + " * from(" + sql + ") as a " + OppositeOrderString;
sql = "select * from(" + sql + ") as b " + orderString + ";";
}
//获取后半部分的某一页
else
{
OppositeOrderString = orderString.ToLower();
OppositeOrderString = OppositeOrderString.Replace("asc", "temp");
OppositeOrderString = OppositeOrderString.Replace("desc", "asc");
OppositeOrderString = OppositeOrderString.Replace("temp", "desc");
n = (aspNetPager.RecordCount - aspNetPager.PageSize * (aspNetPager.CurrentPageIndex - 1)).ToString();
selectString = selectString.Replace("select", "select top " + n);
sql = selectString + " " + fromString + " " + whereString + " " + OppositeOrderString;
sql = "select top " + aspNetPager.PageSize.ToString() + " * from(" + sql + ") as a " + orderString + ";";
}
return DB.GetTable(sql);
}
这是我结合AspNetPager写的分页函数，看有什么缺陷，请大家指教。
