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

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

参考示例代码，如下所示：

        /// <summary>  /// MySql 数据库操作类  /// </summary>  public class MySqlHelper  {   #region MysqlConnection   private static MySql.Data.MySqlClient.MySqlConnection _mysqlConnection;   /// <summary>   /// MySqlConnection 连接信息   /// </summary>   public static MySql.Data.MySqlClient.MySqlConnection MysqlConnection   {    get    {     String mysqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Libor_MySql_QuoteCenter_ConnectionString"].ToString();      if (_mysqlConnection == null)      _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 MySqlHelper._mysqlConnection;    }   }   #endregion     #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 = MysqlConnection;      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 = MysqlConnection;      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();       if (MysqlConnection.State != ConnectionState.Closed)        MysqlConnection.Close();       }       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

欢迎大家阅读《.NET 使用 MySql.Data.dll 动态库操作MySql的相助类-MySqlHelper_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)