DataGrid中分页优化

时间:2022-03-03 09:46:00

 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