datagrid存儲過程分頁完整實例
WebForm2.aspx页面
<%@ Page language="c#" Codebehind="WebForm2.aspx.cs" AutoEventWireup="false" Inherits="page.WebForm2" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm2</title>
<meta name="vs_showGrid" content="False">
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp
ataGrid id="CustomerList" runat="server" AutoGenerateColumns="False">
<Columns>
<asp
oundColumn DataField="id" HeaderText="id"></aspoundColumn>
<aspoundColumn DataField="text" HeaderText="text"></aspoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Right" Mode="NumericPages"></PagerStyle>
</aspataGrid>
<asp:LinkButton id="LinkButton1" runat="server" CommandName="up">LinkButton</asp:LinkButton>
<asp:LinkButton id="LinkButton2" runat="server" CommandName="down">LinkButton</asp:LinkButton>
<asp:Label id="Label1" runat="server"></asp:Label>
</form>
</body>
</HTML>
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
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.Configuration;
namespace page
{
/// <summary>
/// WebForm2 的摘要说明。
/// </summary>
public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid CustomerList;
protected System.Web.UI.WebControls.LinkButton LinkButton1;
protected System.Web.UI.WebControls.LinkButton LinkButton2;
protected System.Web.UI.WebControls.Label Label1;
private function p = new function();
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
TimeSpan startTime = DateTime.Now.TimeOfDay;
if (!Page.IsPostBack)
Bind();
Label1.Text = DateTime.Now.TimeOfDay.Subtract(startTime).TotalMilliseconds.ToString();
}
private void Bind()
{
if (ViewState["PageIndex"] == null)
ViewState["PageIndex"] = 0;
SqlConnection MyConnection=new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
//string strKeyword=Keyword.Text.Trim().Replace("\'","\'\'");
//string strSalesId=Sales.SelectedItem.Value;
int RecordCount=CalcRecordCount();
//RecordNumber.Text=RecordCount.ToString();
//LblRecordNumber.Text=RecordCount.ToString();
string strExpress="Id<>0";
//if (strKeyword!="")
//strExpress=strExpress+" and (companyenname like '%"+strKeyword+"%' or companychname like '%"+strKeyword+"%' or Companyshortname like '%"+strKeyword+"%' or web like '%"+strKeyword+"%' or mainproduct like '%"+strKeyword+"%' or phone like '%"+strKeyword+"%' or memo like '%"+strKeyword+"%' or address like '%"+strKeyword+"%' or linkmanphone like '%"+strKeyword+"%')";
//if (strSalesId!="")
//strExpress=strExpress+" and salesid="+strSalesId;
SqlCommand MyCommand=new SqlCommand();
MyCommand.Connection=MyConnection;
MyCommand.CommandText="GetRecordFromPage";
MyCommand.CommandType=CommandType.StoredProcedure;
MyCommand.Parameters.Add("@tblName","test1");
MyCommand.Parameters.Add("@fldName","id");
MyCommand.Parameters.Add("@strWhere",strExpress);
MyCommand.Parameters.Add("@PageSize",CustomerList.PageSize);
MyCommand.Parameters.Add("@PageIndex",(int)ViewState["PageIndex"] + 1);
MyConnection.Open();
SqlDataReader MyReader = MyCommand.ExecuteReader();
CustomerList.VirtualItemCount = RecordCount;
CustomerList.DataSource = MyReader;
CustomerList.DataKeyField="id";
CustomerList.DataBind();
MyReader.Close();
MyConnection.Close();
MyConnection.Dispose();
}
private int CalcRecordCount()
{
string sql = "select count(id) from test1";
return (int)p.ExecuteScalar(sql);
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.LinkButton1.Command += new System.Web.UI.WebControls.CommandEventHandler(this.up);
this.LinkButton2.Command += new System.Web.UI.WebControls.CommandEventHandler(this.up);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void up(object sender, System.Web.UI.WebControls.CommandEventArgs e)
{
LinkButton btn = (LinkButton)sender;
switch(btn.CommandName)
{
case "up":
ViewState["PageIndex"] = (int)ViewState["PageIndex"] - 1;
break;
case "down":
ViewState["PageIndex"] = (int)ViewState["PageIndex"] + 1;
break;
}
Bind();
}
}
}
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(2000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(1000) -- 排序类型
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where (" + @strWhere + ") "
+ @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder
if @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
end
if @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
exec (@strSQL)
GO
修正一下自己的错误
TimeSpan startTime = DateTime.Now.TimeOfDay; //《－语句条A
if (!Page.IsPostBack)
Bind();
Label1.Text = DateTime.Now.TimeOfDay.Subtract(startTime).TotalMilliseconds.ToString();//《－语句条B
语句条A和B应该放在Bind()子程式的首和尾。
程式修改后的运行时间是每次翻页都为200毫秒多点。
用DATAGRID自带的分页控件进行分页实测100W数据行，翻页时间基本为5000-13000毫秒，主要用DATASET分页。
用存储过程分页本机[内存+虚拟内存]消耗大概
aspnet_wp.exe 实际内存:26,440k 虚拟内存:17,608k
sqlservr.exe 实际内存:26,752k 虚拟内存:29,092k
用.NET的DATASET分页，本机[内存+虚拟内存]消耗大概
aspnet_wp.exe 实际内存:73,512k 虚拟内存:91,352k
注:每次翻页因为DataSet会把数据输入到内存中，所以aspnet_wp.exe消耗的实际内存和虚拟内存相加会高达近400,000k
也就是越翻页，占用内存越多，当然.net会自己释放内存，
但消耗过多的内存也使计算机运行速度变慢了很多。
sqlservr.exe 实际内存:39,664k 虚拟内存:51,132k
