C#的Access数据库操作 AccessHelper类

由于学校的安全级别提高了，我们以前做的网站使用拼接sql方法查询数据会被拦截，所以说网站就必须重构，在重构之余，复习了下AccessHelper类，随便复习了下C#数据库操作。下面我们就开始吧。
先说个工具类，就是池类Pool，很简单的一个类，直接上代码

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data;using System.Data.OleDb;namespace BusinessAdminAcademy.Utility{    /// <summary>    /// 连接对象。    /// </summary>    public class Pool    {        /// <summary>        /// 初始化。        /// </summary>        /// <param name="id">代码。</param>        /// <param name="connection">数据库连接对象。</param>        /// <param name="isUse">使用标志。</param>        /// <param name="openTime">打开时间。</param>        public Pool(Guid id, OleDbConnection connection, bool isUse, DateTime openTime)        {            this.id = id;            IsUse = isUse;            this.connection = connection;            OpenTime = openTime;        }        private Guid id;        /// <summary>        /// 获取连接对象的代码。        /// </summary>        public Guid Id { get { return id; } }        private OleDbConnection connection;        /// <summary>        /// 获取数据库连接对象。        /// </summary>        public OleDbConnection Connection { get { return connection; } }        /// <summary>        /// 获取或设置一个值，该值指示连接对象是否已经使用，true-已经使用，否则没有使用。        /// </summary>        public bool IsUse { get; set; }        /// <summary>        /// 获取或设置连接对象打开数据库的时间。        /// </summary>        public DateTime OpenTime { get; set; }    }}

然后就是AccessHelper类
最开始当然是找到数据库连接字符串

 public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringAccess"].ConnectionString;

然后写一个静态的获取连接的方法

 public static OleDbConnection getConnection()        {            OleDbConnection conn = new OleDbConnection(ConnectionString);            return conn;        }

先来第一个方法，用于简单地执行一段SQL语句，返回影响行数

        /// <summary>        /// 用于简单地执行一段SQL语句        /// </summary>        /// <param name="conn">OleDbConnection</param>        /// <param name="cmdText">command的字符串sql</param>        /// <returns>返回影响行数</returns>        public static int ExecuteNonQuerySimple(OleDbConnection conn, string cmdText)        {            OleDbCommand cmd = new OleDbCommand();            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            int val = cmd.ExecuteNonQuery();            return val;        }

刚刚那个是执行一条简单的sql，那么有参数列表的话就用下面的方法，加了一个参数列表函数。

        /// <summary>        /// 用于执行一段SQL语句，只有三个最简的必要参数，省去了commandtype。        /// </summary>        /// <param name="connString">连接字符串 (Conntection String)</param>        /// <param name="cmdText">command的字符串 (SQL Statement)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>返回影响行数 (effect line number)</returns>        public static int ExecuteNonQuery(OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms)        {            OleDbCommand cmd = new OleDbCommand();            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);            int val = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();            return val;        }

看到刚刚的方法，你或许会问PrepareCommand是什么用的，对的，你没有猜错，正如它的名字一样，准备Command用的

        /// <summary>        /// 在执行SQL语句之前的准备工作        /// </summary>        /// <param name="cmd">command</param>        /// <param name="conn">connection</param>        /// <param name="trans">trasaction</param>        /// <param name="cmdType">command类型</param>        /// <param name="cmdText">command字符串</param>        /// <param name="cmdParms">参数列表</param>        private 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);            }        }

下面的这个方法和刚刚那个差不多，多了一个CommandType，上面那个是使用默认的

        /// <summary>        /// 用于执行一段SQL语句。传入的值是connection.        /// To excute a SQL statement, which reuturns a integer stand for effect line number.        /// a connection is passed in instead of a connection string        /// </summary>        /// <param name="conn">一个以初始化好的OleDbConnection (a Conncection)</param>        /// <param name="CommandType">CommandType</param>        /// <param name="cmdText">command的字符串 (SQL Statement)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>返回影响行数 (effect line number)</returns>        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();            return val;        }

下面这个方法是与事务有关的

        /// <summary>        /// 用于执行一段SQL语句。需要传入一个事物Transaction.        /// To excute a SQL statement, which reuturns a integer stand for effect line number.        /// a transaction is reqired        /// </summary>        /// <param name="trans">一个Trasaction (Trasaction)</param>        /// <param name="cmdType">command的字符串 (SQL Statement)</param>        /// <param name="cmdText">command的类型，具体见：CommandType (Command type)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>返回影响行数 (effect line number)</returns>        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();            return val;        }

下面三个方法是查询数据用的

        /// <summary>        /// 用于执行一个Select语句返回一个datareader，省略了commandtype参数        /// To excute a SQL statement, and reuturns a dataReader.        /// default command type is text        /// </summary>        /// <param name="connString">连接字符串 (Conntection String)</param>        /// <param name="cmdText">command的字符串 (SQL Statement)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>datareader</returns>        public static OleDbDataReader ExecuteReader(Pool pool,OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms)        {            OleDbCommand cmd = new OleDbCommand();            // we use a try/catch here because if the method throws an exception we want to             // close the connection throw code, because no datareader will exist, hence the             // commandBehaviour.CloseConnection will not work            try            {                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                //              cmd.Parameters.Clear();                return rdr;            }            catch            {                ConnectionPool.Close(pool);                throw;            }        }        /// <summary>        /// 用于执行一个Select语句返回一个datareader        /// To excute a SQL statement, and reuturns a dataReader.        /// </summary>        /// <param name="connString">连接字符串 (Connection String)</param>        /// <param name="cmdType">command的字符串 (SQL Statement)</param>        /// <param name="cmdText">command的类型，具体见：CommandType (Command type)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>dataReader</returns>        public static OleDbDataReader ExecuteReader(Pool pool, OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)        {            OleDbCommand cmd = new OleDbCommand();            // we use a try/catch here because if the method throws an exception we want to             // close the connection throw code, because no datareader will exist, hence the             // commandBehaviour.CloseConnection will not work            try            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);                OleDbDataReader rdr = cmd.ExecuteReader();                //              cmd.Parameters.Clear();                return rdr;            }            catch            {                ConnectionPool.Close(pool);                throw;            }        }        /// <summary>        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列，省去了commandtype        /// To excute a SQL statement, and returns the first column of the first line        /// Default command type is text        /// </summary>        /// <param name="connString">连接字符串 (Conntection String)</param>        /// <param name="cmdText">command的字符串 (SQL Statement)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>the first column of the first line</returns>        public static object ExecuteScalar(OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms)        {            OleDbCommand cmd = new OleDbCommand();            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);            object val = cmd.ExecuteScalar();            cmd.Parameters.Clear();            return val;        }        /// <summary>        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列        /// To excute a SQL statement, and returns the first column of the first line        /// </summary>        /// <param name="connString">连接字符串 (Connection String)</param>        /// <param name="cmdType">command的字符串 (SQL Statement)</param>        /// <param name="cmdText">command的类型，具体见：CommandType (Command type)</param>        /// <param name="cmdParms">参数列表 (Paramters)</param>        /// <returns>the first column of the first line</returns>        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();            return val;        }

AccessHelper类的一些基本方法就这样了，很基础。对OleDbConnection、OleDbCommand等一些数据库操作类有一定基础的学习者来说，很简单。