好久沒有寫blog，感覺今年一年都沒怎麼真正開心過，整天有一些事圍繞在身邊，使心情難以平靜下來，真正寫點有意義的東西。部落格園是天天看的，看得多，寫的少，偶爾也是Copy一篇技術文章放一下，便於自己將來查詢。
最近有Winfom做了一個小系統，後臺資料庫是用Access，主要是單機版，考慮方便性，現在總結一些值得後來人參考的地方。
一，資料庫操作基類
一般都要寫個基類，這樣，將來換資料庫，換一下這個DA基類就行了。這個類也是從網上找的。不過感覺有的挺不錯，一般就要返回dataset,datatable,datarow。還有執行sql語句，這裡主要好的地方是可以帶params執行，另一個就是支援事務。拿出來和大家共享一下。
程式碼
public static class AccessHelper
{
//資料庫連線字串
//WebForm
//public static readonly string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//WinForm
//public static readonly string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString;
//public static readonly string conn_str = System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString;
public static readonly string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString + ";Persist Security Info=True";
// 用於快取引數的HASH表
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 給定連線的資料庫用假設引數執行一個sql命令（不返回資料集）
/// </summary>
/// <param name="connectionString">一個有效的連線字串</param>
/// <param name="commandText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>執行命令所影響的行數</returns>
public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 用現有的資料庫連線執行一個sql命令（不返回資料集）
/// </summary>
/// <remarks>
///舉例:
/// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">一個現有的資料庫連線</param>
/// <param name="commandText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>執行命令所影響的行數</returns>
public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
///使用現有的SQL事務執行一個sql命令（不返回資料集）
/// </summary>
/// <remarks>
///舉例:
/// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">一個現有的事務</param>
/// <param name="commandText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>執行命令所影響的行數</returns>
public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 用執行的資料庫連線執行一個返回資料集的sql命令
/// </summary>
/// <remarks>
/// 舉例:
/// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一個有效的連線字串</param>
/// <param name="commandText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>包含結果的讀取器</returns>
public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
//建立一個SqlCommand物件
OleDbCommand cmd = new OleDbCommand();
//建立一個SqlConnection物件
OleDbConnection conn = new OleDbConnection(connectionString);
//在這裡我們用一個try/catch結構執行sql文字命令/儲存過程，因為如果這個方法產生一個異常我們要關閉連線，因為沒有讀取器存在，
//因此commandBehaviour.CloseConnection 就不會執行
try
{
//呼叫 PrepareCommand 方法，對 SqlCommand 物件設定引數
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
//呼叫 SqlCommand 的 ExecuteReader 方法
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除引數
cmd.Parameters.Clear();
return reader;
}
catch
{
//關閉連線，丟擲異常
conn.Close();
throw;
}
}
/// <summary>
/// 返回一個DataSet資料集
/// </summary>
/// <param name="connectionString">一個有效的連線字串</param>
/// <param name="cmdText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>包含結果的資料集</returns>
public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
//建立一個SqlCommand物件，並對其進行初始化
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
//建立SqlDataAdapter物件以及DataSet
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
//填充ds
da.Fill(ds);
// 清除cmd的引數集合
cmd.Parameters.Clear();
//返回ds
return ds;
}
catch
{
//關閉連線，丟擲異常
conn.Close();
throw;
}
}
}
public static DataSet ExecuteDataSet(OleDbConnection conn, string cmdText, params OleDbParameter[] commandParameters)
{
//建立一個SqlCommand物件，並對其進行初始化
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
//建立SqlDataAdapter物件以及DataSet
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
//填充ds
da.Fill(ds);
// 清除cmd的引數集合
cmd.Parameters.Clear();
//返回ds
return ds;
}
catch
{
//關閉連線，丟擲異常
conn.Close();
throw;
}
}
/// <summary>
/// 用指定的資料庫連線字串執行一個命令並返回一個資料集的第一列
/// </summary>
/// <remarks>
///例如:
/// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
///<param name="connectionString">一個有效的連線字串</param>
/// <param name="commandText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>用 Convert.To{Type}把型別轉換為想要的 </returns>
public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 用指定的資料庫連線執行一個命令並返回一個資料集的第一列
/// </summary>
/// <remarks>
/// 例如:
/// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">一個存在的資料庫連線</param>
/// <param name="commandText">儲存過程名稱或者sql命令語句</param>
/// <param name="commandParameters">執行命令所用引數的集合</param>
/// <returns>用 Convert.To{Type}把型別轉換為想要的 </returns>
public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 將引數集合新增到快取
/// </summary>
/// <param name="cacheKey">新增到快取的變數</param>
/// <param name="cmdParms">一個將要新增到快取的sql引數集合</param>
public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// 找回快取引數集合
/// </summary>
/// <param name="cacheKey">用於找回引數的關鍵字</param>
/// <returns>快取的引數集合</returns>
public static OleDbParameter[] GetCachedParameters(string cacheKey)
{
OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms = (OleDbParameter[])((ICloneable)cachedParms).Clone();
return clonedParms;
}
/// <summary>
/// 準備執行一個命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">Sql連線</param>
/// <param name="trans">Sql事務</param>
/// <param name="cmdText">命令文字,例如：Select * from Products</param>
/// <param name="cmdParms">執行命令的引數</param>
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
{
//判斷連線的狀態。如果是關閉狀態，則開啟
if (conn.State != ConnectionState.Open)
conn.Open();
//cmd屬性賦值
cmd.Connection = conn;
cmd.CommandText = cmdText;
//是否需要用到事務處理
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
//新增cmd需要的儲存過程引數
if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
如新增：
程式碼
public int InsertCby(CbyEntity objCbyEntity)
{
int intResult = 0;
string strSql = "insert into cby (dh,xm) values (@dh,@xm)";
OleDbParameter[] prams = {
new OleDbParameter("@dh", objCbyEntity.Dh),
new OleDbParameter("@xm", objCbyEntity.Xm)
};
intResult = AccessHelper.ExecuteNonQuery(AccessHelper.conn_str, strSql, prams);
return intResult;
}
用事務：
程式碼
public bool InsertDfdjzkOrder(DfdjzkEntity etyDfdj, DfdjhmEntity etyDfdjhm, out string errmsg)
{
bool result = true;
errmsg = "";
string strCon = AccessHelper.conn_str;
OleDbConnection con = new OleDbConnection(strCon);
try
{
con.Open();
OleDbTransaction tra = con.BeginTransaction(); //建立事務，開始執行事務
if (this.InsertDfdjzk(tra, etyDfdj) != 1)
{
tra.Rollback();
return false;
}
DfdjhmDA daDfdjhm = new DfdjhmDA();
if (daDfdjhm.UpdateDfdjhm(tra, etyDfdjhm) != 1)
{
tra.Rollback();
return false;
}
tra.Commit();//關閉事務
}
catch (Exception ex)
{
errmsg = ex.Message;
return false;
}
finally
{
con.Close();
}
return result;
}
批量更新時，一定要用同一連線，不然效率肯定狂慢。
程式碼
public bool UpdateXhRqBatch()
{
using (OleDbConnection conn = new OleDbConnection(AccessHelper.conn_str))
{
string strSql = "SELECT * FROM dfdjzk Where 1=1 ";//and djhm>'00050235'
DataSet ds;
ds = AccessHelper.ExecuteDataSet(conn, strSql, null);
foreach (DataRow dr in ds.Tables[0].Rows)
{
string djhm = dr[DfdjzkSchema.DJHM].ToString();
string xhrq = dr[DfdjzkSchema.XHRQ].ToString();
if (!string.IsNullOrEmpty(xhrq) && xhrq.Length == 4)
{
if (xhrq.Substring(0, 2) == "99")
{
xhrq = "19" + xhrq + "01";
}
else
{
xhrq = "20" + xhrq + "01";
}
string updatesql = "update Dfdjzk set xhrq='" + xhrq + "' where djhm ='" + djhm + "' ";
AccessHelper.ExecuteNonQuery(conn, updatesql, null);
}
}
}
return true;
}
2,一些注意點
Access對日期操作時，sql語句要用#
程式碼
string strSql = @"insert into Dfdjzk (zhh,yhdw,bqss1,sqss1,syl1,dj1,je1,
bqss2,sqss2,syl2,dj2,je2,
bqss3,sqss3,syl3,dj3,je3,
je,rzbz,zfbz,djhm,kprq,sssq,ssqh,xhrq,cby,sfy)
values (@zhh,@yhdw,@bqss1,@sqss1,@syl1,@dj1,@je1,
@bqss2,@sqss2,@syl2,@dj2,@je2,
@bqss3,@sqss3,@syl3,@dj3,@je3,
@je,@rzbz,@zfbz,@djhm,#" + objDfdjzkEntity.Kprq.ToString("yyyy-MM-dd") + "#,@sssq,@ssqh,@xhrq,@cby,@sfy)";
Access支援子查詢，不過效率狂低，能不用最好不用，與SQL server沒法比
// string strSql = @"SELECT zhh,ssqh,yhdw from yhdw where zhh<>'' and (glbz=true or
// exists (select 0 from dfdjzk where yhdw.zhh = zhh and sssq='" + sssq + @"')
// )";
用ADO.NET模糊查詢時，和SQL Server一樣。和直接在Access查詢設計器中是不一樣的概念。
if (kplq_end != "")
{
strSql += " And kprq<=#" + kplq_end + "# ";
}
if (kplq_bgn != "")
{
strSql += " And kprq>=#" + kplq_bgn + "# ";
}
if (djhm != "")
{
strSql += " And djhm like '%" + djhm + "%' ";
}
if (zhh != "")
{
strSql += " And zhh like '%" + zhh + "%' ";
}
就先寫這麼多吧。