SOURCE:http://www.cnblogs.com/dreamof/archive/2009/04/17/1438415.html
最近的一个项目中用到了Access,于是自己写了一个AccessHelper,现把代码贴出来,欢迎大家提出宝贵意见!
using
System;
using
System.Data;
using
System.Data.OleDb;
using
System.Collections;
using
System.IO;
using
System.Globalization;
using
System.Configuration;
namespace
ArticleManage
{ public class DataAccess { 定义 #region 定义 protected OleDbCommand Comm; protected OleDbDataAdapter Adap; protected OleDbConnection Conn; // SQL连接 private string _connectString; // 连接串 private string _commandString; // SQL命令 private Hashtable _dict, _result, _mapTable; private DataSet _ds; // 返回结果数据集 private DataRow _recordSet; // 纪录集 private string _tableName; // 表名 private int _recordCount; // 纪录集的行数 private bool _eOF; // 结果集是否为空,是否已经到了结尾 private string DB; private string _deleteOP; private string _path; private StreamWriter SWCreate, SWApp; private string _errorMessage; private bool _writeLog; #endregion 属性集 #region 属性集 /**/ /// <summary> /// 出错信息 /// </summary> /// public string ErrorMessage { get { return this ._errorMessage; } set { this ._errorMessage = value; } } /**/ /**/ /**/ /// <summary> /// 设置或者取得删除的操作者 /// </summary> public string DeleteOP { get { return this ._deleteOP; } set { this ._deleteOP = value; } } /**/ /**/ /**/ /// <summary> /// 取得是否溢出 /// </summary> public bool EOF { get { return this ._eOF; } set { this ._eOF = value; } } /**/ /**/ /**/ /// <summary> /// 取得执行语句后得出的纪录条数 /// </summary> public int RecordCount { get { return this ._recordCount; } set { this ._recordCount = value; } } /**/ /**/ /**/ /// <summary> /// 数据库中的表名 /// </summary> public string TableName { get { return this ._tableName; } set { this ._tableName = value; } } /**/ /**/ /**/ /// <summary> /// 返回的记录集 /// </summary> public DataRow RecordSet { get { return this ._recordSet; } set { this ._recordSet = value; } } /**/ /**/ /**/ /// <summary> /// 返回的数据集 /// </summary> public DataSet DS { get { return this ._ds; } set { this ._ds = value; } } /**/ /**/ /**/ /// <summary> /// 字段和控件的映射表 /// </summary> public Hashtable MapTable { get { return this ._mapTable; } set { this ._mapTable = value; } } /**/ /**/ /**/ /// <summary> /// 修改数据时,作为修改结果 /// </summary> public Hashtable Result { get { return this ._result; } set { this ._result = value; } } /**/ /**/ /**/ /// <summary> /// 保存数据用的字段和值对应的哈希表,修改数据时用作条件 /// </summary> public Hashtable Dict { get { return this ._dict; } set { this ._dict = value; } } /**/ /**/ /**/ /// <summary> /// 查询语句 /// </summary> public string CommandString { get { return this ._commandString; } set { this ._commandString = value; } } /**/ /**/ /**/ /// <summary> /// 连接串 /// </summary> public string ConnectString { get { return this ._connectString; } set { this ._connectString = value; } } #endregion DataAccess的构造函数 #region DataAccess的构造函数 /**/ /// <summary> /// 空构造函数 /// <appSettings> /// <add key="DBConn" value="provider=microsoft.jet.oledb.4.0;data source="/> /// <add key="dbPath" value="~/App_Data/ArticleManage.mdb"/> /// </appSettings> /// </summary> public DataAccess() { ConnectString = System.Configuration.ConfigurationSettings.AppSettings[ " DBConn " ] + System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings[ " dbPath " ]) + " ; " ; Conn = new System.Data.OleDb.OleDbConnection(ConnectString); if (System.Configuration.ConfigurationSettings.AppSettings[ " WriteLog " ] == " true " ) { _writeLog = true ; } else { _writeLog = false ; } } ~ DataAccess() { } /**/ /**/ /**/ /// <summary> /// DataAccess的构造函数 /// <appSettings> /// <add key="DBConn" value="provider=microsoft.jet.oledb.4.0;data source="/> /// <add key="DB1" value="~/App_Data/ArticleManage.mdb"/> /// </appSettings> /// </summary> /// <param name="DB1"> 要访问的数据库名,Web.config里设置的连接字符串对应的key </param> /// <param name="TableName1"> 要访问的数据表名 </param> public DataAccess( string DB1, string TableName1) { this .ErrorMessage = "" ; DB = DB1; TableName = TableName1; try { ConnectString = System.Configuration.ConfigurationSettings.AppSettings[ " DBConn " ] + System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings[ " DB " ]) + " ; " ; if (System.Configuration.ConfigurationSettings.AppSettings[ " WriteLog " ] == " true " ) { _writeLog = true ; } else { _writeLog = false ; } Conn = new System.Data.OleDb.OleDbConnection(ConnectString); Dict = new Hashtable(); Result = new Hashtable(); MapTable = new Hashtable(); DS = new DataSet(); // IS_Open = false; _path = " C://WebDebug.log " ; if (_writeLog) { if ( ! File.Exists(_path)) { using (SWCreate = File.CreateText(_path)) { SWCreate.WriteLine( " " ); SWCreate.Close(); } } using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " " ); } } } catch (Exception e) { this .ErrorMessage = e.ToString(); } } /**/ /**/ /**/ /// <summary> /// DataAccess的构造函数 /// </summary> /// <param name="CST"> 数据库的连接字符串 </param> /// <param name="TableName1"> 要访问的数据表名 </param> /// <param name="flag"> 是否初始化 </param> public DataAccess( string CST, string TableName1, bool flag) { if (flag == true ) { this .ErrorMessage = "" ; TableName = TableName1; try { if (System.Configuration.ConfigurationSettings.AppSettings[ " WriteLog " ] == " true " ) { _writeLog = true ; } else { _writeLog = false ; } ConnectString = CST; Conn = new System.Data.OleDb.OleDbConnection(ConnectString); Dict = new Hashtable(); Result = new Hashtable(); MapTable = new Hashtable(); DS = new DataSet(); _path = " C://WebDebug.log " ; if (_writeLog) { if ( ! File.Exists(_path)) { using (SWCreate = File.CreateText(_path)) { SWCreate.WriteLine( " " ); SWCreate.Close(); } } using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " " ); } } } catch (Exception e) { this .ErrorMessage = e.ToString(); } } } #endregion ExecuteNonQuery #region ExecuteNonQuery /**/ /// <summary> /// 执行无返回结果的SQL /// </summary> /// <param name="strSQL"></param> public void ExecuteNonQuery( string strSQL) { Comm = new OleDbCommand(); OleDbTransaction Trans; Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans; Comm.CommandTimeout = 60 ; try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] CommandString = " + strSQL); SWApp.Close(); } } Comm.ExecuteNonQuery(); Trans.Commit(); this .Conn.Close(); } catch (Exception e) { Trans.Rollback(); this .Conn.Close(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] Error Message : " + e.ToString()); } } this .ErrorMessage = e.ToString(); throw new Exception(e.ToString()); } } /**/ /// <summary> /// 执行无返回结果的SQL /// </summary> /// <param name="param"> 参数集合 </param> /// <param name="strSQL"></param> public void ExecuteNonQuery( string strSQL, ICollection param) { Comm = new OleDbCommand(); OleDbTransaction Trans; Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans; Comm.CommandTimeout = 60 ; try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] CommandString = " + strSQL); SWApp.Close(); } } if (param != null ) { foreach (ParamInfo p in param) { IDbDataParameter pa = Comm.CreateParameter(); pa.ParameterName = p.Name; pa.Value = p.Value; // 处理大文本 if (pa is System.Data.OleDb.OleDbParameter && pa.Value != null && pa.Value.ToString().Length >= 4000 ) { System.Data.OleDb.OleDbParameter p1 = pa as System.Data.OleDb.OleDbParameter; p1.OleDbType = System.Data.OleDb.OleDbType.VarWChar; Comm.Parameters.Add(p1); } else { Comm.Parameters.Add(pa); } } } Comm.ExecuteNonQuery(); FillParameterValue(Comm.Parameters, param); Trans.Commit(); this .Conn.Close(); } catch (Exception e) { Trans.Rollback(); this .Conn.Close(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] Error Message : " + e.ToString()); } } this .ErrorMessage = e.ToString(); throw new Exception(e.ToString()); } } #endregion ExecuteScalar #region ExecuteScalar /**/ /// <summary> /// 返回查询结果的第一行第一列的值 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public object ExecuteScalar( string strSQL) { OleDbTransaction Trans; Comm = new OleDbCommand(); Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans ; Comm.CommandTimeout = 60 ; try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] CommandString = " + strSQL); SWApp.Close(); } } object objResutl = Comm.ExecuteScalar(); Trans.Commit(); this .Conn.Close(); return objResutl; } catch (Exception e) { Trans.Rollback(); this .Conn.Close(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] Error Message : " + e.ToString()); } } this .ErrorMessage = e.ToString(); throw new Exception(e.ToString()); } } /**/ /// <summary> /// 返回查询结果的第一行第一列的值 /// </summary> /// <param name="strSQL"></param> /// <param name="param"> 参数集合 </param> /// <returns></returns> public object ExecuteScalar( string strSQL,ICollection param) { OleDbTransaction Trans; Comm = new OleDbCommand(); Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans ; Comm.CommandTimeout = 60 ; try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] CommandString = " + strSQL); SWApp.Close(); } } if ( param != null ) { foreach ( ParamInfo p in param ) { IDbDataParameter pa = Comm.CreateParameter(); pa.ParameterName = p.Name; pa.Value = p.Value; pa.Direction = p.Direction; Comm.Parameters.Add(pa); } } object objResutl = Comm.ExecuteScalar(); FillParameterValue(Comm.Parameters,param); Trans.Commit(); this .Conn.Close(); return objResutl; } catch (Exception e) { Trans.Rollback(); this .Conn.Close(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ] Error Message : " + e.ToString()); } } this .ErrorMessage = e.ToString(); throw new Exception(e.ToString()); } } #endregion ExecuteDataSet #region ExecuteDataSet /**/ /// <summary> /// 执行SQL语句并返回DataTable对象 /// </summary> public DataSet ExecuteDataSet( string strSQL) { OleDbTransaction Trans; Comm = new OleDbCommand(); Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans ; Comm.CommandTimeout = 60 ; DataSet ds = new DataSet(); try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Begin Get DataSet. " ); SWApp.WriteLine( " CommandString = " + strSQL); SWApp.Close(); } } try { IDataReader dr = Comm.ExecuteReader(); do { DataTable dt = new DataTable(); dt.Locale = CultureInfo.CurrentCulture; DataColumn col = null ; DataRowCollection rows = dr.GetSchemaTable().Rows; foreach (DataRow row in rows) { col = new DataColumn(); col.ColumnName = row[ " ColumnName " ] == null ? null : row[ " ColumnName " ].ToString(); col.Unique = Convert.ToBoolean(row[ " IsUnique " ]); col.AllowDBNull = Convert.ToBoolean(row[ " AllowDBNull " ]); col.ReadOnly = Convert.ToBoolean(row[ " IsReadOnly " ]); col.DataType = row[ " DataType " ] as Type; dt.Columns.Add(col); } while (dr.Read()) { DataRow row = dt.NewRow(); foreach (DataColumn c in dt.Columns) { row[c] = dr[c.ColumnName]; } dt.Rows.Add(row); } ds.Tables.Add(dt); } while (dr.NextResult()); dr.Close(); Trans.Commit(); } catch (Exception e) { Trans.Rollback(); this .ErrorMessage = e.ToString(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Error Message: " + e.ToString()); } } throw new Exception(e.ToString()); } if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> End of Getting DataSet. " ); SWApp.Close(); } } } finally { Conn.Close(); } return ds; } /**/ /// <summary> /// 执行SQL语句并返回DataTable对象 /// <param name="strSQL"> SQL语句 </param> /// <param name="param"> 参数集合 </param> /// </summary> public DataSet ExecuteDataSet( string strSQL, ICollection param) { OleDbTransaction Trans; Comm = new OleDbCommand(); Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans ; Comm.CommandTimeout = 60 ; DataSet ds = new DataSet(); try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Begin Get DataSet. " ); SWApp.WriteLine( " CommandString = " + strSQL); SWApp.Close(); } } try { if ( param != null ) { foreach ( ParamInfo p in param ) { IDbDataParameter pa = Comm.CreateParameter(); pa.ParameterName = p.Name; pa.Value = p.Value; pa.Direction = p.Direction; Comm.Parameters.Add(pa); } } IDataReader dr = Comm.ExecuteReader(); do { DataTable dt = new DataTable(); dt.Locale = CultureInfo.CurrentCulture; DataColumn col = null ; DataRowCollection rows = dr.GetSchemaTable().Rows; foreach (DataRow row in rows) { col = new DataColumn(); col.ColumnName = row[ " ColumnName " ] == null ? null : row[ " ColumnName " ].ToString(); col.Unique = Convert.ToBoolean(row[ " IsUnique " ]); col.AllowDBNull = Convert.ToBoolean(row[ " AllowDBNull " ]); col.ReadOnly = Convert.ToBoolean(row[ " IsReadOnly " ]); col.DataType = row[ " DataType " ] as Type; dt.Columns.Add(col); } while (dr.Read()) { DataRow row = dt.NewRow(); foreach (DataColumn c in dt.Columns) { row[c] = dr[c.ColumnName]; } dt.Rows.Add(row); } ds.Tables.Add(dt); } while (dr.NextResult()); dr.Close(); Trans.Commit(); } catch (Exception e) { Trans.Rollback(); this .ErrorMessage = e.ToString(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Error Message: " + e.ToString()); } } throw new Exception(e.ToString()); } if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> End of Getting DataSet. " ); SWApp.Close(); } } } finally { Conn.Close(); } return ds; } #endregion ExecuteDataTable #region ExecuteDataTable /**/ /// <summary> /// 执行SQL语句并返回DataTable对象 /// </summary> public DataTable ExecuteDataTable( string strSQL) { return ExecuteDataSet(strSQL).Tables[ 0 ]; } /**/ /// <summary> /// 执行SQL语句并返回DataTable对象 /// <param name="strSQL"> SQL语句 </param> /// <param name="param"> 参数集合 </param> /// </summary> public DataTable ExecuteDataTable( string strSQL, ICollection param) { return ExecuteDataSet(strSQL,param).Tables[ 0 ]; } #endregion ExecuteDataReader #region ExecuteDataReader /**/ /// <summary> /// <param name="strSQL"> SQL语句 </param> /// </summary> public IDataReader ExecuteDataReader( string strSQL) { OleDbTransaction Trans; Comm = new OleDbCommand(); Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans; Comm.CommandTimeout = 60 ; IDataReader dr ; try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Begin Get DataReader. " ); SWApp.WriteLine( " CommandString = " + strSQL); SWApp.Close(); } } try { dr = Comm.ExecuteReader(); Trans.Commit(); } catch (Exception e) { Trans.Rollback(); this .ErrorMessage = e.ToString(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Error Message: " + e.ToString()); } } throw new Exception(e.ToString()); } if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> End of Getting DataReader. " ); SWApp.Close(); } } } finally { Conn.Close(); } return dr; } /**/ /// <summary> /// /// <param name="strSQL"> SQL语句 </param> /// <param name="param"> 参数集合 </param> /// </summary> public IDataReader ExecuteDataReader( string strSQL, ICollection param) { OleDbTransaction Trans; Comm = new OleDbCommand(); Conn.Open(); Trans = Conn.BeginTransaction(); Comm.CommandText = strSQL; Comm.Connection = Conn; Comm.Transaction = Trans; Comm.CommandTimeout = 60 ; IDataReader dr; try { if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Begin Get DataReader. " ); SWApp.WriteLine( " CommandString = " + strSQL); SWApp.Close(); } } try { if (param != null ) { foreach (ParamInfo p in param) { IDbDataParameter pa = Comm.CreateParameter(); pa.ParameterName = p.Name; pa.Value = p.Value; pa.Direction = p.Direction; Comm.Parameters.Add(pa); } } dr = Comm.ExecuteReader(); Trans.Commit(); } catch (Exception e) { Trans.Rollback(); this .ErrorMessage = e.ToString(); if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> Error Message: " + e.ToString()); } } throw new Exception(e.ToString()); } if (_writeLog) { using (SWApp = File.AppendText(_path)) { SWApp.WriteLine( " [ " + DateTime.Now.ToString() + " ]==> End of Getting DataReader. " ); SWApp.Close(); } } } finally { Conn.Close(); } return dr; } #endregion FillParameterValue #region FillParameterValue /**/ /// <summary> /// 填充输出型参数和返回值型参数 /// </summary> /// <param name="OutPutParameters"> SQL命令执行后的参数集合 </param> /// <param name="param"> SQL命令执行前的参数集合 </param> void FillParameterValue(System.Data.IDataParameterCollection OutPutParameters, ICollection param) { if (OutPutParameters == null || param == null ) return ; ArrayList procParam = new ArrayList(); foreach (IDbDataParameter OleDbParameter in OutPutParameters) // 记录经过存储过程运算后的参数值 { foreach (ParamInfo p in param) { if (p.Name == OleDbParameter.ParameterName) { procParam.Add( new ParamInfo(p.Name, OleDbParameter.Value, p.Direction, p.Size)); } } } ArrayList procOutParam = param as ArrayList; procOutParam.Clear(); foreach (ParamInfo p in procParam) // 填充参数值 { procOutParam.Add( new ParamInfo(p.Name, p.Value, p.Direction, p.Size)); } } #endregion } /**/ /// <summary> /// SQL参数结构体 /// </summary> public struct ParamInfo { /**/ /// <summary> /// 参数名称 /// </summary> public string Name; /**/ /// <summary> /// 值 /// </summary> public object Value; /**/ /// <summary> /// 参数长度 /// </summary> public int Size; /**/ /// <summary> /// 参数方向 /// </summary> public ParameterDirection Direction; /**/ /// <summary> /// 初始化参数对象 /// </summary> /// <param name="name"> 参数名称 </param> /// <param name="val"> 值 </param> public ParamInfo( string name, object val) { Name = name; Value = val; Direction = ParameterDirection.Input; Size = Value == null ? 50 : Value.ToString().Length; } /**/ /// <summary> /// 初始化参数对象 /// </summary> /// <param name="name"> 参数名称 </param> /// <param name="val"> 值 </param> /// <param name="direction"></param> public ParamInfo( string name, object val, ParameterDirection direction) { Name = name; Value = val; Direction = direction; Size = Value == null ? 50 : Value.ToString().Length; } public ParamInfo( string name, object val, ParameterDirection direction, int size) { Name = name; Value = val; Direction = direction; Size = size; } } }
Feedback
楼主的代码结构很不错,清晰简单,值得肯定。
还可以稍微修改一下,兼容所有的ADO.NET数据库,便会更受欢迎了
--引用--------------------------------------------------
梁逸晨: 楼主的代码结构很不错,清晰简单,值得肯定。
还可以稍微修改一下,兼容所有的ADO.NET数据库,便会更受欢迎了
--------------------------------------------------------
提议不错,正有此打算!
@Jake.NET
这里主要是对脏数据进行事务处理!