DataGrid中的确是带有自动分页功能，但为什么还要花时间来做手动分页呢？ 大家都知道，DataGrid中的数据绑定是一下子从数据库中全部取出，存放在服务器内存中。如果是少量的数据，服务还能承受得了，若是海量数据，恐怕是非得把服务器累垮不可!
说到这,应该明白我今天来谈DataGrid分页的目的了,是否有更方便快捷,而更节约资源,减小网络流量的分页方法呢? 下面我们来谈谈,使用存储过程实现DataGrid分页.
原创--DataGrid自动分页例子，通过存储过程
通过存储过程来进行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 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)
{
}
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/vipxiaotian/archive/2008/01/02/2009996.aspx