写一个通用分页的方法

/// <summary>
/// 通用分页
/// </summary>
/// <param name="Sql">构造完成的SQL字符串</param>
/// <param name="PageIndex">页数</param>
/// <param name="PageSize">页大小</param>
/// <param name="OrderBy">排序字段 比如 ： ID DESC </param>
/// <param name="Paras">参数集</param>
/// <param name="TotalCount">返回总记录数</param>
/// <returns>datatable</returns>
public static DataTable CreateSqlByPageExcuteSql(string Sql,int PageIndex,int PageSize,string OrderBy,SqlParameter[] Paras,ref int TotalCount)
{
StringBuilder sbSql = new StringBuilder();
if (PageIndex == 1)
sbSql.Append("SELECT TOP " + PageSize + " * FROM");
else
sbSql.Append("SELECT * FROM ");
sbSql.Append(" ( SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") as RowNumber,tempTable.*");
sbSql.Append(" FROM ( " + Sql + " ) AS tempTable ) AS tmp ");

if (PageIndex != 1)
sbSql.Append("WHERE RowNumber BETWEEN CONVERT(varchar,(@PageIndex-1)*@PageSize+1) AND CONVERT(varchar,(@PageIndex-1)*@PageSize+@PageSize) ");

sbSql.Append(" SELECT @TotalRecord=count(*) from (" + Sql + ") tempTable");

/*重新构造SqlParameter*/
int index = 0;
int Length = 0;
SqlParameter[] SqlParas;

if (Paras != null && Paras.Length > 0)
{
Length = Paras.Length;
SqlParas = new SqlParameter[Length + 3];
for (int i = 0; i < Paras.Length; i++)
{
SqlParas[i] = Paras[i];
index++;
}
}
else
SqlParas = new SqlParameter[Length + 3];

/*将分页参数追加至SqlParameter*/
SqlParas[index] = new SqlParameter("@PageIndex", SqlDbType.Int);
SqlParas[index].Value = PageIndex;
index++;
SqlParas[index] = new SqlParameter("@PageSize", SqlDbType.Int);
SqlParas[index].Value = PageSize;
index++;
SqlParas[index] = new SqlParameter("@TotalRecord", SqlDbType.Int);
SqlParas[index].Direction = ParameterDirection.Output;
DataTable dtTemp = ExecuteSql(sbSql.ToString(), SqlParas);
TotalCount = (int)SqlParas[index].Value;
return dtTemp;
}