Code
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
namespace Common
{
/**////
/// OleDb 书库访问类
///
public static class OleDbHelper
{
/**////
/// Access 的数据库连接字符串格式.
///
public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};";
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/**////
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
///
///
///
///
///
///
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
///
///
///
///
///
///
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
///
///
///
///
///
///
/// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
///
///
///
///
///
///
/// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
///
///
///
///
///
///
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
///
///
///
///
///
///
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
///
///
///
///
///
///
/// 执行查询，并返回查询所返回的结果数据集.
///
///
///
///
///
///
/// 执行查询，并返回查询所返回的结果数据集.
///
///
///
///
///
///
/// 缓存查询的 OleDb 参数对象.
///
///
///
public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
{
parmCache[cacheKey] = cmdParms;
}
/**////
/// 从缓存获取指定的参数对象数组.
///
///
///
/// 准备命令对象.
///
///
///
///
///
///
///
///
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType)
{
if (connActionType == ConnectionActionType.Open)
{
conn.Open();
}
else
{
if (conn.State != ConnectionState.Open)
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/**////
/// 统一分页显示数据记录
///
/// 数据库连接字符串
/// 当前页码
/// 每页显示的条数
/// 显示的字段
/// 查询的表格
/// 查询的条件
/// 排序的规则
/// out：总页数
/// out：总条数
/// 表的主键
/// 返回DataTable集合
public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id)
{
if (pageIndex < 1) pageIndex = 1;
if (pageSize < 1) pageSize = 10;
if (string.IsNullOrEmpty(fileds)) fileds = "*";
if (string.IsNullOrEmpty(order)) order = "ID desc";
using (OleDbConnection conn = new OleDbConnection(connString))
{
string myVw = string.Format(" {0} ", table);
string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where);
OleDbCommand cmdCount = new OleDbCommand(sqlText, conn);
if (conn.State == ConnectionState.Closed)
conn.Open();
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, fileds, myVw, where, order), conn);
}
else if (pageIndex > pageCount)//超出总页数
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), 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, where, order), pageUpperBound, conn);
cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);
}
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
}
private static string RecordID(string query, int passCount, OleDbConnection conn)
{
OleDbCommand cmd = new OleDbCommand(query, 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);
}
/**////
/// 连接操作类型枚举.
///
enum ConnectionActionType
{
None = 0,
AutoDetection = 1,
Open = 2
}
}
}
