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);