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

namespace drWm.mdal
{
public class MySqlHelper
{
//private static string connstr = @"server=localhost;uid=root;pwd=121535;database=tem;charset=utf8";
private static string connstr = @"Host=127.0.0.1;UserName=root;Password=drpassword;Database=watermonitor;Port=3406;CharSet=utf8;Allow Zero Datetime=true";

    #region 执行查询语句，返回MySqlDataReader  
    /// <summary>  
    /// 执行查询语句，返回MySqlDataReader  
    /// </summary>  
    /// <param name="sqlString"></param>  
    /// <returns></returns>  
    public static MySqlDataReader ExecuteReader(string sqlString)  
    {  
        MySqlConnection connection = new MySqlConnection(connstr);  
        MySqlCommand cmd = new MySqlCommand(sqlString, connection);  
        MySqlDataReader myReader = null;  
        try  
        {  
            connection.Open();  
            myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
            return myReader;  
        }  
        catch (System.Data.SqlClient.SqlException e)  
        {  
            connection.Close();  
            throw new Exception(e.Message);  
        }  
        finally  
        {  
            if (myReader == null)  
            {  
                cmd.Dispose();  
                connection.Close();  
            }  
        }  
    }  
    #endregion

    #region 执行带参数的查询语句，返回MySqlDataReader  
    /// <summary>  
    /// 执行带参数的查询语句，返回MySqlDataReader  
    /// </summary>  
    /// <param name="sqlString"></param>  
    /// <param name="cmdParms"></param>  
    /// <returns></returns>  
    public static MySqlDataReader ExecuteReader(string sqlString, params MySqlParameter\[\] cmdParms)  
    {  
        MySqlConnection connection = new MySqlConnection(connstr);  
        MySqlCommand cmd = new MySqlCommand();  
        MySqlDataReader myReader = null;  
        try  
        {  
            PrepareCommand(cmd, connection, null, sqlString, cmdParms);  
            myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
            cmd.Parameters.Clear();  
            return myReader;  
        }  
        catch (System.Data.SqlClient.SqlException e)  
        {  
            connection.Close();  
            throw new Exception(e.Message);  
        }  
        finally  
        {  
            if (myReader == null)  
            {  
                cmd.Dispose();  
                connection.Close();  
            }  
        }  
    }  
    #endregion

    #region 执行sql语句,返回执行行数  
    /// <summary>  
    /// 执行sql语句,返回执行行数  
    /// </summary>  
    /// <param name="sql"></param>  
    /// <returns></returns>  
    public static int ExecuteSql(string sql)  
    {  
        using (MySqlConnection conn = new MySqlConnection(connstr))  
        {  
            using (MySqlCommand cmd = new MySqlCommand(sql, conn))  
            {  
                try  
                {  
                    conn.Open();  
                    int rows = cmd.ExecuteNonQuery();  
                    return rows;  
                }  
                catch (MySql.Data.MySqlClient.MySqlException e)  
                {  
                    conn.Close();  
                    throw e;  
                }  
                finally  
                {  
                    cmd.Dispose();  
                    conn.Close();  
                }  
            }  
        }  
    }  
    #endregion

    #region 执行带参数的sql语句，并返回执行行数  
    /// <summary>  
    /// 执行带参数的sql语句，并返回执行行数  
    /// </summary>  
    /// <param name="sqlString"></param>  
    /// <param name="cmdParms"></param>  
    /// <returns></returns>  
    public static int ExecuteSql(string sqlString, params MySqlParameter\[\] cmdParms)  
    {  
        using (MySqlConnection connection = new MySqlConnection(connstr))  
        {  
            using (MySqlCommand cmd = new MySqlCommand())  
            {  
                try  
                {  
                    PrepareCommand(cmd, connection, null, sqlString, cmdParms);  
                    int rows = cmd.ExecuteNonQuery();  
                    cmd.Parameters.Clear();  
                    return rows;  
                }  
                catch (System.Data.SqlClient.SqlException E)  
                {  
                    throw new Exception(E.Message);  
                }  
                finally  
                {  
                    cmd.Dispose();  
                    connection.Close();  
                }  
            }  
        }  
    }  
    #endregion

