ASP.NET技巧：access下的分页方案

2024-07-10 13:10:17

字体：大中小

来源：转载

供稿：网友

具体不多说了，只贴出相关源码~

using system;
using system.collections.generic;
using system.text;
using system.data;
using system.data.oledb;
using system.web;

/**//// <summary>
/// 名称：access下的分页方案（仿sql存储过程）
/// 作者：cncxz(虫虫)
/// blog：http://cncxz.cnblogs.com
/// </summary>
public class adopager
{
protected string m_connstring;
protected oledbconnection m_conn;

    public adopager()
    {
        createconn(string.empty);
    }
    public adopager(string dbpath)
    {
        createconn(dbpath);
    }

    private void createconn(string dbpath)
    {
        if (string.isnullorempty(dbpath))
        {
            string str = system.configuration.configurationmanager.appsettings["dbpath"] as string;
            if (string.isnullorempty(str))
                str = "~/app_data/db.mdb";
            m_connstring = string.format(@"provider=microsoft.jet.oledb.4.0;data source={0}", httpcontext.current.server.mappath(str));
        }
        else
            m_connstring = string.format(@"provider=microsoft.jet.oledb.4.0;data source={0}", dbpath);

        m_conn = new oledbconnection(m_connstring);
    }
    /**//// <summary>
    /// 打开连接
    /// </summary>
    public void connopen()
    {
        if (m_conn.state != connectionstate.open)
            m_conn.open();
    }
    /**//// <summary>
    /// 关闭连接
    /// </summary>
    public void connclose()
    {
        if (m_conn.state != connectionstate.closed)
            m_conn.close();
    }

    private string recordid(string query, int passcount)
    {
        oledbcommand cmd = new oledbcommand(query, m_conn);
        string result = string.empty;
        using (idatareader dr = cmd.executereader())
        {
            while (dr.read())
            {
                if (passcount < 1)
                {
                    result += "," + dr.getint32(0);
                }
                passcount--;
            }
        }
        return result.substring(1);
    }

    /**//// <summary>
    /// 获取当前页应该显示的记录，注意：查询中必须包含名为id的自动编号列，若不符合你的要求，就修改一下源码吧 :)
    /// </summary>
    /// <param name="pageindex">当前页码</param>
    /// <param name="pagesize">分页容量</param>
    /// <param name="showstring">显示的字段</param>
    /// <param name="querystring">查询字符串，支持联合查询</param>
    /// <param name="wherestring">查询条件，若有条件限制则必须以where 开头</param>
    /// <param name="orderstring">排序规则</param>
    /// <param name="pagecount">传出参数：总页数统计</param>
    /// <param name="recordcount">传出参数：总记录统计</param>
    /// <returns>装载记录的datatable</returns>
    public datatable executepager(int pageindex, int pagesize, string showstring, string querystring, string wherestring, string orderstring, out int pagecount, out int recordcount)
    {
        if (pageindex < 1) pageindex = 1;
        if (pagesize < 1) pagesize = 10;
        if (string.isnullorempty(showstring)) showstring = "*";
        if (string.isnullorempty(orderstring)) orderstring = "id desc";
        connopen();
        string myvw = string.format(" ( {0} ) tempvw ", querystring);
        oledbcommand cmdcount = new oledbcommand(string.format(" select count(0) as recordcount from {0} {1}", myvw, wherestring), m_conn);

recordcount = convert.toint32(cmdcount.executescalar());

        if ((recordcount % pagesize) > 0)
            pagecount = recordcount / pagesize + 1;
        else
            pagecount = recordcount / pagesize;
        oledbcommand cmdrecord;
        if (pageindex == 1)//第一页
        {
            cmdrecord = new oledbcommand(string.format("select top {0} {1} from {2} {3} order by {4} ", pagesize, showstring, myvw, wherestring, orderstring), m_conn);
        }
        else if (pageindex > pagecount)//超出总页数
        {
            cmdrecord = new oledbcommand(string.format("select top {0} {1} from {2} {3} order by {4} ", pagesize, showstring, myvw, "where 1=2", orderstring), m_conn);
        }
        else
        {
            int pagelowerbound = pagesize * pageindex;
            int pageupperbound = pagelowerbound - pagesize;
            string recordids = recordid(string.format("select top {0} {1} from {2} {3} order by {4} ", pagelowerbound, "id", myvw, wherestring, orderstring), pageupperbound);
            cmdrecord = new oledbcommand(string.format("select {0} from {1} where id in ({2}) order by {3} ", showstring, myvw, recordids, orderstring), m_conn);

        }
        oledbdataadapter dataadapter = new oledbdataadapter(cmdrecord);
        datatable dt=new datatable();
        dataadapter.fill(dt);
        connclose();
        return dt;
    }
}

还有调用示例：html代码

<%@ page language="c#" autoeventwireup="true" codefile="default.aspx.cs" inherits="_default" %>

<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">

示例的codebehind代码

using system;
using system.data;
using system.configuration;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;
using system.collections.generic;

public partial class _default : system.web.ui.page
{
    private adopager mm_pager;
    protected adopager m_pager
    {
        get{
            if (mm_pager == null)
                mm_pager = new adopager();
            return mm_pager;
        }
    }
    protected void page_load(object sender, eventargs e)
    {
        if(!ispostback)
            loaddata();
    }
    private int pageindex = 1;
    private int pagesize = 20;
    private int pagecount = -1;
    private int recordcount = -1;

    private void loaddata()
    {
        string strquery = "select a.*,b.kindtext from tabletest a left join tablekind b on a.kindcode=b.kindcode ";
        string strshow = "id,subject,kindcode,kindtext";

        datatable dt = m_pager.executepager(pageindex, pagesize, strshow, strquery, "", "id desc", out pagecount, out recordcount);
        gridview1.datasource = dt;
        gridview1.databind();
        label1.text = string.format("共{0}条记录，每页{1}条，页次{2}/{3}",recordcount,pagesize,pageindex,pagecount);
    }


    protected void btnjump_click(object sender, eventargs e)
    {
        int.tryparse(txtpagesize.text, out pageindex);
        loaddata();
    }
}