当前位置: 数据库>sqlserver
sqlserver 存储过程分页(按多条件排序)
来源: 互联网 发布时间:2014-09-05
本文导语: cs页面调用代码: 代码如下: public int TotalPage = 0; public int PageCurrent = 1; public int PageSize = 25; public int RowsCount = 0; string userid, username; public DataTable dt = new DataTable(); public string path, userwelcome; public string opt,cid; protected void Page_Load(objec...
cs页面调用代码:
public int TotalPage = 0;
public int PageCurrent = 1;
public int PageSize = 25;
public int RowsCount = 0;
string userid, username;
public DataTable dt = new DataTable();
public string path, userwelcome;
public string opt,cid;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.Params["page"] == null || Request.Params["page"].ToString().Equals(""))
PageCurrent = 1;
else
PageCurrent=int.Parse(Request.Params["page"].ToString());
this.getPage(out TotalPage, out RowsCount, PageSize, PageCurrent);
}
}
//调用存储过程的函数
private void getPage(out int totalPage, out int rowsCount, int pageSize, int currentPage)
{
SqlParameter[] parameters = {
new SqlParameter("@TotalPage", SqlDbType.Int,4),
new SqlParameter("@RowsCount", SqlDbType.Int,4),
new SqlParameter("@PageSize", SqlDbType.Int,4),
new SqlParameter("@CurrentPage", SqlDbType.Int,4),
new SqlParameter("@SelectFields", SqlDbType.NVarChar,700),
new SqlParameter("@IdField",SqlDbType.NVarChar,50),
new SqlParameter("@OrderField", SqlDbType.NVarChar,200),
new SqlParameter("@OrderType", SqlDbType.NVarChar,2),
new SqlParameter("@TableName", SqlDbType.NVarChar,300),
new SqlParameter("@strWhere", SqlDbType.NVarChar,300),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Value = "a.RLId,a.companyName,a.webSite,a.isRL,a.ordernum,a.isrl,a.userid";
parameters[5].Value = "a.RLId";
parameters[6].Value = " a.isrl asc , a.orderNum ";
parameters[7].Value = "1";
parameters[8].Value = "qiYeRenling a";
parameters[9].Value = "1=1";//
DataSet ds = Wm23Abc.DBUtility.DbHelperSQL.RunProcedure("getRecordByPage", parameters, "dt");
dt = ds.Tables[0];
totalPage = int.Parse(parameters[0].Value.ToString());
rowsCount = int.Parse(parameters[1].Value.ToString());
}
.aspx页面代码:
公司名称公司网址认领状态
排序值:
是否认领:
代码如下:
public int TotalPage = 0;
public int PageCurrent = 1;
public int PageSize = 25;
public int RowsCount = 0;
string userid, username;
public DataTable dt = new DataTable();
public string path, userwelcome;
public string opt,cid;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.Params["page"] == null || Request.Params["page"].ToString().Equals(""))
PageCurrent = 1;
else
PageCurrent=int.Parse(Request.Params["page"].ToString());
this.getPage(out TotalPage, out RowsCount, PageSize, PageCurrent);
}
}
//调用存储过程的函数
private void getPage(out int totalPage, out int rowsCount, int pageSize, int currentPage)
{
SqlParameter[] parameters = {
new SqlParameter("@TotalPage", SqlDbType.Int,4),
new SqlParameter("@RowsCount", SqlDbType.Int,4),
new SqlParameter("@PageSize", SqlDbType.Int,4),
new SqlParameter("@CurrentPage", SqlDbType.Int,4),
new SqlParameter("@SelectFields", SqlDbType.NVarChar,700),
new SqlParameter("@IdField",SqlDbType.NVarChar,50),
new SqlParameter("@OrderField", SqlDbType.NVarChar,200),
new SqlParameter("@OrderType", SqlDbType.NVarChar,2),
new SqlParameter("@TableName", SqlDbType.NVarChar,300),
new SqlParameter("@strWhere", SqlDbType.NVarChar,300),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Value = "a.RLId,a.companyName,a.webSite,a.isRL,a.ordernum,a.isrl,a.userid";
parameters[5].Value = "a.RLId";
parameters[6].Value = " a.isrl asc , a.orderNum ";
parameters[7].Value = "1";
parameters[8].Value = "qiYeRenling a";
parameters[9].Value = "1=1";//
DataSet ds = Wm23Abc.DBUtility.DbHelperSQL.RunProcedure("getRecordByPage", parameters, "dt");
dt = ds.Tables[0];
totalPage = int.Parse(parameters[0].Value.ToString());
rowsCount = int.Parse(parameters[1].Value.ToString());
}
.aspx页面代码:
公司名称公司网址认领状态
排序值:
是否认领:
存储过程代码:
代码如下:
CREATE proc [dbo].[getRecordByPage]
@TotalPage int output,--总页数
@RowsCount int output,--总条数
@PageSize int,--每页多少数据
@CurrentPage int,--当前页数
@SelectFields nvarchar(1000),--select 语句但是不包含select
@IdField nvarchar(50),--主键列
@OrderField nvarchar(50),--排序字段,如果是多个字段,除最后一个字段外,后面都要加排序条件(asc/desc),不包含order by,最后一个排序字段不用加排序条件
@OrderType nvarchar(4),--1升序,0降序
@TableName nvarchar(200),--表名
@strWhere nvarchar(300)--条件
As
Begin
declare @RecordCount float
declare @PageNum int --分页依据数
Declare @Compare nvarchar(50)--比较字段区分min或者max
Declare @Compare1 nvarchar(2) --大于号“>” 或者小于号"@TotalPage)
Set @CurrentPage=@TotalPage
if(@CurrentPage