通用分页函数(ORACLE版)
因为项目需要编写,不得不写一些乱七八糟的分页处理程序,后来写来写去写多了就做了一个通用函数,目前贴出来的是ORACLE版的.
/// <summary>
/// 函数名称:BaseList
/// 功能描述:将符合指定条件的数据表记录列表绑定给DATASET返回
/// </summary>
/// <param name="dgList">指定装填DataSet返回给前台</param>
/// <param name="strTb">操作表名</param>
/// <param name="strField_f">指定返回给客户端的字段</param>
/// <param name="strField_l">指定返回给选择集的字段</param>
/// <param name="strQ">查询条件</param>
/// <param name="strOrder">字段排序字串</param>
/// <param name="unPage">申请显示页号</param>
/// <param name="unPageSize">每页最大显示记录数,如果取值小于1则表示全选,此时入参unPage无效</param>
/// <returns>成功则返回符合条件的记录条数,否则返回-1</returns>
protected long BaseList (ref DataSet dgList,string strTb,string strField_f,string strField_l,string strQ,string strOrder,uint unPage,uint unPageSize)
{
OleDbConnection cnnRjb = new System.Data.OleDb.OleDbConnection();
OleDbCommand cmdRjb=new OleDbCommand();
OleDbDataAdapter dapLoad=new OleDbDataAdapter();
StringBuilder stbSql=new StringBuilder("select "); //完整SQL语句
StringBuilder stbSqlB=new StringBuilder("");
ulong ulRn=unPage*unPageSize; //当前页之前的记录数
long lReturn=-1;
//最终SQL语句组成结构:
//(总体)select :strField_f from (select :strField_l from (select :strField_l from :strTb where :strQ) where rownum<=:unPageSize minus (select :strField_l from (select :strField_l from :strTb where :strQ) where rownum<=(:unPage*unPageSize)) order by ???
// |---------A------------| |----------------------------------------B----------------------------------------|
//构造SQL语句
//构造A区
if (strTb=="")
{
return(lReturn);
}
if (strField_f=="")
{
stbSql.Append("* ");
}
else
{
stbSql.Append(strField_f);
}
stbSql.Append(" from ");
if (unPageSize<1) //记录全选
{
stbSql.Append(strTb);
if (strQ!="")
{
stbSql.Append(" where "+strQ);
}
}
else //分页选择
{
//构造B区
stbSqlB.Append("select ");
if (strField_l=="")
{
stbSqlB.Append("* ");
}
else
{
stbSqlB.Append(strField_l);
}
stbSqlB.Append(" from ");
stbSqlB.Append("("+stbSqlB.ToString());
stbSqlB.Append(strTb);
if (strQ!="")
{
stbSqlB.Append(" where "+strQ);
}
if (strOrder!=null || strOrder!="")
stbSqlB.Append(" order by "+strOrder);
stbSqlB.Append(") where rownum<=");
//组合完整SQL语句
stbSql.Append("("+stbSqlB.ToString()+(unPageSize+ulRn).ToString()+" minus ("+stbSqlB.ToString()+ulRn.ToString()+"))");
}
// order by
if (strOrder!=null || strOrder!="")
stbSql.Append(" order by "+strOrder);
Conn(cnnRjb); //Conn为自定义的函数,作用为将定义好的链接字串赋给数据库链接函数
cmdRjb.Connection=cnnRjb;
dapLoad.SelectCommand=new OleDbCommand(stbSql.ToString(),cnnRjb);
try
{
cnnRjb.Open();
dapLoad.Fill(dgList,strTb);
stbSql=new StringBuilder("select count(*) from (select rownum from "+strTb);
if (strQ!="")
{
stbSql.Append(" where "+strQ);
}
stbSql.Append(") x");
cmdRjb.CommandText=stbSql.ToString();
lReturn=Convert.ToInt64(cmdRjb.ExecuteScalar());
}
catch(Exception e)
{
//错误处理...
}
finally
{
cnnRjb.Close();
}
return(lReturn);
}