通过存储过程来进行DataGrid自动分页,效率很高,可以进行百万和千万级的分页 自己通过50万条记录测试,翻至任何页,需时小于一秒 呵呵,仅供自己以后察看使用,所以代码写的不很规范 所需的存储过程如下:
CREATE PROCEDURE UP_GetRecordByPage @tblName varchar(
255
),
--
表名 @fldName varchar(
255
),
--
主键字段名 @PageSize
int
=
10
,
--
页尺寸 @PageIndex
int
=
1
,
--
页码 @IsReCount bit
=
1
,
--
返回记录总数, 非
0
值则返回 @OrderType bit
=
0
,
--
设置排序类型, 非
0
值则降序 @strWhere varchar(
1000
)
=
''
--
查询条件 (注意: 不要加 where) AS declare @strSQL varchar(
6000
)
--
主语句 declare @strTmp varchar(
100
)
--
临时变量 declare @strOrder varchar(
400
)
--
排序类型
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
@IsReCount
!=
0
set
@strSQL
=
'
select count(*) as Total from [
'
+
@tblName
+
'
]
'
+
'
where
'
+
@strWhere exec (@strSQL) GO
Windows C# 页面代码
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
_0921test
{ /**/ /// <summary> /// DataGridMostDataDisplay 的摘要说明。 /// </summary> public class DataGridMostDataDisplay : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label2; protected System.Web.UI.WebControls.DataGrid dgShowC; protected System.Web.UI.HtmlControls.HtmlForm Form1; protected System.Web.UI.WebControls.TextBox TextBox1; protected System.Web.UI.WebControls.Button btnGOTO; protected System.Web.UI.WebControls.ImageButton ibtnFirstPage; protected System.Web.UI.WebControls.ImageButton ibtnPrevousPage; protected System.Web.UI.WebControls.ImageButton ibtnNextPage; protected System.Web.UI.WebControls.ImageButton ibtnLastPage; protected System.Web.UI.WebControls.Label Label1; protected System.Data.SqlClient.SqlConnection sqlConnection1; static int Records = 0 ; // 记录总数 int PageSize = 10 ; // 页大小 static int PageIndex = 1 ; // 当前页 static int PageCount = 0 ; protected System.Web.UI.WebControls.Label Label3; protected System.Web.UI.WebControls.Label Label4; protected System.Web.UI.WebControls.Label Label5; string strWhere = "" ; protected System.Web.UI.WebControls.Button btnBindData; protected System.Web.UI.WebControls.TextBox txtShipAddress; protected System.Web.UI.WebControls.TextBox txtShipName; static string strWhereO = "" ; private void Page_Load( object sender, System.EventArgs e) { // 在此处放置用户代码以初始化页面 } Web 窗体设计器生成的代码 #region Web 窗体设计器生成的代码 override protected void OnInit(EventArgs e) { // // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。 // InitializeComponent(); base .OnInit(e); } /**/ /// <summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 /// </summary> private void InitializeComponent() { this .sqlConnection1 = new System.Data.SqlClient.SqlConnection(); this .btnBindData.Click += new System.EventHandler( this .btnBindData_Click); this .dgShowC.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler( this .dgShowC_ItemCreated); this .dgShowC.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler( this .dgShowC_PageIndexChanged); this .dgShowC.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler( this .dgShowC_ItemDataBound); this .ibtnFirstPage.Click += new System.Web.UI.ImageClickEventHandler( this .ibtnFirstPage_Click); this .ibtnPrevousPage.Click += new System.Web.UI.ImageClickEventHandler( this .ibtnPrevousPage_Click); this .ibtnNextPage.Click += new System.Web.UI.ImageClickEventHandler( this .ibtnNextPage_Click); this .ibtnLastPage.Click += new System.Web.UI.ImageClickEventHandler( this .ibtnLastPage_Click); this .btnGOTO.Click += new System.EventHandler( this .btnGOTO_Click); // // sqlConnection1 // this .sqlConnection1.ConnectionString = " workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGS " + " Z;persist security info=False;initial catalog=Northwind " ; this .Load += new System.EventHandler( this .Page_Load); } #endregion private void BindGridC() { string connectionString = " workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind " ; string sqlstr = " SELECT * FROM Test where TID < 1000 " ; try { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter Dta = new SqlDataAdapter(sqlstr,connection); Dta.Fill(ds, " ds " ); Dta.Dispose(); this .dgShowC.DataSource = ds; this .dgShowC.VirtualItemCount = 5 ; this .dgShowC.DataBind(); Records = ds.Tables[ 0 ].Rows.Count; this .Label1.Text = " 共有: " + Records.ToString() + " 记录 " ; this .Label2.Text = " 页数: " + PageIndex + " / " + Records / 20 ; } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } } } catch (SqlException SQLexc) { Response.Write( " 提取数据时出现错误: " + SQLexc.ToString()); } } // 分页获取数据列表 public DataSet GetList( int PageSize, int PageIndex, string strWhere) { SqlParameter[] parameters = { new SqlParameter( " @tblName " , SqlDbType.VarChar, 255 ), new SqlParameter( " @fldName " , SqlDbType.VarChar, 255 ), new SqlParameter( " @PageSize " , SqlDbType.Int), new SqlParameter( " @PageIndex " , SqlDbType.Int), new SqlParameter( " @IsReCount " , SqlDbType.Bit), new SqlParameter( " @OrderType " , SqlDbType.Bit), new SqlParameter( " @strWhere " , SqlDbType.VarChar, 1000 ), } ; parameters[ 0 ].Value = " Test " ; parameters[ 1 ].Value = " TID " ; parameters[ 2 ].Value = PageSize; parameters[ 3 ].Value = PageIndex; parameters[ 4 ].Value = 0 ; parameters[ 5 ].Value = 0 ; parameters[ 6 ].Value = strWhere; return RunProcedure( " UP_GetRecordByPage " ,parameters, " ds " ); } // 分页获取数据列表 public DataSet GetListC( int PageSize, int PageIndex, string strWhere) { SqlParameter[] parameters = { new SqlParameter( " @tblName " , SqlDbType.VarChar, 255 ), new SqlParameter( " @fldName " , SqlDbType.VarChar, 255 ), new SqlParameter( " @PageSize " , SqlDbType.Int), new SqlParameter( " @PageIndex " , SqlDbType.Int), new SqlParameter( " @IsReCount " , SqlDbType.Bit), new SqlParameter( " @OrderType " , SqlDbType.Bit), new SqlParameter( " @strWhere " , SqlDbType.VarChar, 1000 ), } ; parameters[ 0 ].Value = " Test " ; parameters[ 1 ].Value = " TID " ; parameters[ 2 ].Value = PageSize; parameters[ 3 ].Value = PageIndex; parameters[ 4 ].Value = 1 ; parameters[ 5 ].Value = 0 ; parameters[ 6 ].Value = strWhere; return RunProcedure( " UP_GetRecordByPage " ,parameters, " ds " ); } // 创建 SqlCommand 对象实例(用来返回一个整数值) private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters ); command.Parameters.Add( new SqlParameter ( " ReturnValue " , SqlDbType.Int, 4 ,ParameterDirection.ReturnValue, false , 0 , 0 , string .Empty,DataRowVersion.Default, null )); return command; } /**/ /// 执行存储过程,返回影响的行数 /// </summary> /// <param name="storedProcName"> 存储过程名 </param> /// <param name="parameters"> 存储过程参数 </param> /// <param name="rowsAffected"> 影响的行数 </param> /// <returns></returns> public static int RunProcedure( string storedProcName, IDataParameter[] parameters, out int rowsAffected ) { string connectionString = " workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind " ; using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection,storedProcName, parameters ); rowsAffected = command.ExecuteNonQuery(); result = ( int )command.Parameters[ " ReturnValue " ].Value; // Connection.Close(); return result; } } // 执行存储过程 public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName ) { string connectionString = " workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind " ; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); sqlDA.Fill( dataSet, tableName ); connection.Close(); return dataSet; } } // 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值) private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand( storedProcName, connection ); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { command.Parameters.Add( parameter ); } return command; } // 可以得到@@RowCount public static object GetSingle( string SQLString) { string connectionString = " workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind " ; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString,connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null )) || (Object.Equals(obj,System.DBNull.Value))) { return null ; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw new Exception(e.Message); } } } } // 根据存储过程绑定 private void BindGridStore() { strWhere = " ShipName like " + " '% " + this .txtShipName.Text + " %' and ShipAddress like " + " '% " + this .txtShipAddress.Text + " %' " ; try { try { DataSet ds = GetList(PageSize,PageIndex,strWhere); this .dgShowC.DataSource = ds.Tables[ 0 ].DefaultView; this .dgShowC.DataBind(); if (strWhere != strWhereO) { DataSet dsrc = GetListC(PageSize,PageIndex,strWhere); if (dsrc.Tables[ 0 ].Columns.Count == 1 ) { Records = Convert.ToInt32(dsrc.Tables[ 0 ].Rows[ 0 ][ 0 ].ToString()); ; this .Label1.Text = " 总记录数: " + Records.ToString(); } strWhereO = strWhere; } PageCount = Records / 10 ; this .Label2.Text = " 当前页数: " + PageIndex + " / " + PageCount; } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } } catch (SqlException SQLexc) { Response.Write( " 提取数据时出现错误: " + SQLexc.ToString()); } } private void dgShowC_PageIndexChanged( object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e) { this .dgShowC.CurrentPageIndex = e.NewPageIndex; PageIndex = e.NewPageIndex + 1 ; this .BindGridC(); } // 控制四个翻页按钮的显示 private void ibtnVisible( bool first, bool previous, bool next, bool last) { this .ibtnFirstPage.Visible = first; this .ibtnPrevousPage.Visible = previous; this .ibtnNextPage.Visible = next; this .ibtnLastPage.Visible = last; } private void btnBindData_Click( object sender, System.EventArgs e) { // this.BindGridC(); this .BindGridStore(); PageIndex = 1 ; this .ibtnVisible( false , false , true , true ); } private void dgShowC_ItemDataBound( object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { // 鼠标移动到每项时颜色交替效果 if (e.Item.ItemType != ListItemType.Header) { e.Item.Attributes.Add( " OnMouseOut " , " this.style.backgroundColor='Transparent';this.style.color='Black' " ); e.Item.Attributes.Add( " OnMouseOver " , " this.style.backgroundColor='#cacee1';this.style.color='Blue' " ); } // 鼠标的形状为小手 e.Item.Attributes[ " style " ] = " Cursor:hand " ; } private void ibtnFirstPage_Click( object sender, System.Web.UI.ImageClickEventArgs e) { this .ibtnVisible( false , false , true , true ); PageIndex = 1 ; this .BindGridStore(); } private void ibtnPrevousPage_Click( object sender, System.Web.UI.ImageClickEventArgs e) { if (PageIndex == 2 ) { this .ibtnVisible( false , false , true , true ); PageIndex = 1 ; this .BindGridStore(); } else { this .ibtnVisible( true , true , true , true ); PageIndex = PageIndex - 1 ; this .BindGridStore(); } } private void ibtnNextPage_Click( object sender, System.Web.UI.ImageClickEventArgs e) { if (PageIndex == Records / 10 - 1 ) this .ibtnVisible( true , true , false , false ); else this .ibtnVisible( true , true , true , true ); PageIndex = PageIndex + 1 ; this .BindGridStore(); } private void ibtnLastPage_Click( object sender, System.Web.UI.ImageClickEventArgs e) { this .ibtnVisible( true , true , false , false ); PageIndex = PageCount; this .BindGridStore(); } private void dgShowC_ItemCreated( object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Pager) { foreach (Control c in e.Item.Cells[ 0 ].Controls) { if (c is Label) // 当前页数 { Label lblpage = (Label)c; // lblpage.ForeColor= System.Drawing.ColorTranslator.FromHtml("#e78a29"); // #e78a29 ,#FF0000 // lblpage.Font.Bold=true; lblpage.Text = " [<font color=#e78a29><b> " + lblpage.Text + " </b></font>] " ; // ((Label)c).ForeColor = System.Drawing.Color.Green; // break; } if (c is LinkButton) // 链接的其他页数 { LinkButton linkButton = (LinkButton)c; linkButton.Text = " [ " + linkButton.Text + " ] " ; } } } } // 跳转页面 private void btnGOTO_Click( object sender, System.EventArgs e) { PageIndex = System.Convert.ToInt32( this .TextBox1.Text); this .BindGridStore(); this .TextBox1.Text = "" ; } private void btnBindData_Click( object sender, System.EventArgs e) { } } }