C# 操作 ACCESS数据库

using System;
using System.Data;
using System.Data.OleDb;

namespace LuoTools.DB
{
    /// <summary>
    /// AccessDb 的摘要说明，以下信息请完整保留
    /// 请在数据传递完毕后调用Close()方法，关闭数据链接。
    /// </summary>
    public class AccessDbClass
    {

        #region 变量声明处
        public OleDbConnection Conn;
        public string ConnString;//连接字符串
        #endregion

        #region 构造函数与连接关闭数据库
        <summary>
        /// 构造函数
        /// </summary>
        /// <param name="Dbpath">ACCESS数据库路径</param>
        public AccessDbClass(string Dbpath)
        {
            ConnString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
            ConnString += Dbpath;
            ConnString += ";Jet OLEDB:Database Password=123";
            Conn = new OleDbConnection(ConnString);
            //Conn.Open();
        }

        <summary>
        /// 打开数据源链接
        /// </summary>
        /// <returns></returns>
        public OleDbConnection DbConn()
        {

            if (Conn != null && Conn.State == ConnectionState.Closed)
                Conn.Open();

            return Conn;
        }

        <summary>
        /// 请在数据传递完毕后调用该函数，关闭数据链接。
        /// </summary>
        public void Close()
        {
            if (Conn != null && Conn.State != ConnectionState.Closed)
                Conn.Close();
        }
        #endregion

        #region 数据库基本操作
        <summary>
        /// 根据SQL命令返回数据DataTable数据表,
        /// 可直接作为dataGridView的数据源
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public DataTable SelectToDataTable(string SQL)
        {
            try
            {
                DbConn();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                DataTable Dt = new DataTable();
                adapter.Fill(Dt);

                return Dt;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                this.Close();
            }
        }

        <summary>
        /// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="subtableName">在返回的数据集中所添加的表的名称</param>
        /// <returns></returns>
        public DataSet SelectToDataSet(string SQL, string subtableName)
        {
            try
            {
                DbConn();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                DataSet Ds = new DataSet();
                Ds.Tables.Add(subtableName);
                adapter.Fill(Ds, subtableName);
                return Ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="subtableName">添加的表名</param>
        /// <param name="DataSetName">被添加的数据集名</param>
        /// <returns></returns>
        public DataSet SelectToDataSet(string SQL, string subtableName, DataSet DataSetName)
        {
            try
            {
                this.DbConn();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                DataTable Dt = new DataTable();
                DataSet Ds = new DataSet();
                Ds = DataSetName;

                adapter.Fill(DataSetName, subtableName);
                return Ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                this.Close();
            }
        }

        <summary>
        /// 根据SQL命令返回OleDbDataAdapter，
        /// 使用前请在主程序中添加命名空间System.Data.OleDb
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
        {
            try
            {
                this.DbConn();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                return adapter;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                this.Close();
            }
        }

        <summary>
        /// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public bool ExecuteSQLNonquery(string SQL)
        {
            OleDbCommand cmd = new OleDbCommand(SQL, Conn);
            try
            {
                this.DbConn();
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// 根据DataTable更新会数据库
        /// </summary>
        /// <param name="dstablename"></param>
        /// <param name="selecttext"></param>
        /// <param name="g"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool UpdateDataTable(string SQL, DataTable dt)
        {
            bool f = false;
            int i = 0;
            try
            {
                if (SQL != null && SQL != "")
                {
                    this.DbConn();
                    OleDbDataAdapter dataadapter = new OleDbDataAdapter(SQL, this.Conn);
                    OleDbCommandBuilder cb = new OleDbCommandBuilder(dataadapter);
                    cb.QuotePrefix = "[";
                    cb.QuoteSuffix = "]";

                    dataadapter.UpdateCommand = cb.GetUpdateCommand();
                    dataadapter.InsertCommand = cb.GetInsertCommand();
                    dataadapter.DeleteCommand = cb.GetDeleteCommand();

                    if (dt != null && dt.GetChanges() != null)
                    {
                        i = dataadapter.Update(dt.GetChanges());
                        dt.AcceptChanges();
                    }
                    if (i > 0)
                    {
                        f = true;
                    }
                }

            }
            catch (Exception ex) { throw ex; }
            finally { this.Close(); }

            return f;
        }

        /// <summary>
        /// 根据DataSet更新会数据库
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="ds"></param>
        /// <param name="subtableName"></param>
        /// <returns></returns>
        public bool UpdateDataSet(string SQL, DataSet ds, String subtableName)
        {
            bool f = false;
            int i = 0;
            try
            {
                if (SQL != null && SQL != "")
                {
                    this.DbConn();
                    OleDbDataAdapter dataadapter = new OleDbDataAdapter(SQL, this.Conn);
                    OleDbCommandBuilder cb = new OleDbCommandBuilder(dataadapter);
                    cb.QuotePrefix = "[";
                    cb.QuoteSuffix = "]";
                    dataadapter.UpdateCommand = cb.GetUpdateCommand();
                    if (ds != null)
                    {
                        if (subtableName != null && subtableName != "")
                            i = dataadapter.Update(ds, subtableName);
                        else
                            i = dataadapter.Update(ds);

                        ds.AcceptChanges();
                    }
                    if (i > 0)
                    {
                        f = true;
                    }
                }

            }
            catch (Exception ex) { throw ex; }
            finally { this.Close(); }

            return f;
        }

        #endregion
    }

}