键盘敲击者cncxz

  博客园  :: 首页  ::  :: 联系 :: 订阅 订阅  :: 管理
  34 Posts :: 0 Stories :: 290 Comments :: 14 Trackbacks

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

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 />
        
&nbsp; 转到第<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();
    }

}



最后附上工程文件下载地址:http://www.cnblogs.com/Files/cncxz/AdoPager.rar
posted on 2006-06-28 19:23  cncxz(虫虫)  阅读(...)  评论(... 编辑 收藏