C#操作Access通用類執行個體

這篇文章主要介紹了C#操作Access通用類,執行個體分析了C#操作access的各種常見技巧,非常具有實用價值,需要的朋友可以參考下

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.OleDb;using System.Collections;/// <summary>/// AcceHelper 的摘要說明/// </summary>public static class AccessHelper{  //資料庫連接字串  public static readonly string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  // 用於緩衝參數的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;      }    }  }  /// <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);    }  }}