楼主的代码结构很不错，清晰简单，值得肯定。
还可以稍微修改一下，兼容所有的ADO.NET数据库，便会更受欢迎了
还可以稍微修改一下，兼容所有的ADO.NET数据库，便会更受欢迎了
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; } } 
}
Tag标签:
AccessHelper
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
