今意外看见一贴子谈OleDbHelper，突然想起自已一直用的由SqlHelper类转成的OleDbHelper，在此分享，自己用了二年了，还没发现异常， 代码如下： using System; using System.Data; using System.Data.OleDb; using System.Configuration; using System.Colle
今意外看见一贴子谈OleDbHelper，突然想起自已一直用的由SqlHelper类转成的OleDbHelper，在此分享，自己用了二年了，还没发现异常， 代码如下：
using System; using System.Data; using System.Data.OleDb; using System.Configuration; using System.Collections; using System.Data.Sql; using System.Text; namespace Lihui.Common { /// /// Summary description for OleDbHelper /// public class OleDbHelper { //Database connection strings public static readonly string CONN_STRING = ConfigurationManager.AppSettings[ "OleDbConnectionString" ]; public static readonly string CONN_STRING1 = ConfigurationManager.AppSettings[ "OleDbConnectionString1" ]; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized( new Hashtable()); #region =ExecuteNonQuery= public static int ExecuteNonQuery( string connString, CommandType cmdType, string cmdText) { return ExecuteNonQuery(connString, cmdType, cmdText, null ); } public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText) { return ExecuteNonQuery(conn, cmdType, cmdText, null ); } public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText) { return ExecuteNonQuery(trans, cmdType, cmdText, null ); } 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); int val = cmd.ExecuteNonQuery(); //清除cmd的参数 cmd.Parameters.Clear(); if (conn.State == ConnectionState.Open) { conn.Close(); } return val; } } public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); if (conn.State == ConnectionState.Open) { conn.Close(); } return val; } 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); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } return val; } #endregion #region =ExecuteReader= public static OleDbDataReader ExecuteReader( string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of OleDbParameters return ExecuteReader(connectionString, commandType, commandText, (OleDbParameter[]) null ); } 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); OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); if (conn.State == ConnectionState.Open) { conn.Close(); } return rdr; } catch { conn.Close(); throw ; } } #endregion #region =ExecuteDataset= public static DataSet ExecuteDataset( string connectionString, CommandType commandType, string commandText) { return ExecuteDataset(connectionString, commandType, commandText, (OleDbParameter[]) null ); } public static DataSet ExecuteDataset( string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters) { using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); //调用重载方法 return ExecuteDataset(cn, commandType, commandText, commandParameters); } } public static DataSet ExecuteDataset(OleDbConnection connection, CommandType commandType, string commandText) { return ExecuteDataset(connection, commandType, commandText, (OleDbParameter[]) null ); } public static DataSet ExecuteDataset(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters) { //创建一个OleDbCommand对象，并对其进行初始化 OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, (OleDbTransaction) null , commandType, commandText, commandParameters); //创建OleDbDataAdapter对象以及DataSet OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); //填充ds da.Fill(ds); // 清除cmd的参数集合 cmd.Parameters.Clear(); if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } //返回ds return ds; } #endregion #region =ExecuteDataTable= public static DataTable ExecuteDataTable( string connectionString, CommandType commandType, string commandText) { return ExecuteDataTable(connectionString, commandType, commandText, (OleDbParameter[]) null ); } public static DataTable ExecuteDataTable( string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters) { using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); //调用重载方法 return ExecuteDataTable(cn, commandType, commandText, commandParameters); } } public static DataTable ExecuteDataTable(OleDbConnection connection, CommandType commandType, string commandText) { return ExecuteDataTable(connection, commandType, commandText, (OleDbParameter[]) null ); } public static DataTable ExecuteDataTable(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters) { //创建一个OleDbCommand对象，并对其进行初始化 OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, (OleDbTransaction) null , commandType, commandText, commandParameters); //创建OleDbDataAdapter对象以及DataSet OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); //填充ds da.Fill(ds); // 清除cmd的参数集合 cmd.Parameters.Clear(); if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } //返回ds return ds.Tables[0]; } #endregion #region =ExecuteScalar= public static object ExecuteScalar( string connString, CommandType cmdType, string cmdText) { return ExecuteScalar(connString, cmdType, cmdText, null ); } 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); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if (conn.State == ConnectionState.Open) { conn.Close(); } return val; } } public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText) { return ExecuteScalar(conn, cmdType, cmdText, null ); } public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if (conn.State == ConnectionState.Open) { conn.Close(); } return val; } #endregion 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 clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } public static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms) { //判断连接的状态。如果是关闭状态，则打开 if (conn.State != ConnectionState.Open) conn.Open(); //cmd属性赋值 cmd.Connection = conn; cmd.CommandText = cmdText; //是否需要用到事务处理 if (trans != null ) cmd.Transaction = trans; cmd.CommandType = cmdType; //添加cmd需要的存储过程参数 if (cmdParms != null ) { foreach (OleDbParameter parm in cmdParms) cmd.Parameters.Add(parm); } } } }
使用方法跟SqlHelper类相似。查看更多关于也谈OleDbHelper,由SqlHelper类转OleDbHelper类，access操作的详细内容...