/** * 類說明：對MYSQL數據庫的操作類 */ using System; using System.Data; using MySql.Data.MySqlClient; namespace Niunan.BYLW.Utility { /// <summary>對MYSQL數據庫的操作類 /// /// </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>創建Command對象 /// /// </summary> /// <param name="sql">SQL語句</param> public void CreateCommand(string sql) { conn.Open(); cmd = new MySqlCommand(sql, conn); } /// <summary>添加參數 /// /// </summary> /// <param name="paramName">參數名稱</param> /// <param name="value">值</param> public void AddParameter(string paramName, object value) { cmd.Parameters.Add(new MySqlParameter(paramName, value)); } /// <summary>執行不帶參數的增刪改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>執行查詢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語句查詢出的結果的第一行第一列的值 /// /// </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; } } }