asp.net 数据分页控件（不用存储过程)

asp教程.net 数据分页控件（不用存储过程)

本分页控件原理

分页的方法很多，有用存储过程的，有不用存储过程的，还有在c#中用datagridview的虚拟模式的，目的只有一个，对大数据量进行处理，让用户体验得到提高，软件速度得到提升。本分页控件主要是用了下面的sql语句，我相信你能看懂的，存储过程分页也是用类似的sql代码：

 

 1 /*tablename           :表名            如:tbtestdata
 2   sqlwherestatement   :sql where表达式 如:where表达式为空
 3   primarykey          :主键            如:uniqueid
 4   pagesize            :分页大小        如:50
 5   pageindex           :当前页          如:8
 6   orderfield          :排序字段        如:insetdatatime
 7 */
 8
 9 select top 50 * from tbtestdata
10 where  uniqueid not in
11     (
12         select top (50 * 8) uniqueid  from tbtestdata  order by insetdatatime desc
13     )
14 order by insetdatatime  desc
 

原理就这么简单。

 

 

 

分页控件代码

       （一）、实例数据库教程代码

创建实例数据库。

 

create table [tbtestdata](
    [uniqueid] [bigint] not null,
    [companyname] [varchar](200) null,
    [companycode] [varchar](50) null,
    [address] [varchar](500) null,
    [owner] [varchar](100) null,
    [memo] [varchar](2000) null,
    [insetdatatime] [datetime] null,
 constraint [pk_tbtestdata] primary key clustered
(
    [uniqueid] asc
)with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary]
) on [primary]

go

alter table [dbo].[tbtestdata] add  constraint [df_tbtestdata_insetdatatime]  default (getdate()) for [insetdatatime]
go

--生成实例数据
declare @intrownumber int;
select @intrownumber = 1;
while @intrownumber < 1000000
begin
   
    insert into tbtestdata(uniqueid,companyname,companycode,address,owner,memo)
    values(@intrownumber,'companyname' + cast(@intrownumber as varchar(2000)),
           'companycode' + cast(@intrownumber as varchar(2000)),'address'+ cast(@intrownumber as varchar(2000)),
           'owner' + cast(@intrownumber as varchar(2000)),'memo' + cast(@intrownumber as varchar(2000)));   
    select @intrownumber = @intrownumber + 1
end
（二）、分页控件代码。

 

namespace dotnet.controls
{
    ///

    /// 分页控件（使用代码实现，不用存储过程）
    /// ucpagecontrolbycode
    /// 修改纪录
    ///    
    ///     2010-01-06 胡勇 修改转到某页由原来的keypress方法改为keydown，让用户按回车键确认转页，以防止连续绑定两次。
    ///     2011-01-06 胡勇 增加对分页控件的初始化代码:public datatable initializepagecontrol()。
    ///     2011-01-05 胡勇 创建分页控件
    ///     2011-04-02 胡勇 优化代码、减少不必要的私有变量，去掉多余的代码
    ///    
    ///
    ///     胡勇
    ///     80368704
    ///     [email protected]
    ///
    ///

    [toolboxitem(true)]
    [defaultevent("oneventpageclicked")]
    [toolboxbitmap(typeof(ucpagecontrolbycode), "images.ucpagecontrolbycodeicon.png")]
    [description("分页控件（使用代码实现，不用存储过程）")]
    public partial class ucpagecontrolbycode : usercontrol
    {
        #region 私有变量
        int recordcount = 0; //记录数
        int pagecount   = 0; //总页数
        int pageindex   = 0; //当前页
        #endregion

        #region 自定义事件
        ///

        /// 单击分页按钮(第一页、上一页、下一页、最后页、跳页)时发生
        ///

        [category("ucpagecontrolbycode"), description("单击分页按钮时发生")]
        public event eventhandler oneventpageclicked;
        #endregion

        #region 自定义属性
        private int    _pagesize          = 50;             //分页大小
        private string _sqlwherestatement = string.empty;   //mssql where语句
        private string _sqlconnstring     = string.empty;   //mssql 数据库连接字符串
        private string _tablename         = string.empty;   //表名       
        private string _orderfield        = string.empty;   //数据表的排序字段
        private string _primarykey        = string.empty;   //数据表的主键
        private string _queryfieldlist    = "*";            //字段列表（默认为：*)
        private datatable _pagetable      = new datatable();
      
        ///

        /// 返回当前页码
        ///

