經典的「選擇縣市 → 自動載入該縣市下的鄉鎮」二級聯動下拉選單,透過 DropDownList 控制項的 AutoPostBack 與 SelectedIndexChanged 事件,配合資料庫關聯查詢即可輕鬆實現。
-- 縣市表
CREATE TABLE Province (
ProID INT PRIMARY KEY,
ProName NVARCHAR(20) NOT NULL
);
-- 鄉鎮表
CREATE TABLE City (
CityID INT PRIMARY KEY,
ProID INT FOREIGN KEY REFERENCES Province(ProID),
CityName NVARCHAR(20) NOT NULL
);
-- 範例資料
INSERT INTO Province VALUES (1, N'台北市'), (2, N'新北市'), (3, N'桃園市');
INSERT INTO City VALUES (1, 1, N'中正區'), (2, 1, N'大同區'), (3, 1, N'中山區');
INSERT INTO City VALUES (4, 2, N'板橋區'), (5, 2, N'三重區'), (6, 2, N'中和區');
INSERT INTO City VALUES (7, 3, N'桃園區'), (8, 3, N'中壢區'), (9, 3, N'平鎮區');
<asp:DropDownList ID="ddlProvince" runat="server" AutoPostBack="true"
OnSelectedIndexChanged="ddlProvince_SelectedIndexChanged">
</asp:DropDownList>
<asp:DropDownList ID="ddlCity" runat="server">
</asp:DropDownList>
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class CascadingDropdown : System.Web.UI.Page
{
// 連接字串從 web.config 讀取
readonly string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindProvinces();
}
}
private void BindProvinces()
{
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("SELECT ProID, ProName FROM Province ORDER BY ProID", conn))
{
conn.Open();
ddlProvince.DataSource = cmd.ExecuteReader();
ddlProvince.DataTextField = "ProName";
ddlProvince.DataValueField = "ProID";
ddlProvince.DataBind();
// 觸發一次城市綁定 (顯示第一個縣市的鄉鎮)
ddlProvince_SelectedIndexChanged(null, EventArgs.Empty);
}
}
protected void ddlProvince_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlProvince.SelectedValue == "") return;
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("SELECT CityID, CityName FROM City WHERE ProID = @ProID ORDER BY CityID", conn))
{
cmd.Parameters.AddWithValue("@ProID", ddlProvince.SelectedValue); // 參數化查詢防注入
conn.Open();
ddlCity.DataSource = cmd.ExecuteReader();
ddlCity.DataTextField = "CityName";
ddlCity.DataValueField = "CityID";
ddlCity.DataBind();
}
}
}
@ProID 參數,防止 SQL Injection。Page_Load 綁定縣市後,手動呼叫一次城市綁定邏輯,確保畫面一致。使用 UpdatePanel 包裹兩個 DropDownList,或改用 jQuery AJAX 呼叫 WebMethod/PageMethod/Web API 取得城市 JSON 資料,前端動態填充 <option>,避免整頁閃爍:
// 前端 jQuery 範例
$('#ddlProvince').change(function() {
var provinceId = $(this).val();
$.getJSON('/api/cities', { provinceId: provinceId }, function(data) {
var ddlCity = $('#ddlCity').empty();
$.each(data, function(i, item) {
ddlCity.append($('
同樣邏輯延伸:ddlCity_SelectedIndexChanged 綁定 ddlDistrict (區/里),以此類推。建議將共同邏輯封裝為泛型方法 BindDropdown(ddl, table, textField, valueField, whereClause, parameters)。
此模式廣泛應用於地址選擇、產品分類、組織架構等層級資料選擇場景,掌握後可靈活套用。