C# Mysql操作类

using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows;
using System.Collections.Generic;
using System.Collections;
using System.Diagnostics;

namespace ERP
{
    class MySQLClass
    {
        //MySqlCommandBuilder sb1 = new MySqlCommandBuilder(myAdapter); //sb1 到头来也没用过，但只要有这一句，update就不会再报上述错误，神奇吧
        //局域网中使用的软件，用户数也较少，尽量使用长连接来做，所以需要手动关闭数据连接
        public static string connStr = String.Format("server={0};user id={1}; password={2}; port={3}; database=fszj; pooling=false;", "127.0.0.1", "root", "fsccwjc", 3306);
        public static MySqlConnection conn;
        public static MySqlCommand cmd;
        /// <summary>
        /// 批量操作每批次记录数
        /// </summary>
        public static int BatchSize = 2000;

        /// <summary>
        /// 超时时间
        /// </summary>
        public static int CommandTimeOut = 600;

        //private string connStr = String.Format("server={0};user id={1}; password={2}; port={3}; database=fszj; pooling=false; Allow Zero Datetime=True", "127.0.0.1", "root", "fsccwjc", 3306);
        //连接句中包含Allow Zero Datetime=True ，会导致datagrid中使用日期选择器需要转换类型，更严重的是，使用转换会导致textblock stringformat失效


