C#操作mysql数据库的封装类（封装函数方法）

前言：

C#操作mysql需要添加相应版本的MySql.Data.dll引用（.NET4.0就引入.NET4.0版本的MySql.Data.dll），不会的可以看我的另一篇文章：https://blog.csdn.net/qq15577969/article/details/79518731

.NET4.0版本的MySql.Data.dll下载地址：https://download.csdn.net/download/qq15577969/12084311

MysqlBase.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Diagnostics;
using MySql.Data.MySqlClient;

namespace 充值网关
{
    class MysqlBase
    {
        private MySqlConnection conn = null;
        private MySqlCommand command = null;
        private MySqlDataReader reader = null;

        /// <summary>
        /// 构造方法里建议连接
        /// </summary>
        /// <param name="connstr"></param>
        public MysqlBase(string connstr)
        {
            conn = new MySqlConnection(connstr);
        }
        /// <summary>
        /// 检测数据库连接状态
        /// </summary>
        /// <returns></returns>
        public bool CheckConnectStatus()
        {
            bool result = false;
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    result = true;
                }

            }
            catch
            {
                result = false;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        /// <summary>
        /// 增、删、改公共方法
        /// </summary>
        /// <returns></returns>
        public int commonExecute(string sql)
        {
            int res = -1;
            try
            {
                //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Open();
                command = new MySqlCommand(sql, conn);
                res = command.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
            }
            conn.Close();
            return res;
        }
        /// <summary>
        /// 查询方法
        /// 注意：尽量不要用select * from table表（返回的数据过长时，DataTable可能会出错），最好指定要查询的字段。
        /// </summary>
        /// <returns></returns>
        public DataTable query(string sql)
        {
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            command = new MySqlCommand(sql, conn);
            DataTable dt = new DataTable();
            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(reader);
            }
            return dt;
        }
        /// <summary>
        /// 获取DataSet数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, string tablename)
        {
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            command = new MySqlCommand(sql, conn);
            DataSet dataset = new DataSet();
            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            adapter.Fill(dataset, tablename);
            conn.Close();
            return dataset;
        }
        /// <summary>
        /// 实现多SQL语句执行的数据库事务
        /// </summary>
        /// <param name="SQLStringList">SQL语句集合（多条语句）</param>
        public bool ExecuteSqlTran(List<string> SQLStringList)
        {
            bool flag = false;
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            MySqlCommand cmd = conn.CreateCommand();
            //开启事务
            MySqlTransaction tran = this.conn.BeginTransaction();
            cmd.Transaction = tran;//将事务应用于CMD
            try
            {
                foreach (string strsql in SQLStringList)
                {
                    if (strsql.Trim() != "")
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tran.Commit();//提交事务（不提交不会回滚错误）
                flag = true;
            }
            catch (Exception)
            {
                tran.Rollback();
                flag = false;
            }
            finally
            {
                conn.Close();
            }
            return flag;
        }

    }
}

使用示例：

1、实例类

string mysqlStr = "Database=mydata_db;Data Source=127.0.0.1;User Id=root;Password=root;;pooling=false;CharSet=utf8;port=3306;"
MysqlBase mysqlBase = new MysqlBase(mysqlStr);

2、查询

//查询
string sql = "select * from student where id=" + id;
DataTable dt = mysqlBase.query(sql);
if (dt.Rows.Count != 0)
{
    int id = Convert.ToInt32(dt.Rows[0][0]);
    string name = dt.Rows[0][1];
}
else
{
    MessageBox.Show("无数据");
}

3、增、删、改

string sql = "update student set name='张三',age=20 where id="+id;
int res = mysqlBase.commonExecute(sql);
if (res > 0)
{
    MessageBox.Show("更新成功");
}else{
    MessageBox.Show("更新失败");
}

上面演示的是更新操作，增加和删除是类似的。