也谈OleDbHelper,由SqlHelper类转OleDbHelper类,access操作

    今意外看见一贴子谈OleDbHelper,突然想起自已一直用的由SqlHelper类转成的OleDbHelper,在此分享,自己用了二年了,还没发现异常, 代码如下:
  1. using System;
  2. using System.Data;
  3. using System.Data.OleDb;
  4. using System.Configuration;
  5. using System.Collections;
  6. using System.Data.Sql;
  7. using System.Text;
  8. namespace Lihui.Common
  9. {
  10.     /// <summary>
  11.     /// Summary description for OleDbHelper
  12.     /// </summary>
  13.     public class OleDbHelper
  14.     {
  15.         //Database connection strings
  16.         public static readonly string CONN_STRING = ConfigurationManager.AppSettings["OleDbConnectionString"];
  17.         public static readonly string CONN_STRING1 = ConfigurationManager.AppSettings["OleDbConnectionString1"];
  18.         // Hashtable to store cached parameters
  19.         private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  20.         #region =ExecuteNonQuery=
  21.         public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText)
  22.         {
  23.             return ExecuteNonQuery(connString, cmdType, cmdText, null);
  24.         }
  25.         public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText)
  26.         {
  27.             return ExecuteNonQuery(conn, cmdType, cmdText, null);
  28.         }
  29.         public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText)
  30.         {
  31.             return ExecuteNonQuery(trans, cmdType, cmdText, null);
  32.         }
  33.         public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  34.         {
  35.             OleDbCommand cmd = new OleDbCommand();
  36.             using (OleDbConnection conn = new OleDbConnection(connString))
  37.             {
  38.                 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
  39.                 int val = cmd.ExecuteNonQuery();
  40.                 //清除cmd的参数
  41.                 cmd.Parameters.Clear();
  42.                 if (conn.State == ConnectionState.Open)
  43.                 {
  44.                     conn.Close();
  45.                 }
  46.                 return val;
  47.             }
  48.         }
  49.         public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  50.         {
  51.             OleDbCommand cmd = new OleDbCommand();
  52.             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
  53.             int val = cmd.ExecuteNonQuery();
  54.             cmd.Parameters.Clear();
  55.             if (conn.State == ConnectionState.Open)
  56.             {
  57.                 conn.Close();
  58.             }
  59.             return val;
  60.         }
  61.         public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  62.         {
  63.             OleDbCommand cmd = new OleDbCommand();
  64.             PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
  65.             int val = cmd.ExecuteNonQuery();
  66.             cmd.Parameters.Clear();
  67.             if (cmd.Connection.State == ConnectionState.Open)
  68.             {
  69.                 cmd.Connection.Close();
  70.             }
  71.             return val;
  72.         }
  73.         #endregion
  74.         #region =ExecuteReader=
  75.         public static OleDbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
  76.         {
  77.             //pass through the call providing null for the set of OleDbParameters
  78.             return ExecuteReader(connectionString, commandType, commandText, (OleDbParameter[])null);
  79.         }
  80.         public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  81.         {
  82.             OleDbCommand cmd = new OleDbCommand();
  83.             OleDbConnection conn = new OleDbConnection(connString);
  84.             try
  85.             {
  86.                 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
  87.                 OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  88.                 cmd.Parameters.Clear();
  89.                 if (conn.State == ConnectionState.Open)
  90.                 {
  91.                     conn.Close();
  92.                 }
  93.                 return rdr;
  94.             }
  95.             catch
  96.             {
  97.                 conn.Close();
  98.                 throw;
  99.             }
  100.         }
  101.         #endregion
  102.         #region =ExecuteDataset=
  103.         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
  104.         {
  105.             return ExecuteDataset(connectionString, commandType, commandText, (OleDbParameter[])null);
  106.         }
  107.         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  108.         {
  109.             using (OleDbConnection cn = new OleDbConnection(connectionString))
  110.             {
  111.                 cn.Open();
  112.                 //调用重载方法
  113.                 return ExecuteDataset(cn, commandType, commandText, commandParameters);
  114.             }
  115.         }
  116.         public static DataSet ExecuteDataset(OleDbConnection connection, CommandType commandType, string commandText)
  117.         {
  118.             return ExecuteDataset(connection, commandType, commandText, (OleDbParameter[])null);
  119.         }
  120.         public static DataSet ExecuteDataset(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  121.         {
  122.             //创建一个OleDbCommand对象,并对其进行初始化
  123.             OleDbCommand cmd = new OleDbCommand();
  124.             PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters);
  125.             //创建OleDbDataAdapter对象以及DataSet
  126.             OleDbDataAdapter da = new OleDbDataAdapter(cmd);
  127.             DataSet ds = new DataSet();
  128.             //填充ds
  129.             da.Fill(ds);
  130.             // 清除cmd的参数集合   
  131.             cmd.Parameters.Clear();
  132.             if (cmd.Connection.State == ConnectionState.Open)
  133.             {
  134.                 cmd.Connection.Close();
  135.             }
  136.             //返回ds
  137.             return ds;
  138.         }
  139.         #endregion
  140.         #region =ExecuteDataTable=
  141.         public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
  142.         {
  143.             return ExecuteDataTable(connectionString, commandType, commandText, (OleDbParameter[])null);
  144.         }
  145.         public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  146.         {
  147.             using (OleDbConnection cn = new OleDbConnection(connectionString))
  148.             {
  149.                 cn.Open();
  150.                 //调用重载方法
  151.                 return ExecuteDataTable(cn, commandType, commandText, commandParameters);
  152.             }
  153.         }
  154.         public static DataTable ExecuteDataTable(OleDbConnection connection, CommandType commandType, string commandText)
  155.         {
  156.             return ExecuteDataTable(connection, commandType, commandText, (OleDbParameter[])null);
  157.         }
  158.         public static DataTable ExecuteDataTable(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  159.         {
  160.             //创建一个OleDbCommand对象,并对其进行初始化
  161.             OleDbCommand cmd = new OleDbCommand();
  162.             PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters);
  163.             //创建OleDbDataAdapter对象以及DataSet
  164.             OleDbDataAdapter da = new OleDbDataAdapter(cmd);
  165.             DataSet ds = new DataSet();
  166.             //填充ds
  167.             da.Fill(ds);
  168.             // 清除cmd的参数集合   
  169.             cmd.Parameters.Clear();
  170.             if (cmd.Connection.State == ConnectionState.Open)
  171.             {
  172.                 cmd.Connection.Close();
  173.             }
  174.             //返回ds
  175.             return ds.Tables[0];
  176.         }
  177.         #endregion
  178.         #region =ExecuteScalar=
  179.         public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText)
  180.         {
  181.             return ExecuteScalar(connString, cmdType, cmdText, null);
  182.         }
  183.         public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  184.         {
  185.             OleDbCommand cmd = new OleDbCommand();
  186.             using (OleDbConnection conn = new OleDbConnection(connString))
  187.             {
  188.                 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
  189.                 object val = cmd.ExecuteScalar();
  190.                 cmd.Parameters.Clear();
  191.                 if (conn.State == ConnectionState.Open)
  192.                 {
  193.                     conn.Close();
  194.                 }
  195.                 return val;
  196.             }
  197.         }
  198.         public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText)
  199.         {
  200.             return ExecuteScalar(conn, cmdType, cmdText, null);
  201.         }
  202.         public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  203.         {
  204.             OleDbCommand cmd = new OleDbCommand();
  205.             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
  206.             object val = cmd.ExecuteScalar();
  207.             cmd.Parameters.Clear();
  208.             if (conn.State == ConnectionState.Open)
  209.             {
  210.                 conn.Close();
  211.             }
  212.             return val;
  213.         }
  214.         #endregion
  215.         public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
  216.         {
  217.             parmCache[cacheKey] = cmdParms;
  218.         }
  219.         public static OleDbParameter[] GetCachedParameters(string cacheKey)
  220.         {
  221.             OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
  222.             if (cachedParms == null)
  223.                 return null;
  224.             OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
  225.             for (int i = 0, j = cachedParms.Length; i < j; i++)
  226.                 clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
  227.             return clonedParms;
  228.         }
  229.         public static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
  230.         {
  231.             //判断连接的状态。如果是关闭状态,则打开
  232.             if (conn.State != ConnectionState.Open)
  233.                 conn.Open();
  234.             //cmd属性赋值
  235.             cmd.Connection = conn;
  236.             cmd.CommandText = cmdText;
  237.             //是否需要用到事务处理
  238.             if (trans != null)
  239.                 cmd.Transaction = trans;
  240.             cmd.CommandType = cmdType;
  241.             //添加cmd需要的存储过程参数
  242.             if (cmdParms != null)
  243.             {
  244.                 foreach (OleDbParameter parm in cmdParms)
  245.                     cmd.Parameters.Add(parm);
  246.             }
  247.         }
  248.     }
  249. }
    使用方法跟SqlHelper类相似。  
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值