using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace Xiaobin.Shop.DAL
{
    /// <summary>對MYSQL數(shù)據(jù)庫的操作類
    /// 
    /// </summary>
    public class MYSQLHelper
    {
        private MySqlConnection conn = null;
        private MySqlCommand cmd = null;
        private MySqlDataReader sdr = null;


        public MYSQLHelper()
        {
            //string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
            string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
            conn = new MySqlConnection(connStr);
        }


        /// <summary>創(chuàng)建Command對象
        /// 
        /// </summary>
        /// <param name="sql">SQL語句</param>
        public void CreateCommand(string sql)
        {
            conn.Open();
            cmd = new MySqlCommand(sql, conn);
        }


        /// <summary>添加參數(shù)
        /// 
        /// </summary>
        /// <param name="paramName">參數(shù)名稱</param>
        /// <param name="value">值</param>
        public void AddParameter(string paramName, object value)
        {
            cmd.Parameters.Add(new MySqlParameter(paramName, value));
        }


        /// <summary>執(zhí)行不帶參數(shù)的增刪改SQL語句
        ///  
        /// </summary>
        /// <param name="cmdText">增刪改SQL語句</param>
        /// <param name="ct">命令類型</param>
        /// <returns></returns>
        public bool ExecuteNonQuery()
        {
            int res;
            try
            {
                res = cmd.ExecuteNonQuery();
                if (res > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return false;
        }


        /// <summary>執(zhí)行查詢SQL語句
        ///  
        /// </summary>
        /// <param name="cmdText">查詢SQL語句</param>
        /// <returns></returns>
        public DataTable ExecuteQuery()
        {
            DataTable dt = new DataTable();
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }


        /// <summary>返回查詢SQL語句查詢出的結(jié)果的第一行第一列的值
        /// 
        /// </summary>
        /// <returns></returns>
        public string ExecuteScalar()
        {
            string res = "";
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    res = obj.ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return res;
        }


    }
}