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

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

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">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>分页演示</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <br />
          转到第<asp:TextBox ID="txtPageSize" runat="server" Width="29px">1</asp:TextBox>页<asp:Button ID="btnJump" runat="server" Text="Go" OnClick="btnJump_Click" /><br />
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" Width="90%">
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#EFF3FB" />
            <EditRowStyle BackColor="#2461BF" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>

    </div>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </form>
</body>
</html>

示例的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();
    }
}

 

时间： 2024-09-21 04:15:34

ASP.NET技巧：access下的分页方案_实用技巧的相关文章

水晶报表asp.net的webform下基本用法实例_实用技巧

本文实例讲述了水晶报表asp.net的webform下基本用法.分享给大家供大家参考. 具体实现方法如下: 复制代码 代码如下: protected void Page_Init(object sender, EventArgs e) {      ConfigureCrystalReport();  } protected void Page_Unload(object sender, EventArgs e)  {          if (rptDocument == null)     

asp.net Datalist控件实现分页功能_实用技巧

在.aspx页面里的代码 复制代码 代码如下: <asp:DataList ID="DataList1" runat="server" Width="976px" Height="745px" BorderWidth="2px" CellPadding="2" CellSpacing="2" RepeatColumns="7" RepeatD

ASP.NET MVC4 Razor模板简易分页效果_实用技巧

一.无数据提交 第一步,建立一个 Controller命名为PageIndex的空控制器,自定义一个方法如下:    public ActionResult PageIndex(string action, string controller, int currentPage, int pageCount) { //int count = db.Product.Count(); ViewBag.PageCount = pageCount;//从操作中获取总数据页数将传入分页视图页面 ViewBa

asp.net 结合mysql存储过程进行分页代码_实用技巧

不过在网上找了一些,发现都有一个特点--就是不能传出总记录数,干脆自己研究吧.终于,算是搞出来了,效率可能不是很好,但是我也觉得不错了.贴代码吧直接:也算是对自己学习mysql的一个记录. 复制代码 代码如下: CREATE PROCEDURE p_pageList ( m_pageNo int , m_perPageCnt int , m_column varchar(1000) , m_table varchar(1000) , m_condition varchar(1000), m_or

Asp.net+jquery+.ashx文件实现分页思路_实用技巧

今天看到一个.java哥们写过的在页面直接请求数据列表的程序代码.它是实现选中客户联系人后,无刷新的弹出div罗列其它联系人列表的功能.忽然想到既然可以请求联系人列表,而且无刷新.那么取复杂的数据列表呢,后来想到了数据分页.我现在用了自己写的一个分页控件.但是效率有时候感觉不是很高,它是以 用户控件+存储过程+分页处理类 来实现分页的.但是无可避免的就碰到了刷新的问题即使分页很快,但是只要这"刷"的一下总是感觉很不爽.而且还要页面编译一遍,还要在服务端处理ViewState.以及其它的

asp.net 数据访问层 存储过程分页语句_实用技巧

所以最好在数据访层分页,如果这样就要使用存储过程来分页.以下是以pubs 数据库中的employee表为例来进行数据分页的存储过程,你可以参考它根据实际情况来创建自己的存储过程. 注:@pageindex 数据页的索引,@dataperpage 每页的记录数目,@howmanyrecords 用来获取总的记录数. 复制代码 代码如下: create proc getdata @pageindex int,@dataperpage int,@howmanyrecords int output as

ASP.NET 连接ACCESS数据库的简单方法_实用技巧

index.aspx 复制代码 代码如下: <%@ Page Language="C#" %><%@ import Namespace="System.Data" %><%@ import Namespace="System.Data.OleDb" %><script runat="server">    // Insert page code here    //    voi

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

access|asp.net|分页|技巧 具体不多说了,只贴出相关源码~ 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.cnbl

Asp.Net Couchbase Memcached图文安装调用开发_实用技巧

安装服务端 服务端下载地址:http://www.couchbase.com/download 选择适合自己的进行下载安装就可以了,我这里选择的是Win7 64. 在安装服务端如果发生如下所示的错误,我在win7 64安装的过程中就遇到了. 这个时候可以先撤销安装.通过CMD命令运行regedit.展开HKEY_LOCAL_MACHINE\Software\Microsoft\ Windows\ CurrentVersion分支,在窗口的右侧区域找到名为"ProgramFilesDir"