漫漫技术人生路

C#

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

VisualC # .NET 中如何通过 SQLServer 动态页通过 ASP.NET 中大结果集存储过程

注意:这篇文章是由无人工介入的自动的机器翻译系统翻译完成。这些文章是微软为不懂英语的用户提供的, 以使他们能够理解这些文章的内容。微软不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的使用所引起的任何直接的, 或间接的可能的问题负责。
文章编号 : 829142
最后修改 : 2004年8月20日
修订 : 1.2

概要

本文介绍如何使用 SQL 存储过程来通过大型结果集 Microsoft ASP.NET 中动态页。

实现的方法

有三种主要分页技术, 您可以使用 ASP.NET 中要显示的结果集有限子集, 来滚动这个有限子集

这些技术, 以及其优点和缺点, 如下:
1. 第一种方法是: 选择整个结果集每次, 并且将放弃对您不想显示记录。 这是最有效分页技术。
2. 如果结果集是昂贵以生成二方法很有用。 此技术, 中您存储在缓存, 通过在 ASP.NET 进程, 或数据库, 中其他表中使用会话变量结果集 (或主键值), 然后从结果集中阅读适当行。 如果结果集是相当小, 因为它是最好实现机制以数据超此占用存储技术效果最佳。 此技术是另一个缺点是可成为陈旧数据。
3. 三技巧是, 动态更改查询以选择仅记录进行下一页的数据所需要。 通过使用本文中提供, 示例代码演示此技术。 此技术 salient 功能如下:
您可动态设置页面大小。
您可以筛选数据。
您使用存储过程代替动态 SQL 来限制对表访问。
结果排序多个字段上而不在主键。
排序字段可包含重复。

通过 SQL 查询动态改变页面

下面示例应用程序阐释分页通过按学生名称、 姓氏、 名字和中间初始排序, 学生记录。 页面大小 25、 100 和 500 记录之间变化。

局域网 (LAN) 上那些可选择大页面大小时 注意 与慢连接用户可选择小页面大小。

创建表架构

以下 SQL 查询创建基本表架构。 SchoolID 字段和 AreaID 字段用于筛选特定学到特定学校或结果。 要创建表架构, 请按照下列步骤:
1. 单击 开始
2. 指向 程序 , 指向 MicrosoftSQLServer , 依次 查询分析器
3. 选择 sqlServernameSQLServer 中。 其中 sqlServername 是 SQLServer 服务器的名称
4. 键入您 loginName 并且 密码 在相应文本框中, 然后单击 确定
5. 查询 选项卡, 单击 更改数据库
6. 选择 Northwind 数据库, 并单击 确定
7. 以下代码复制到文本框中 查询
CREATE TABLE [dbo].[Students] (
	[StudentID] [int] IDENTITY (1, 1) NOT NULL ,
	[AreaID] [int] NULL ,
	[SchoolID] [int] NULL ,
	[TeacherID] [int] NULL ,
	[FirstName] [varchar] (20) NOT NULL ,
	[MI] [varchar] (5) NULL ,
	[LastName] [varchar] (20) NOT NULL ,	
	[Address] [varchar] (50) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Students] WITH NOCHECK ADD 
	CONSTRAINT [PK_Students] PRIMARY KEY  NONCLUSTERED 
	(
		[StudentID]
	)  ON [PRIMARY] 
GO
8. 查询 选项卡, 单击 执行

创建存储过程

1. 以下代码复制到文本框是查询分析器 查询 来创建 NextStudentPage 存储过程:
CREATE PROCEDURE NextStudentPage

@SID INT,
@PageSize INT = 1,
@AreaID INT = NULL,
@SchoolID INT = NULL
AS

DECLARE @LName VARCHAR(20)
DECLARE @FName VARCHAR(20)
DECLARE @MI VARCHAR(5)

/*
Locate additional parameter values for the last row of the current page.

You must do this because an ASP.NET Datagrid control only stores
key values and does not store additional field values that you need.
*/

