AccessHelper類使用和案例

C#

1.AccessHelper類庫

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

namespace AccessHelper
{
    public class OleDBHelper
    {
        //【Access連結字元的幾種形式】：_AccessrConnectionString12\_InterBaseConnString\_AccessrConnectionString\_AccessrConnectionStringWithPass\_OracleConnectionStringWithPass
        public static readonly String _AccessrConnectionString12 = @"Provider=Microsoft.ACE.OLEDB.12.0;Password={1};Data Source={0};Persist Security Info=True";
        public static readonly string _InterBaseConnString = @"Isolation Level=65536;User ID={1};SQL Dialect=3;Character Set=;Data Source=""{0}"";Password={2};Provider=""IBOLE.Provider.v4"";Logging Level=0;Lock Resolution=0;Persist Security Info=True;Silent Mode=False;Role=;Mode=Share Deny None;Extended Properties=;Thread Model=1";
        public static readonly String _SqlServerConnectionString = "Provider=SQLOLEDB.1;Password={2};Persist Security Info=True;User ID={1};Initial Catalog={3};Data Source='{0}'";
        public static readonly String _AccessrConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password={1};Data Source={0};Persist Security Info=True";
        public static readonly String _AccessrConnectionStringWithPass = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:System database=;Jet OLEDB:Database Password={1}";
        public static readonly String _OracleConnectionStringWithPass = @"OleDbConnection(""Provider=OraOLEDB.Oracle.1;Server=192.168.85.131; Data  Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.85.131)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = cyyf))); User ID=cyyf;Password=bjcyyf;"");";

    //Access初始化
    private OleDbConnection _Conn = null;

    //Acess連結字元串
    public string ConnectionStringLocalTransaction = "";

    public void SetConnPara4InterBase(string userid, string pass, string filepath)
    {
        this.ConnectionStringLocalTransaction = string.Format(_InterBaseConnString, filepath, userid, pass);
    }
    /// <summary>
    /// 擷取連結字元串：分步驟擷取
    /// </summary>
    /// <param name="ServerIP">位址</param>
    /// <param name="DB">資料庫名</param>
    /// <param name="UserID">使用者名</param>
    /// <param name="Pwd">密碼</param>
    public void SetConnPara4SqlServer(string ServerIP, string DB, string UserID, string Pwd)
    {
        this.ConnectionStringLocalTransaction = string.Format(_SqlServerConnectionString, ServerIP, UserID, Pwd, DB);
    }
    /// <summary>
    /// FileName：擷取Access資料庫檔案位址（eg：DB_FILE:B:\\檔案下載下傳\AS-2000.mdb）
    /// Pwd：密碼（DB_PASSWORD:sasa）
    /// </summary>
    /// <param name="FileName"></param>
    /// <param name="Pwd"></param>
    public void SetConnPara4Access(string FileName, string Pwd)
    {
        SetConnPara4Access(FileName, Pwd, false);
    }

    /// <summary>
    /// 對是否有密碼進行判斷
    /// _AccessrConnectionStringWithPass：表示有密碼
    /// _AccessrConnectionString：表示沒有密碼
    /// </summary>
    /// <param name="FileName"></param>
    /// <param name="Pwd"></param>
    /// <param name="officetr"></param>
    public void SetConnPara4Access(string FileName, string Pwd,bool officetr=false)
    {
        if (!officetr)
        {
            if (Pwd.Trim() != "")
                this.ConnectionStringLocalTransaction = string.Format(_AccessrConnectionStringWithPass, FileName, Pwd);
            else
                this.ConnectionStringLocalTransaction = string.Format(_AccessrConnectionString, FileName, Pwd);
        }
        else
            this.ConnectionStringLocalTransaction = string.Format(_AccessrConnectionString12, FileName, Pwd);
    }

    public void SetConnPara4Oracle(string SID, string DBName, string User, string PWD)
    {
        //this.ConnectionStringLocalTransaction = string.Format();

    }
    /// <summary>
    /// 連結Access資料庫
    /// </summary>
    /// <returns></returns>
    public OleDbConnection GetConnection()
    {
        if (this._Conn == null)
            this._Conn = new OleDbConnection(ConnectionStringLocalTransaction);

        return this._Conn;
    }
    /// <summary>
    /// 測試Access
    /// 差別是：可以【有密碼測試】或【無密碼測試】
    /// </summary>
    /// <returns></returns>
    public Boolean TestConn()
    {
        OleDbConnection conn = this.GetConnection();
        try
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            return true;
        }
        finally
        {
            conn.Close();
        }
    }

    public void SetConnPara4InterBase()
    {
        
    }
    /// <summary>
    /// 測試access資料庫連接配接
    /// 差別：必須有密碼測試
    /// </summary>
    /// <param name="filepath">Access路徑位址</param>
    /// <param name="pass">Access密碼</param>
    /// <returns></returns>
    public static Boolean TestAccessDBConn(string filepath,string pass)
    {
        //連結Access
        OleDbConnection conn = new OleDbConnection(string.Format(_AccessrConnectionStringWithPass, filepath, pass));
        try
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            return true;
        }
        finally
        {
            conn.Close();
        }
    }
    /// <summary>
    /// 對Access表進行擷取資料表或者一行資料
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable DoQueryWithoutPK(string sql)
    {
        OleDbConnection conn = GetConnection();
        try
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
        finally { conn.Close(); }
    }

    /// <summary>
    /// 對Access表進行增删改
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int DoExecSql(string sql)
    {
        OleDbConnection conn = GetConnection();
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            return cmd.ExecuteNonQuery();
        }
        finally
        {
            conn.Close();
        }
    }
  }
}
           

2.AccessHelper調用案例

（1）路徑調用

private OleDBHelper _oleDbHelper = null;
        private OleDBHelper getHelper()
        {
            if (_oleDbHelper == null)
            {
                string DB_FILE = "B:\\檔案下載下傳\Access庫.mdb";
                string DB_PASSWORD = "123";
                _oleDbHelper = new OleDBHelper();
                //調不同形式，根據不同方法調用，我調用的是别人給的類庫，所有就選擇SetConnPara4Access()方法調用
                _oleDbHelper.SetConnPara4Access(DB_FILE, DB_PASSWORD);

        }
        return _oleDbHelper;
    }
           

（2）AccessHelper具體方法使用

《1》進行增删改的方法調用

string sql = "update 表名稱 set 字段名=‘修改字段内容’ where 字段ID = '{0}'  ";
sql = string.Format(sql, '12345678');
//getHelper()調用上面的（1）内容
int sglstr= getHelper().DoExecSql(sql);
if (sglstr>0)
 {
    MessageBox.Show("成功！");
  }
  else
  {
      MessageBox.Show("失敗！");
  }
           

《2》擷取表中資料的方法調用

string sql = "select a.* from 表名稱 where 字段ID = '{0}'  ";
sql = string.Format(sql, '12345678');
//getHelper()調用上面的（1）内容
DataTable dt = getHelper().DoQueryWithoutPK(sql);