1。数据库结构为：(在SQL当中建立一个数据库后，直接在SQL结构查询器当中执行以下SQL脚本)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mobile]') and OBJECTPROPERTY(id, N'IsUserTable') = 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=N'SELECT @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 ( N'SELECT 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;
&nbs