C# connect to MySQL

在此分享一 C#與mysql連結的class，

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

namespace SpotMap
{
    class mysql
    {
        string dbHost = "localhost";
        string dbUser = "root";
        string dbPass = "root";
        string dbName = "POI";
        MySqlConnection conn = null;

        public mysql() {
            // 如果有特殊的編碼在database後面請加上;CharSet=編碼, utf8請使用utf8_general_ci
            //insert into解決中文亂碼問題，關鍵在於連結mysql需加入charset為utf8。
            string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName + ";Charset=utf8";
            conn = new MySqlConnection(connStr);

            // 連線到資料庫
            try
            {
                conn.Open();
            } catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("無法連線到資料庫.");
                        break;
                    case 1045:
                        Console.WriteLine("使用者帳號或密碼錯誤,請再試一次.");
                        break;
                }
            }
        }

        public void runCmd(string sql)
        {
            //單純的SQL指令（如create table等）
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }

        public List<List<String>> runSQL(string SQL)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL, conn);
                MySqlDataReader myData = cmd.ExecuteReader();
                //myData.FieldCount

                List<List<String>> results = new List<List<String>>();
                if (!myData.HasRows)
                {
                    // 如果沒有資料,顯示沒有資料的訊息
                    Console.WriteLine("No data.");
                }
                else
                {
                    int count = myData.FieldCount;
                    // initialize the List structure with fields count
                    for (int i=0; i&lt;count; i++)
                        results.Add(new List&lt;String&gt;());
                    // 讀取資料並且顯示出來
                    while (myData.Read())
                    {
                        for (int i = 0; i &lt; count; i++)
                            results[i].Add(myData.GetString(i));
                    }
                }
                myData.Close();
                return results;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine(&quot;Error &quot; + ex.Number + &quot; : &quot; + ex.Message);
            }
            return new List&lt;List&lt;String&gt;&gt;();
        }

        public void dumpData&lt;T&gt;(List&lt;List&lt;T&gt;&gt; data) {
            int size = data[0].Count;

            for (int i = 0; i &lt; size; i++)
            {
                for (int j = 0; j &lt; data.Count; j++)
                {
                    Console.Write(data[j][i] + "\t");
                }
                Console.WriteLine("");
            }
        }
    }
}