    #region 执行查询语句，返回DataSet  
    /// <summary>  
    /// 执行查询语句，返回DataSet  
    /// </summary>  
    /// <param name="sql"></param>  
    /// <returns></returns>  
    public static DataSet GetDataSet(string sql)  
    {  
        using (MySqlConnection conn = new MySqlConnection(connstr))  
        {  
            DataSet ds = new DataSet();  
            try  
            {  
                conn.Open();  
                MySqlDataAdapter DataAdapter = new MySqlDataAdapter(sql, conn);  
                DataAdapter.Fill(ds);  
            }  
            catch (Exception ex)  
            {  
                throw ex;  
            }  
            finally  
            {  
                conn.Close();  
            }  
            return ds;  
        }  
    }  
    #endregion

    #region 执行带参数的查询语句，返回DataSet  
    /// <summary>  
    /// 执行带参数的查询语句，返回DataSet  
    /// </summary>  
    /// <param name="sqlString"></param>  
    /// <param name="cmdParms"></param>  
    /// <returns></returns>  
    public static DataSet GetDataSet(string sqlString, params MySqlParameter\[\] cmdParms)  
    {  
        using (MySqlConnection connection = new MySqlConnection(connstr))  
        {  
            MySqlCommand cmd = new MySqlCommand();  
            PrepareCommand(cmd, connection, null, sqlString, cmdParms);  
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))  
            {  
                DataSet ds = new DataSet();  
                try  
                {  
                    da.Fill(ds, "ds");  
                    cmd.Parameters.Clear();  
                }  
                catch (System.Data.SqlClient.SqlException ex)  
                {  
                    throw new Exception(ex.Message);  
                }  
                finally  
                {  
                    cmd.Dispose();  
                    connection.Close();  
                }  
                return ds;  
            }  
        }  
    }  
    #endregion

    #region 执行带参数的sql语句，并返回object  
    /// <summary>  
    /// 执行带参数的sql语句，并返回object  
    /// </summary>  
    /// <param name="sqlString"></param>  
    /// <param name="cmdParms"></param>  
    /// <returns></returns>  
    public static object GetSingle(string sqlString, params MySqlParameter\[\] cmdParms)  
    {  
        using (MySqlConnection connection = new MySqlConnection(connstr))  
        {  
            using (MySqlCommand cmd = new MySqlCommand())  
            {  
                try  
                {  
                    PrepareCommand(cmd, connection, null, sqlString, cmdParms);  
                    object obj = cmd.ExecuteScalar();  
                    cmd.Parameters.Clear();  
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
                    {  
                        return null;  
                    }  
                    else  
                    {  
                        return obj;  
                    }  
                }  
                catch (System.Data.SqlClient.SqlException e)  
                {  
                    throw new Exception(e.Message);  
                }  
                finally  
                {  
                    cmd.Dispose();  
                    connection.Close();  
                }  
            }  
        }  
    }  
    #endregion

    /// <summary>  
    /// 执行存储过程,返回数据集  
    /// </summary>  
    /// <param name="storedProcName">存储过程名</param>  
    /// <param name="parameters">存储过程参数</param>  
    /// <returns>DataSet</returns>  
    public static DataSet RunProcedureForDataSet(string storedProcName, IDataParameter\[\] parameters)  
    {  
        using (MySqlConnection connection = new MySqlConnection(connstr))  
        {  
            DataSet dataSet = new DataSet();  
            connection.Open();  
            MySqlDataAdapter sqlDA = new MySqlDataAdapter();  
            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);  
            sqlDA.Fill(dataSet);  
            connection.Close();  
            return dataSet;  
        }  
    }

    /// <summary>  
    /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)  
    /// </summary>  
    /// <param name="connection">数据库连接</param>  
    /// <param name="storedProcName">存储过程名</param>  
    /// <param name="parameters">存储过程参数</param>  
    /// <returns>SqlCommand</returns>  
    private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName,  
        IDataParameter\[\] parameters)  
    {  
        MySqlCommand command = new MySqlCommand(storedProcName, connection);  
        command.CommandType = CommandType.StoredProcedure;  
        foreach (MySqlParameter parameter in parameters)  
        {  
            command.Parameters.Add(parameter);  
        }  
        return command;  
    }

    #region 装载MySqlCommand对象  
    /// <summary>  
    /// 装载MySqlCommand对象  
    /// </summary>  
    private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, 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 = CommandType.Text; //cmdType;  
        if (cmdParms != null)  
        {  
            foreach (MySqlParameter parm in cmdParms)  
            {  
                cmd.Parameters.Add(parm);  
            }  
        }  
    }  
    #endregion

}  

}