我早用第二种了，不过效率没有楼主说的那麽低吧。29秒，是不是看错了。
#region 分页
/// <summary>
/// 转换参数
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
private SqlParameter[] CollectionToArray(SqlParameter[] param)
{
if(param == null) return null;
SqlParameter[] ps = new SqlParameter[param.Length];
param.CopyTo(ps,0);
return ps;
}
/// <summary>
/// 分页
/// </summary>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="group">group by</param>
/// <param name="sort">条件,必填</param>
/// <returns>结果集</returns>
public DataSet PageResult(string fields,string table,string filter,string group, string sort )
{
return PageResult(1,0,"",fields,table,filter,group,sort,"");
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="sort">排序</param>
/// <param name="param">参数</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk,string fields,string table
,string filter,string sort,ArrayList param)
{
SqlParameter[] sqlparams = new SqlParameter[param.Count];
param.CopyTo(sqlparams,0);
return PageResult( pageno , pagesize , pk, fields, table
, filter,"", sort,"", sqlparams);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="sort">排序</param>
/// <param name="param">参数</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk,string fields,string table
,string filter,string sort,SqlParameter[] param)
{
return PageResult( pageno , pagesize , pk, fields, table
, filter,"", sort,"", param);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少行</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="group">group by</param>
/// <param name="sort">排序 必填</param>
/// <param name="sum">统计</param>
/// <param name="param">参数</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk,string fields,string table
,string filter,string group,string sort,string sum,SqlParameter[] param)
{
//定义
string sortdesc = "";
string orderby = "";
string where ="";
string groupby = "";
string realfields = "";
DataSet data = new DataSet();
DataTable result;
int count = 0;
StringBuilder sql = new StringBuilder();
//组织语句
if(filter != "")
{
where = "where " + filter.ToString();
}
if(group != "")
{
groupby = "group by " + group;
//group = ClearPrefix(group);
}
if(sort == "")
{
if(pk !="" ||pk!="*")
{
sort = pk;
}
}
if(sort != "")
{
orderby = "order by " + sort;
sortdesc = "order by " + ReplaceSortDesc(ClearPrefix(sort));
sort = "order by " + ClearPrefix(sort);
}
realfields = ClearPrefix(fields);
//构建SQL
if(pagesize == 0)
{
sql.AppendFormat("select {0} from {1} {2} {3} {4} ",
fields,table,where,groupby,orderby);
#if DEBUG
ApplicationLog.WriteLog(sql.ToString());
#endif
result = ExcuteQuery( sql.ToString(),CollectionToArray(param),"pageresult");
count = result.Rows.Count;
}
else
{
StringBuilder sqlcount = new StringBuilder();
if(groupby != "")
{
//聚合
sqlcount.AppendFormat("select count(1) from (select {0} from {1} {2} {3} ) as t1_counttable",
group,table,where,groupby);
}
else
{
sqlcount.AppendFormat("select count(1) from {0} {1} {2} ",
table,where,groupby);
//非聚合
}
//求总记录数
object obj = ExecuteScalar(sqlcount.ToString(),CollectionToArray(param));
if(Convert.IsDBNull( obj))
{
count = 0;
}
else
{
count = Convert.ToInt32( obj);
}
if(count == 0)
{
sql.AppendFormat("select {0} from {1} {2} {3} {4} ",
fields,table,where,groupby,orderby);
}
else
{
//是否超出最后一页
int lastno = (count+pagesize-1)/pagesize;
//当前页有多少记录
int lastsize = pagesize;
if(pageno >=lastno)
{
pageno = lastno;
lastsize = count - pagesize * pageno+pagesize;
}
if(pageno <= 1)
{
sql.AppendFormat("select top {0} {1} from {2} {3} {4} {5}",
pagesize,fields,table,where,groupby,orderby);
}
else
{
sql.AppendFormat("select {0} from (",realfields);
sql.AppendFormat("select top {0} {1} from ",lastsize,realfields);
sql.AppendFormat("(select top {0} {1} from {2} {3} {4} {5}) as t1 ",
pageno*pagesize,fields,table,where,groupby,orderby);
sql.AppendFormat(" {0} ) as t2", sortdesc);
sql.AppendFormat(" {0} ", sort);
}
}
#if DEBUG
ApplicationLog.WriteLog(sql.ToString());
#endif
result = ExcuteQuery( sql.ToString(),CollectionToArray(param),"pageresult");
}
data.Tables.Add(result);
DataTable counttable = new DataTable("count");
counttable.Columns.Add(new DataColumn("countdata",typeof(int)));
DataRow row = counttable.NewRow();
///总记录数
row[0] = count;
counttable.Rows.Add(row);
data.Tables.Add(counttable);
//统计求和
if(sum != "")
{
StringBuilder sqlsum = new StringBuilder();
sqlsum.AppendFormat("select {0} from {1} {2}",sum,table,where);
#if DEBUG
ApplicationLog.WriteLog(sqlsum.ToString());
#endif
DataTable tablesum =
ExcuteQuery( sqlsum.ToString(),CollectionToArray(param),"sumresult");
data.Tables.Add(tablesum);
}
Close();
return data;
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="group">group by</param>
/// <param name="sort">排序</param>
/// <param name="sum">统计</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk ,string fields ,string table ,
string filter,string group, string sort , string sum)
{
return PageResult(pageno,pagesize,pk,fields,table,filter,group,sort,sum,null);
}
#endregion