要建一個Model名為City
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MVCTest.Models
{
public class City
{
public string CityId { get; set; }
public string CityName { get; set; }
}
}
以下是HomeController.cs
using MVCTest.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MVCTest.Controllers
{
public class HomeController : Controller
{
string connString = "server=127.0.0.1;port=3306;user id=root;password=Acuteboy1215;database=mvctest;charset=utf8;";
MySqlConnection conn = new MySqlConnection();
public ActionResult Index()
{
conn.ConnectionString = connString;
string sql = @" SELECT `id`, `city` FROM `city`";
MySqlCommand cmd = new MySqlCommand(sql, conn);
List<City> list = new List<City>();
if (conn.State != ConnectionState.Open)
conn.Open();
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while(dr.Read())
{
City city = new City();
city.CityId = dr["id"].ToString();
city.CityName = dr["city"].ToString();
list.Add(city);
}
}
if (conn.State != ConnectionState.Closed)
conn.Close();
ViewBag.List = list;
return View();
}
}
}
這次寫錯了string sql = @" SELECT `id`, `city` FROM `city`";
寫成
string sql = @" SELECT `id`, `city` FORM `city`";
造成錯誤
MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`city`' at line 1'
無法順利找到city的資料庫
排除後就可以正常執行
以下是Index.cshtml
@{
ViewBag.Title = "Home Page";
Layout = null;
var list = ViewBag.List;
}
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
<div>
@for(int i=0;i< list.Count;i++)
{
@Html.Raw($"代號: {list[i].CityId}, 名稱: {list[i].CityName}")
<br />
}
</div>
原作者他的說明：
稍微做一下說明吧，
要用DataReader讀取要用Command的ExecuteReader()讀取資料，
然後將資料放在Model中，
(不一定要放在Model中，不過用Model其實還蠻方便的)
最後再將資料傳到前端顯示出來，
也有其他轉換成string的方式，
不過試過幾種之後發現 .ToString()的方式不會拋出例外，
除非資料會有Null的情況那就先判斷不是Null再讀資料就好了。
