AccessHelper 类
- namespace DataAccess
- {
- public class AccessHelper
- {
- private OleDbConnection conn;
- private static readonly string OleDbConnectionString = ConfigurationManager.ConnectionStrings["OledbConnectionStrings"].ConnectionString;
- /// <summary>
- /// OleDb 数据库访问助手
- /// </summary>
- public AccessHelper()
- {
- this.conn = new OleDbConnection(OleDbConnectionString);
- }
- /// <summary>
- /// 打开链接对象
- /// </summary>
- private void ConnOpen()
- {
- if (this.conn.State == ConnectionState.Closed)
- {
- this.conn.Open();
- }
- }
- /// <summary>
- /// 关闭链接对象
- /// </summary>
- private void ConnClose()
- {
- if (this.conn.State == ConnectionState.Open)
- {
- this.conn.Close();
- }
- }
- /// <summary>
- /// 释放链接对象资源
- /// </summary>
- public void Dispose()
- {
- this.conn.Close();
- this.conn.Dispose();
- this.conn = null;
- }
- #region 执行成员
- /// <summary>
- /// 操作 OleDb 数据库并返回影响行。
- /// </summary>
- /// <param name="cmd">命令对象</param>
- /// <returns>影响行</returns>
- public int ExecuteNonQuery(OleDbCommand cmd)
- {
- int val;
- cmd.Connection = this.conn;
- ConnOpen();
- try
- {
- val = cmd.ExecuteNonQuery();
- }
- catch
- {
- throw;
- }
- finally
- {
- cmd.Parameters.Clear();
- this.Dispose();
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库并返回影响行。(支持事务处理)
- /// </summary>
- /// <param name="cmd">多个命令对象</param>
- /// <returns>最后命令对象影响行</returns>
- public int ExecuteNonQuery(OleDbCommand[] cmd)
- {
- int val;
- ConnOpen();
- OleDbTransaction sqlts = this.conn.BeginTransaction();
- try
- {
- for (int i = 0; i < cmd.Length; i++)
- {
- cmd[i].Connection = this.conn;
- cmd[i].Transaction = sqlts;
- cmd[i].ExecuteNonQuery();
- cmd[i].Parameters.Clear();
- }
- sqlts.Commit();
- val = 1;
- }
- catch
- {
- sqlts.Rollback();
- throw;
- }
- finally
- {
- this.Dispose();
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库并返回影响行。
- /// </summary>
- /// <param name="sqlstr">SQL 语句</param>
- /// <returns>影响行</returns>
- public int ExecuteNonQuery(string sqlstr)
- {
- int val;
- OleDbCommand cmd = new OleDbCommand(sqlstr, this.conn);
- this.conn.Open();
- try
- {
- val = cmd.ExecuteNonQuery();
- }
- catch
- {
- throw;
- }
- finally
- {
- this.Dispose();
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库并返回第一行第一列数据。
- /// </summary>
- /// <param name="cmd">命令对象</param>
- /// <returns>第一行第一列数据</returns>
- public object ExecuteScalar(OleDbCommand cmd)
- {
- object val;
- cmd.Connection = this.conn;
- ConnOpen();
- try
- {
- val = cmd.ExecuteScalar();
- }
- catch
- {
- throw;
- }
- finally
- {
- cmd.Parameters.Clear();
- this.Dispose();
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库并返回第一行第一列数据。
- /// </summary>
- /// <param name="sqlstr">SQL 语句</param>
- /// <returns>第一行第一列数据</returns>
- public object ExecuteScalar(string sqlstr)
- {
- object val;
- OleDbCommand cmd = new OleDbCommand(sqlstr, this.conn);
- this.conn.Open();
- try
- {
- val = cmd.ExecuteScalar();
- }
- catch
- {
- throw;
- }
- finally
- {
- this.Dispose();
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库返回只向前数据流(需要手动关闭流)。
- /// </summary>
- /// <param name="cmd">命令对象</param>
- /// <returns>只向前数据流</returns>
- public OleDbDataReader ExecuteReader(OleDbCommand cmd)
- {
- OleDbDataReader val;
- cmd.Connection = this.conn;
- ConnOpen();
- try
- {
- val = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- }
- catch
- {
- cmd.Parameters.Clear();
- this.Dispose();
- throw;
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库返回只向前数据流(需要手动关闭流)。
- /// </summary>
- /// <param name="sqlstr">SQL 语句</param>
- /// <returns>只向前数据流</returns>
- public OleDbDataReader ExecuteReader(string sqlstr)
- {
- OleDbDataReader val;
- OleDbCommand cmd = new OleDbCommand(sqlstr, this.conn);
- ConnOpen();
- try
- {
- val = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- }
- catch
- {
- this.Dispose();
- throw;
- }
- return val;
- }
- /// <summary>
- /// 操作 OleDb 数据库返回已填充的数据集。
- /// </summary>
- /// <param name="cmd">命令对象</param>
- /// <returns>已填充的数据集</returns>
- public DataSet ExecuteDataSet(OleDbCommand cmd)
- {
- cmd.Connection = this.conn;
- OleDbDataAdapter da = new OleDbDataAdapter(cmd);
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds);
- }
- catch
- {
- throw;
- }
- finally
- {
- da.Dispose();
- cmd.Parameters.Clear();
- this.Dispose();
- }
- return ds;
- }
- /// <summary>
- /// 操作 OleDb 数据库返回已填充的数据集。
- /// </summary>
- /// <param name="sqlstr">SQL 语句</param>
- /// <returns>已填充的数据集</returns>
- public DataSet ExecuteDataSet(string sqlstr)
- {
- OleDbCommand cmd = new OleDbCommand(sqlstr, this.conn);
- OleDbDataAdapter da = new OleDbDataAdapter(cmd);
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds);
- }
- catch
- {
- throw;
- }
- finally
- {
- da.Dispose();
- cmd.Parameters.Clear();
- this.Dispose();
- }
- return ds;
- }
- #endregion