IF @SID IS NULL
   SELECT @LName='', @FName='', @MI='', @SID=0
ELSE
   SELECT TOP 1 @LName = LastName, @FName = FirstName, @MI = MI
   FROM STUDENTS
   WHERE StudentID = @SID

/* RESTRICT THE NUMBER OF ROWS RETURNED. */

SET ROWCOUNT 25
IF @PageSize=2 SET ROWCOUNT 100
IF @PageSize=3 SET ROWCOUNT 500

/* Select the next page of data. */

SELECT * FROM STUDENTS
WHERE ((LastName>@LName)
   OR    (LastName=@LName AND FirstName>@FName)
   OR    (LastName=@LName AND FirstName=@FName AND MI>@MI)
   OR    (LastName=@LName AND FirstName=@FName AND MI=@MI AND StudentID>@SID))
AND   (@AreaID IS NULL  OR AreaID=@AreaID)
AND   (@SchoolID IS NULL OR SchoolID=@SchoolID)
ORDER BY LastName, FirstName, MI, StudentID

/* TURN OFF THE ROWCOUNT LIMIT. */

SET ROWCOUNT 0
2. 查询 选项卡, 单击 执行
3. 以下代码复制到文本框是查询分析器 查询 来创建 PrevStudentPage 存储过程:
CREATE PROCEDURE PrevStudentPage

@SID INT,
@PageSize INT = 1,
@AreaID INT = NULL,
@SchoolID INT = NULL
AS

DECLARE @LName VARCHAR(20)
DECLARE @FName VARCHAR(20)
DECLARE @MI VARCHAR(5)

/*
Locate additional parameter values for the first row of the current page.

You must do this because an ASP.NET Datagrid control only stores
key values and does not store additional field values that you need.
*/

IF @SID IS NULL
   /* SELECT A VALUE BEYOND THE LAST RECORD. */
   SELECT TOP 1 @LName = LastName, @FName = FirstName, @MI = MI, @SID = StudentID + 1
   FROM STUDENTS
   ORDER BY LastName DESC, FirstName DESC, MI DESC, StudentID DESC
ELSE
   SELECT TOP 1 @LName = LastName, @FName = FirstName, @MI = MI
   FROM STUDENTS
   WHERE StudentID = @SID

/* Restrict the number of rows returned. */

SET ROWCOUNT 25
IF @PageSize=2 SET ROWCOUNT 100
IF @PageSize=3 SET ROWCOUNT 500

/* Select the previous page of data - This returns in descending order. */

SELECT * INTO #TempStudent FROM STUDENTS
WHERE ((LastName<@LName)
   OR    (LastName=@LName AND FirstName<@FName)
   OR    (LastName=@LName AND FirstName=@FName AND MI<@MI)
   OR    (LastName=@LName AND FirstName=@FName AND MI=@MI AND StudentID<@SID))
AND   (@AreaID IS NULL  OR AreaID=@AreaID)
AND   (@SchoolID IS NULL OR SchoolID=@SchoolID)
ORDER BY LastName DESC, FirstName DESC, MI DESC, StudentID DESC

/* Reorder the records in ascending order. */

SELECT * FROM #TempStudent ORDER BY LastName, FirstName, MI, StudentID

/* Clean up the database. */

SET ROWCOUNT 0
DROP TABLE #TempStudent
4. 查询 选项卡, 单击 执行

创建 ASP.NETWeb 应用程序

