using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;

namespace BLL.Helper
{
    public class MySqlHelper
    {
        public string _connectionString = ConfigurationManager.ConnectionStrings["zxdContext"].ToString();
         
           


        private MySqlParameter[] GetParameterByany(dynamic parameters)
        {
            if (parameters == null)
                return null;

            var ilistStr = new List<MySqlParameter>();
            if (parameters is IDictionary<string, object>)
            {
                foreach (var kv in (parameters as IDictionary<string, object>))
                {
                    //当数值为空时，参数值不去掉
                    ilistStr.Add(new MySqlParameter { ParameterName = "@" + kv.Key, Value = kv.Value });
                }
            }
            else
            {
                var piList = parameters.GetType().GetProperties();
                foreach (var p in piList)
                {
                    //当数值为空时，参数值不去掉
                    ilistStr.Add(new MySqlParameter { ParameterName = "@" + p.Name, Value = p.GetValue(parameters, null) });
                }
            }

            var spList = ilistStr.ToArray();
            foreach (MySqlParameter p in spList)
            {
                if (p.Value == null)
                {
                    p.Value = DBNull.Value;
                }
            }
            return spList;
        }


        #region  inner

        /// <summary>
        ///  给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdSql">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        protected int _ExecuteNonQuery(CommandType cmdType, string cmdSql, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                _PrepareCommand(cmd, conn, null, cmdType, cmdSql, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// 准备执行一个命令
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="conn">OleDb连接</param>
        /// <param name="trans">OleDb事务</param>
        /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
        /// <param name="cmdText">命令文本,例如:Select * from Products</param>
        /// <param name="cmdParms">执行命令的参数</param>
        protected void _PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        #endregion

        public int ExecuteNonQuery(string sql, dynamic parameters)
        {
            try
            {
                return _ExecuteNonQuery(System.Data.CommandType.Text, sql, parameters); 
            }
            catch (Exception ex)
            {
                 
            }
            return -1;
        }


        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns></returns>
        protected DataSet _ExecuteDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            //创建一个MySqlCommand对象
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
                _PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                //调用 MySqlCommand  的 ExecuteReader 方法
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();

                adapter.Fill(ds);
                //清除参数
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

         


        /// <summary>
        /// DataTable 转换成 List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<T> DataTableToList<T>(DataTable table) where T : new()
        {
            var listT = new List<T>();
            var list = typeof(T).GetProperties();
            var dataColumns = table.Columns;
            for (var i = 0; i < table.Rows.Count; i++)
            {
                var model = new T();
                foreach (var p in list)
                {
                    if (dataColumns.Contains(p.Name) && table.Rows[i][p.Name] != DBNull.Value)
                    {
                        try
                        {
                            var obj = Convert.ChangeType(table.Rows[i][p.Name], p.PropertyType);
                            p.SetValue(model, obj, null);
                        }
                        catch (Exception)
                        {
                            p.SetValue(model, table.Rows[i][p.Name], null);
                        }
                    }
                }
                listT.Add(model);
            }
            return listT;
        }

        public IList<IDictionary<string, object>> QueryDicList(string sql, dynamic paramobj)
        {
            IList<IDictionary<string, object>> result = new List<IDictionary<string, object>>();
            try
            { 
                if (paramobj == null)
                {
                    return  SelectDicList(sql, null);
                }
                else
                {
                    return  SelectDicList(sql, GetParameterByany(paramobj));
                }
            }
            catch (Exception ex)
            { 
            }
            return result;
        }


        public IList<IDictionary<string, object>> SelectDicList(string sql, MySqlParameter[] parameters)
        {
            IList<IDictionary<string, object>> result = new List<IDictionary<string, object>>();
            var ds = _ExecuteDataSet(System.Data.CommandType.Text, sql, parameters);
            //格式转换
            if (ds.Tables.Count > 0)
            {
                var table = ds.Tables[0];
                var columns = table.Columns;
                foreach (DataRow row in table.Rows)
                {
                    IDictionary<string, object> r = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);//不区分大小写
                    foreach (var col in columns)
                    {
                        var colName = col.ToString();
                        if (!r.ContainsKey(colName))
                        {
                            r.Add(col.ToString(), row[col.ToString()]);
                        }
                    }
                    result.Add(r);
                }
            }
            return result;
        }







    }
}