using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Runtime.InteropServices;
using System.Xml;
using System.Configuration;
using System.Collections.Generic;
using System.Reflection;


namespace IMLibrary.MySqlData
{

    /// <summary>
    /// 字段
    /// </summary>
    public class Field
    {
        /// <summary>
        /// 字段名称
        /// </summary>
        public string Name { set; get; }
        /// <summary>
        /// 字段类型
        /// </summary>
        public Type Type { set; get; }
    }

    /// <summary>
    /// SQL数据库访问类
    /// </summary>
    public sealed partial class DataAccess
    {
        /// <summary>
        /// 设置或获取数据库联接字符串
        /// </summary>
        public static string ConnectionString = "";
        /// <summary>
        /// 当前数据库表集合
        /// </summary> 
        public static Dictionary<string, List<Field>> Tables = new Dictionary<string, List<Field>>();
        private DataAccess()
        {

        }

        #region  扩展操作

        #region 创建Sql参数
        /// <summary>
        /// 是否初始化数据库表及字段
        /// </summary>
        private static bool isIniTables = false;
        /// <summary>
        /// 初始化
        /// </summary>
        public  static void IniTables()
        {
            if (!isIniTables)//如果从未进行数据库表信息初始化
            {
                isIniTables = true;
                string sql = "show tables;"; 
                using (var dr = GetReaderBySql(sql))
                {
                    if (dr != null)
                        while (dr.Read())
                            Tables.Add(dr.GetString(0).ToLower(), new List<Field>());
                } 
                foreach (var t in Tables)
                {
                    sql = "show columns from " + t.Key + ";";
                    using (var dr = GetReaderBySql(sql))
                    {
                        if (dr != null)
                            while (dr.Read())
                                t.Value.Add(new Field { Name = dr.GetString(0).ToLower(), Type = dr.GetValue(1) as Type });
                    }
                } 
            }
        }

        /// <summary>
        /// 创建MySql参数
        /// </summary>
        /// <param name="ParameterName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static MySqlParameter MySqlP(string ParameterName, object value)
        {
            return new MySqlParameter(ParameterName, value);
            //var par = new MySqlParameter(ParameterName, value);
            //par.MySqlDbType = ConvertType(value.GetType());
            //return par;
        }

        public static MySqlDbType ConvertType(Type t)
        {
            switch (Type.GetTypeCode(t))
            {
                case TypeCode.Boolean:
                    return MySqlDbType.Bit  ;
                case TypeCode.Byte:
                    return MySqlDbType.Byte;
                case TypeCode.DateTime:
                    return MySqlDbType.DateTime;
                case TypeCode.Decimal:
                    return MySqlDbType.Decimal;
                case TypeCode.Double:
                    return MySqlDbType.Double;
                case TypeCode.Int16:
                    return MySqlDbType.Int16;
                case TypeCode.Int32:
                    return MySqlDbType.Int32;
                case TypeCode.Int64:
                    return MySqlDbType.Int64;
                case TypeCode.SByte:
                    return MySqlDbType.Int16;
                case TypeCode.Single:
                    return MySqlDbType.Float;
                case TypeCode.String:
                    return MySqlDbType.VarString;
                case TypeCode.UInt16:
                    return MySqlDbType.UInt16;
                case TypeCode.UInt32:
                    return MySqlDbType.UInt32;
                case TypeCode.UInt64:
                    return MySqlDbType.UInt64;
                default:
                    if (t == typeof(byte[]))
                    {
                        return MySqlDbType.Binary;
                    }
                    return MySqlDbType.VarString;
            }
        }
        #endregion
        

        #region 判断某表是否存在
        /// <summary>
        /// 获得表中所有字段
        /// 返回空为表不存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<Field> GetTable(string tableName)
        {
            tableName = tableName.Trim().ToLower(); 
            List<Field> Fields;
            Tables.TryGetValue(tableName, out Fields);
            return Fields;
        }
        #endregion

