C#操作MySQL的類

C#操作MySQL的類

public class MySqlService
    {
        private static log4net.ILog logger = log4net.LogManager.GetLogger(typeof(MySqlService));

        //server=localhost;user id=root;password=root;persist security info=True;database=test
        //Data Source=127.0.0.1;port=3306;Initial Catalog=tsyw;user id=root;password=q2ii3sfc;Charset=gbk
        private string conn_str = "server=localhost;user id=root;password=q2ii3sfc;persist security info=True;database=win008";

        public MySqlService()
        {

        }
        /// <summary>
        /// 構造函數
        /// </summary>
        /// <param name="conn_str"></param>
        public MySqlService(string conn_str)
        {
            this.conn_str = conn_str;
        }

        /// <summary>
        /// 執行sql語句返回List-Hashtable數據集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<Hashtable> Select(string sql)
        {
            List<Hashtable> lst = new List<Hashtable>();
            using (MySqlConnection connection = new MySqlConnection(this.conn_str))
            {
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    MySqlDataReader mdr = cmd.ExecuteReader();
                    int columnCount = mdr.FieldCount;
                    string _key = string.Empty;
                    while (mdr.Read())
                    {
                        Hashtable ht = new Hashtable();
                        for(int i = 0; i < columnCount; i++)
                        {
                            _key = mdr.GetName(i);
                            ht[_key] = mdr[i];
                        }

                        lst.Add(ht);
                    }
                    mdr.Close();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    logger.Warn("執行操作語句異常, SQL:" + sql, ex);
                }
            }
            return lst;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public System.Data.DataSet Query(string sql)
        {
            return null;
        }

        /// <summary>
        /// 執行操作語句 delete/insert/update
        /// <para>返回影響行數</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            int rest = 0;
            using (MySqlConnection connection = new MySqlConnection(this.conn_str))
            {
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    rest = cmd.ExecuteNonQuery();
                    rest = (int)(cmd.LastInsertedId>0?cmd.LastInsertedId:rest);

                    connection.Close();
                }
                catch (Exception ex)
                {
                    logger.Warn("執行操作語句異常, SQL:" + sql, ex);
                }
            }

            return rest;
        }

        public Hashtable FetchRow(string sql)
        {
            Hashtable ht = new Hashtable();
            using (MySqlConnection connection = new MySqlConnection(this.conn_str))
            {
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    MySqlDataReader mdr = cmd.ExecuteReader();
                    int columnCount = mdr.FieldCount;
                    string _key = string.Empty;
                    while (mdr.Read())
                    {
                        for (int i = 0; i < columnCount; i++)
                        {
                            _key = mdr.GetName(i);
                            ht[_key] = mdr[i];
                        }
                        break;
                    }
                    mdr.Close();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    logger.Warn("執行操作語句異常, SQL:" + sql, ex);
                }
            }
            return ht;
        }

        /// <summary>
        /// 獲取語句第一行第一列數據
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T FetchFirst<T>(string sql) {
            T t;
            using (MySqlConnection connection = new MySqlConnection(this.conn_str))
            {
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    t = (T)cmd.ExecuteScalar();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    t = default(T);
                    logger.Warn("執行操作語句異常, SQL:" + sql, ex);
                }
            }
            return t;
        }

        /// <summary>
        /// 檢查鏈接是否OK
        /// </summary>
        /// <returns></returns>
        public bool Ping()
        {
            bool rest = false;
            using (MySqlConnection connection = new MySqlConnection(this.conn_str))
            {
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand("select 1", connection);
                    cmd.ExecuteScalar();
                    connection.Close();
                    rest = true;
                }
                catch
                {
                    rest = false;
                }
            }

            return rest;
        }

    }