最近给一朋友使用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;
}
@UserPwd，@ID等参数次序与new OleDbParameter[] { ppwd, pmark, pby, pid }是一致的。
- 时间参数的使用
在Access中，时间的匹配查询是如RecordDt BETWEEN #开始时间# AND #结束时间#这样，用#来包裹时间。如果用之前的那种参数赋值方式new
- RecordDt BETWEEN @sdt AND @edt
参数赋值时必须指明参数类型。
OleDbParameter pSdt = new OleDbParameter("@sdt", OleDbType.DBDate);
pSdt.Value = 具体的开始时间; - 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中的查询建立助手来对比。
