下面这个方法原来是操作 sqlserver 数据库的 ,可以获得一个分页的sql语句, 我想把它改成能操作oracle 数据库,该怎么改啊
private static string GetPageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
{
RecordCount = 0;
PageCount = 0;
if (PageSize <= 0)
{
PageSize = 10;
}
string SqlCount = "select count(" + IndexField + ") from " + SqlTablesAndWhere;
cmd.CommandText = SqlCount;
RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
if (RecordCount % PageSize == 0)
{
PageCount = RecordCount / PageSize;
}
else
{
PageCount = RecordCount / PageSize + 1;
}
if (PageIndex > PageCount)
PageIndex = PageCount;
if (PageIndex < 1)
PageIndex = 1;
string Sql = null;
if (PageIndex == 1)
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
}
else
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from ";
if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
{
string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
Sql += _where + ") and (";
}
else
{
Sql += SqlTablesAndWhere + " where (";
}
Sql += IndexField + " not in (select top " + (PageIndex - 1) * PageSize + " " + IndexField + " from " + SqlTablesAndWhere + " " + OrderFields;
Sql += ")) " + OrderFields;
}
return Sql;
}
private static string GetPageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
{
RecordCount = 0;
PageCount = 0;
if (PageSize <= 0)
{
PageSize = 10;
}
string SqlCount = "select count(" + IndexField + ") from " + SqlTablesAndWhere;
cmd.CommandText = SqlCount;
RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
if (RecordCount % PageSize == 0)
{
PageCount = RecordCount / PageSize;
}
else
{
PageCount = RecordCount / PageSize + 1;
}
if (PageIndex > PageCount)
PageIndex = PageCount;
if (PageIndex < 1)
PageIndex = 1;
string Sql = null;
if (PageIndex == 1)
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
}
else
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from ";
if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
{
string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
Sql += _where + ") and (";
}
else
{
Sql += SqlTablesAndWhere + " where (";
}
Sql += IndexField + " not in (select top " + (PageIndex - 1) * PageSize + " " + IndexField + " from " + SqlTablesAndWhere + " " + OrderFields;
Sql += ")) " + OrderFields;
}
return Sql;
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货