sqlserver2005简单分页解决方案学习
一、简单分页存储过程
1、”not in" 方式
代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <冯俊杰>
-- Create date: <2010-03-22>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_page1]
@pageindex int,--当前页码
@pagesize int--分页大小/*
AS
BEGIN
SELECT TOP( @pagesize) *
FROM s_table
WHERE nid NOT IN
(
SELECT top (@pagesize*(@pageindex-1)) nid FROM s_table ORDER BY nid
)
ORDER BY nid
END
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <冯俊杰>
-- Create date: <2010-03-22>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_page1]
@pageindex int,--当前页码
@pagesize int--分页大小/*
AS
BEGIN
SELECT TOP( @pagesize) *
FROM s_table
WHERE nid NOT IN
(
SELECT top (@pagesize*(@pageindex-1)) nid FROM s_table ORDER BY nid
)
ORDER BY nid
END
2、max id方式
代码
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v4.7.11
* Time: 2010-3-22 18:08:52
************************************************************/
USE test
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <冯俊杰>
-- Create date: <2010-03-22>
-- Description: <maxid分页>
-- =============================================
ALTER PROCEDURE pro_page2
@pagesize INT, --页码大小
@pageindex INT--当前页码
AS
BEGIN
SELECT TOP(@pagesize) *
FROM s_table
WHERE nid > (
SELECT ISNULL(MAX(nid), 0)
FROM (
SELECT TOP(@pagesize * (@pageindex -1)) *
FROM s_table ORDER BY nid
)A
)
ORDER BY nid
END
EXEC pro_page2 10,2
* Code formatted by SoftTree SQL Assistant ?v4.7.11
* Time: 2010-3-22 18:08:52
************************************************************/
USE test
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <冯俊杰>
-- Create date: <2010-03-22>
-- Description: <maxid分页>
-- =============================================
ALTER PROCEDURE pro_page2
@pagesize INT, --页码大小
@pageindex INT--当前页码
AS
BEGIN
SELECT TOP(@pagesize) *
FROM s_table
WHERE nid > (
SELECT ISNULL(MAX(nid), 0)
FROM (
SELECT TOP(@pagesize * (@pageindex -1)) *
FROM s_table ORDER BY nid
)A
)
ORDER BY nid
END
EXEC pro_page2 10,2
3、rownumber 内置函数---效率最高
代码
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v4.7.11
* Time: 2010-3-22 22:24:18
************************************************************/
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v4.7.11
* Time: 2010-3-22 22:23:58
************************************************************/
-- ================================================
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--rownumber over:为sqlserver2005内置函数,功能为范围制定查询的连续顺序号
-- =============================================
alter PROCEDURE pro_page3
@pagesize INT,
@pageindex INT
AS
BEGIN
SELECT TOP(@pagesize) *
FROM (
SELECT nid,
ROW_NUMBER() OVER(ORDER BY nid) AS rownumber
FROM s_table
)a
WHERE rownumber > (@pagesize * (@pageindex -1))
END
GO
--
* Code formatted by SoftTree SQL Assistant ?v4.7.11
* Time: 2010-3-22 22:24:18
************************************************************/
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v4.7.11
* Time: 2010-3-22 22:23:58
************************************************************/
-- ================================================
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--rownumber over:为sqlserver2005内置函数,功能为范围制定查询的连续顺序号
-- =============================================
alter PROCEDURE pro_page3
@pagesize INT,
@pageindex INT
AS
BEGIN
SELECT TOP(@pagesize) *
FROM (
SELECT nid,
ROW_NUMBER() OVER(ORDER BY nid) AS rownumber
FROM s_table
)a
WHERE rownumber > (@pagesize * (@pageindex -1))
END
GO
--
二、简单C#代码实现
前台页面：
代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div><a href="http://www.163.com">xinlang</a></div>
<div>
<asp:GridView ID="GridView1" runat="server"
onrowdatabound="GridView1_RowDataBound">
</asp:GridView>
</div>
<div>
<asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">首页</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="LinkButton3" runat="server" onclick="LinkButton3_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="LinkButton4" runat="server" onclick="LinkButton4_Click">尾页</asp:LinkButton>
</div>
</form>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div><a href="http://www.163.com">xinlang</a></div>
<div>
<asp:GridView ID="GridView1" runat="server"
onrowdatabound="GridView1_RowDataBound">
</asp:GridView>
</div>
<div>
<asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">首页</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="LinkButton3" runat="server" onclick="LinkButton3_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="LinkButton4" runat="server" onclick="LinkButton4_Click">尾页</asp:LinkButton>
</div>
</form>
</body>
</html>
后台代码：
代码
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
private static string contring = "Data Source=LENOVO-FENGJUNJ;Initial Catalog=test;User ID=sa";
private static int TotalCount = 10;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GDataBind(1);
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
//首页
this.GDataBind(1);
}
/// <summary>
/// 数据绑定
/// </summary>
/// <param name="pageindex"></param>
private void GDataBind(int pageindex)
{
int pagesize = 10;
SqlConnection con = new SqlConnection(contring);
con.Open();
SqlCommand commd = new SqlCommand();
commd.Connection = con;
commd.CommandType = CommandType.StoredProcedure;
commd.CommandText = "pro_page3";
commd.Parameters.Add("@pageindex", SqlDbType.Int, 50);
commd.Parameters.Add("@pagesize", SqlDbType.Int, 50);
commd.Parameters["@pageindex"].Value = pageindex;
commd.Parameters["@pagesize"].Value = pagesize;
SqlDataAdapter sd = new SqlDataAdapter(commd);
DataTable dt=new DataTable();
sd.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
if (this.pageindex >= TotalCount)
{
LinkButton3.Enabled = false;
}
else
{
if (this.pageindex<= 1)
{
LinkButton2.Enabled = false;
}
else
{
LinkButton2.Enabled=true;
}
}
}
private int pageindex
{
get
{ if (ViewState["pageindex"]==null)
{
ViewState["pageindex"]=1;
}
return Convert.ToInt32(ViewState["pageindex"].ToString());
}
set
{
ViewState["pageindex"] = value;
}
}
protected void LinkButton2_Click(object sender, EventArgs e)
{
//上一页
this.pageindex--;
this.GDataBind(pageindex);
}
protected void LinkButton3_Click(object sender, EventArgs e)
{
//下一页
this.pageindex++;
this.GDataBind(pageindex);
}
protected void LinkButton4_Click(object sender, EventArgs e)
{
this.pageindex = TotalCount;
this.GDataBind(pageindex);
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
}
}
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
private static string contring = "Data Source=LENOVO-FENGJUNJ;Initial Catalog=test;User ID=sa";
private static int TotalCount = 10;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GDataBind(1);
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
//首页
this.GDataBind(1);
}
/// <summary>
/// 数据绑定
/// </summary>
/// <param name="pageindex"></param>
private void GDataBind(int pageindex)
{
int pagesize = 10;
SqlConnection con = new SqlConnection(contring);
con.Open();
SqlCommand commd = new SqlCommand();
commd.Connection = con;
commd.CommandType = CommandType.StoredProcedure;
commd.CommandText = "pro_page3";
commd.Parameters.Add("@pageindex", SqlDbType.Int, 50);
commd.Parameters.Add("@pagesize", SqlDbType.Int, 50);
commd.Parameters["@pageindex"].Value = pageindex;
commd.Parameters["@pagesize"].Value = pagesize;
SqlDataAdapter sd = new SqlDataAdapter(commd);
DataTable dt=new DataTable();
sd.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
if (this.pageindex >= TotalCount)
{
LinkButton3.Enabled = false;
}
else
{
if (this.pageindex<= 1)
{
LinkButton2.Enabled = false;
}
else
{
LinkButton2.Enabled=true;
}
}
}
private int pageindex
{
get
{ if (ViewState["pageindex"]==null)
{
ViewState["pageindex"]=1;
}
return Convert.ToInt32(ViewState["pageindex"].ToString());
}
set
{
ViewState["pageindex"] = value;
}
}
protected void LinkButton2_Click(object sender, EventArgs e)
{
//上一页
this.pageindex--;
this.GDataBind(pageindex);
}
protected void LinkButton3_Click(object sender, EventArgs e)
{
//下一页
this.pageindex++;
this.GDataBind(pageindex);
}
protected void LinkButton4_Click(object sender, EventArgs e)
{
this.pageindex = TotalCount;
this.GDataBind(pageindex);
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
}
}