        public int pageindex
        {
            get
            {
                return pageindex + 1;
            }
        }

        ///

        /// 得到或设置分页大小(默认为:50)
        ///

        [browsable(true), category("ucpagecontrolbycode"), description("得到或设置分页大小(默认为:50)")]
        public int pagesize
        {
            get
            {
                return _pagesize;
            }
            set
            {
                _pagesize = value;
            }
        }
       
        ///

        /// sql语句的where表达式
        ///

        [browsable(false), category("ucpagecontrolbycode"), description("得到或设置sql语句的where表达式")]
        public string sqlwherestatement
        {
            get
            {
                return _sqlwherestatement;
            }
            set
            {
                _sqlwherestatement = value;
            }
        }
       
        ///

        /// 得到或设置sqlserver的连接字符串
        ///

        [browsable(false), category("ucpagecontrolbycode"), description("得到或设置sqlserver的连接字符串")]
        public string sqlconnstring
        {
            get
            {
                return _sqlconnstring;
            }
            set
            {
                _sqlconnstring = value;
            }
        }

        ///

        /// 得到用户单击分页按钮后返回的datatable
        ///

        [browsable(false), category("ucpagecontrolbycode"), description("得到用户单击分页按钮后返回的datatable")]
        public datatable pagetable
        {
            get
            {
                return _pagetable;
            }
        }
       
        ///

        /// 设置或得到与分页控件绑定的表名或视图名
        ///

        [browsable(true), category("ucpagecontrolbycode"), description("设置或得到与分页控件绑定的表名或视图名")]
        public string tablename
        {
            get
            {
                return _tablename;
            }
            set
            {
                _tablename = value;
            }
        }
       
        ///

        /// 设置或得到分页控件排序字段
        ///

        [browsable(true), category("ucpagecontrolbycode"), description("设置或得到分页控件排序字段")]
        public string orderfield
        {
            get
            {
                return _orderfield;
            }
            set
            {
                _orderfield = value;
            }
        }
       
        ///

        /// 设置或得到分页控件绑定数据表的主键
        ///

        [browsable(true), category("ucpagecontrolbycode"), description("设置或得到分页控件绑定数据表的主键")]
        public string primarykey
        {
            get
            {
                return _primarykey;
            }
            set
            {
                _primarykey = value;
            }
        }
       
        ///

        /// 设置或得到分页控件绑定的字段列表(默认为:*)
        ///

        [browsable(true), category("ucpagecontrolbycode"), description("设置或得到分页控件绑定的字段列表(默认为:*)")]
        public string queryfieldlist
        {
            get
            {
                return _queryfieldlist;
            }
            set
            {
                _queryfieldlist = value;
            }
        }
        #endregion

        #region 构造函数
        ///

        /// 分页控件（使用代码实现，不用存储过程）
        ///

        public ucpagecontrolbycode()
        {
            initializecomponent();
        }
        #endregion

        #region 分页实现相关代码

        #region void setucpagecontrolpars(string connstr, string wherestatement, string tbname, string orderfield, string primarykeyname, string fieldlist):给ucpagecontrolbycode控件传递必需参数
        ///

        /// 给ucpagecontrolbycode控件传递必需参数
        ///

        /// 连接字符串
        /// mssql where语句
        /// 数据表名或视力名
        /// 排序字段
        /// 主键值
        /// 字段列表(默认为:*)
        public void setucpagecontrolpars(string connstr, string wherestatement, string tbname
                                       , string orderfield, string primarykeyname, string fieldlist)
        {
            if (string.isnullorempty(connstr.trim()))
            {
                dialoghelper.showerrormsg("温馨提示：n无可用的数据库连接！");
                return;
            }
            else
            {
                this.sqlconnstring = connstr;
            }
            this.sqlwherestatement = wherestatement;
            this.tablename = tbname;
            this.orderfield = orderfield;
            this.primarykey = primarykeyname;
            if (!string.isnullorempty(fieldlist.trim()))
            {
                this.queryfieldlist = fieldlist;
            }
        }
        #endregion

        #region datatable initializepagecontrol():初始化ucpagecontrolbycode
        ///

        /// 绑定ucpagecontrolbycode(并返回包含当前页的datatable)
        ///

