/// <summary>
/// 根据sql,排序条件和分页条件得到实体对象集合
/// 参数设置类似：sql="select * from table1 where name like '%aa%'"; orderBy=" ID desc,Age asc"
/// </summary>
/// <param name="pageHelper"></param>
/// <param name="sql"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public DataSet GetDataSetBySqlPaged(PageHelper pageHelper, string sql, string orderBy)
{
if (string.IsNullOrEmpty(orderBy))
{
return null;
}
int rowMin = pageHelper.PageSize * pageHelper.PageIndex;
int rowMax = pageHelper.PageSize * (pageHelper.PageIndex + 1);
string rowIdColumn = "select Row_number() over (order by " + orderBy + ") as RowID,";
Regex regex = new Regex("select", RegexOptions.IgnoreCase);
string sqlPaged = "select * from (" + regex.Replace(sql, rowIdColumn) + ") as t where RowID>" + rowMin + " and RowID<=" + rowMax;
Regex regexFrom = new Regex("From", RegexOptions.IgnoreCase);
sql = regexFrom.Replace(sql, "from");
string sqlCount = "select count(1) as RecordCount " + sql.Substring(sql.IndexOf("from"));
DataSet ds = SqlHelper.ExecuteDataSet(sqlPaged + ";" + sqlCount, CommandType.Text);
if (ds.Tables.Count >= 2 && !string.IsNullOrEmpty(ds.Tables[1].Rows[0]["RecordCount"].ToString()))
{
pageHelper.RecordCount = int.Parse(ds.Tables[1].Rows[0]["RecordCount"].ToString());
}
return ds;
}
/// <summary>
/// 根据sql,排序条件和分页条件得到实体对象集合
/// 参数设置类似：sql="select * from table1 where name like '%aa%'"; orderBy=" ID desc,Age asc"
/// </summary>
/// <param name="pageHelper"></param>
/// <param name="sql"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public DataSet GetDataSetBySqlPaged(PageHelper pageHelper, string sql, string orderBy)
{
return new BaseDataAccessNonGeneric().GetDataSetBySqlPaged(pageHelper, sql, orderBy);
}
