一个简单的存储过程数据分页

作者： unknow

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;

   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>

返回顶部

下一篇：一个通过DataSet操作XML的类 >>