近日公司一网站项目，要调用其它系统(call center系统)的oracle数据库数据，只能连接查询，无法创建存储过程，所以只能在sql语句上动脑筋实现分页：
///
/// Oracle通用分页查询函数 by 菩提树下的杨过 2010-01-07
///
///
///
///
///
///
///
///
///
///
public DataSet QueryOracle(string tableName, string fields, string sqlWhere, string orderFields, int pageSize, int pageIndex, out int recordCount, out int pageCount)
{
if (string.IsNullOrEmpty(sqlWhere)) { sqlWhere = "1=1"; }
DataSet ds = null;
EnLib.Database _db = null;
recordCount = 0;
pageCount = 0;
try
{
_db = EnLib.DatabaseFactory.CreateDatabase();
//先计算总记录数
string sql = string.Format("select Count(*) from {0} where {1}", tableName, sqlWhere);
recordCount = int.Parse(_db.ExecuteScalar(CommandType.Text, sql).ToString());
//再确定总页数
if (recordCount % pageSize == 0)
{
pageCount = recordCount / pageSize;
}
else
{
pageCount = recordCount / pageSize + 1;
}
if (pageIndex <= 0) { pageIndex = 1; }
if (pageIndex > pageCount) { pageIndex = pageCount; }
//确定起始点
int _start = pageSize * (pageIndex - 1) + 1;
int _end = _start + pageSize - 1;
if (_end > recordCount) { _end = recordCount; }
//如果fields为*，则查询所有字段列表
if (fields == "*")
{
sql = "select table_name,column_name from user_tab_columns where table_name='" + tableName.ToUpper().Trim() + "'";
IDataReader dr = _db.ExecuteReader(CommandType.Text, sql);
string _allFields = "";
while (dr.Read())
{
_allFields += dr[1] + ",";
}
dr.Close();
fields = _allFields.Trim(',');
if (orderFields.Length == 0)
{
orderFields = fields.Split(',')[0];
}
}
if (orderFields.Length == 0)
{
orderFields = fields.Split(',')[0];
}
//构造sql语句
sql = string.Format("select RecordID,{0} from (select rownum RecordID,{0} from (select {0} from {1} where {2} order by {3})) where RecordID between {4} and {5}", fields, tableName, sqlWhere, orderFields, _start, _end); ds = _db.ExecuteDataSet(CommandType.Text, sql);
}
catch
{
}
return ds;
}
注:用到了微软的企业库EnLib
