实用至上--Access2007使用中的一些知识点

　　最近给一朋友使用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即可。

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

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

• 时间参数的使用

　　在Access中，时间的匹配查询是如RecordDt BETWEEN  #开始时间# AND #结束时间#这样，用#来包裹时间。如果用之前的那种参数赋值方式new OleDbParameter(para,value)就会出问题，可以用如下两种方式来实现：

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 OleDbParameter。

• 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中的查询建立助手来对比。

转载于:https://www.cnblogs.com/nh022/archive/2013/05/05/3051186.html