        /// datatable
        public datatable bindpagecontrol()
        {
            recordcount       = gettotalrecordcount();                           //获取总记录数 
            pagecount         = recordcount / pagesize - modpage();              //保存总页数（减去modpage()函数防止sql语句执行时溢出查询范围，可以用存储过程分页算法来理解这句）
            pageindex         = 0;                                               //保存一个为0的页面索引值到pageindex  
            lblpagecount.text = (recordcount / pagesize + overpage()).tostring();//显示lblpagecount、lblreccount的状态  
            lblreccount.text  = recordcount.tostring();
               
            if (recordcount <= pagesize)
            {
                txtgotopage.enabled = false;
            }
            else
            {
                txtgotopage.enabled = true;
            }

            return tdatabind(); 
        }
        #endregion
 

        #region 余页计算与总记录数
        ///

        /// 计算余页
        ///

        ///         
        private int overpage()
        {
            int returnvalue = 0;

            if (recordcount % pagesize != 0)
            {
                returnvalue = 1;
            }         

            return returnvalue;
        }

        ///

        /// 计算余页，防止sql语句执行时溢出查询范围  
        ///

        ///
        private int modpage()
        {
            int returnvalue = 0;

            if (recordcount % pagesize == 0 && recordcount != 0)
            {
                returnvalue = 1;
            }
           
            return returnvalue;
        }

        ///

        /// 计算总记录数
        ///

        /// 记录总数
        private int gettotalrecordcount()
        {
            int returnvalue     = 0;
            string sqlstatement = "select count(1) as rowscount from " + tablename;

            if (sqlwherestatement.trim().length > 0)
            {
                sqlstatement = "select count(1) as rowscount from " + tablename + " where " + sqlwherestatement;
            }

            sqldatareader dr = null;
            try
            {
                dr = dbhelpersql.executereader(sqlstatement, sqlconnstring);
                if (dr.read())
                {
                    returnvalue = int32.parse(dr["rowscount"].tostring());
                }
               
            }
            catch(exception ex)
            {
                dialoghelper.showerrormsg(ex.message);
            }
            finally
            {
                dr.close();
                dr.dispose();
            }

            return returnvalue;
        }
        #endregion

        #region datatable tdatabind():数据绑定
        private datatable tdatabind()
        {
            stringbuilder sbsqlstatement = new stringbuilder();
            bool isforward  = pageindex + 1 > 1;
            bool isbackward = (pageindex != pagecount);
            btnfirstpage.enabled = isforward;
            btnprevpage.enabled  = isforward;           
            btnnextpage.enabled  = isbackward;
            btnlastpage.enabled  = isbackward;           

            if (string.isnullorempty(sqlwherestatement.trim()))
            {
                sbsqlstatement.append("select top " + pagesize + "  " + queryfieldlist + " from " + tablename + " where " + primarykey + " not in(select top ");
                sbsqlstatement.append(pagesize * pageindex + "  " + primarykey + " from " + tablename);
                sbsqlstatement.append(" order by " + orderfield +" desc) order by " + orderfield + " desc");
            }
            else
            {
                sbsqlstatement.append("select top " + pagesize + "  " + queryfieldlist + " from " + tablename + " where " + sqlwherestatement + " and " + primarykey + " not in(select top ");
                sbsqlstatement.append(pagesize * pageindex + "  " + primarykey + "  from " + tablename + " where " + sqlwherestatement + "  order by " + orderfield + " desc) order by " + orderfield + " desc");
            }

            _pagetable          = dbhelpersql.query(sbsqlstatement.tostring(), sqlconnstring).tables[0];
            lblcurrentpage.text = (pageindex + 1).tostring();
            txtgotopage.text    = (pageindex + 1).tostring();
            return _pagetable;
        }
        #endregion

        #region 按钮事件代码
        private void btnfirstpage_click(object sender, eventargs e)
        {
            pageindex  = 0;           
            _pagetable = tdatabind();

            if (oneventpageclicked != null)
            {
                oneventpageclicked(this, null);
            }
        }

        private void btnprevpage_click(object sender, eventargs e)
        {
            pageindex--;            
            _pagetable = tdatabind();

            if (oneventpageclicked != null)
            {
                oneventpageclicked(this, null);
            }
        }

        private void btnnextpage_click(object sender, eventargs e)
        {
            pageindex++;
            _pagetable = tdatabind();

            if (oneventpageclicked != null)
            {
                oneventpageclicked(this, null);
            }
        }

        private void btnlastpage_click(object sender, eventargs e)
        {
            pageindex  = pagecount;      
            _pagetable = tdatabind();

            if (oneventpageclicked != null)
            {
                oneventpageclicked(this, null);
            }
        }

