Access 通用访问类 OleDbHelper

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
{
    
/**//// <summary>
    
/// OleDb 书库访问类
    
/// </summary>
    public static class OleDbHelper
    {
        
/**//// <summary>
        
/// Access 的数据库连接字符串格式.
        
/// </summary>
        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());

        
/**//// <summary>
        
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
        
/// </summary>
        
/// <param name="connString"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
            }
        }

        
/**//// <summary>
        
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
        
/// </summary>
        
/// <param name="conn"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
        }

        
/**//// <summary>
        
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
        
/// </summary>
        
/// <param name="trans"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
        }

        
/**//// <summary>
        
/// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
        
/// </summary>
        
/// <param name="connString"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
            }
        }

        
/**//// <summary>
        
/// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
        
/// </summary>
        
/// <param name="conn"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
            }
        }

        
/**//// <summary>
        
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
        
/// </summary>
        
/// <param name="connString"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
            }
        }

        
/**//// <summary>
        
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
        
/// </summary>
        
/// <param name="conn"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
        }

        
/**//// <summary>
        
/// 执行查询,并返回查询所返回的结果数据集.
        
/// </summary>
        
/// <param name="connString"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
            }
        }

        
/**//// <summary>
        
/// 执行查询,并返回查询所返回的结果数据集.
        
/// </summary>
        
/// <param name="conn"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <returns></returns>
        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;
        }

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

        
/**//// <summary>
        
/// 从缓存获取指定的参数对象数组.
        
/// </summary>
        
/// <param name="cacheKey"></param>
        
/// <returns></returns>
        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;
        }

        
/**//// <summary>
        
/// 准备命令对象.
        
/// </summary>
        
/// <param name="cmd"></param>
        
/// <param name="conn"></param>
        
/// <param name="trans"></param>
        
/// <param name="cmdType"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        
/// <param name="connActionType"></param>
        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);
            }
        }
        
/**//// <summary>
        
/// 统一分页显示数据记录
        
/// </summary>
        
/// <param name="connString">数据库连接字符串</param>
        
/// <param name="pageIndex">当前页码</param>
        
/// <param name="pageSize">每页显示的条数</param>
        
/// <param name="fileds">显示的字段</param>
        
/// <param name="table">查询的表格</param>
        
/// <param name="where">查询的条件</param>
        
/// <param name="order">排序的规则</param>
        
/// <param name="pageCount">out:总页数</param>
        
/// <param name="recordCount">out:总条数</param>
        
/// <param name="id">表的主键</param>
        
/// <returns>返回DataTable集合</returns>
        public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string wherestring 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);
        }

        
/**//// <summary>
        
/// 连接操作类型枚举.
        
/// </summary>
        enum ConnectionActionType
        {
            None 
= 0,
            AutoDetection 
= 1,
            Open 
= 2
        }
    }
}
原文地址:https://www.cnblogs.com/tonybinlj/p/1508135.html