最近给一朋友使用Access2007搞了一个小系统,因为之前大都是用SQL Server来作系统DB,窃以为MS的Access也应该差不多,毕竟同出一处嘛,但在具体的使用中却遇到了一些纠结或易混淆的知识、技巧。特此记录下,为自己,也为感兴趣的博友、读者们做个备忘录吧。

  备忘主要包含如下几点:

  • DB 链接

  用OLEDB的方式连接Access,连接串为:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb;Jet OLEDB:Database Password=123

  其中,Provider可能因为.Net Framework版本不同而有相应的版本对应;Data Source可相对路径或绝对路径。

  上述字串建议最好用VS自带的DB链接工具来自动生成。

  • AccessHelper

  Access数据操作类和一般网上流传的SQLHelper大同小异,主要是替换为System.Data.OleDb即可。




ACCESS做一个数据库方便调用_SQL

ACCESS做一个数据库方便调用_Access_02

View AccessHelper


///using System.Data.OleDb;
///using System.IO;
///using System.Reflection;
///using System.Configuration;

/// <summary>
/// Access operation class
/// </summary>
internal class AccessHelper
{
    private static OleDbConnection connection;
    public static OleDbConnection Connection
    {
        get
        {
            string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + GetDbPath() + ";Jet OLEDB:Database Password=" + GetDbPwd();

            if (connection == null)
            {
                connection = new OleDbConnection(conn);
                connection.Open();
            }
            else if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection.Open();
            }
            else if (connection.State == System.Data.ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
            return connection;
        }
    }

    private static string GetDbPath()
    {
        string dbConn = ConfigurationManager.AppSettings["DBPath"].ToString();

        if (string.IsNullOrWhiteSpace(dbConn))
       {
            dbConn = Path.GetDirectoryName(Assembly.GetAssembly(typeof(AccessHelper)).Location) + @"\DB.accdb";
        }
        return dbConn;
    }

    private static string GetDbPwd()
    {
        string dbPwd = ConfigurationManager.AppSettings["DBPwd"].ToString().Trim();

        if (string.IsNullOrWhiteSpace(dbPwd))
        {
            return "123";
        }
        else
        {
            return dbPwd;
        }
    }

    /// <summary>
    /// 返回执行的行数
    /// </summary>
    public static int ExecuteCommand(string safeSql)
    {
        using (OleDbCommand cmd = new OleDbCommand(safeSql, Connection))
        {
            int result = cmd.ExecuteNonQuery();
            return result;
        }
    }
    public static int ExecuteCommand(string sql, params OleDbParameter[] values)
    {
        using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
        {
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
    }
 
    ...Others Methods

    public static DataTable GetDataTable(string safeSql)
    {
        using (OleDbCommand cmd = new OleDbCommand(safeSql, Connection))
        {
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
    public static DataTable GetDataTable(string sql, params OleDbParameter[] values)
    {
        using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
        {
            cmd.Parameters.AddRange(values);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}



  • DB操作参数使用

  Access数据操作时,使用@ID这种格式来表示参数,不是用:ID格式。如下例:



public DataTable GetSalesOne(int sn)
{
     string sql = @"SELECT o.*
                    FROM BOOrder o LEFT OUTER JOIN
                         MCMember m ON o.MemberCode = m.MemberCode
                    WHERE  o.ID = @ID";

      OleDbParameter pid = new OleDbParameter("@ID", sn);
      return AccessHelper.GetDataTable(sql, new OleDbParameter[] { pid });
}



  另外格外注意的是:如果参数比较多的时候,OleDbParameter[]中的参数次序一定要严格与SQL语句中的参数次序一一对应,Access执行时根据SQL中的参数次序顺序匹配OleDbParameter[]中的参数。在AccssHelper中是这样添加参数的:cmd.Parameters.AddRange(values)。如下例:



public bool EditOne(string pwd, string mark)
{
    OleDbParameter ppwd = new OleDbParameter("@UserPwd", pwd);
    OleDbParameter pmark = new OleDbParameter("@Description", mark);
    OleDbParameter pby = new OleDbParameter("@RecordBy", PsvParams.CurrentUserName);
    OleDbParameter pid = new OleDbParameter("@ID", PsvParams.CurrentUserID);

    var sql = @"UPDATE  ADUser
                SET UserPwd = @UserPwd, Description = @Description, 
                    RecordBy = @RecordBy, RecordDt = NOW()
                WHERE   ID = @ID";

    var res = AccessHelper.ExecuteCommand(sql, 
new OleDbParameter[] { ppwd, pmark, pby, pid });

    if (res == 1)
    {
        return true;
    }
    return false;
}



@UserPwd,@ID等参数次序与new OleDbParameter[] { ppwd, pmark, pby, pid }是一致的。

  • 时间参数的使用

  在Access中,时间的匹配查询是如RecordDt BETWEEN  #开始时间# AND #结束时间#这样,用#来包裹时间。如果用之前的那种参数赋值方式new

  1. RecordDt BETWEEN  @sdt AND @edt
    参数赋值时必须指明参数类型
    OleDbParameter pSdt = new OleDbParameter("@sdt", OleDbType.DBDate); 
    pSdt.Value = 具体的开始时间;
  2. RecordDt BETWEEN  #{0}# AND #{1}#
    字符替代的方式实现:
    sql = sql.Replace("{0}", sdt.ToString("yyyy-MM-dd HH:mm:ss")).Replace("{1}", edt.ToString("yyyy-MM-dd HH:mm:ss"));
    当然,如果用字符替代,那SQL中的参数都可采用这种方式而不必new
  • Access中存储过程的另类实现

  在Access中实现存储过程(SP)比较麻烦,更重要的是SP中不能有事务处理等其他高级DB操作。

      同时操作多个表中的数据时这些弊端就出现了,因此取巧使用了OLEDB中的Transaction来实现。上代码:



/// <summary>
/// ExecuteNonQuery with Transaction 
/// sqls:SQL clause 
/// paramList:SQL's parameters
/// </summary>
public static bool ExecuteCommand(Dictionary<string, string> sqls, 
                Dictionary<string, OleDbParameter[]> paramList)
{
     OleDbCommand cmd = new OleDbCommand();
     cmd.Connection = Connection;
     cmd.Transaction = Connection.BeginTransaction();
     try
     {
          foreach (var sqlk in sqls.Keys)
          {
              var sql = sqls[sqlk];
              var pams = paramList[sqlk];
              cmd.CommandText = sql;
              cmd.Parameters.Clear();
              cmd.Parameters.AddRange(pams);
              var res = cmd.ExecuteNonQuery();
           }
           cmd.Transaction.Commit();
           return true;
      }
     catch (Exception ex)
     {
           cmd.Transaction.Rollback();
           //throw ex;
           return false;
     }
     finally
     {
           cmd.Dispose();
           if (Connection.State == ConnectionState.Open)
           {
                Connection.Close();
           }
     }
}



  实现思路:将多个SQL语句(如update,insert,delete)打包后统一放到一个Transaction中执行。使用Dictionary打包是因为可以用Key值来区别SQL语句及其参数,届时可方便匹配(foreach (var sqlk in sqls.Keys))。

  • 其他注意点

  Access中可以建立“查询”,作为表一样可以进行Select,类似于视图。

  Access DB当前时间是NOW(),不是GetDate()。

  ...

  其他关键词的使用可借助Access中的查询建立助手来对比。