MySQL操作类

using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace WXD.MySQL.Common
{
    public class MySqlCommHelper
    {

        public delegate void RefreshSubProcessBarHandler(int total, int current);

        public static RefreshSubProcessBarHandler refreshSubProcessBar;
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private static MySqlConnection Connection { get; set; }
        /// <summary>
        ///打开数据库
        /// </summary>
        /// <param name="connsql"></param>
        /// <returns></returns>
        public static MySqlConnection OpenConnection()
        {
            try
            {
                string connsql = AppCommon.MySQLConnection;

                if (!string.IsNullOrEmpty(connsql))
                {
                    if (Connection == null)
                    {
                        Connection = new MySqlConnection(AppCommon.MySQLConnection)
                        {
                            ConnectionString = connsql
                        };
                    }

                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Open(); // 打开数据库连接
                    }
                    return Connection;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("打开数据库连接失败，{0}", ex.Message));
                return null;
            }
        }

        public static MySqlConnection OpenConnection(string connStr)
        {
            try
            {
                if (!string.IsNullOrEmpty(connStr))
                {

                    MySqlConnection Connection = new MySqlConnection(connStr)
                    {
                        ConnectionString = connStr
                    };

                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Open(); // 打开数据库连接
                    }
                    return Connection;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("打开数据库连接失败，{0}", ex.Message));
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryKey"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string Query<T>(PropertyInfo primaryKey, string value) where T : new()
        {
            string resultstr = string.Empty;
            try
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                string select = string.Format("Select * from {0} where {1}='{2}'", type.Name, primaryKey.Name, value);
                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return resultstr;
                MySqlCommand _sqlCom = new MySqlCommand(select, connection);
                using (MySqlDataReader reader = _sqlCom.ExecuteReader())
                {
                    T t = default(T);
                    while (reader.Read())
                    {
                        t = new T();
                        foreach (PropertyInfo property in properties)
                        {
                            if (!string.IsNullOrEmpty(reader[property.Name].ToString()))
                            {
                                property.SetMethod.Invoke(t, new object[] { reader[property.Name] });
                            }
                        }
                    }
                    if (t != null)
                    {
                        resultstr = JsonConvert.SerializeObject(t);
                    }
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("查询数据库失败，{0}", ex.Message));
            }
            finally
            {
            }
            return resultstr;
        }

        public static string Query<T>() where T : new()
        {
            string resultstr = string.Empty;
            try
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                string select = string.Format("Select * from {0}", type.Name);
                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return resultstr;
                MySqlCommand _sqlCom = new MySqlCommand(select, connection);
                using (MySqlDataReader reader = _sqlCom.ExecuteReader())
                {
                    List<T> tList = new List<T>();
                    while (reader.Read())
                    {
                        T t = new T();
                        foreach (PropertyInfo property in properties)
                        {
                            if (!string.IsNullOrEmpty(reader[property.Name].ToString()))
                            {
                                property.SetMethod.Invoke(t, new object[] { reader[property.Name] });
                            }
                        }
                        tList.Add(t);
                    }
                    resultstr = JsonConvert.SerializeObject(tList);
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("查询数据库失败，{0}", ex.Message));
            }
            finally
            {
            }
            return resultstr;
        }

        public static string QueryDetail<T>(PropertyInfo key, string value, string year) where T : new()
        {
            string resultstr = string.Empty;
            try
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                string select = string.Format("Select * from {0} where {1}='{2}'", year + "_" + type.Name, key.Name, value);
                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return resultstr;
                MySqlCommand _sqlCom = new MySqlCommand(select, connection);
                using (MySqlDataReader reader = _sqlCom.ExecuteReader())
                {
                    List<T> tList = new List<T>();
                    while (reader.Read())
                    {
                        T t = new T();
                        foreach (PropertyInfo property in properties)
                        {
                            if (!string.IsNullOrEmpty(reader[property.Name].ToString()))
                            {
                                property.SetMethod.Invoke(t, new object[] { reader[property.Name] });
                            }
                        }
                        tList.Add(t);
                    }
                    resultstr = JsonConvert.SerializeObject(tList);
                }

            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("查询数据库失败，{0}", ex.Message));
            }
            finally
            {
            }
            return resultstr;
        }

        public static string Query<T>(string sql) where T : new()
        {
            string resultstr = string.Empty;
            try
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return resultstr;
                MySqlCommand _sqlCom = new MySqlCommand(sql, connection);
                using (MySqlDataReader reader = _sqlCom.ExecuteReader())
                {
                    List<T> tList = new List<T>();
                    while (reader.Read())
                    {
                        T t = new T();
                        foreach (PropertyInfo property in properties)
                        {
                            if (!string.IsNullOrEmpty(reader[property.Name].ToString()))
                            {
                                property.SetMethod.Invoke(t, new object[] { reader[property.Name] });
                            }
                        }
                        tList.Add(t);
                    }
                    resultstr = JsonConvert.SerializeObject(tList);
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("查询数据库失败，{0}", ex.Message));
            }
            finally
            {
            }
            return resultstr;
        }

        public static bool GetTables(string tgtConn, string srcDatabase, ref List<string> tables)
        {

            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection()
                {
                    ConnectionString = string.Format("{0};Initial Catalog={1}", tgtConn, srcDatabase)
                };
                connection.Open();
                string sql = "show tables";
                MySqlCommand _mySqlCom = new MySqlCommand(sql, connection);
                using (MySqlDataReader reader = _mySqlCom.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        tables.Add(reader[0].ToString());
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }
        }

        public static bool CreateDabaBase(string connStr, string dababaseName, string script)
        {
            MySqlConnection conn = null;
            FileInfo file = null;
            string sql = null;
            try
            {
                if (string.IsNullOrEmpty(dababaseName))
                    return false;
                if (!File.Exists(script))
                    return false;
                file = new FileInfo(script);
                sql = file.OpenText().ReadToEnd();
                sql = sql.Replace("DATABASENAME", dababaseName);
                conn = OpenConnection(connStr);
                if (conn != null)
                {
                    MySqlCommand _mySqlCom = new MySqlCommand(sql, conn);
                    object re = _mySqlCom.ExecuteScalar();
                }
                return true;
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("创建数据库失败，{0};\r\nSQL:{1}", ex.Message, sql));
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DisposeConnection()
        {
            if (Connection != null)
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
                Connection.Dispose();
                Connection = null;
            }
        }

        public static bool TestConnection()
        {
            try
            {
                string connsql = string.Format("Data Source={0};uid={1};pwd={2}", AppCommon.MysqlAddr, AppCommon.UserName, AppCommon.Password);

                if (!string.IsNullOrEmpty(connsql))
                {
                    MySqlConnection connection = new MySqlConnection(connsql)
                    {
                        ConnectionString = connsql
                    };
                    connection.Open(); // 打开数据库连接
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("打开数据库连接失败，{0}", ex.Message));
                return false;
            }
        }

        public static bool TestConnection(string connStr)
        {
            try
            {
                if (!string.IsNullOrEmpty(connStr))
                {
                    MySqlConnection connection = new MySqlConnection(connStr)
                    {
                        ConnectionString = connStr
                    };
                    connection.Open(); // 打开数据库连接
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("打开数据库连接失败，{0}", ex.Message));
                return false;
            }
        }

        public static bool Insert<T>(T results)
        {
            int j = 0;
            try
            {
                Type type = results.GetType();
                PropertyInfo[] properties = type.GetProperties();
                string keys = string.Empty;
                string values = string.Empty;
                foreach (PropertyInfo property in properties)
                {
                    object value = results.GetPropertyValue(property);

                    keys += property.Name + ",";
                    if (value != null && !string.IsNullOrEmpty(value.ToString()))
                    {
                        if (property.PropertyType == typeof(int))
                        {
                            values += value.ToString() + ",";
                        }
                        else if (property.PropertyType == typeof(DateTime))
                        {
                            values += "\"" + DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "\",";
                        }
                        else
                        {
                            values += "\"" + value.ToString() + "\",";
                        }
                    }
                    else
                    {
                        if (property.PropertyType != typeof(string))
                        {
                            values += "NULL,";
                        }
                        else
                        {
                            values += "\" \",";
                        }
                    }
                }
                string insert = string.Format("Insert Into {0} ({1}) values ({2})", type.Name, keys.TrimEnd(','), values.TrimEnd(','));
                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return false;
                MySqlCommand _sqlCom = new MySqlCommand(insert, Connection);
                j = _sqlCom.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("插入数据失败,{0}", ex.Message));
            }
            return j > 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="property"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool Delete<T>(PropertyInfo property, string value)
        {
            int j = 0;
            try
            {
                Type type = typeof(T);
                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return false;
                string delete = string.Format("Delete from {0} where {1}='{2}'", type.Name, property.Name, value);
                MySqlCommand _sqlCom = new MySqlCommand(delete, Connection);
                j = _sqlCom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("删除数据失败,{0}", ex.Message));
            }
            return j > 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="prinaryKey">主键</param>
        /// <param name="results">对象实例</param>
        /// <returns></returns>
        public static bool Update<T>(PropertyInfo prinaryKey, T results) where T : new()
        {
            int j = 0;
            try
            {
                Type type = results.GetType();
                PropertyInfo[] properties = type.GetProperties();

                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return false;
                string select = string.Format("Select * from {0} where {1} = '{2}'", type.Name, prinaryKey.Name, results.GetPropertyValue(prinaryKey));

                MySqlCommand mycom = new MySqlCommand(select, Connection);

                string updateValue = "";
                using (MySqlDataReader reader = mycom.ExecuteReader())
                {
                    T t = new T();
                    while (reader.Read())
                    {
                        foreach (PropertyInfo property in properties)
                        {
                            if (!string.IsNullOrEmpty(reader[property.Name].ToString()))
                            {
                                property.SetMethod.Invoke(t, new object[] { reader[property.Name] });
                            }
                        }
                    }
                    updateValue = string.Empty;
                    foreach (PropertyInfo property in properties)
                    {
                        object value = results.GetPropertyValue(property);
                        object tvalue = t.GetPropertyValue(property);
                        if (!value.Equals(tvalue))
                        {
                            updateValue += property.Name + "=";

                            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                            {
                                if (property.PropertyType == typeof(int))
                                {
                                    updateValue += value.ToString() + ",";
                                }
                                else if (property.PropertyType == typeof(DateTime))
                                {
                                    updateValue += "\"" + DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "\",";
                                }
                                else
                                {
                                    updateValue += "\"" + value.ToString() + "\",";
                                }
                            }
                            else
                            {
                                if (property.PropertyType != typeof(string))
                                {
                                    updateValue += "NULL,";
                                }
                                else
                                {
                                    updateValue += "\" \",";
                                }
                            }
                        }
                    }
                }

                string update = string.Format("Update {0} set {1} where {2}='{3}'", type.Name, updateValue.TrimEnd(','), prinaryKey.Name, results.GetPropertyValue(prinaryKey));
                MySqlCommand _sqlCom = new MySqlCommand(update, Connection);
                j = _sqlCom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("更新数据失败,{0}", ex.Message));
            }
            finally
            {
            }
            return j > 0;
        }

        public static string[] GetDatabase()
        {
            List<string> dbs = new List<string>();
            try
            {
                string connsql = string.Format("Data Source={0};uid={1};pwd={2}", AppCommon.MysqlAddr, AppCommon.UserName, AppCommon.Password);

                if (!string.IsNullOrEmpty(connsql))
                {
                    MySqlConnection connection = new MySqlConnection(connsql)
                    {
                        ConnectionString = connsql
                    };
                    connection.Open(); // 打开数据库连接
                    string sql = "show databases";

                    MySqlCommand _mySqlCom = new MySqlCommand(sql, connection);
                    using (MySqlDataReader reader = _mySqlCom.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dbs.Add(reader["Database"].ToString());
                        }
                    }
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("打开数据库连接失败，{0}", ex.Message));
            }
            return dbs.ToArray();

        }

        public static string[] GetDatabase(string connStr)
        {
            List<string> dbs = new List<string>();
            try
            {
                if (!string.IsNullOrEmpty(connStr))
                {
                    MySqlConnection connection = new MySqlConnection(connStr)
                    {
                        ConnectionString = connStr
                    };
                    connection.Open(); // 打开数据库连接
                    string sql = "show databases";

                    MySqlCommand _mySqlCom = new MySqlCommand(sql, connection);
                    using (MySqlDataReader reader = _mySqlCom.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dbs.Add(reader["Database"].ToString());
                        }
                    }
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("打开数据库连接失败，{0}", ex.Message));
            }
            return dbs.ToArray();

        }

        public static bool CreateDabaBase(string dababaseName, string script)
        {
            MySqlConnection conn = null;
            FileInfo file = null;
            string sql = null;
            try
            {
                if (string.IsNullOrEmpty(dababaseName))
                    return false;
                if (!File.Exists(script))
                    return false;
                file = new FileInfo(script);
                sql = file.OpenText().ReadToEnd();
                sql = sql.Replace("DATABASENAME", dababaseName);
                conn = OpenConnection();
                if (conn != null)
                {
                    MySqlCommand _mySqlCom = new MySqlCommand(sql, conn);
                    object re = _mySqlCom.ExecuteScalar();
                }
                return true;
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("创建数据库失败，{0};\r\nSQL:{1}", ex.Message, sql));
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static bool ExistDB(string databaseName)
        {
            List<string> databases = GetDatabase().ToList();
            return databases.Exists(r => r.ToLower() == databaseName.ToLower());
        }

        public static bool GetTables(string srcDatabase, ref List<string> tables)
        {
            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection()
                {
                    ConnectionString = string.Format("Data Source={0};Initial Catalog={1};uid={2};pwd={3}", AppCommon.MysqlAddr, srcDatabase, AppCommon.UserName, AppCommon.Password)
                };
                connection.Open();
                string sql = "show tables";
                MySqlCommand _mySqlCom = new MySqlCommand(sql, connection);
                using (MySqlDataReader reader = _mySqlCom.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tables.Add(reader[0].ToString());
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }
        }

        public static bool AsyncTable(string srcLink, string srcTable, string tgtLink, string tgtTable)
        {
            string insert = "";
            try
            {
                //List<string> datas = new List<string>();
                MySqlConnection source = new MySqlConnection(srcLink)
                {
                    ConnectionString = srcLink
                };
                source.Open(); // 打开数据库连接            

                MySqlConnection target = new MySqlConnection(tgtLink)
                {
                    ConnectionString = tgtLink
                };
                target.Open();
                int totalCount = 0;
                int current = 0;
                string sqlCount = string.Format("Select Count(1) as TCount from {0}", srcTable);
                MySqlCommand mycom = new MySqlCommand(sqlCount, source);
                using (MySqlDataReader readerCount = mycom.ExecuteReader())
                {
                    while (readerCount.Read())
                    {
                        totalCount = int.Parse(readerCount["TCount"].ToString());
                    }
                }
                if (totalCount > 0)
                {
                    string sql = string.Format("Select * from {0}", srcTable);
                    MySqlCommand _mySqlCom = new MySqlCommand(sql, source);
                    using (MySqlDataReader reader = _mySqlCom.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            try
                            {
                                current++;
                                if (refreshSubProcessBar != null)
                                {
                                    refreshSubProcessBar.Invoke(totalCount, current);
                                }
                                string context = "";
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    int value = 0;
                                    if (string.IsNullOrEmpty(reader[i].ToString()))
                                    {
                                        if (reader.IsDBNull(i))
                                        {
                                            context += "NULL,";
                                        }
                                        else
                                        {
                                            context += "' ',";
                                        }
                                    }
                                    else if (int.TryParse(reader[i].ToString(), out value))
                                    {
                                        context += reader[i] + ",";
                                    }
                                    else if (reader[i] is byte[])
                                    {
                                        context += "'" + Encoding.UTF8.GetString((byte[])reader[i]) + "',";
                                    }
                                    else
                                    {
                                        context += "N'" + reader[i].ToString() + "',";
                                    }
                                }
                                //datas.Add(context.TrimEnd(','));
                                insert = string.Format("SET FOREIGN_KEY_CHECKS = 0;Insert Into {0} values ({1});SET FOREIGN_KEY_CHECKS = 1;", tgtTable, context.TrimEnd(','));
                                MySqlCommand _sqlCom = new MySqlCommand(insert, target);
                                int j = _sqlCom.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                Logging.Error(string.Format("同步数据失败！{0},\r\n{1}", ex.Message, insert));
                            }
                            finally
                            {

                            }
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("同步数据失败！{0},\r\n{1}", ex.Message, insert));
                return false;
            }
        }

        public static string QueryByEntity<T>(T t) where T : new()
        {
            string resultstr = string.Empty;
            try
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                string select = string.Format("Select * from {0} {1}", type.Name, "{0}");
                string where = string.Empty;
                foreach (PropertyInfo property in properties)
                {
                    var value = t.GetPropertyValue(property);
                    if (value != null && !value.Equals(property.GetDefaultValue()))
                    {
                        if (string.IsNullOrEmpty(where))
                        {
                            where = string.Format(" where {0}='{1}' ", property.Name, value);
                        }
                        else
                        {
                            where = string.Format(" {0} and {1} = '{2}' ", where, property.Name, value);
                        }
                    }
                }
                select = string.Format(select, where);

                MySqlConnection connection = OpenConnection();
                if (connection == null)
                    return resultstr;
                MySqlCommand _sqlCom = new MySqlCommand(select, connection);
                using (MySqlDataReader reader = _sqlCom.ExecuteReader())
                {
                    List<T> tList = new List<T>();
                    while (reader.Read())
                    {
                        T t1 = new T();
                        foreach (PropertyInfo property in properties)
                        {
                            if (!string.IsNullOrEmpty(reader[property.Name].ToString()))
                            {
                                property.SetMethod.Invoke(t1, new object[] { reader[property.Name] });
                            }
                        }
                        tList.Add(t1);
                    }
                    resultstr = JsonConvert.SerializeObject(tList);
                }

            }
            catch (Exception ex)
            {
                Logging.Error(string.Format("查询数据库失败，{0}", ex.Message));
            }
            finally
            {
            }
            return resultstr;
        }
    }
}