也谈OleDbHelper,由SqlHelper类转OleDbHelper类,access操作
原创
2016-06-07 15:05:54
992浏览
今意外看见一贴子谈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 { public class OleDbHelper { public static readonly string CONN_STRING = ConfigurationManager.AppSettings[ "OleDbConnectionString" ]; public static readonly string CONN_STRING1 = ConfigurationManager.AppSettings[ "OleDbConnectionString1" ]; 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.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) { 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 cmd = new OleDbCommand(); PrepareCommand(cmd, connection, (OleDbTransaction) null , commandType, commandText, commandParameters); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } 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 cmd = new OleDbCommand(); PrepareCommand(cmd, connection, (OleDbTransaction) null , commandType, commandText, commandParameters); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } 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.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); } } } } 使用方法跟SqlHelper类相似。
声明: 本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。