C#的Access数据库操作 AccessHelper类

亦凉 2022-08-03 00:09 275阅读 0赞

由于学校的安全级别提高了，我们以前做的网站使用拼接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等一些数据库操作类有一定基础的学习者来说，很简单。