[WPF]C# mysql操作封装类简化操作,支持多线程操作

来源:赵克立博客 分类: WPF 标签:WPFC#多线程发布时间:2017-08-18 16:23:59最后更新:2017-08-24 11:32:38浏览:2287
版权声明:
本文为博主原创文章,转载请声明原文链接...谢谢。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);



微信号:kelicom QQ群:215861553 紧急求助须知
Win32/PHP/JS/Android/Python