C#操作Access通用類實例
更新時間:2015年06月28日 11:16:18 作者:xeonfeng
這篇文章主要介紹了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 { //數(shù)據(jù)庫連接字符串 public static readonly string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; // 用于緩存參數(shù)的HASH表 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// 給定連接的數(shù)據(jù)庫用假設(shè)參數(shù)執(zhí)行一個sql命令(不返回數(shù)據(jù)集) /// </summary> /// <param name="connectionString">一個有效的連接字符串</param> /// <param name="commandText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>執(zhí)行命令所影響的行數(shù)</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> /// 用現(xiàn)有的數(shù)據(jù)庫連接執(zhí)行一個sql命令(不返回數(shù)據(jù)集) /// </summary> /// <remarks> ///舉例: /// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="conn">一個現(xiàn)有的數(shù)據(jù)庫連接</param> /// <param name="commandText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>執(zhí)行命令所影響的行數(shù)</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> ///使用現(xiàn)有的SQL事務(wù)執(zhí)行一個sql命令(不返回數(shù)據(jù)集) /// </summary> /// <remarks> ///舉例: /// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="trans">一個現(xiàn)有的事務(wù)</param> /// <param name="commandText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>執(zhí)行命令所影響的行數(shù)</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> /// 用執(zhí)行的數(shù)據(jù)庫連接執(zhí)行一個返回數(shù)據(jù)集的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">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>包含結(jié)果的讀取器</returns> public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters) { //創(chuàng)建一個SqlCommand對象 OleDbCommand cmd = new OleDbCommand(); //創(chuàng)建一個SqlConnection對象 OleDbConnection conn = new OleDbConnection(connectionString); //在這里我們用一個try/catch結(jié)構(gòu)執(zhí)行sql文本命令/存儲過程,因為如果這個方法產(chǎn)生一個異常我們要關(guān)閉連接,因為沒有讀取器存在, //因此commandBehaviour.CloseConnection 就不會執(zhí)行 try { //調(diào)用 PrepareCommand 方法,對 SqlCommand 對象設(shè)置參數(shù) PrepareCommand(cmd, conn, null, cmdText, commandParameters); //調(diào)用 SqlCommand 的 ExecuteReader 方法 OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); //清除參數(shù) cmd.Parameters.Clear(); return reader; } catch { //關(guān)閉連接,拋出異常 conn.Close(); throw; } } /// <summary> /// 返回一個DataSet數(shù)據(jù)集 /// </summary> /// <param name="connectionString">一個有效的連接字符串</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>包含結(jié)果的數(shù)據(jù)集</returns> public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters) { //創(chuàng)建一個SqlCommand對象,并對其進(jìn)行初始化 OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdText, commandParameters); //創(chuàng)建SqlDataAdapter對象以及DataSet OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); try { //填充ds da.Fill(ds); // 清除cmd的參數(shù)集合 cmd.Parameters.Clear(); //返回ds return ds; } catch { //關(guān)閉連接,拋出異常 conn.Close(); throw; } } } /// <summary> /// 用指定的數(shù)據(jù)庫連接字符串執(zhí)行一個命令并返回一個數(shù)據(jù)集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> ///<param name="connectionString">一個有效的連接字符串</param> /// <param name="commandText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>用 Convert.To{Type}把類型轉(zhuǎn)換為想要的 </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> /// 用指定的數(shù)據(jù)庫連接執(zhí)行一個命令并返回一個數(shù)據(jù)集的第一列 /// </summary> /// <remarks> /// 例如: /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="conn">一個存在的數(shù)據(jù)庫連接</param> /// <param name="commandText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param> /// <returns>用 Convert.To{Type}把類型轉(zhuǎn)換為想要的 </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> /// 將參數(shù)集合添加到緩存 /// </summary> /// <param name="cacheKey">添加到緩存的變量</param> /// <param name="cmdParms">一個將要添加到緩存的sql參數(shù)集合</param> public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// 找回緩存參數(shù)集合 /// </summary> /// <param name="cacheKey">用于找回參數(shù)的關(guān)鍵字</param> /// <returns>緩存的參數(shù)集合</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> /// 準(zhǔn)備執(zhí)行一個命令 /// </summary> /// <param name="cmd">sql命令</param> /// <param name="conn">Sql連接</param> /// <param name="trans">Sql事務(wù)</param> /// <param name="cmdText">命令文本,例如:Select * from Products</param> /// <param name="cmdParms">執(zhí)行命令的參數(shù)</param> private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms) { //判斷連接的狀態(tài)。如果是關(guān)閉狀態(tài),則打開 if (conn.State != ConnectionState.Open) conn.Open(); //cmd屬性賦值 cmd.Connection = conn; cmd.CommandText = cmdText; //是否需要用到事務(wù)處理 if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; //添加cmd需要的存儲過程參數(shù) if (cmdParms != null) { foreach (OleDbParameter parm in cmdParms) cmd.Parameters.Add(parm); } } }
希望本文所述對大家的C#程序設(shè)計有所幫助。
您可能感興趣的文章:
- C#數(shù)據(jù)庫操作類AccessHelper實例
- c#連接access數(shù)據(jù)庫操作類分享
- asp.net(C#) Access 數(shù)據(jù)操作類
- ASP.net(c#)用類的思想實現(xiàn)插入數(shù)據(jù)到ACCESS例子
- C# Access數(shù)據(jù)庫增刪查改的簡單方法
- ACCESS的參數(shù)化查詢,附VBSCRIPT(ASP)和C#(ASP.NET)函數(shù)
- C#動態(tài)創(chuàng)建Access數(shù)據(jù)庫及表的方法
- C#編程實現(xiàn)連接ACCESS數(shù)據(jù)庫實例詳解
- C#對Access進(jìn)行增刪改查的完整示例
- C#使用ADO.Net部件來訪問Access數(shù)據(jù)庫的方法
- C#通過oledb訪問access數(shù)據(jù)庫的方法
- C#實現(xiàn)Access通用訪問類OleDbHelper完整實例
相關(guān)文章
C# WebService發(fā)布以及IIS發(fā)布
這篇文章主要介紹了C# WebService發(fā)布以及IIS發(fā)布的相關(guān)資料,感興趣的小伙伴們可以參考一下2016-07-07Unity3D使用鼠標(biāo)旋轉(zhuǎn)縮放平移視角
這篇文章主要為大家詳細(xì)介紹了Unity3D使用鼠標(biāo)旋轉(zhuǎn)縮放平移視角,文中示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2019-07-07unity 如何判斷鼠標(biāo)是否在哪個UI上(兩種方法)
這篇文章主要介紹了unity 判斷鼠標(biāo)是否在哪個UI上的兩種實現(xiàn)方法,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-04-04