IDC资讯中心
1。数据库结构为：(在sql当中建立一个数据库后，直接在sql结构查询器当中执行以下sql脚本)
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[mobile]) and objectproperty(id, nisusertable) = 1)
drop table [dbo].[mobile]
go
create table [dbo].[mobile] (
[mobileid] [int] identity (1, 1) not null ,
[mobiletype] [nvarchar] (50) collate chinese_prc_ci_as null
) on [primary]
go
2。存储过程，（可以直接在数据库中新建存储过程，然后复制到数据库）
/*
存储过程分页
*/
create procedure proc_paging
(
@tbname nvarchar(255) ,
@sql nvarchar(4000), –不带排序语句的sql语句
@page int, –页码
@recsperpage int, –每页容纳的记录数
@id varchar(255), –需要排序的不重复的id号
@sort varchar(255) , –排序字段及规则
@pagecount int output –总页数
)
as
begin
declare @sql1 nvarchar(4000)
set @sql1=nselect @pagecount=count(*)
+n from +@tbname
exec sp_executesql @sql1,n@pagecount int output,@pagecount output
set @pagecount=(@pagecount+@recsperpage-1)/@recsperpage
end
begin
declare @str nvarchar(4000)
set @str=select top +cast(@recsperpage as varchar(20))+ * from (+@sql+) t where t.+@id+ not in (select top +cast((@recsperpage*(@page-1)) as varchar(20))+ +@id+ from (+@sql+) t9 order by +@sort+) order by +@sort
–print @str
–exec sp_executesql @str
–exec @str
declare @str1 nvarchar(400)
declare @str2 nvarchar(400)
set @str1 = cast(@recsperpage as varchar(20))
set @str2 = cast((@recsperpage*(@page-1)) as varchar(20))
exec ( nselect top +@str1+ n * from (+@sql+n) t where t.+@id+n not in (select top +@str2+n +@id+n from (+@sql+n) t9 order by +@sort+n) order by +@sort )
end
go
3。程序代码：（index.aspx.cs文件）
using system;
using system.collections;
using system.componentmodel;
using system.data;
using system.drawing;
using system.web;
using system.web.sessionstate;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
namespace joyes.test
{
/// <summary>
//————————————————–
//–功能模块:存储过程分页
//–说明:很简单
//–编写人:黄治强
//–编写时间:2005.9.5
//—————————————————
/// </summary>
public class index : system.web.ui.page
{
protected system.web.ui.webcontrols.datagrid dg;
protected system.web.ui.webcontrols.label lblpaging;
private void page_load(object sender, system.eventargs e)
{
// 在此处放置用户代码以初始化页面
if( !page.ispostback )
{
if(request.querystring[“page”] == null)
{
//第一次开启页面时默认传递的页码参数为1
databinddg2(1);
}
else
{
databinddg2(int.parse(request.querystring[“page”].tostring()));
}
}
}
/// <summary>
/// 利用存储过程取出数据并邦定到datagrid
/// </summary>
/// <param name=”intpage”>需要传递的翻页页码的get参数(int)</param>
private void databinddg2(int intpage)
{
string str1 = ” select * from mobile “;//不带排序语句的sql语句
int intrecsperpage = 10;//每页容纳的记录数
string strid = ” mobileid “;//需要排序的不重复的id号
string strsort = ” mobileid “;//排序字段及规则
string strtbname = “mobile”;//数据库当中的要提取数据的表
sqlconnection con = new sqlconnection(“uid=sa;pwd=123456;database=test;”);
sqlcommand cmd = new sqlcommand(“proc_paging”,con);
cmd.commandtype = commandtype.storedprocedure;
cmd.parameters.add(new sqlparameter(“@sql”,sqldbtype.nvarchar,4000));
cmd.parameters.add(new sqlparameter(“@page”,sqldbtype.int));
cmd.parameters.add(new sqlparameter(“@recsperpage”,sqldbtype.int));
cmd.parameters.add(new sqlparameter(“@id”,sqldbtype.nvarchar,255));
cmd.parameters.add(new sqlparameter(“@sort”,sqldbtype.nvarchar,255));
cmd.parameters.add(new sqlparameter(“@tbname”,sqldbtype.nvarchar,255));
sqlparameter parameterpagecount = new sqlparameter(“@pagecount”,sqldbtype.int);
parameterpagecount.direction = parameterdirection.output;
cmd.parameters.add(parameterpagecount);
cmd.parameters[“@sql”].value = str1;
cmd.parameters[“@page”].value = intpage;
cmd.parameters[“@recsperpage”].value = intrecsperpage;
cmd.parameters[“@id”].value = strid;
cmd.parameters[“@sort”].value = strsort;
cmd.parameters[“@tbname”].value = strtbname;
try
{
using(sqldataadapter ad = new sqldataadapter(cmd))
{
dataset ds = new dataset();
ad.fill(ds);
dg.datasource = ds.tables[0].defaultview;
dg.databind();
}
}
catch(exception error)
{
string strerror = error.tostring();
}
finally
{
if( con != null || con.state == connectionstate.open )
{
con.close();
}
}
lblpaging.text = getlblpagingbind(request.querystring[“page”],parameterpagecount.value.tostring());
}
/// <summary>
/// 返回分页工具栏html编码
/// </summary>
/// <param name=”strparameter”>需要传递的翻页页码的get参数(string)</param>
/// <param name=”strpagecount”>表的总页数(string)</param>
/// <returns>strpagebar</returns>
public string getlblpagingbind(string strparameter,string strpagecount)
{
string strpage = string.empty;
if( strparameter == null )
{ strpage = “1”; }
else
{ strpage = strparameter; }
//设置页码
string strpagebar=””;
if (dg.allowpaging.tostring() == “false” )
{
strpagebar+=”<nobr>\n”;
strpagebar+=”[当前页]:”+(int.parse(strpage)).tostring()+”/”+strpagecount+” \n”;
if (strpage == “1”)
{
strpagebar+=”<a disabled=disabled>[第一页]</a> \n”;
strpagebar+=”<a disabled=disabled>[上一页]</a> \n”;
}
else
{
strpagebar+=”<a href=\”?”+strparameter+”&page=1\”>[第一页]</a> \n”;
strpagebar+=”<a href=\”?”+strparameter+”&page=”+(int.parse(strpage)-1).tostring()+”\”>[上一页]</a> \n”;
}
if (strpage == strpagecount)
{
strpagebar+=”<a disabled=disabled>[下一页]</a> \n”;
strpagebar+=”<a disabled=disabled>[最后一页]</a> \n”;
}
else
{
strpagebar+=”<a href=\”?”+strparameter+”&page=”+(int.parse(strpage)+1).tostring()+”\”>[下一页]</a> \n”;
strpagebar+=”<a href=\”?”+strparameter+”&page=”+strpagecount+”\”>[最后一页]</a> \n”;
}
strpagebar+=@”<script language=javascript>function goto(page){if(!/^\d+$/.test(page))alert(页码格式不正确); else this.location=?&page=+page;}</script>”;
strpagebar+=”<a href=\”javascript:goto(document.getelementbyid(tboxpage).value)\” id=linkgoto>[跳转到第]</a>\n”;
strpagebar+=”<input class=input id=tboxpage type=text size=3 onkeydown=javascript:if(event.keycode==13){goto(this.value);return false;}>\n”;
strpagebar+=”[页]\n”;
strpagebar+=”</nobr>”;
}
return strpagebar;
}
#region web 窗体设计器生成的代码
override protected void oninit(eventargs e)
{
//
// codegen: 该调用是 asp.net web 窗体设计器所必需的。
//
initializecomponent();
base.oninit(e);
}
/// <summary>
/// 设计器支持所需的方法 – 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void initializecomponent()
{
this.load += new system.eventhandler(this.page_load);
}
#endregion
}
}
4。web页面代码(index.aspx)
<%@ page language=”c#” codebehind=”index.aspx.cs” autoeventwireup=”false” inherits=”joyes.test.index” %>
<!doctype html public “-//w3c//dtd html 4.0 transitional//en” >
<html>
<head>
<title>webform1</title>
<meta content=”microsoft visual studio .net 7.1″ name=”generator”>
<meta content=”c#” name=”code_language”>
<meta content=”javascript” name=”vs_defaultclientscript”>
<meta content=”http://schemas.microsoft.com/intellisense/ie5″ name=”vs_targetschema”>
</head>
<body ms_positioning=”gridlayout”>
<form id=”form1″ method=”post” runat=”server”>
<font face=”宋体”>
<asp:datagrid id=”dg” style=”z-index: 102; left: 40px; position: absolute; top: 40px” runat=”server”
autogeneratecolumns=”false” height=”96px”>
<columns>
<asp:boundcolumn datafield=”mobiletype” headertext=”手机类型”></asp:boundcolumn>
<asp:boundcolumn datafield=”mobileid” headertext=”铃声名字”></asp:boundcolumn>
<asp:boundcolumn datafield=”mobileid” headertext=”mobileid”></asp:boundcolumn>
</columns>
<pagerstyle mode=”numericpages”></pagerstyle>
</asp:datagrid><asp:label id=”lblpaging” style=”z-index: 103; left: 48px; position: absolute; top: 368px”
runat=”server”></asp:label></font></form>
</body>
</html>
