AccessHelper Access数据库访问类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

using System.Configuration;

namespace DBUtility
{
    public class AccessHelper
    {
        //解密数据连接字段
        private static string m_connString = "";

        public static string ConnString
        {
            get
            {
                if (m_connString == "")
                {
                    m_connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Command.Pars.AccrssPath + "'";
                }
                return AccessHelper.m_connString;
            }
        }



        /// <summary>
        /// 转换数据格式
        /// </summary>
        /// <param name="reader">数据源</param>
        /// <returns>数据列表</returns>
        private static DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader)
        {
            DataTable dt_tmp = null;
            DataRow dr = null;
            int data_column_count = 0;
            int i = 0;

            data_column_count = reader.FieldCount;
            dt_tmp = BuildAndInitDataTable(data_column_count);

            if (dt_tmp == null)
            {
                return null;
            }

            while (reader.Read())
            {
                dr = dt_tmp.NewRow();

                for (i = 0; i < data_column_count; ++i)
                {
                    dr[i] = reader[i];
                }

                dt_tmp.Rows.Add(dr);
            }

            return dt_tmp;
        }

        /// <summary>
        /// 创建并初始化数据列表
        /// </summary>
        /// <param name="Field_Count">列的个数</param>
        /// <returns>数据列表</returns>
        private static DataTable BuildAndInitDataTable(int Field_Count)
        {
            DataTable dt_tmp = null;
            DataColumn dc = null;
            int i = 0;

            if (Field_Count <= 0)
            {
                return null;
            }

            dt_tmp = new DataTable();

            for (i = 0; i < Field_Count; ++i)
            {
                dc = new DataColumn(i.ToString());
                dt_tmp.Columns.Add(dc);
            }

            return dt_tmp;
        }

        /// <summary>
        /// 从数据库里面获取数据
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns>数据列表</returns>
        public DataTable GetDataTableFromDB(string strSql)
        {
            OleDbConnection conn = new OleDbConnection(ConnString);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                OleDbCommand ole_command = new OleDbCommand(strSql, conn);
                OleDbDataReader ole_reader = ole_reader = ole_command.ExecuteReader(CommandBehavior.Default);

                dt = ConvertOleDbReaderToDataTable(ref ole_reader);

                ole_reader.Close();
                ole_reader.Dispose();
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

            return dt;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回结果</returns>
        public int ExcuteSql(string strSql)
        {
            int nResult = 0;
            OleDbConnection conn = new OleDbConnection(ConnString);
            try
            {
                conn.Open();
                OleDbCommand ole_command = new OleDbCommand(strSql, conn);
                nResult = ole_command.ExecuteNonQuery();
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.ToString());
                return nResult;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
            return nResult;
        }

        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object ExcuteScalar(string strSql)
        {
            object result = null;
            OleDbConnection conn = new OleDbConnection(ConnString);
            try
            {
                conn.Open();
                OleDbCommand ole_command = new OleDbCommand(strSql, conn);
                result = ole_command.ExecuteScalar();
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.ToString());
                return null;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

            return result;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="lstSql">要提交的数据库语句集合</param>
        /// <returns></returns>
        public bool ExcuteTranscation(List<string> lstSql)
        {
            bool result = false;
            OleDbConnection conn = new OleDbConnection(ConnString);
            conn.Open();
            OleDbTransaction tran = conn.BeginTransaction();
            OleDbCommand comm = conn.CreateCommand();
            comm.Transaction = tran;
            try
            {
                foreach (string strSql in lstSql)
                {
                    comm.CommandText = strSql;
                    comm.ExecuteNonQuery();
                }
                tran.Commit();
                result = true;
            }
            catch
            {
                tran.Rollback();
            }
            finally
            {
                conn.Close();
                comm.Dispose();
                conn.Dispose();
            }
            return result;
        }

    }
}