        private void txtgotopage_keydown(object sender, keyeventargs e)
        {
            if (e.keycode == keys.enter)
            {
                try
                {                                 
                    if (int32.parse(txtgotopage.text) > (recordcount / pagesize + overpage()) || int32.parse(txtgotopage.text) <= 0)
                    {
                        dialoghelper.showwarningmsg("页码范围越界!");
                        txtgotopage.clear();
                        txtgotopage.focus();
                    }
                    else
                    {
                        pageindex  = int32.parse(txtgotopage.text.tostring()) - 1;
                        _pagetable = tdatabind();

                        if (oneventpageclicked != null)
                        {
                            oneventpageclicked(this, null);
                        }
                    }
                }
                catch (exception ex) //捕获由用户输入不正确数据类型时造成的异常   
                {
                    dialoghelper.showwarningmsg(ex.message);
                    txtgotopage.clear();
                    txtgotopage.focus();
                }
            }
        }
        #endregion

        #endregion
    }
}
 

 

分页控件使用实例

     客户端使用代码如下：

    

view code
 1 using system;
 2 using system.collections.generic;
 3 using system.componentmodel;
 4 using system.data;
 5 using system.drawing;
 6 using system.configuration;
 7 using dotnet.controls;
 8 using system.text;
 9 using system.windows.forms;
10 using dotnet.common;
11 using dotnet.winform.utilities;
12
13 namespace dotnet.winform.example
14 {
15     public partial class frmucpagecontrolbycodetest : form
16     {
17         public frmucpagecontrolbycodetest()
18         {
19             initializecomponent();
20         }
21
22         private void frmucpagecontrolbycodetest_shown(object sender, eventargs e)
23         {
24             //初始化方法一
25             //ucpagecontrolbycode.sqlconnstring = configurationsettings.apps教程ettings["dbconnection"];
26             //ucpagecontrolbycode.sqlwherestatement = "1=1";
27             //ucpagecontrolbycode.tablename = "tbtestdata";
28             //ucpagecontrolbycode.orderfield = "uniqueid";
29             //ucpagecontrolbycode.primarykey = "uniqueid";
30             //ucpagecontrolbycode.queryfieldlist = "*";
31
32             //初始化方法二
33             ucpagecontrolbycode.setucpagecontrolpars(configurationsettings.appsettings["dbconnection"], "1=1", "tbtestdata",
34                 "uniqueid", "uniqueid", "*");
35             datatable dttest = new datatable();
36             dttest = ucpagecontrolbycode.bindpagecontrol();
37             ucdatagridview.datasource = dttest;
38
39             //绑定查询项
40             dictionary diclistqueryitems = new dictionary();
41             foreach (datacolumn dc in dttest.columns)
42             {
43                 diclistqueryitems.add(dc.columnname, dc.datatype.tostring());
44             }
45             uccombinquery1.setqueryitems(diclistqueryitems);
46         }
47
48         private void ucdatagridview_databindingcomplete(object sender, datagridviewbindingcompleteeventargs e)
49         {
50             gbmain.text = "当前共:" + ucdatagridview.rows.count.tostring() + "条数据。";
51         }
52
53         private void ucpagecontrolbycode_oneventpageclicked(object sender, eventargs e)
54         {
55             ucdatagridview.datasource = null;
56             ucdatagridview.datasource = ucpagecontrolbycode.pagetable;
57         }
58
59         private void uccombinquery1_onqueryclicked(object sender, eventargs e)
60         {
61             try
62             {
63                 splasher.show(typeof(frmsplash));
64                 splasher.status = "正在查找数据，请稍等...";
65                 system.threading.thread.sleep(450);
66                 ucdatagridview.datasource = null;
67                 ucpagecontrolbycode.sqlwherestatement = uccombinquery1.queryexpression; //指定查询表达式
68                 ucdatagridview.datasource = ucpagecontrolbycode.bindpagecontrol(); //绑定datagridview
69             }
70             catch (exception ex)
71             {
72                 ucpagecontrolbycode.sqlwherestatement = "1<>1";
73                 ucdatagridview.datasource = ucpagecontrolbycode.bindpagecontrol(); //绑定datagridview
74                 splasher.status = ex.message;
75                 system.threading.thread.sleep(1000);
76             }
77             finally
78             {
79                 system.threading.thread.sleep(100);
80                 splasher.status = "查找完毕...";              
81                 splasher.close();               
82             }
83         }
84     }
85 }