ADO.NET 数据库访问之数据分页

来源：qqread 作者：佚名出处：巧巧读书 2008-04-30 进入讨论组

程序尽可能地降低其使用的复杂性，它的优点是使用方便。现在这个程序还有一个缺点，那就是第读取一页的数据，都要打开一次数据库，这个问题是可以解决的，那就是要求在不使用之用执行一个Close，关闭数据库，这是以牺牲程序的安全性为代价的。

各个方面需要权衡利弊。

     * 使用说明：
     * 首先创建一个MyDataPage类实例mypage，然后设置数据库连接串、查询表、查询列、查询条件、
     * 排序条件等。然后执行mypage.DoPaging()，注意检查其返回值，返回真表示分页成功，否则
     * 应查看mypage.ErrMessage属性。成功后，就可以使用mypage.GetData(<页编号>)读取数据了.
     *
     * 示例：
     *
     * using MyLibrary.DataAccess;
     * MyDataPage myPage = new MyDataPage(
     *   "provider=sqloledb;server=(local);uid=sa;pwd=oohacker;database=Northwind",
     *   "Product",
     *   "ProductId,ProductName",
     *   "SupplierId<>1",
     *   "SupplierId ASC, ProductId DESC",
     *   20);
     *
     * if (myPage.DoPaging())
     * {
     *   Console.Write("Total Records: {0} Total Pages: {1} ",
     *     myPage.RecordCount,
     *     myPage.PageCount);
     *
     *   for (int i=1; i<=myPage.PageCount; ++i)
     *   {
     *     Console.Write("Page {0} ", i);
     *     DataTable table = myPage.GetData(i);
     *     for (int j=0; j<table.Rows.Count; ++j)
     *     {
     *       Console.Write("#{0}:{1} ",
     *         table.Rows[j]["ProductId"],
     *         table.Rows[j]["ProductName"]);
     *     }
     *   }
     * }
     * else
     * {
     *   Console.Write("分页失败！原因：{0} ", myPage.ErrMessage);
     * }
     *

    using System;
    using System.Text;
    using System.Data;
    using System.Data.OleDb;
    using System.Collections;

    namespace MyLibrary.DataAccess
    ...{
      public class MyDataPage
      ...{
    成员变量#region 成员变量
    const int defaltPageSize = 10;
    private int recordCount;
    private int pageCount;
    private int pageSize;
    private string table;
    private string columns;
    private string conditions;
    private string orders;
    private string connectionString;
    private string errorMessage;
    private bool isDirty;
    #endregion

    构造函数#region 构造函数
    public MyDataPage(string _connectionString, string _table)

    ...{
      Init(_connectionString, _table, "*", "", "", defaltPageSize);
    }

    public MyDataPage(string _connectionString, string _table, int _pageSize)
    ...{
      Init(_connectionString, _table, "*", "", "", _pageSize);
    }

    public MyDataPage(string _connectionString, string _table, string _columns, int _pageSize)
    ...{
      Init(_connectionString, _table, _columns, "", "", _pageSize);
    }

    public MyDataPage(string _connectionString, string _table, string _columns, string _conditions, int _pageSize)
    ...{
      Init(_connectionString, _table, _columns, _conditions, "", _pageSize);
    }

public MyDataPage(string _connectionString,

    string _table, string _columns, string _conditions, string _orders, int _pageSize)
    ...{
      Init(_connectionString, _table, _columns, _conditions, _orders, _pageSize);
    }

private void Init(string _connectionString,

    string _table, string _columns, string _coditions, string _orders, int _pageSize)
    ...{
      this.recordCount = -1;
      this.pageCount = -1;
      this.PageSize = _pageSize;
      this.Table = _table;
      this.Columns = _columns;
      this.Conditions = _coditions;
      this.Orders = _orders;
      this.connectionString = _connectionString;
      this.isDirty = false;
    }
    #endregion

    // 获取和设置页面大小
    public int PageSize
    ...{
      set
      ...{
        pageSize = (value >=10 && value <= 1000) ? value : defaltPageSize;
        isDirty = true;
      }
      get
      ...{
        return pageSize;
      }
    }

    // 获取记录数
    public int RecordCount
    ...{
      get ...{ return recordCount; }
    }

    // 获取页面数
    public int PageCount
    ...{
      get ...{ return pageCount; }
    }

    // 获取和设置表名
    public string Table
    ...{
      set ...{ this.table = value.Trim(); isDirty = true; }
      get ...{ return this.table; }
    }