ADO.NET 数据库访问之数据分页
来源:互联网 发布:杨辉三角c语言程序数组 编辑:程序博客网 时间:2024/05/01 03:55
程序尽可能地降低其使用的复杂性,它的优点是使用方便。现在这个程序还有一个缺点,那就是第读取一页的数据,都要打开一次数据库,这个问题是可以解决的,那就是要求在不使用之用执行一个Close,关闭数据库,这是以牺牲程序的安全性为代价的。各个方面需要权衡利弊。
/**//******************************************************************************
* Copryright(c) My Studio Inc. 2008-2009, all rights reserved.
* 程序名称:数据库分页读取类
* 日 期:2008年4月26日
* 作 者:汪思言
* 邮 箱:oohacker@163.com
*
* 使用说明:
* 首先创建一个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; }
}
// 获取和设置要读取的列
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);
}
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;
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];
}
}
}
* Copryright(c) My Studio Inc. 2008-2009, all rights reserved.
* 程序名称:数据库分页读取类
* 日 期:2008年4月26日
* 作 者:汪思言
* 邮 箱:oohacker@163.com
*
* 使用说明:
* 首先创建一个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; }
}
// 获取和设置要读取的列
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);
}
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;
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];
}
}
}
- ADO.NET 数据库访问之数据分页
- ADO.NET数据库访问
- ADO.NET数据库访问
- Ado.Net 访问数据库
- ADO.NET访问数据库
- ADO.NET数据访问
- ADO.NET 数据访问
- C#数据库编程 之 ADO.NET的数据库访问小结
- ADO.net实体框架访问不到数据库最新数据问题
- 使用ADO.NET访问数据库
- 通过ADO.NET访问数据库
- ADO.NET数据库访问入门
- 使用ADO.NET访问数据库
- 使用ADO.NET访问数据库
- 通过ADO.NET访问数据库
- 数据库访问ADO.NET结构图
- ADO.NET数据库访问入门
- 数据库访问ADO.NET结构图
- 通过sql语句获取的基本信息
- T-SQL生成两个新的真正的公历年历
- 感悟
- 空间数据挖掘技术理论及方法
- 对会计中凭证输入模块的一点看法
- ADO.NET 数据库访问之数据分页
- 伟大的解决方案—DataWindow.Net(二)
- 用IE登录服务器上的em出现的乱码的解决
- 几十个实用的PL/SQL(5)
- 几十个实用的PL/SQL(4)
- 几十个实用的PL/SQL(3)
- 几十个实用的PL/SQL(2)
- 几十个实用的PL/SQL(1)
- 发现数据对象--开发的关键