C#连接mysql数据库帮助类-MysqlDblHelper

帮助类代码如下：

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

namespace Basic
{
    class MysqlDblHelper
    {
        //连接数据库
        //static string connectionStrings = "server=127.0.0.1;port=3306;user=root;password=root;database=basic;sslmode=none;charset=utf8;";
        static string connectionStrings = "server=127.0.0.1;port=3306;user=root;password=root;database=test;sslmode=none;charset=utf8;";
        static MySqlConnection conn = new MySqlConnection(connectionStrings);

        public static void InitConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }

        //增删改
        public static bool ExecuteNonQuery(string sqlStr)
        {
            InitConn();
            MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result > 0;
        }

        //执行集合函数
        public static object ExecuteScalar(string sqlStr)
        {
            InitConn();
            MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
            object result = cmd.ExecuteScalar();
            conn.Close();
            return result;
        }

        //查询，获取DataTable
        public static DataTable GetTable(string sqlStr)
        {
            InitConn();
            MySqlCommand cmd = new MySqlCommand(sqlStr);
            cmd.Connection = conn;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        //二进制数据增删改
        public static bool Binary(string sqlStr, Dictionary parameters)
        {
            InitConn();
            MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
            foreach (KeyValuePair kvp in parameters)
            {
                cmd.Parameters.Add("@" + kvp.Key, MySqlDbType.LongBlob);
                cmd.Parameters[0].Value = kvp.Value;
            }
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result > 0;
        }
    }
}

使用方法：

//这里仅举例带文件的数据上传数据库   musicUploadZip和videoUploadZip为要上传文件的路径
string sqlStr = "insert into user (name,music,video) values ('张三',@music,@video);";
Dictionary parameters = new Dictionary();
parameters.Add("music", File.ReadAllBytes(musicUploadZip));
parameters.Add("video", File.ReadAllBytes(videoUploadZip));
if (MysqlDblHelper.Binary(sqlStr, parameters))
{
    MessageBox.Show("上传成功");
}