sqlserver 存储过程分页(按多条件排序)

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页面代码：

<table id=”SXFSTable” style=”width:100%;” class=”table”>

<tr><td><b>公司名称</b></td><td><b>公司网址</b></td><td><b>认领状态</b></td></tr>

<%for (int i = 0; i < dt.Rows.Count; i++)

{

%>

<tr>

<td><%= dt.Rows[i][“companyName”].ToString() %>排序值：<%= dt.Rows[i][“ordernum”].ToString() %></td>

<td><%= dt.Rows[i][“webSite”].ToString() %>

是否认领：<%=dt.Rows [i][“userid”].ToString () %></td>

<td><%= dt.Rows[i][“isRL”].ToString().Equals(“0”) ? “<a href=\”javascript:;\” onclick=\”renLing(event,'”+dt.Rows[i][“RLId”].ToString()+”‘);\”>认领该企业</a>” : “<font color=\”red\”>该企业已被认领</font>”%></td>

</tr>

<%

}

%>

</table>

</div>

<div style=”margin-left:auto; margin-right:auto; width:70%; text-align:left; font-size:9pt;”>

第 <%=PageCurrent %> 页共 <%=RowsCount %> 条共 <%=TotalPage%> 页

<% if (PageCurrent != 1)

{

%>

<a href=”test.aspx”>首　页</a>

<a href=”test.aspx?page=<%=PageCurrent-1%20%>”>上一页</a>

<%

}

if (PageCurrent != TotalPage)

{

%>

<a href=”test.aspx?page=<%=PageCurrent+1%20%>”>下一页</a>

<a href=”test.aspx?page=<%=TotalPage%>”>末　页</a>

<%

}

%>

</div>

存储过程代码：

复制代码代码如下:

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) –大于号“>” 或者小于号”<“

Declare @OrderSql nvarchar(10)–排序字段

declare @Sql nvarchar(4000)

Declare @TemSql nvarchar(1000)

Declare @nRd int

declare @afterRows int

declare @tempTableName nvarchar(10)

if(@OrderType=’1′)

Begin

set @OrderSql=’ asc’

End

Else

Begin

set @OrderSql= ‘ desc’

End

if(isnull(@strWhere, ”)<>”)

Set @strWhere = @strWhere

if(@strWhere=”)

Set @strWhere=’ 1=1 ‘

Set @TemSql=’Select @RecordCount=Count(1) from ‘+@TableName +’ where ‘+@strWhere

exec sp_executesql @TemSql,N’@RecordCount float output’,@RecordCount output

Set @RowsCount=@RecordCount

Set @TotalPage= ceiling(@RecordCount/@PageSize)

if(@CurrentPage>@TotalPage)

Set @CurrentPage=@TotalPage

if(@CurrentPage<1)

Set @CurrentPage=1

if(@PageSize<1)

Set @PageSize=1

print(@RecordCount)

if(@CurrentPage=1)

Begin

set Rowcount @PageSize

set @Sql=’select ‘+ @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’

‘+@OrderSql +’,’+@IdField +’ asc’

–print(@Sql)

exec sp_executeSql @Sql

End

else if(@CurrentPage=@TotalPage)

begin

set @afterRows=@RowsCount-(@CurrentPage-1)*@PageSize

set RowCount @afterRows

if(@OrderType=’1′)

begin

set @OrderField=REPLACE(@OrderField,’asc’,’lai512343975′)//这里用变量将asc和desc互换，哈哈，太神了

set @OrderField=REPLACE(@OrderField,’desc’,’asc’)

set @OrderField=REPLACE(@OrderField,’lai512343975′,’desc’)

set @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’ desc’+’,’+@IdField +’ asc’

end

else

begin

set @OrderField=REPLACE(@OrderField,’desc’,’lai512343975′)

set @OrderField=REPLACE(@OrderField,’asc’,’desc’)

set @OrderField=REPLACE(@OrderField,’lai512343975′,’asc’)

set @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’ asc ‘ +’,’+@IdField+ ‘ asc’

print(@Sql)

end

–print(@Sql)

exec sp_executeSql @Sql

end

else

Begin

set @nRd=@PageSize* (@CurrentPage-1)

print(@nRd)

set RowCount @PageSize

set @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ and ‘+@IdField + ‘ not in (select top ‘+ cast(@nRd as nvarchar(10))+’ ‘+@IdField+’ from ‘+@TableName+’ where ‘+ @strWhere+’ order by ‘+@OrderField +’ ‘+@OrderSql+’,’+@IdField +’ asc) ‘ + ‘ order by ‘+ @OrderField + ‘ ‘ +@OrderSql+’,’+@IdField +’ asc’

exec sp_executeSql @Sql

–Print(@sql)

End

end

GO

微信