        public MySQLClass() //初始化
        {
            open();
        }
        #region 查询数据连接状态
        /// <summary>
        /// 查询数据连接状态
        /// </summary>
        /// <returns>返回连接状态</returns>
        public static Boolean GetConnectionState() //返回连接状态
        {
            if (conn != null) //如果连接不为空
            {
                if (conn.State.Equals(ConnectionState.Open))
                {
                    return true;  //如果连接状态为打开，返回真
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        #endregion
        #region 打开数据连接
        /// <summary>
        /// 打开数据连接
        /// </summary>
        /// <returns>返回数据库打开操作是否成功</returns>
        public static Boolean open()
        {
            if (conn != null) //如果连接不为空
            {
                if (conn.State.Equals(ConnectionState.Open)) return true;  //如果连接状态为打开，返回真
            }
            try
            {
                conn = new MySqlConnection(connStr);
                conn.Open();
            }
            catch
            {
                return false;
            }
            return true;
        }
        #endregion
        #region 关闭数据连接
        /// <summary>
        /// 关闭数据连接
        /// </summary>
        public static void close()
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 获取MySqlCommand对象
        /// <summary>
        /// //由conn链接创建MySqlCommand
        /// </summary>
        /// <param name="conn">由类创建时自动获取，无须提供</param>
        /// <returns></returns>
        public static MySqlCommand GetMySQLCommand()
        {
            if (cmd != null) return cmd;
            try
            {
                open();
                cmd = conn.CreateCommand();
                return cmd;
            }
            catch
            {
                return null;
            }
        }
        #endregion
        #region  创建MySqlDataReader对象
        /// <summary>
        /// 创建一个MySqlDataReader对象,返回执行SQL语句后得到的结果集
        /// CommandBehavior.SingleResult 执行返回一个结果集，并不关闭连接
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>返回MySqlDataReader对象</returns>
        public MySqlDataReader GetMySQLReader(string sqlStr)
        {
            open();
            GetMySQLCommand();
            cmd.CommandText = sqlStr;
            MySqlDataReader mysqlread = cmd.ExecuteReader(CommandBehavior.SingleResult);
            return mysqlread;
        }
        #endregion
        #region 创建MySqlDataAdapter对象
        /// <summary>
        /// 创建MySqlDataAdapter
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns></returns>
        public MySqlDataAdapter GetMySQLAdapter(string sqlStr)
        {
            open();
            GetMySQLCommand();
            cmd.CommandText = sqlStr;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            return da;
        }
        #endregion
        #region  执行MySqlCommand命令,返回执行成功与否
        /// <summary>
        /// 执行单句commandText，返回执行成功与否
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        public static Boolean ExecuteSQL(string commandText)
        {
            if (!open()) return false;
            if (GetMySQLCommand() == null) return false;
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = commandText;
                cmd.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 执行多句commandText，返回执行成功与否
        /// </summary>
        /// <param name="commandText">SQL语句集</param>
        public static Boolean ExecuteSQL(List<string> commandText)
        {
            if (commandText == null) return false; //传入命令集为空
            if (!open()) return false;
            if (GetMySQLCommand() == null) return false;
            MySqlTransaction trans=conn.BeginTransaction();
            cmd.Transaction = trans; //绑定事务
            try
            {
                string sql;
                for (int i = 0; i < commandText.Count; i++)
                {
                    sql = commandText[i];
                    if (sql.Trim() != "")
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                return true;
            }
            catch(MySqlException ex)
            {
                Debug.WriteLine("执行SQL语句时出现异常："+ex.Message);
                trans.Rollback();
                return false;
            }
        }
        /// <summary>
        /// 执行事务，返回执行成功与否
        /// </summary>
        /// <param name="sp_Name">存储过程名</param>
        /// <param name="parms">参数集</param>
        /// <returns></returns>
        public static Boolean ExecuteSQL(string sp_Name, params MySqlParameter[] parms)
        {
            GetMySQLCommand();
            MySqlTransaction trans = conn.BeginTransaction();
            PrepareCommand(trans, CommandType.StoredProcedure, sp_Name, parms);
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
                cmd.Parameters.Clear();
                return true;
            }
            catch
            {
                trans.Rollback();
                cmd.Parameters.Clear();
                return false;
            }
        }
        #endregion
        #region 获取DataSet
        /// <summary>
        /// 执行SQL查询语句获取dataset
        /// </summary>
        /// <param name="commandText">查询语句</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string commandText)
        {
            open();
            return MySqlHelper.ExecuteDataset(conn, commandText);
        }
        /// <summary>
        /// 执行存储过程获取dataset
        /// </summary>
        /// <param name="sp_Name">存储过程名</param>
        /// <param name="parms">参数集</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sp_Name, params MySqlParameter[] parms)
        {
            GetMySQLCommand();
            PrepareCommand(null, CommandType.StoredProcedure, sp_Name, parms);
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            cmd.Parameters.Clear();
            return ds;
        }
        #endregion

        #region 通过SQL查询语句获取datatable
        /// <summary>
        /// 查询记录并填充到datatable，返回该datatable
        /// </summary>
        /// <param name="commandText">查询语句</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string commandText)
        {
            return MySqlHelper.ExecuteDataset(conn, commandText).Tables[0];
        }

        /// <summary>
        /// 执行存储过程，返回datatable
        /// </summary>
        /// <param name="sp_Name">存储过程名</param>
        /// <param name="parms">参数集</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sp_Name, params MySqlParameter[] parms)
        {
            try
            {
                GetMySQLCommand();
                PrepareCommand(null, CommandType.StoredProcedure, sp_Name, parms);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                cmd.Parameters.Clear();
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
           
        }
        #endregion

        #region 获取第一行第一列的值
        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string commandText)
        {
            GetMySQLCommand();
            PrepareCommand(null, CommandType.Text, commandText, null);
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 执行存储过程,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="sp_Name">存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string sp_Name, params MySqlParameter[] parms)
        {
            GetMySQLCommand();
            MySqlTransaction temTrans = null;
            PrepareCommand(temTrans,CommandType.StoredProcedure, sp_Name, parms);
            object retval = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return retval;
        }
        #endregion
        /// <summary>
        /// 前处理，准备执行
        /// </summary>
        /// <param name="_trans">事务，不为NULL则使用事务方式执行</param>
        /// <param name="cmdType">命令类型（SQL语句或存储过程）</param>
        /// <param name="cmdText">SQL语句或存储过程名</param>
        /// <param name="cmdParms">参数集</param>
        private static void PrepareCommand(MySqlTransaction _trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();
            GetMySQLCommand();
            cmd.CommandText = cmdText;

            if (_trans != null)
                cmd.Transaction = _trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null && cmdParms.Length > 0)
            {
                //预处理MySqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
                foreach (MySqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null) || ((string)parameter.Value==""))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}