DataGrid 存储过程的分页
<%@ import namespace="system.data.sqlclient" %>
<%@ import namespace="system.data" %>
<script runat="server">
dim connorthwind as sqlconnection
dim strsql as string
dim strselect as string
dim intstartindex as integer
dim intendindex as integer
dim intrecordcount as integer
dim cmdsql as sqlcommand
sub page_load
btnfirst.text = "首页"
btnprev.text = "上一页"
btnnext.text = "下一页"
btnlast.text = "末页"
connorthwind = new sqlconnection( "server=192.168.4.1;uid=sa;pwd=111111;database=yourdbname" )
if not ispostback then
binddatagrid
end if
end sub
sub binddatagrid
dim cmdselect as sqlcommand
dim dtrrecordcount as sqldatareader
intendindex = dgrdproducts.pagesize
cmdselect = new sqlcommand( "newspaged", connorthwind )
cmdselect.commandtype = commandtype.storedprocedure
cmdselect.parameters.add( "@pageindex", intstartindex )
cmdselect.parameters.add( "@pagesize ", intendindex )
connorthwind.open()
dtrrecordcount = cmdselect.executereader()
while dtrrecordcount.read()
intrecordcount=dtrrecordcount(0)
end while
dgrdproducts.virtualitemcount = (intrecordcount / dgrdproducts.pagesize)
dtrrecordcount.nextresult()
dgrdproducts.datasource = dtrrecordcount
dgrdproducts.databind()
connorthwind.close()
end sub
sub dgrdproducts_pageindexchanged( s as object, e as datagridpagechangedeventargs )
intstartindex = e.newpageindex
dgrdproducts.currentpageindex = e.newpageindex
binddatagrid
end sub
sub pagerbuttonclick(byval sender as object, byval e as eventargs)
dim arg as string = sender.commandargument
select case arg
case "next"
if (dgrdproducts.currentpageindex < (dgrdproducts.pagecount - 1)) then
dgrdproducts.currentpageindex += 1
end if
case "prev"
if (dgrdproducts.currentpageindex > 0) then
dgrdproducts.currentpageindex -= 1
end if
case "last"
dgrdproducts.currentpageindex = (dgrdproducts.pagecount - 1)
case else
page number
dgrdproducts.currentpageindex = system.convert.toint32(arg)
end select
intstartindex=dgrdproducts.currentpageindex
binddatagrid
end sub
sub dgrdproducts_editcommand( s as object, e as datagridcommandeventargs )
dgrdproducts.edititemindex = e.item.itemindex
intstartindex = dgrdproducts.currentpageindex
binddatagrid
end sub
sub dgrdproducts_updatecommand( s as object, e as datagridcommandeventargs )
dim intarticleid as integer
dim txttopic as textbox
dim txteditor as textbox
dim strtopic as string
dim streditor as string
intarticleid = dgrdproducts.datakeys( e.item.itemindex )
txttopic = e.item.cells( 1 ).controls( 0 )
txteditor = e.item.cells( 2 ).controls( 0 )
strtopic = txttopic.text
streditor = txteditor.text
strsql = "update tb_article set topic=@topic, " _
& "editor=@editor where articleid=@articleid"
cmdsql = new sqlcommand( strsql, connorthwind )
cmdsql.parameters.add( "@topic", strtopic )
cmdsql.parameters.add( "@editor", streditor )
cmdsql.parameters.add( "@articleid", intarticleid )
connorthwind.open()
cmdsql.executenonquery()
connorthwind.close()
dgrdproducts.edititemindex = -1
binddatagrid
end sub
sub dgrdproducts_cancelcommand( s as object, e as datagridcommandeventargs )
dgrdproducts.edititemindex = -1
binddatagrid
end sub
</script>
<html>
<head><title>datagridcustompaging.aspx</title></head>
<body>
<form runat="server">
<asp:datagrid runat="server"
id="dgrdproducts"
oneditcommand="dgrdproducts_editcommand"
onupdatecommand="dgrdproducts_updatecommand"
oncancelcommand="dgrdproducts_cancelcommand"
datakeyfield="a_articleid"
autogeneratecolumns="false"
showheader="true"
allowpaging="true"
allowcustompaging="true"
headerstyle-backcolor="salmon"
pagesize="10"
onpageindexchanged="dgrdproducts_pageindexchanged"
pagerstyle-mode="numericpages"
alternatingitemstyle-backcolor="#eeaaee"
font-size="10pt"
font-name="verdana"
cellspacing="0"
cellpadding="3"
gridlines="both"
borderwidth="1"
bordercolor="black"
pagerstyle-horizontalalign="right">
<alternatingitemstyle backcolor="#eeeeee"></alternatingitemstyle>
<columns>
<asp:boundcolumn
headertext="序列号"
datafield="articleid"
readonly="true" />
<asp:boundcolumn
headertext="标题"
datafield="topic" />
<asp:boundcolumn
headertext="编辑者"
datafield="editor" />
<asp:editcommandcolumn
edittext="edit!"
updatetext="update!"
canceltext="cancel!" />
<asp:hyperlinkcolumn
headertext="编辑"
datanavigateurlfield="articleid"
datanavigateurlformatstring="details.aspx?id={0}"
text="编辑"/>
</columns>
</asp:datagrid>
<asp:linkbutton id="btnfirst" onclick="pagerbuttonclick" runat="server" font-name="verdana" font-size="8pt" forecolor="navy" commandargument="0"></asp:linkbutton>
<asp:linkbutton id="btnprev" onclick="pagerbuttonclick" runat="server" font-name="verdana" font-size="8pt" forecolor="navy" commandargument="prev"></asp:linkbutton>
<asp:linkbutton id="btnnext" onclick="pagerbuttonclick" runat="server" font-name="verdana" font-size="8pt" forecolor="navy" commandargument="next"></asp:linkbutton>
<asp:linkbutton id="btnlast" onclick="pagerbuttonclick" runat="server" font-name="verdana" font-size="8pt" forecolor="navy" commandargument="last"></asp:linkbutton>
</form>
</html>
下面是存储过程：
create procedure newspaged
(
@pageindex int,
@pagesize int
)
as
begin
declare @pagelowerbound int
declare @pageupperbound int
declare @rowstoreturn int
-- first set the rowcount
set @rowstoreturn = @pagesize * (@pageindex + 1)
set rowcount @rowstoreturn
-- set the page bounds
set @pagelowerbound = @pagesize * @pageindex
set @pageupperbound = @pagelowerbound + @pagesize + 1
-- create a temp table to store the select results
create table #pageindex
(
indexid int identity (1, 1) not null,
articleid int,
)
-- insert into the temp table
insert into #pageindex (articleid)
select
articleid
from
tablename
order by
articleid desc
-- return total count
select count(articleid) from tablename
-- return paged results
select
o.articleid,o.topic,editor
from
tablename o,
#pageindex pageindex
where
o.articleid = pageindex.articleid and
pageindex.indexid > @pagelowerbound and
pageindex.indexid < @pageupperbound
order by
pageindex.indexid
end
