前提
需要MySql.Data.dll并引用
百度网盘下载:
链接:https://pan.baidu.com/s/1HUk8NOlXtWNlicmsOVrwJg
提取码:1xro
MySqlHelper.cs具体代码如下:
using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
public abstract class MySqlHelper
{
//数据库连接字符串
public static string connectionString = "server=localhost;port=3306;database=mysqltest;user=root;password=123456";
//用于缓存参数的hash表
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 不带参的增删改查
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static int? ExecuteNonQuery(string sql)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, sql, null);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}",ex.Message);
}
return null;
}
}
/// <summary>
/// 带参数的增删改
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="commandParameters">带参数组</param>
/// <returns></returns>
public static int? ExecuteNonQuery(string sql, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, sql, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}",ex.Message);
}
return null;
}
}
/// <summary>
/// 带参并执行事务的增删改
/// </summary>
/// <param name="trans">sql事务</param>
/// <param name="cmdType">命令类型:储存or文本</param>
/// <param name="sql"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static int? ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string sql, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, trans, cmdType, sql, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}",ex.Message);
}
return null;
}
}
/// <summary>
/// 不带参数的查询数据个数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int? ExecuteScalar(string sql)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, sql, null);
int val = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}",ex.Message);
}
return null;
}
}
/// <summary>
/// 带参的查询数据个数
/// </summary>
/// <param name="sql"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static int? ExecuteScalar(string sql, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, sql, commandParameters);
int val = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
}
return null;
}
}
/// <summary>
/// 带参流查询(不建议使用)
/// 原因:当DAL中的方法调用MySqlHelper时,流会自动关闭而传不出值
/// </summary>
/// <param name="sql"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static MySqlDataReader ExecuteReader(string sql,params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, sql, commandParameters);
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
//关闭连接,抛出异常
conn.Close();
throw;
}
}
}
/// <summary>
/// 提供命令类型的带参流查询(不建议使用)
/// 原因:当DAL中的方法调用MySqlHelper时,流会自动关闭而传不出值
/// </summary>
/// <param name="cmdType"></param>
/// <param name="sql"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static MySqlDataReader ExecuteReader(CommandType cmdType, string sql, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, null, cmdType, sql, commandParameters);
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
conn.Close();
//throw;
}
return null;
}
}
/// <summary>
/// 带参返回一个dataset查询(建议使用)
/// </summary>
/// <param name="sql"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, sql, commandParameters);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
conn.Close();
return ds;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
}
return null;
}
}
/// <summary>
/// 提供命令类型的带参返回一个DataSet查询(建议使用)
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="sql"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataSet GetDataSet(CommandType cmdType, string sql, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程,因为如果这个方法产生一个异常我们要关闭连接,因为没有读取器存在,
try
{
//调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, sql, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
//清除参数
cmd.Parameters.Clear();
conn.Close();
return ds;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
throw ex;
}
}
/// <summary>
/// 将参数集合添加到缓存
/// </summary>
/// <param name="cacheKey">添加到缓存的变量</param>
/// <param name="commandParameters">一个将要添加到缓存的sql参数集合</param>
public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// 找回缓存参数集合
/// </summary>
/// <param name="cacheKey">用于找回参数的关键字</param>
/// <returns></returns>
public static MySqlParameter[] GetCachedParameters(string cacheKey)
{
MySqlParameter[] cachedParam = (MySqlParameter[])parmCache[cacheKey];
if (cachedParam == null) return null;
MySqlParameter[] clonedParms = new MySqlParameter[cachedParam.Length];
for (int i = 0, j = cachedParam.Length; i < j; i++)
{
clonedParms[i] = (MySqlParameter)((ICloneable)cachedParam[i]).Clone();
}
return clonedParms;
}
/// <summary>
/// 准备执行一个命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">MySql连接</param>
/// <param name="cmdText">命令文本——sql语句</param>
/// <param name="cmdParms">执行命令参数</param>
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, string cmdText, MySqlParameter[] cmdParms)
{
try
{
if (conn.State != ConnectionState.Open) conn.Open();
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
cmd.CommandText = cmdText;
cmd.Connection = conn;
}
catch (Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
}
}
/// <summary>
/// 准备执行一个命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">MySql连接</param>
/// <param name="trans">MySql事务</param>
/// <param name="cmdType">命令类型——
/// 储存过程:CommandType.StoredProcedure
/// 储存文本:CommandType.Text
/// </param>
/// <param name="cmdText">执行文本——sql语句</param>
/// <param name="cmdParms">执行命令参数</param>
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
try
{
if (conn.State != ConnectionState.Open) conn.Open();
if (trans != null) cmd.Transaction = trans;
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
}
catch(Exception ex)
{
Console.WriteLine("错误:{0}", ex.Message);
}
}
}
}