1. 启动 MicrosoftVisualStudio.NET。
2. 文件 菜单, 指向 新建 , 然后单击 项目
3. 单击 项目类型 , 下 VisualC # 项目 , 然后单击 模板 ASP.NETWebApplication@@@
4. 命名 PagingTest 项目。 默认情况下, 创建 WebForm 1 .aspx。
5. 右击 WebForm 1 , 然后单击 查看 HTML 源
6. 用以下代码替换现有代码:
<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="PagingTest.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
   <HEAD>
      <title>WebForm1</title>      
   </HEAD>
   <body MS_POSITIONING="GridLayout">
      <form id="Form1" method="post" runat="server">
         <asp:button id="Button1" style="Z-INDEX: 101; LEFT: 309px; 
         POSITION: absolute; TOP: 233px" runat="server" Text="Button"></asp:button>
         <asp:label id="Label1" style="Z-INDEX: 102; LEFT: 310px;
         POSITION: absolute; TOP: 190px" runat="server">Click Button to add Records</asp:label>
       </form>
   </body>
</HTML>
7. 右击 WebForm 1 , 并单击 查看代码。
8. 用以下代码替换现有代码:
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 PagingTest
{
	/// <summary>
	/// Summary description for WebForm1.
	/// </summary>
	public class WebForm1 : System.Web.UI.Page
	{
         
      protected System.Web.UI.WebControls.Button Button1;
      protected System.Data.SqlClient.SqlConnection sqlConnection1;     
      string [] Fname = new string[5] {"Jhon","Martin","Rob","Leo","Amey"};
      string [] Lname = new string[5] {"Thompson","McMillan","Rob","King","Starr"};
      string [] MI    = new string[5] {"M","R","B","K","P"};
      int AreaIDv;
      int SchoolIDv;
      int TeacherIDv;    
      string Addressv; 
      string insertCmd ="";
      SqlCommand myCommand;
      protected System.Web.UI.WebControls.Label Label1;
      SqlConnection Conn;

		private void Page_Load(object sender, System.EventArgs e)
		{		
	      Label1.Visible=true;         
         //string Fname1=Fname[2];              
		}

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: The ASP.NET Web Form Designer needs this call.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
         this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
         this.Button1.Click += new System.EventHandler(this.Button1_Click);
         // 
         // sqlConnection1
         // 
         this.sqlConnection1.ConnectionString = "data source= Servername;initial catalog=pubs;" +
            "persist security info=False;user id=sa;packet size=4096";
         this.Load += new System.EventHandler(this.Page_Load);

      }
		#endregion

      private void Button1_Click(object sender, System.EventArgs e)
      { 
         Conn = new SqlConnection("server= Servername;uid=sa;pwd=sa;database=Northwind;");
         Conn.Open();
         for(int i=0;i<1000;i++)
         {    
            Random rn = new Random();
            int  j=0;
            j = rn.Next(1,5);           
            AreaIDv = rn.Next(1,10);   
            TeacherIDv=rn.Next(10,20);  
            SchoolIDv=rn.Next(1,10);  
            Addressv = rn.Next(101, 1999) + " "  + " St.";

            insertCmd = "insert into Students (FirstName,LastName,MI,AreaID,SchoolID,TeacherID,Address)"+
               "values ('"+Fname[j]+"','"+Lname[j]+"','"+MI[j]+"',"+AreaIDv+","+SchoolIDv+","+
               ""+TeacherIDv+",'"+Addressv+"')";                  

            myCommand = new SqlCommand(insertCmd, Conn);
            myCommand.ExecuteNonQuery(); 
         }       
         Conn.Close();
         Label1.Text="Records are Added in Database";
      }
  }
}
9. 生成 菜单上, 单击 BuildSolution@@@
10. 在 SolutionExplorer@@, 右击 PagingTest , 指向 添加 , 依次 添加 Web 窗体
11. 名称 文本框中, 键入 Paging.aspx , 然后单击 打开
12. 右击 Paging.aspx , 然后单击 查看 HTML 源
13. 用以下代码替换现有代码:
<%@ Page language="c#" Codebehind="Paging.aspx.cs" AutoEventWireup="false" Inherits="PagingTest.WebForm2" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
   <HEAD>
      <title>Paging</title>  
   </HEAD>
   <body MS_POSITIONING="GridLayout">
      <form id="WebForm2" method="post" runat="server">
         <asp:datagrid id="DataGrid1" style="Z-INDEX: 100; LEFT: 186px;
         POSITION: absolute; TOP: 265px" runat="server"></asp:datagrid>
         <asp:label id="Label3" style="Z-INDEX: 111; LEFT: 5px; 
         POSITION: absolute; TOP: 117px" runat="server">
         Enter  2 - for Page length 100 lines . 3 for Page length 500 lines </asp:label>
         <asp:label id="Label2" style="Z-INDEX: 110; LEFT: 7px;
         POSITION: absolute; TOP: 91px" runat="server">
         Enter School Code to Filter</asp:label>
         <asp:textbox id="txtArea" style="Z-INDEX: 101; LEFT: 211px;
         POSITION: absolute; TOP: 38px" runat="server" Width="52px"></asp:textbox>
         <asp:textbox id="txtSchool" style="Z-INDEX: 102; LEFT: 210px; 
         POSITION: absolute; TOP: 85px" runat="server" Width="52px"></asp:textbox>
         <asp:textbox id="txtPageSize" style="Z-INDEX: 103; LEFT: 209px; 
         POSITION: absolute; TOP: 144px" runat="server" Width="54px"></asp:textbox>
         <asp:label id="lblEOF" style="Z-INDEX: 104; LEFT: 344px;
         POSITION: absolute; TOP: 225px" runat="server">
         No records available for this query</asp:label>
         <asp:button id="btnFirst" style="Z-INDEX: 105; LEFT: 176px;
         POSITION: absolute; TOP: 175px" runat="server" Text="ButtonFirst"></asp:button>
         <asp:button id="btnNext" style="Z-INDEX: 106; LEFT: 293px;
         POSITION: absolute; TOP: 177px" runat="server" Text="ButtonNext"></asp:button>
         <asp:button id="btnPrev" style="Z-INDEX: 107; LEFT: 419px;
         POSITION: absolute; TOP: 176px" runat="server" Text="ButtonPrev"></asp:button>
         <asp:button id="btnLast" style="Z-INDEX: 108; LEFT: 551px;
         POSITION: absolute; TOP: 178px" runat="server" Text="ButtonLast"></asp:button>
         <asp:Label id="Label1" style="Z-INDEX: 109; LEFT: 8px;
         POSITION: absolute; TOP: 35px" runat="server">Enter Area Code to Filter</asp:Label>
       </form>
   </body>
