.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