[WPF]C# mysql操作封装类简化操作,支持多线程操作
版权声明：
本文为博主原创文章，转载请声明原文链接...谢谢。o_0。
更新时间：
2017-08-24 11:32:38
温馨提示：
学无止境,技术类文章有它的时效性,请留意文章更新时间,如发现内容有误请留言指出,防止别人"踩坑",我会及时更新文章
使用之前请先引用mysql.data.dll
简单的一些增删改操作,请看下面一篇文章
https://www.zhaokeli.com/article/8172.html
MyDb.cs 类支持多线程操作,里面使用 Mutex 互斥实现操作数据库时把线程挂起来同步数据
里面用到啦一个记录日志的功能,可以到下面地址下载
https://www.zhaokeli.com/article/8180.html
如果不需要记录
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Threading;
namespace Ank.Class
{
class MyDb
{
private static MyDb db = null;
private static Mutex m_mutex = null;//多线程时互斥锁
private MySqlConnection m_conn;
private string connstr = "";
private string _sql = "";
private Log m_log = null;
private MySqlCommand m_cmd = null;
private MySqlDataReader m_reader = null;
private MyDb(string host, string user, string pwd, string database)
{
try
{
m_log = Log.getInstance();
m_mutex = new Mutex();
this.connstr = "server=" + host + ";user id=" + user + ";password=" + pwd + ";database=" + database + ";charset=utf8mb4;"; //根据自己的设置
m_conn = new MySqlConnection(this.connstr);
//打开数据库连接
m_conn.Open();
}
catch (Exception ex)
{
this.errorMsg(ex);
throw;
}
}
static public MyDb getInstance(string host, string user, string pwd, string database)
{
if (db == null)
{
db = new MyDb(host, user, pwd, database);
}
return db;
}
/**
* 取数据表主键
**/
private string getPrimary(string tablename = "")
{
string key = "";
try
{
m_cmd = new MySqlCommand("SHOW COLUMNS FROM " + tablename, m_conn);
m_reader = m_cmd.ExecuteReader();
//如果有数据就输出
if (m_reader.HasRows)
{
//逐行读取数据输出
while (m_reader.Read())
{
string zizeng = m_reader.GetString("Extra");
if (zizeng == "auto_increment")
{
key = m_reader.GetString("Field");
break;
}
}
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
}
return key;
}
/**
**/
public int query(string sql)
{
m_mutex.WaitOne();
this.reConn();
//sql查询
try
{
if (sql != null)
{
this._sql = sql;
m_cmd = new MySqlCommand(this._sql, m_conn);
return m_cmd.ExecuteNonQuery();
}
else
{
return 0;
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
m_mutex.ReleaseMutex();
}
return 0;
}
/**
* 插入数据成功返回自增id,没有自增id的返回1
**/
public int insert(string tablename, Dictionary<string, string> updatedata)
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
try
{
m_cmd = m_conn.CreateCommand();
//取更新的所有键
string field1 = "";
string field2 = "";
foreach (string key in updatedata.Keys)
{
field1 += "," + key;
field2 += ",@" + key;
m_cmd.Parameters.AddWithValue("@" + key.ToLower(), updatedata[key]);
}
this._sql = "INSERT INTO " + tablename + " (" + field1.Trim(',') + ") VALUES (" + field2.Trim(',') + ")";
m_cmd.CommandText = this._sql;
int result = m_cmd.ExecuteNonQuery();
closeHandle();
if (result == 1)
{
string prid = this.getPrimary(tablename);
if (prid != "")
{
var data = this.get(tablename, prid, "", prid + " desc");
return int.Parse(data[prid]);
}
else
{
return 1;
}
}
}
catch (Exception ex)
{
this.errorMsg(ex);
this.closeHandle();
}
finally
{
m_mutex.ReleaseMutex();
}
return 0;
}
/**
* 插入数据
**/
public int update(string tablename, Dictionary<string, string> updatedata, string where = "")
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
try
{
m_cmd = m_conn.CreateCommand();
//取更新的所有键
string field1 = "";
foreach (string key in updatedata.Keys)
{
field1 += "," + key + "=@" + key;
m_cmd.Parameters.AddWithValue("@" + key.ToLower(), updatedata[key]);
}
this._sql = "UPDATE " + tablename + " SET " + field1.Trim(',') + " WHERE " + where;
m_cmd.CommandText = this._sql;
return m_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
m_mutex.ReleaseMutex();
}
return 0;
}
public List<Dictionary<string, string>> select(string tablename = "", string fields = "", string where = "", string order = "", string limit = "")
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
List<Dictionary<string, string>> datalist = new List<Dictionary<string, string>>();
this._sql = "select " + (fields != "" ? fields : "*") + " from " + tablename + " " + (where != "" ? (" where " + where) : "") + " " + (order != "" ? (" order by " + order) : "") + (limit != "" ? (" limit " + limit) : "");
try
{
m_cmd = new MySqlCommand(this._sql, m_conn);
m_reader = m_cmd.ExecuteReader();
//如果有数据就输出
if (m_reader.HasRows)
{
//逐行读取数据输出
while (m_reader.Read())
{
Dictionary<string, string> coldata = new Dictionary<string, string>();
//取所有field
for (int i = 0; i < m_reader.FieldCount; i++)
{
string fieldname = m_reader.GetName(i).Trim();
string value = m_reader.GetString(fieldname);
coldata.Add(fieldname, value);
}
datalist.Add(coldata);
}
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
m_mutex.ReleaseMutex();
}
return datalist;
}
/**
* 返回一条记录
* */
public Dictionary<string, string> get(string tablename = "", string fields = "", string where = "", string order = "")
{
var dict = this.select(tablename, fields, where, order, "1");
if (dict.Count > 0)
{
return dict[0];
}
else
{
return new Dictionary<string, string>();
}
}
public int count(string tablename = "", string where = "")
{
var dict = this.get(tablename, "", where);
return dict.Count;
}
public bool has(string tablename = "", string where = "")
{
var num = this.count(tablename, where);
if (num > 0)
{
return true;
}
else
{
return false;
}
}
~MyDb()
{
try
{
if (m_conn.State == System.Data.ConnectionState.Open)
{
m_conn.Close();
m_conn.Dispose();
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
}
}
/************************************************************************/
/* 如果连接已经关闭就重新连接记录集是打开状态的关闭 */
/************************************************************************/
private void reConn()
{
try
{
//防止因网络或其它情况下连接断开时重新连接
if (m_conn.State == System.Data.ConnectionState.Closed)
{
m_conn = new MySqlConnection(this.connstr);
m_conn.Open();
}
this.closeHandle();
}
catch (Exception ex)
{
this.errorMsg(ex);
}
}
private void errorMsg(Exception ex = null)
{
if (ex != null)
{
Console.WriteLine(ex.Message);
m_log.write("\r\n", "db");
m_log.write(ex.Message, "db");
m_log.write(ex.ToString(), "db");
}
}
private void closeHandle()
{
try
{
if (m_cmd != null)
{
m_cmd.Dispose();
}
if (m_reader != null && m_reader.IsClosed == false)
{
m_reader.Close();
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
m_cmd = null;
m_reader = null;
}
}
}
}使用方法
MyDb db = MyDb.getInstance("localhost", "root", "*********", "************");
var datalist = db.select("article","*","","","1,10");
Console.WriteLine(datalist[0]["article_id"].ToString());
var data = new Dictionary<string,string>();
data.Add("title", "新数据");
data.Add("content", "很多内容");
int result=db.insert("article", data);
Console.WriteLine(result);
data = new Dictionary<string, string>();
data.Add("title", "更改新数据");
data.Add("content", "更改很多内容");
result = db.update("kl_article", data,"article_id=8150");
Console.WriteLine(result);
