.NET 使用 MySql.Data.dll 動態(tài)庫操作MySql的幫助類--MySqlHelper
參考演示樣例代碼,例如以下所看到的:
/// <summary> /// MySql 數(shù)據(jù)庫操作類 /// </summary> public class MySqlHelper { /// <summary> /// MysqlConnection /// </summary> private static MySql.Data.MySqlClient.MySqlConnection MysqlConnection; /// <summary> /// 獲MySql 連接置信息 /// </summary> /// <returns></returns> public static MySql.Data.MySqlClient.MySqlConnection GetCon() { String mysqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Libor_MySql_QuoteCenter_ConnectionString"].ToString(); if (MysqlConnection == null) using (MysqlConnection = new MySql.Data.MySqlClient.MySqlConnection(mysqlConnectionString)) { }; if (MysqlConnection.State == System.Data.ConnectionState.Closed) MysqlConnection.Open(); if (MysqlConnection.State == System.Data.ConnectionState.Broken) { MysqlConnection.Close(); MysqlConnection.Open(); } return MysqlConnection; } #region 運行MySQL語句或存儲過程,返回受影響的行數(shù) /// <summary> /// 運行MySQL語句或存儲過程 /// </summary> /// <param name="type">命令類型</param> /// <param name="sqlString">sql語句</param> /// <param name="pstmt">參數(shù)</param> /// <returns>運行結(jié)果</returns> public static int ExecuteNonQuery(CommandType type, String sqlString, MySql.Data.MySqlClient.MySqlParameter[] para) { try { using (MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand()) { com.Connection = GetCon(); com.CommandText = @sqlString; com.CommandType = type; if (para != null) com.Parameters.AddRange(para); int val = com.ExecuteNonQuery(); com.Parameters.Clear(); return val; } } catch (Exception ex) { Logger.Error("運行MySQL語句或存儲過程,異常!", ex); return 0; } finally { if (MysqlConnection.State != ConnectionState.Closed) MysqlConnection.Close(); } } /// <summary> /// 運行帶事務(wù)的SQL語句或存儲過程 /// </summary> /// <param name="trans">事務(wù)</param> /// <param name="type">命令類型</param> /// <param name="sqlString">SQL語句</param> /// <param name="pstmt">參數(shù)</param> /// <returns>運行結(jié)果</returns> public static int ExecuteNonQuery(MySql.Data.MySqlClient.MySqlTransaction trans, CommandType type, String sqlString, MySql.Data.MySqlClient.MySqlParameter[] para) { try { using (MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand()) { com.Connection = MysqlConnection; com.CommandText = @sqlString; com.CommandType = type; if (para != null) com.Parameters.AddRange(para); if (trans != null) com.Transaction = trans; int val = com.ExecuteNonQuery(); com.Parameters.Clear(); return val; } } catch (Exception ex) { Logger.Error("運行MySQL語句或存儲過程2,異常!", ex); return 0; } finally { if (MysqlConnection.State != ConnectionState.Closed) MysqlConnection.Close(); } } #endregion #region 運行SQL語句或存儲過程,返回 DataTable /// <summary> /// 運行SQL語句或存儲過程,返回 DataTable /// </summary> /// <param name="type">命令類型</param> /// <param name="sqlString">SQL語句</param> /// <param name="pstmt">參數(shù)</param> /// <returns>運行結(jié)果</returns> public static DataTable ExecuteReaderToDataTable(CommandType type, String sqlString, MySql.Data.MySqlClient.MySqlParameter[] para) { DataTable dt = new DataTable(); MySql.Data.MySqlClient.MySqlDataReader dr = null; try { using (MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand()) { com.Connection = GetCon(); com.CommandText = @sqlString; com.CommandType = type; if (para != null) com.Parameters.AddRange(para); using (dr = com.ExecuteReader(CommandBehavior.CloseConnection)) { if (dr != null) dt.Load(dr); com.Parameters.Clear(); } return dt; } } catch (Exception ex) { Logger.Error("運行SQL語句或存儲過程,返回 DataTable,異常!", ex); return null; } finally { if (dr != null && !dr.IsClosed) dr.Close(); if (MysqlConnection.State != ConnectionState.Closed) MysqlConnection.Close(); } } #endregion }
特別說明:
? ? ? ? ? ? ? 1、MySql.Data.dll? mysql官網(wǎng)提供的組件,下載后加入引用到當(dāng)前項目就可以使用
? ? ? ? ? ?2、參數(shù)化處理
? ? ? ? ? ? ? ? 在SQLServer中參數(shù)化處 理符號為"@", 參數(shù)化演示樣例 如:
SqlParameter[] param = { new SqlParameter("@TABLEDATA", tableData) };? ? ? ? ? ? ? ?在MySql中參數(shù)化處理符號為“?”,參數(shù)化示比如:
MySql.Data.MySqlClient.MySqlParameter[] paras = { new MySql.Data.MySqlClient.MySqlParameter("?LIBOR_NAME",name), };其它參考文章例如以下:
http://www.jb51.net/article/30342.htm
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
