AccessStudy/AccessStudy.Core/OdbcUtil.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;

namespace AccessStudy.Core
{
    /// <summary>
    /// 注意：参数用？代替，以顺序计数！
    /// 如：select * from　tableA where id=? and age>?
    /// </summary>
    public class OdbcUtil : IDisposable
    {
        public OdbcConnection DbConnection { get; set; }

        public OdbcUtil()
        {
            var accessFile = $"{AppDomain.CurrentDomain.BaseDirectory}AccessDB\\Demo.accdb";
            var connetString = $@"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={accessFile};";
            DbConnection = new OdbcConnection(connetString);
        }

        public OdbcUtil(string connetString)
        {
            DbConnection = new OdbcConnection(connetString);
        }

        public OdbcUtil(OdbcConnection connectionStringBuilder)
        {
            DbConnection = new OdbcConnection(connectionStringBuilder.ConnectionString);
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        public bool HasDataTable(string dataTableName)
        {
            var hosTable = false;

            try
            {
                OpenConnect();
                hosTable = DbConnection.GetSchema("Tables", new string[4] { null, null, dataTableName, "TABLE" }).Rows.Count > 0;
            }
            catch (Exception ex)
            {
                hosTable = false;
                throw ex;
            }
            finally
            {
                CloseConnect();
            }

            return false;
        }

        /// <summary>
        /// 获取DataSet
        /// </summary>
        public DataSet GetDataSet(string dataTableName)
        {
            DataSet dataSet = new DataSet();
            try
            {
                OpenConnect();
                var command = new OdbcCommand()
                {
                    Connection = DbConnection,
                    CommandType = CommandType.Text,
                    CommandText = $"select * from {dataTableName};",
                };

                var adapter = new OdbcDataAdapter(command);

                adapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                CloseConnect();
            }

            return dataSet;
        }

        /// <summary>
        /// 获取DataSet
        /// </summary>
        public DataSet GetDataSet(string sqlText, List<OdbcParameter> parameters = null)
        {
            DataSet dataSet = new DataSet();
            try
            {
                OpenConnect();
                var command = new OdbcCommand()
                {
                    Connection = DbConnection,
                    CommandType = CommandType.Text,
                    CommandText = sqlText,
                };

                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters.ToArray());
                }

                var adapter = new OdbcDataAdapter(command);

                adapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                CloseConnect();
            }

            return dataSet;
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        public DataTable GetDataTable(string dataTableName)
        {
            DataSet dataSet = GetDataSet(dataTableName);
            return dataSet.Tables[0];
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        public DataTable GetDataTable(string sqlText, List<OdbcParameter> parameters = null)
        {
            DataSet dataSet = GetDataSet(sqlText, parameters);
            return dataSet.Tables[0];
        }

        /// <summary>
        /// 获取DataReader
        /// 切记：用完之后主动关闭连接
        /// </summary>
        public OdbcDataReader GetDataReader(string dataTableName)
        {
            OdbcDataReader dataReader = null;
            try
            {
                OpenConnect();
                var command = new OdbcCommand()
                {
                    Connection = DbConnection,
                    CommandType = CommandType.Text,
                    CommandText = $"select * from {dataTableName};",
                };

                dataReader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                CloseConnect();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //CloseConnect();
            }

            return dataReader;
        }

        /// <summary>
        /// 获取DataReader
        /// 切记：用完之后主动关闭连接
        /// </summary>
        public OdbcDataReader GetDataReader(string sqlText, List<OdbcParameter> parameters = null)
        {
            OdbcDataReader dataReader = null;
            try
            {
                OpenConnect();
                var command = new OdbcCommand()
                {
                    Connection = DbConnection,
                    CommandType = CommandType.Text,
                    CommandText = sqlText,
                };

                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters.ToArray());
                }

                dataReader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                CloseConnect();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //CloseConnect();
            }

            return dataReader;
        }

        /// <summary>
        /// 获取第一行第一列的值
        /// 不存在则为null
        /// </summary>
        public object GetScalar(string sqlText, List<OdbcParameter> parameters = null)
        {
            object result = null;

            try
            {
                OpenConnect();
                var command = new OdbcCommand()
                {
                    Connection = DbConnection,
                    CommandType = CommandType.Text,
                    CommandText = sqlText,
                };

                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters.ToArray());
                }

                result = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }

            return result;
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string sqlText, List<OdbcParameter> parameters = null)
        {
            var result = 0;

            try
            {
                OpenConnect();
                var command = new OdbcCommand()
                {
                    Connection = DbConnection,
                    CommandType = CommandType.Text,
                    CommandText = sqlText,
                };

                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters.ToArray());
                }

                result = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }

            return result;
        }

        /// <summary>
        /// 打开当前连接
        /// </summary>
        public void OpenConnect()
        {
            if (DbConnection.State == ConnectionState.Closed)
            {
                DbConnection.Open();
            }
        }

        /// <summary>
        /// 关闭当前连接
        /// </summary>
        public void CloseConnect()
        {
            if (DbConnection.State != ConnectionState.Closed)
            {
                DbConnection.Close();
            }
        }

        public void Dispose()
        {
            if (DbConnection.State == ConnectionState.Open)
            {
                DbConnection.Close();
            }

            DbConnection.Dispose();
        }
    }
}