C#实现Access通用访问类OleDbHelper完整实例

本文实例讲述了C#实现Access通用访问类OleDbHelper。分享给大家供大家参考，具体如下：

最近在做一个项目数据库用的是Access,第一次使用Access数据库，刚开始做有些不顺，数据库的操作和SqlServer稍有些不同，而异常跟踪得到的信息也没有什么意义，经过几天的反复寻找问题，总算解决了一些问题，为了访问Access 数据库，我写了一个用于专门访问的类来操作数据库，其中包括，执行数据库命令，返回 DataSet，返回单条记录，返回DataReader，通用分页方法等几个常用的的操作方法。请各位提出意见，以便我完善这个类。虽是参考SqlHelper 但是比其简单的多，所有的代码如下：

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 语句并返回受影响的行数.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      using (OleDbConnection conn = new OleDbConnection(connString))
      {
 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
 int val = cmd.ExecuteNonQuery();
 cmd.Parameters.Clear();
 return val;
      }
    }
    /// 

    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
      int val = cmd.ExecuteNonQuery();
      cmd.Parameters.Clear();
      return val;
    }
    /// 

    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None);
      int val = cmd.ExecuteNonQuery();
      cmd.Parameters.Clear();
      return val;
    }
    /// 

    /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      OleDbConnection conn = new OleDbConnection(connString);
      try
      {
 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
 OleDbDataReader rdr = cmd.ExecuteReader();
 cmd.Parameters.Clear();
 return rdr;
      }
      catch
      {
 conn.Close();
 throw;
      }
    }
    /// 

    /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      try
      {
 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
 OleDbDataReader rdr = cmd.ExecuteReader();
 cmd.Parameters.Clear();
 return rdr;
      }
      catch
      {
 conn.Close();
 throw;
      }
    }
    /// 

    /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      using (OleDbConnection conn = new OleDbConnection(connString))
      {
 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
 object val = cmd.ExecuteScalar();
 cmd.Parameters.Clear();
 return val;
      }
    }
    /// 

    /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
      object val = cmd.ExecuteScalar();
      cmd.Parameters.Clear();
      return val;
    }
    /// 

    /// 执行查询，并返回查询所返回的结果数据集.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      using (OleDbConnection conn = new OleDbConnection(connString))
      {
 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
 OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 DataSet ds = new DataSet();
 da.Fill(ds);
 cmd.Parameters.Clear();
 return ds;
      }
    }
    /// 

    /// 执行查询，并返回查询所返回的结果数据集.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
    {
      OleDbCommand cmd = new OleDbCommand();
      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
      OleDbDataAdapter da = new OleDbDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds);
      cmd.Parameters.Clear();
      return ds;
    }
    /// 

    /// 缓存查询的 OleDb 参数对象.
    /// 
    /// 
    /// 
    public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
    {
      parmCache[cacheKey] = cmdParms;
    }
    /// 

    /// 从缓存获取指定的参数对象数组.
    /// 
    /// 
    /// 
    public static OleDbParameter[] GetCachedParameters(string cacheKey)
    {
      OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
      if (cachedParms == null)
 return null;
      OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
      for (int i = 0, j = cachedParms.Length; i < j; i++)
 clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
      return clonedParms;
    }
    /// 

    /// 准备命令对象.
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    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
    }
  }
}

更多关于C#相关内容感兴趣的读者可查看本站专题：《C#程序设计之线程使用技巧总结》、《C#操作Excel技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》

希望本文所述对大家C#程序设计有所帮助。