原创--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 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)
        {
        
        }

        
    }
}

转载于:https://www.cnblogs.com/Dragon-China/archive/2007/03/06/665180.html