MySql查詢數據

引用的DLL

MySql.Data.MySqlClient
System.Data

City實體

    public class City
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string CountryCode { get; set; }
        public string District { get; set; }
        public int Population { get; set; }
    }

連接字元串

string connectionStr = "server=127.0.0.1;database=資料庫;User ID=root;password=密碼";

實現邏輯

        public List<City> GetCityList()
        {
            List<City> cityList = new List<City>();
            string sqlStr = "select * from city";
            using (MySqlConnection con = new MySqlConnection(connectionStr))
            {
                con.Open();
                MySqlCommand command = new MySqlCommand();
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                command.Connection = con;
                command.CommandText = sqlStr;
                using (MySqlDataAdapter da = new MySqlDataAdapter(command))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds, "city");
                    foreach (DataRow inst in ds.Tables[0].Rows)
                    {
                        City city = new City();
                        city.ID = int.Parse(inst["ID"].ToString());
                        city.Name = inst["Name"].ToString();
                        city.CountryCode = inst["CountryCode"].ToString();
                        city.District = inst["District"].ToString();
                        city.Population = int.Parse(inst["Population"].ToString());
                        cityList.Add(city);

                    }
                }
            }
            return cityList;
        }