        #region 获得表中字段
        /// <summary>
        /// 获得表中字段
        /// 返回空为字段不存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public static Field GetField(string tableName, string field)
        {
            tableName = tableName.Trim().ToLower();
            field = field.Trim().ToLower();
            var Fields = GetTable(tableName);
            if (Fields == null)
                return null;
            foreach (var f in Fields)
                if (f.Name == field)
                    return f;
            return null;
        }
        #endregion

        #region 一个对像插入一张数据表操作
        /// <summary>
        /// 一个对像插入一张数据表操作
        /// </summary>
        /// <param name="obj">插入的对像</param>
        /// <param name="TableName">插入的表格</param>
        /// <returns></returns>
        public static int InsertOne(object obj, string TableName)
        {
            List<object> objs = new List<object>();
            objs.Add(obj);
            List<string> Tables = new List<string>();
            Tables.Add(TableName);
            return InsertMore(objs, Tables.ToArray());
        }
        #endregion

        #region 更新多个对像，在多张表中
        /// <summary>
        /// 更新多个对像，在多张表中
        /// </summary>
        /// <param name="objs">对像集合</param>
        /// <param name="ConditionField">要匹配的条件字段</param>
        /// <param name="Tables">表集合</param>
        /// <returns></returns>
        public static int UpdateMore(List<object> objs, string ConditionField, params string[] Tables)
        {
            IniTables();//先初始化数据表信息
            if (ConditionField != null)
                ConditionField = ConditionField.ToLower();


            if (objs == null || objs.Count == 0) return 0;
            if (Tables == null || Tables.Length == 0) return 0;

            List<MySqlParameter> MySqlParameters = new List<MySqlParameter>();

            var sql = "";
            int row = 0;
            bool t = false;//是否效SQL
            try
            {
                foreach (var tab in Tables)
                {
                    var fields =  GetTable(tab);
                    if (fields != null)//如果查询表存在
                    {
                        foreach (var obj in objs)
                        {
                            var update = "update " + tab + "  set ";
                            var where = " where ";

                            bool isValid = false;
                            PropertyInfo[] ps = obj.GetType().GetProperties();
                            int FieldCount = 0;//字段总数
                            foreach (PropertyInfo p in ps)
                            {
                                if (GetField(tab, p.Name) != null)
                                {
                                    if (p.Name.ToLower() != ConditionField)
                                    {
                                        if (FieldCount == 0)
                                        {
                                            update += p.Name + "=@" + p.Name + row.ToString();
                                        }
                                        else
                                        {
                                            update += "," + p.Name + "=@" + p.Name + row.ToString();
                                        }
                                        FieldCount++;
                                    }
                                    else
                                    {
                                        where += p.Name + "=@" + p.Name + row.ToString();
                                    }

                                    MySqlParameters.Add(MySqlP(p.Name + row.ToString(), p.GetValue(obj, null)));

                                    isValid = true;
                                }
                            }
                            row++;

                            if (isValid)//如果有字段值插入，则是有效的SQL
                            {
                                t = true;
                                sql += update + where + ";\n";
                            }
                        }
                    }
                }
                Console.WriteLine(sql);

                if (t)
                    return  ExecSql(sql, MySqlParameters.ToArray());
            }
            catch
            { }
            return 0;
        }
        #endregion

