自用MySql数据库工具类

需要先在nuget上搜索并安装 MySql.Data 包

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


public class DBUtil
{
    private static string connectstring = "data source=IP地址;database=数据库名;user id=用户名;password=密码;SslMode=None;pooling=true;charset=utf8;";

    public static DataSet GetData(string sql)
    {
        DataSet ds = new DataSet();
        MySqlConnection conn = new MySqlConnection(connectstring);
        try
        {
            conn.Open();
            MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
            da.Fill(ds);
        }
        catch (Exception ex)
        {

        }
        finally
        {
            conn.Close();
        }
        return ds;
    }

    /// <summary>
    /// 带参数查询
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="dic_args">sql语句中参数前面带@符号</param>
    /// <returns></returns>
    public static DataSet GetData(string sql, Dictionary<string, object> dic_args)
    {
        DataSet ds = new DataSet();
        MySqlConnection conn = new MySqlConnection(connectstring);
        try
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(sql, conn);

            if (dic_args != null && dic_args.Count != 0)
            {
                Dictionary<string, object>.Enumerator it = dic_args.GetEnumerator();
                while (it.MoveNext())
                {
                    cmd.Parameters.AddWithValue(it.Current.Key, it.Current.Value);
                }
            }
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(ds);
        }
        catch (Exception ex)
        {

        }
        finally
        {
            conn.Close();
        }

        return ds;
    }

    public class SqlCommandInfo
    {
        public string Sql { get; set; }
        public Dictionary<string, object> Parameters { get; set; } = new Dictionary<string, object>();
    }

    /// <summary>
    /// 执行多条sql语句（带事务）
    /// </summary>
    /// <param name="sql_args"></param>
    public static bool SetData(List<SqlCommandInfo> sql_args)
    {
        MySqlConnection conn = new MySqlConnection(connectstring);
        conn.Open();
        MySqlTransaction tran = conn.BeginTransaction();
        try
        {
            if (sql_args == null || sql_args.Count == 0)
            {
                return false;
            }

            foreach (SqlCommandInfo sql_cmd in sql_args)
            {
                MySqlCommand cmd = new MySqlCommand(sql_cmd.Sql, conn);

                if (sql_cmd.Parameters != null && sql_cmd.Parameters.Count != 0)
                {
                    Dictionary<string, object>.Enumerator it2 = sql_cmd.Parameters.GetEnumerator();
                    while (it2.MoveNext())
                    {
                        cmd.Parameters.AddWithValue(it2.Current.Key, it2.Current.Value);
                    }
                }
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            tran.Rollback();
            conn.Close();
            return false;
        }
        finally
        {
            if (conn.State != ConnectionState.Closed)
            {
                tran.Commit();
                conn.Close();
            }
        }
        return true;
    }
}