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 %>”>上一页</a>

<%

}

if (pagecurrent != totalpage)

{

%>

<a href=”test.aspx?page=<%=pagecurrent+1 %>”>下一页</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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