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

程序尽可能地降低其使用的复杂性，它的优点是使用方便。现在这个程序还有一个缺点，那就是第读取一页的数据，都要打开一次数据库，这个问题是可以解决的，那就是要求在不使用之用执行一个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) [Page]
    ...{
      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; }
    } [Page]

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

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

    // 获取和设置要读取的列
    public string Columns
    ...{
      set
      ...{
        this.columns = value.Trim();
        if (this.columns == "") this.columns = "*";
        isDirty = true;
      }
      get ...{ return this.columns; }
    }

    // 获取和设置读取的条件
    public string Conditions
    ...{
      set ...{ this.conditions = value.Trim(); isDirty = true; }
      get ...{ return this.conditions; }
    }

    // 获取或设置排序
    public string Orders
    ...{
      set ...{ this.orders = value.Trim(); isDirty = true; }
      get ...{ return this.orders; }
    }

    // 获取设置连接字串
    public string ConnectionString

    ...{
      set ...{ this.connectionString = value; isDirty = true; }
      get ...{ return this.connectionString; }
 }

    // 获取SQL查询命令
    public string SelectCommand
    ...{
      get
      ...{
        StringBuilder command = new StringBuilder(256);
        command.AppendFormat("select {0} from [{1}] ", columns, table);
        if (conditions != "")
        ...{
          command.AppendFormat(" where {0}", conditions);
        }
        if (orders != "")
        ...{
          command.AppendFormat(" order by {0}", orders);
        } [Page]
        return command.ToString();
      }
    }

    // 获取异常信息
    public string ErrMessage
    ...{
      get ...{ return errorMessage; }
    }

    // 执行分页
    public bool DoPaging()
    ...{
      errorMessage = "";
      // 生成统计SQL
      StringBuilder sqlParas = new StringBuilder(256);
      sqlParas.AppendFormat("from [{0}] ", table);
      if (conditions != "")
      ...{
        sqlParas.AppendFormat(" where {0}", conditions);
      }
      String sqlCount = "select count(*) as [RowCount] " + sqlParas.ToString();

      // 开始统计
      OleDbConnection conn = new OleDbConnection(connectionString);
      try
      ...{
        conn.Open();
      }
      catch (Exception e)
      ...{
        errorMessage = e.Message;
        return false;
      }

      // 执行分页获取记录数和页面数信息
      OleDbDataAdapter adapter = new OleDbDataAdapter();
      DataSet ds = new DataSet();
      try
      ...{
        // 统计总记录数， 得出记录总和页面数
        adapter.SelectCommand = new OleDbCommand(sqlCount, conn);
        adapter.Fill(ds, "Count");
        recordCount = (int)(ds.Tables["count"].Rows[0])["RowCount"];
        ds.Clear();

        pageCount = recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0);
      }
      catch (Exception e)
      ...{
        errorMessage = e.Message;
        return false;
      }
      finally
      ...{
        conn.Close();
      }

      isDirty = false; [Page]
      return true;
    }

    // 执行分页查询
public DataTable GetData(int page)
    ...{
      errorMessage = "";
      if (isDirty)
      ...{
        errorMessage = "查询条件已改变，必须先执行分页才能再次读取数据";
        return null;
      }

      // 开始查询
      OleDbConnection conn = new OleDbConnection(connectionString);
      try
      ...{
        conn.Open();
      }
      catch (Exception e)
      ...{
        errorMessage = e.Message;
        return null;
      }

      // 执行分页并读取数据
      OleDbDataAdapter adapter = new OleDbDataAdapter();
      DataSet ds = new DataSet();
      try
      ...{
        // 执行分页并读取数据
        if (page > pageCount)
        ...{
          errorMessage = "页面数溢出！";
          return null;
        }
        int recordStartIndex = (page - 1) * pageSize;
        adapter.SelectCommand = new OleDbCommand(this.SelectCommand, conn);
        adapter.Fill(ds, recordStartIndex, pageSize, table);
      }
      catch (Exception e)
      ...{
        errorMessage = e.Message;
        return null;
      }
      finally
      ...{
        conn.Close();
      }
      return ds.Tables[table];
    }
  }
}