        #region 同一个对像集合插入到多张数据表操作
        /// <summary>
        /// 同一个对像集合插入到多张数据表操作
        /// </summary>
        /// <param name="objs">插入的对像</param>
        /// <param name="Tables">插入的表格</param>
        /// <returns></returns>
        public static int InsertMore(List<object> objs, params string[] Tables)
        {
            IniTables();//先初始化数据表信息
            if (objs == null || objs.Count == 0) return 0;
            if (Tables == null || Tables.Length == 0) return 0;

            List<MySqlParameter> MySqlParameters = new List<MySqlParameter>();

            var sql = "";
            int row = 0;
            bool t = false;//是否效SQL
            try
            {
                foreach (var tab in Tables)
                {
                    var fields =  GetTable(tab);
                    if (fields != null)//如果查询表存在
                    {
                        foreach (var obj in objs)
                        {
                            var insert = "insert into " + tab + "(";
                            var values = " values(";
                            bool isValid = false;
                            PropertyInfo[] ps = obj.GetType().GetProperties();
                            int FieldCount = 0;//字段总数
                            foreach (PropertyInfo p in ps)
                            {
                                if (GetField(tab, p.Name) != null)
                                {
                                    if (FieldCount == 0)
                                    {
                                        insert += p.Name;
                                        values += "@" + p.Name + row.ToString();
                                    }
                                    else
                                    {
                                        insert += "," + p.Name;
                                        values += ",@" + p.Name + row.ToString();
                                    }

                                    MySqlParameters.Add(MySqlP(p.Name + row.ToString(), p.GetValue(obj, null)));

                                    FieldCount++;

                                    isValid = true;
                                }
                            }
                            row++;

                            if (isValid)//如果有字段值插入，则是有效的SQL
                            {
                                t = true;
                                insert += ")";
                                values += ")";
                                sql += insert + values + ";\n";
                            }
                        }
                    }
                }
                Console.WriteLine(sql);
                if (t)
                    return  ExecSql(sql, MySqlParameters.ToArray());
            }
            catch
            { }
            return 0;
        }
        #endregion

        #region 数据更新操作
        /// <summary>
        /// 数据更新操作
        /// </summary>
        /// <param name="o">更新的对像</param>
        /// <param name="TableName">表格</param>
        /// <param name="ConditionField">条件字段</param>
        /// <returns></returns>
        public static int Update(object obj, string TableName, string ConditionField)
        {
            List<object> objs = new List<object>();
            objs.Add(obj);
            List<string> Tables = new List<string>();
            Tables.Add(TableName);
            return UpdateMore(objs, ConditionField, Tables.ToArray());
        }
        #endregion

        #region 数据查询操作
        /// <summary>
        /// 数据查询操作
        /// </summary>
        /// <param name="o">生成的对像</param>
        /// <param name="TableName">表格</param>
        /// <param name="ConditionString">条件字符串</param>
        /// <returns></returns>
        public static List<T> Select<T>(string TableName, string ConditionString)
        {
            IniTables();//先初始化数据表信息

            if (GetTable(TableName) == null)//如果查询表不存在，则退出
                return null;

            string sql = "select * from " + TableName + " where " + ConditionString;

            return GetObjectsBySql<T>(sql);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> Select<T>(string sql)
        {
            IniTables();//先初始化数据表信息

            return GetObjectsBySql<T>(sql);
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="o"></param>
        /// <param name="TableName"></param>
        /// <param name="ConditionString"></param>
        /// <returns></returns>
        public static int Delete(string TableName, string ConditionString)
        {
            string sql = "delete  from " + TableName + " where " + ConditionString;
            return ExecSql(sql);
        }
        #endregion

        #endregion


        private static void AssignParameterValues(MySqlParameter[] commandParameters, object[] parameterValues, bool IncludeReturnVarParameter)
        {
            if ((commandParameters != null) && (parameterValues != null))
            {
                int num;
                if (IncludeReturnVarParameter)
                {
                    num = 1;
                    if (commandParameters.Length != (parameterValues.Length + 1))
                    {
                        throw new ArgumentException("Parameter count does not match Parameter Value count.");
                    }
                }
                else
                {
                    num = 0;
                    if (commandParameters.Length != parameterValues.Length)
                    {
                        throw new ArgumentException("Parameter count does not match Parameter Value count.");
                    }
                }
                int index = 0;
                int length = parameterValues.Length;
                while (index < length)
                {
                    commandParameters[index + num].Value = parameterValues[index];
                    index++;
                }
            }
        }

        private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
        {
            foreach (MySqlParameter parameter in commandParameters)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                command.Parameters.Add(parameter);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">参数列表</param>
        /// <returns></returns>
        public static int ExecProc(string spName, params object[] parameterValues)
        {
            return ExecProc(ConnectionString, spName, parameterValues);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="ReturnValue">返回值</param>
        /// <param name="parameterValues">参数列表</param>
        /// <returns></returns>
        public static int ExecProc(string spName, out object ReturnValue, params object[] parameterValues)
        {
            return ExecProc(ConnectionString, spName, out ReturnValue, parameterValues);
        }

        private static int ExecProc(string connectionString, string spName, params object[] parameterValues)
        {
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                MySqlParameter[] spParameterSet = DataAccessParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues, false);
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
        }

        private static int ExecProc(string connectionString, string spName, out object ReturnValue, params object[] parameterValues)
        {
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                MySqlParameter[] commandParameters = DataAccessParameterCache.GetSpParameterSet(connectionString, spName, true);
                AssignParameterValues(commandParameters, parameterValues, true);
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, out ReturnValue, commandParameters);
            }
            return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, out ReturnValue);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">参数列表</param>
        /// <returns></returns>
        public static int ExecSql(string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecSql(ConnectionString, commandText, commandParameters);
        }

        private static int ExecSql(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(connectionString, CommandType.Text, commandText, commandParameters);
        }

        private static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataSet(connectionString, commandType, commandText, null);
        }