</HTML>
14. 右击 Paging.aspx , 然后单击 查看代码
15. 用以下代码替换现有代码:
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 PagingTest
{
	/// <summary>
	/// Summary description for Paging.
	/// </summary>
  public class Paging : System.Web.UI.Page
 {
      protected System.Web.UI.WebControls.TextBox txtArea;
      protected System.Web.UI.WebControls.TextBox txtSchool;
      protected System.Web.UI.WebControls.TextBox txtPageSize;
      protected System.Web.UI.WebControls.Label lblEOF;
      protected System.Web.UI.WebControls.Button btnFirst;
      protected System.Web.UI.WebControls.Button btnNext;
      protected System.Web.UI.WebControls.Button btnPrev;
      protected System.Web.UI.WebControls.Button btnLast;
      protected System.Web.UI.WebControls.DataGrid DataGrid1;

      SqlConnection cnNwind;
      SqlCommand cmdNext ;
      protected System.Web.UI.WebControls.Label Label1;
      protected System.Web.UI.WebControls.Label Label2;
      protected System.Web.UI.WebControls.Label Label3;
      SqlCommand cmdPrev ;

      private void Page_Load(object sender, System.EventArgs e)
      {
         // Put user code to initialize the page here
         if (this.IsPostBack == false) btnFirst_Click(null,null);
      }

      private void btnFirst_Click(object sender, System.EventArgs e)
      {
         cmdNext.Parameters["@SID"].Value = System.DBNull.Value;
         cmdNext.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
         cmdNext.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
         cmdNext.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
         RetrieveData(cmdNext);
      }

      private void btnPrev_Click(object sender, System.EventArgs e)
      {
         if (DataGrid1.DataKeys.Count == 0)
            cmdPrev.Parameters["@SID"].Value = System.DBNull.Value;
         else
            cmdPrev.Parameters["@SID"].Value = (int)(DataGrid1.DataKeys[0]);
			
         cmdPrev.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
         cmdPrev.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
         cmdPrev.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
         RetrieveData(cmdPrev);
      }

      private void btnNext_Click(object sender, System.EventArgs e)
      {
         int Count;
         Count = DataGrid1.DataKeys.Count;

         if (Count == 0)
            cmdNext.Parameters["@SID"].Value = System.DBNull.Value;
         else
            cmdNext.Parameters["@SID"].Value = (int)(DataGrid1.DataKeys[Count - 1]);

         cmdNext.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
         cmdNext.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
         cmdNext.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
         RetrieveData(cmdNext);
      }

      private void btnLast_Click(object sender, System.EventArgs e)
      {
         cmdPrev.Parameters["@SID"].Value = System.DBNull.Value;
         cmdPrev.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
         cmdPrev.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
         cmdPrev.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
         RetrieveData(cmdPrev);
      }

      private object FixNumber(string inValue)
      {
         int Value;
         try
         {
            Value = int.Parse(inValue);
            return Value;
         }
         catch
         {
            return System.DBNull.Value;
         }
      }

      private void RetrieveData(SqlCommand cmd)
      {
         SqlDataReader dr;
         try
         {
            cnNwind.Open();
            dr = cmd.ExecuteReader();           
            DataGrid1.DataSource=dr;
            DataGrid1.DataKeyField="StudentID";
            DataGrid1.DataBind();
            if (DataGrid1.Items.Count>0)
            {
               DataGrid1.Visible=true;
               lblEOF.Visible=false;
            }
            else
            {
               DataGrid1.Visible=false;
               lblEOF.Visible=true;
            }
            dr.Close();
         }
         catch(Exception e1)
         {
            Response.Write(e1.ToString());
            // display error message in a label control
            // must be made invisible in the try block
         }
         finally
         {
            if (cnNwind.State != ConnectionState.Closed) cnNwind.Close();
         }
      }

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: The ASP.NET Web Form Designer needs this call.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
      private void InitializeComponent()
      {    
         this.cnNwind = new System.Data.SqlClient.SqlConnection();
         this.cmdNext = new System.Data.SqlClient.SqlCommand();
         this.cmdPrev = new System.Data.SqlClient.SqlCommand();
         // 
         // cnNwind
         // 
         this.cnNwind.ConnectionString = "server=servername ;uid=sa;pwd=sa;database=Northwind;";
         // 
         // cmdNext
         // 
         this.cmdNext.CommandText = "[NextStudentPage]";
         this.cmdNext.CommandType = System.Data.CommandType.StoredProcedure;
         this.cmdNext.Connection = this.cnNwind;
         this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, 
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SID", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageSize", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@AreaID", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SchoolID", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         // 
         // cmdPrev
         // 
         this.cmdPrev.CommandText = "[PrevStudentPage]";
         this.cmdPrev.CommandType = System.Data.CommandType.StoredProcedure;
         this.cmdPrev.Connection = this.cnNwind;
         this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, 
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SID", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageSize", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@AreaID", 
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SchoolID",
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
            ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
         this.btnFirst.Click += new System.EventHandler(this.btnFirst_Click);
         this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
         this.btnPrev.Click += new System.EventHandler(this.btnPrev_Click);
         this.btnLast.Click += new System.EventHandler(this.btnLast_Click);
         this.Load += new System.EventHandler(this.Page_Load);

      }

		#endregion
     
  }
}
16. 生成 菜单上, 单击 BuildSolution@@@
posted on 2006-09-22 12:12  javaca88  阅读(261)  评论(0编辑  收藏  举报