.NET 使用 MySql.Data.dll 动态库操作MySql的帮助类--MySqlHelper

.NET 使用 MySql.Data.dll 动态库操作MySql的帮助类--MySqlHelper

參考演示样例代码，例如以下所看到的：

/// <summary>/// MySql 数据库操作类/// </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语句或存储过程,返回受影响的行数/// <summary>/// 运行MySQL语句或存储过程/// </summary>/// <param name="type">命令类型</param>/// <param name="sqlString">sql语句</param>/// <param name="pstmt">參数</param>/// <returns>运行结果</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>/// 运行带事务的SQL语句或存储过程/// </summary>/// <param name="trans">事务</param>/// <param name="type">命令类型</param>/// <param name="sqlString">SQL语句</param>/// <param name="pstmt">參数</param>/// <returns>运行结果</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">參数</param>/// <returns>运行结果</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官网提供的组件,下载后加入引用到当前项目就可以使用

           2、參数化处理

               在SQLServer中參数化处理符号为"@"，參数化演示样例如：

         SqlParameter[] param = { new SqlParameter("@TABLEDATA", tableData)};

在MySql中參数化处理符号为“?”,參数化示比如：

         MySql.Data.MySqlClient.MySqlParameter[] paras = {new MySql.Data.MySqlClient.MySqlParameter("?LIBOR_NAME",name),};

其它參考文章例如以下：

http://www.jb51.net/article/30342.htm