        private static DataSet ExecuteDataSet(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteDataSet(connection, null, commandType, commandText, commandParameters);
        }

        private static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                return ExecuteDataSet(connection, commandType, commandText, commandParameters);
            }
        }

        private static DataSet ExecuteDataSet(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            return dataSet;
        }

        private static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connectionString, commandType, commandText, (MySqlParameter[])null);
        }

        private static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(connection, null, commandType, commandText, commandParameters);
        }

        private static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, out object ReturnValue)
        {
            return ExecuteNonQuery(connectionString, commandType, commandText, out ReturnValue, null);
        }

        private static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
        }

        private static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, out object ReturnValue, params MySqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(connection, null, commandType, commandText, out ReturnValue, commandParameters);
        }

        private static int ExecuteNonQuery(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            return command.ExecuteNonQuery();
        }

        private static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, out object ReturnValue, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                return ExecuteNonQuery(connection, commandType, commandText, out ReturnValue, commandParameters);
            }
        }

        private static int ExecuteNonQuery(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, out object ReturnValue, params MySqlParameter[] commandParameters)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            int num = command.ExecuteNonQuery();
            ReturnValue = command.Parameters["@RETURN_VALUE"].Value;
            return num;
        }

        private static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(connectionString, commandType, commandText, null);
        }

        private static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            MySqlDataReader reader;
            MySqlConnection connection = new MySqlConnection(connectionString);
            connection.Open();
            try
            {
                reader = ExecuteReader(connection, null, commandType, commandText, SqlConnectionOwnership.Internal, commandParameters);
            }
            catch
            {
                connection.Close();
                throw;
            }
            return reader;
        }

        private static MySqlDataReader ExecuteReader(MySqlConnection connection, CommandType commandType, string commandText, SqlConnectionOwnership connectionOwnership, params MySqlParameter[] commandParameters)
        {
            return ExecuteReader(connection, null, commandType, commandText, connectionOwnership, commandParameters);
        }

        private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, SqlConnectionOwnership connectionOwnership, MySqlParameter[] commandParameters)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            if (connectionOwnership == SqlConnectionOwnership.External)
            {
                return command.ExecuteReader();
            }
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        private static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connectionString, commandType, commandText, null);
        }

        private static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteScalar(connection, null, commandType, commandText, commandParameters);
        }

        private static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                return ExecuteScalar(connection, commandType, commandText, commandParameters);
            }
        }

        private static object ExecuteScalar(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            return command.ExecuteScalar();
        }

        //private static XmlReader ExecuteXmlReader(string connectionString, CommandType commandType, string commandText)
        //{
        //    return ExecuteXmlReader(connectionString, commandType, commandText, null);
        //}

        //private static XmlReader ExecuteXmlReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        //{
        //    return ExecuteXmlReader(connection, null, commandType, commandText, commandParameters);
        //}

        //private static XmlReader ExecuteXmlReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        //{
        //    XmlReader reader;
        //    MySqlConnection connection = new MySqlConnection(connectionString);
        //    connection.Open();
        //    try
        //    {
        //        reader = ExecuteXmlReader(connection, null, commandType, commandText, commandParameters);
        //    }
        //    catch
        //    {
        //        connection.Close();
        //        throw;
        //    }
        //    return reader;
        //}

        //private static XmlReader ExecuteXmlReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters)
        //{
        //    MySqlCommand command = new MySqlCommand();
        //    PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
        //    return command.ExecuteXmlReader();
        //}

        /// <summary>
        /// 执行存储过程以获取DataSet
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数列表</param>
        /// <returns>返回DATASET</returns>
        public static DataSet GetDataSetByProc(string spName, params object[] parameterValues)
        {
            return GetDataSetByProc(ConnectionString, spName, parameterValues);
        }

        private static DataSet GetDataSetByProc(string connectionString, string spName, params object[] parameterValues)
        {
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                MySqlParameter[] spParameterSet = DataAccessParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues, false);
                return ExecuteDataSet(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return ExecuteDataSet(connectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 执行SQL命令以获取DataSet
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="commandParameters">参数列表</param>
        /// <returns>返回DATASET</returns>
        public static DataSet GetDataSetBySql(string commandText, params MySqlParameter[] commandParameters)
        {
            return GetDataSetBySql(ConnectionString, commandText, commandParameters);
        }

        /// <summary>
        /// 执行SQL命令以获取DataSet
        /// </summary>
        /// <param name="connectionString">联接字符串</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">参数列表</param>
        /// <returns>返回DATASET</returns>
        public static DataSet GetDataSetBySql(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteDataSet(connectionString, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 执行存储过程以获取Reader
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数列表</param>
        /// <returns>返回Reader</returns>
        public static MySqlDataReader GetReaderByProc(string spName, params object[] parameterValues)
        {
            return GetReaderByProc(ConnectionString, spName, parameterValues);
        }

        private static MySqlDataReader GetReaderByProc(string connectionString, string spName, params object[] parameterValues)
        {
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                MySqlParameter[] spParameterSet = DataAccessParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues, false);
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 执行SQL命令以获取Reader
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="commandParameters">参数列表</param>
        /// <returns>返回Reader</returns>
        public static MySqlDataReader GetReaderBySql(string commandText, params MySqlParameter[] commandParameters)
        {
            return GetReaderBySql(ConnectionString, commandText, commandParameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static MySqlDataReader GetReaderBySql(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteReader(connectionString, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public static object GetScalarByProc(string spName, params object[] parameterValues)
        {
            return GetScalarByProc(ConnectionString, spName, parameterValues);
        }

        private static object GetScalarByProc(string connectionString, string spName, params object[] parameterValues)
        {
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                MySqlParameter[] spParameterSet = DataAccessParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues, false);
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object GetScalarBySql(string commandText, params MySqlParameter[] commandParameters)
        {
            return GetScalarBySql(ConnectionString, commandText, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object GetScalarBySql(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        {
            return ExecuteScalar(connectionString, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="spName"></param>
        /// <returns></returns>
        //public static XmlReader GetXmlReaderByProc(string connectionString, string spName)
        //{
        //    return ExecuteXmlReader(connectionString, CommandType.StoredProcedure, spName);
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="spName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        //public static XmlReader GetXmlReaderByProc(string connectionString, string spName, params object[] parameterValues)
        //{
        //    if ((parameterValues != null) && (parameterValues.Length > 0))
        //    {
        //        MySqlParameter[] spParameterSet = DataAccessParameterCache.GetSpParameterSet(connectionString, spName);
        //        AssignParameterValues(spParameterSet, parameterValues, false);
        //        return ExecuteXmlReader(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
        //    }
        //    return ExecuteXmlReader(connectionString, CommandType.StoredProcedure, spName);
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        //public static XmlReader GetXmlReaderBySql(string connectionString, string commandText)
        //{
        //    return ExecuteXmlReader(connectionString, CommandType.Text, commandText);
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        //public static XmlReader GetXmlReaderBySql(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        //{
        //    return ExecuteXmlReader(connectionString, CommandType.Text, commandText, commandParameters);
        //}

        private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        private enum SqlConnectionOwnership
        {
            Internal,
            External
        }
    }
}

