private static string Sql2005FormatSql(string strsql, PageInfo pageInfo, AbstractDbHelper oleDb)
{
if (pageInfo.KeyName == null || pageInfo.KeyName == "")
{
throw new Exception("分页KeyName属性不能为空，如：pageInfo.KeyName==\"Id\" 或 pageInfo.KeyName==\"Id|Desc\"");
}
int starRecordNum = pageInfo.startNum;
int endRecordNum = pageInfo.endNum;
int index = strsql.ToLower().LastIndexOf("order by");
string _strsql = null;
if (index != -1)
{
_strsql = strsql.Remove(index);
}
else
{
_strsql = strsql;
}
string sql_totalRecord = "select TOP 1 count(*) from (" + _strsql + ") A";
Object obj = oleDb.GetDataResult(sql_totalRecord);
pageInfo.totalRecord = Convert.ToInt32(obj == DBNull.Value ? 0 : obj);
string _sql = _strsql;
string[] orderbys = pageInfo.KeyName.Split(new char[] { '|' });
string orderbyname, orderby;
if (orderbys.Length != 2)
{
orderbyname = orderbys[0];
orderby = "desc";
}
else
{
orderbyname = orderbys[0];
orderby = orderbys[1];
}
strsql = @"select * from
(
select row_number() over(order by {3} {4}) as rownum,t.* from ({0}) t
) as a where rownum between {1} and {2}";
strsql = String.Format(strsql, _sql, starRecordNum, endRecordNum, orderbyname, orderby);
return(strsql);
}
private static string OracleFormatSql(string strsql, PageInfo pageInfo, AbstractDbHelper oleDb)
{
int starRecordNum = pageInfo.startNum;
int endRecordNum = pageInfo.endNum;
string sql_totalRecord = "select count(*) from (" + strsql + ") A";
Object obj = oleDb.GetDataResult(sql_totalRecord);
pageInfo.totalRecord = Convert.ToInt32(obj == DBNull.Value ? 0 : obj);
strsql = " select * from( select a.*,rownum rn from ( " + strsql + " ) a ) where rn between " + starRecordNum.ToString() + " and " + endRecordNum.ToString();
return(strsql);
}
