通用分页函数(SQLSERVER版)
下面的函数在ACCESS下运行也是可以的.但这个版本有一个小问题没有解决,那就是如果自定义排序方式时,会工作不正常,一直没有解决,希望大家能给出一些意见.
/// <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语句
long lReturn=-1;
//构造SQL语句
if (strTb=="")
{
return(lReturn);
}
if (strField_f=="")
{
stbSql.Append("* ");
}
else
{
stbSql.Append(strField_f);
}
stbSql.Append(" from ");
if ( strQ.Length!=0 )
stbWhere.Append(" and "+strQ);
if ( strOrder=="" )
strOrd=" order by id desc";
else
strOrd=" order by "+strOrder;
if (unPageSize<1)
stbSql=new StringBuilder("select "+strField+" from "+strTb+" where "+strQ+strOrd);
else
if (unPage<2)
stbSql=new StringBuilder("select top "+unPageSize.ToString()+strField+" from "+strTb+" where "+strQ+strOrd);
else
stbSql=new StringBuilder("select top "+unPageSize.ToString()+strField+" from "+strTb+" where "+strQ+" and id not in "+"(select top "+Convert.ToString((unPage-1)*unPageSize)+" id "+" from "+strTb+" where "+strQ+strOrd+")"+strOrd);
lgEvent.WriteLog2txt("基数据列表","SQL语句:\n"+stbSql.ToString()+"\n\n");
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 id from "+strTb);
if (strQ!="")
{
stbSql.Append(" where "+strQ);
}
stbSql.Append(") as x");
cmdRjb.CommandText=stbSql.ToString();
lReturn=Convert.ToInt64(cmdRjb.ExecuteScalar());
}
catch(Exception e)
{
//错误处理
}
finally
{
cnnRjb.Close();
}
return(lReturn);
}