C#MySqlHelper类

前提

需要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);
            }
        }
    }
}