C#连接MySQL数据库帮助类-MysqlDblHelper
帮助类代码如下:
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data;
namespace Demo
{
class MysqlDblHelper
{
//连接数据库
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);
private 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<string, byte[]> parameters)
{
InitConn();
MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
int i = 0;
foreach (KeyValuePair<string, byte[]> kvp in parameters)
{
cmd.Parameters.Add("@" + kvp.Key, MySqlDbType.LongBlob);
cmd.Parameters[i].Value = kvp.Value;
i++;
}
int result = cmd.ExecuteNonQuery();
conn.Close();
return result > 0;
}
}
}
使用方法:
//这里仅举例带文件的数据上传数据库 musicUploadZip和videoUploadZip为要上传文件的路径
string sqlStr = "insert into user (name,music,video) values ('张三',@music,@video);";
Dictionary<string, byte[]> parameters = new Dictionary<string, byte[]>();
parameters.Add("music", File.ReadAllBytes(musicUploadZip));
parameters.Add("video", File.ReadAllBytes(videoUploadZip));
if (MysqlDblHelper.Binary(sqlStr, parameters))
{
MessageBox.Show("上传成功");
}