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); }