using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Data;

namespace DBHelper
{
///

/// 数据库的通用访问代码 苏飞修改 /// /// 此类为抽象类， /// 不允许实例化，在应用时直接调用即可 ///

public abstract class SqlHelper
{
///

/// 数据库连接字符串 ///

    public static readonly string connectionString = System.Configuration.ConfigurationManager.AppSettings\["con"\].ToString().Trim();  
    // Hashtable to store cached parameters  
    private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

    #region//ExecteNonQuery方法

    /// <summary>  
    ///执行一个不需要返回值的MySqlCommand命令，通过指定专用的连接字符串。  
    /// 使用参数数组形式提供参数列表  
    /// </summary>  
    /// <param name="connectionString">一个有效的数据库连接字符串</param>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个数值表示此MySqlCommand命令执行后影响的行数</returns>  
    public static int ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        MySqlCommand cmd = new MySqlCommand();  
        using (MySqlConnection conn = new MySqlConnection(connectionString))  
        {  
            //通过PrePareCommand方法将参数逐个加入到MySqlCommand的参数集合中  
            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
            int val = cmd.ExecuteNonQuery();  
            //清空MySqlCommand中的参数列表  
            cmd.Parameters.Clear();  
            return val;  
        }  
    }

    /// <summary>  
    ///执行一个不需要返回值的MySqlCommand命令，通过指定专用的连接字符串。  
    /// 使用参数数组形式提供参数列表  
    /// </summary>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个数值表示此MySqlCommand命令执行后影响的行数</returns>  
    public static int ExecteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {

        return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters);  
    }

    /// <summary>  
    ///存储过程专用  
    /// </summary>  
    /// <param name="cmdText">存储过程的名字</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个数值表示此MySqlCommand命令执行后影响的行数</returns>  
    public static int ExecteNonQueryProducts(string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters);  
    }

    /// <summary>  
    ///Sql语句专用  
    /// </summary>  
    /// <param name="cmdText">T\_Sql语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个数值表示此MySqlCommand命令执行后影响的行数</returns>  
    public static int ExecteNonQueryText(string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecteNonQuery(CommandType.Text, cmdText, commandParameters);  
    }

    #endregion  
    #region//GetTable方法

    /// <summary>  
    /// 执行一条返回结果集的MySqlCommand，通过一个已经存在的数据库连接  
    /// 使用参数数组提供参数  
    /// </summary>  
    /// <param name="connecttionString">一个现有的数据库连接</param>  
    /// <param name="cmdTye">MySqlCommand命令类型</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
    public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, MySqlParameter\[\] commandParameters)  
    {  
        MySqlCommand cmd = new MySqlCommand();  
        DataSet ds = new DataSet();  
        using (MySqlConnection conn = new MySqlConnection(connecttionString))  
        {  
            PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters);  
            MySqlDataAdapter adapter = new MySqlDataAdapter();  
            adapter.SelectCommand = cmd;  
            adapter.Fill(ds);  
        }  
        DataTableCollection table = ds.Tables;  
        return table;  
    }

    /// <summary>  
    /// 执行一条返回结果集的MySqlCommand，通过一个已经存在的数据库连接  
    /// 使用参数数组提供参数  
    /// </summary>  
    /// <param name="cmdTye">MySqlCommand命令类型</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
    public static DataTableCollection GetTable(CommandType cmdTye, string cmdText, MySqlParameter\[\] commandParameters)  
    {  
        return GetTable(connectionString,cmdTye, cmdText, commandParameters);  
    }

    /// <summary>  
    /// 存储过程专用  
    /// </summary>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
    public static DataTableCollection GetTableProducts(string cmdText, MySqlParameter\[\] commandParameters)  
    {  
        return GetTable(CommandType.StoredProcedure, cmdText, commandParameters);  
    }

    /// <summary>  
    /// Sql语句专用  
    /// </summary>  
    /// <param name="cmdText"> T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
    public static DataTableCollection GetTableText(string cmdText, MySqlParameter\[\] commandParameters)  
    {  
        return GetTable(CommandType.Text, cmdText, commandParameters);  
    }  
    #endregion

    /// <summary>  
    /// 为执行命令准备参数  
    /// </summary>  
    /// <param name="cmd">SqlCommand 命令</param>  
    /// <param name="conn">已经存在的数据库连接</param>  
    /// <param name="trans">数据库事物处理</param>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">Command text，T-SQL语句 例如 Select \* from Products</param>  
    /// <param name="cmdParms">返回带参数的命令</param>  
    private static 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);  
        }  
    }

    /// <summary>  
    /// Execute a MySqlCommand that returns a resultset against the database specified in the connection string  
    /// using the provided parameters.  
    /// </summary>  
    /// <param name="connectionString">一个有效的数据库连接字符串</param>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>A MySqlDataReader containing the results</returns>  
    public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        MySqlCommand cmd = new MySqlCommand();  
        MySqlConnection conn = new MySqlConnection(connectionString);  
        // we use a try/catch here because if the method throws an exception we want to  
        // close the connection throw code, because no datareader will exist, hence the  
        // commandBehaviour.CloseConnection will not work  
        try  
        {  
            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
            MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
            cmd.Parameters.Clear();  
            return rdr;  
        }  
        catch  
        {  
            conn.Close();  
            throw;  
        }  
    }  
    #region//ExecuteDataSet方法

    /// <summary>  
    /// return a dataset  
    /// </summary>  
    /// <param name="connectionString">一个有效的数据库连接字符串</param>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>return a dataset</returns>  
    public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        MySqlConnection conn = new MySqlConnection(connectionString);  
        MySqlCommand cmd = new MySqlCommand();  
        try  
        {  
            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
            MySqlDataAdapter da = new MySqlDataAdapter();  
            DataSet ds = new DataSet();  
            da.SelectCommand = cmd;  
            da.Fill(ds);  
            return ds;  
        }  
        catch  
        {  
            conn.Close();  
            throw;  
        }  
    }

    /// <summary>  
    /// 返回一个DataSet  
    /// </summary>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>return a dataset</returns>  
    public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters);  
    }

    /// <summary>  
    /// 返回一个DataSet  
    /// </summary>  
    /// <param name="cmdText">存储过程的名字</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>return a dataset</returns>  
    public static DataSet ExecuteDataSetProducts(string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecuteDataSet(connectionString, CommandType.StoredProcedure, cmdText, commandParameters);  
    }

    /// <summary>  
    /// 返回一个DataSet  
    /// </summary>

    /// <param name="cmdText">T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>return a dataset</returns>  
    public static DataSet ExecuteDataSetText(string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters);  
    }

    public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        MySqlConnection conn = new MySqlConnection(connectionString);  
        MySqlCommand cmd = new MySqlCommand();  
        try  
        {  
            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
            MySqlDataAdapter da = new MySqlDataAdapter();  
            DataSet ds = new DataSet();  
            da.SelectCommand = cmd;  
            da.Fill(ds);  
            DataView dv = ds.Tables\[0\].DefaultView;  
            dv.Sort = sortExpression + " " + direction;  
            return dv;  
        }  
        catch  
        {  
            conn.Close();  
            throw;  
        }  
    }  
    #endregion

    #region // ExecuteScalar方法

    /// <summary>  
    /// 返回第一行的第一列  
    /// </summary>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个对象</returns>  
    public static object ExecuteScalar(CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecuteScalar(SqlHelper.connectionString, cmdType, cmdText, commandParameters);  
    }

    /// <summary>  
    /// 返回第一行的第一列存储过程专用  
    /// </summary>  
    /// <param name="cmdText">存储过程的名字</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个对象</returns>  
    public static object ExecuteScalarProducts(string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, cmdText, commandParameters);  
    }

    /// <summary>  
    /// 返回第一行的第一列Sql语句专用  
    /// </summary>  
    /// <param name="cmdText">者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>返回一个对象</returns>  
    public static object ExecuteScalarText(string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        return ExecuteScalar(SqlHelper.connectionString, CommandType.Text, cmdText, commandParameters);  
    }

    /// <summary>  
    /// Execute a MySqlCommand that returns the first column of the first record against the database specified in the connection string  
    /// using the provided parameters.  
    /// </summary>  
    /// <remarks>  
    /// e.g.:  
    ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));  
    /// </remarks>  
    /// <param name="connectionString">一个有效的数据库连接字符串</param>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>  
    public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        MySqlCommand cmd = new MySqlCommand();

        using (MySqlConnection connection = new MySqlConnection(connectionString))  
        {  
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);  
            object val = cmd.ExecuteScalar();  
            cmd.Parameters.Clear();  
            return val;  
        }  
    }

    /// <summary>  
    /// Execute a MySqlCommand that returns the first column of the first record against an existing database connection  
    /// using the provided parameters.  
    /// </summary>  
    /// <remarks>  
    /// e.g.:  
    ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));  
    /// </remarks>  
    /// <param name="connectionString">一个有效的数据库连接字符串</param>  
    /// <param name="cmdType">MySqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
    /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
    /// <param name="commandParameters">以数组形式提供MySqlCommand命令中用到的参数列表</param>  
    /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>  
    public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter\[\] commandParameters)  
    {  
        MySqlCommand cmd = new MySqlCommand();  
        PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);  
        object val = cmd.ExecuteScalar();  
        cmd.Parameters.Clear();  
        return val;  
    }

    #endregion

    /// <summary>  
    /// add parameter array to the cache  
    /// </summary>  
    /// <param name="cacheKey">Key to the parameter cache</param>  
    /// <param name="cmdParms">an array of SqlParamters to be cached</param>  
    public static void CacheParameters(string cacheKey, params MySqlParameter\[\] commandParameters)  
    {  
        parmCache\[cacheKey\] = commandParameters;  
    }

    /// <summary>  
    /// Retrieve cached parameters  
    /// </summary>  
    /// <param name="cacheKey">key used to lookup parameters</param>  
    /// <returns>Cached SqlParamters array</returns>  
    public static MySqlParameter\[\] GetCachedParameters(string cacheKey)  
    {  
        MySqlParameter\[\] cachedParms = (MySqlParameter\[\])parmCache\[cacheKey\];  
        if (cachedParms == null)  
            return null;  
        MySqlParameter\[\] clonedParms = new MySqlParameter\[cachedParms.Length\];  
        for (int i = 0, j = cachedParms.Length; i < j; i++)  
            clonedParms\[i\] = (MySqlParameter)((ICloneable)cachedParms\[i\]).Clone();  
        return clonedParms;  
    }

    /// <summary>  
    /// 检查是否存在  
    /// </summary>  
    /// <param name="strSql">Sql语句</param>  
    /// <returns>bool结果</returns>  
    public static bool Exists(string strSql)  
    {  
        int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, null));  
        if (cmdresult == 0)  
        {  
            return false;  
        }  
        else  
        {  
            return true;  
        }  
    }

    /// <summary>  
    /// 检查是否存在  
    /// </summary>  
    /// <param name="strSql">Sql语句</param>  
    /// <param name="cmdParms">参数</param>  
    /// <returns>bool结果</returns>  
    public static bool Exists(string strSql, params MySqlParameter\[\] cmdParms)  
    {  
        int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, cmdParms));  
        if (cmdresult == 0)  
        {  
            return false;  
        }  
        else  
        {  
            return true;  
        }  
    }  
}  

}