数据适配器做省市级联

底层数据Adress表

省市关系

select PCity from Adress where Pcode = 0 --选择省 select PCity from Adress where Pcode = 17 ---选择市 --关系 --Id(1-16)----> 省 =====>Pcode= 0 --Pcode=Id = 1 =====> Id=1 的省的所有市。 

数据库Adress表中PCity，Id,Pcode 之间逻辑

Pcode—>Id ;Id—–>PCity

Pcode—>PCity ;Id—–>Pcode

逻辑顺序：

假定参数id—>Pcode–sql:Pcode–>dt:PCity/Id—-sql:Id—>dt:PCity/Pcode–>PCity

制作工具

Visual Studio 2015

制作材料

Label1 ： 省

Label2： 市

ComBox1：cmbProvince

ComBox2 : cmbPCity

显示效果

当加载窗体时，直接默认加载第一个省份和该省份的第一个市区。

逻辑思路

输入参数id–调用方法LoadData()—调用Pcode=id—绑定PCity/Id—显示省信息—下拉省索引触发事件–调用Id = id —绑定PCity/Id—-显示市信息

涉及事件

1.窗体加载事件

private void Form1_Load(object sender, EventArgs e) { DataTable dt = LoadData(0); this.cmbProvince.DisplayMember = "PCity"; this.cmbProvince.ValueMember = "Id"; this.cmbProvince.DataSource = dt; } 

目的：窗体加载时显示省初始化数据。

2.省级下拉索引触发事件

 private void cmbProvince_SelectedIndexChanged(object sender, EventArgs e) { int id = (int)this.cmbProvince.SelectedValue; DataTable dt = LoadData(id); cmbCity.DisplayMember = "PCity"; cmbCity.ValueMember = "Id"; this.cmbCity.DataSource = dt; } 

目的：选择省级索引时，触发显示市级索引。

注：ComboBox –ValueMember/DisPlayMember 注意理解。

设计方法

自定义加载数据方法：

DataTable LoadData(int id) { string connStr = "Data Source=.; Initial Catalog = TestSchool; Integrated Security =TRUE"; string sql = "Select Id,PCity from Adress where Pcode= @id"; SqlParameter p = new SqlParameter("@id", id); SqlDataAdapter sda = new SqlDataAdapter(sql, connStr); sda.SelectCommand.Parameters.Add(p); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } 

目的： 连接数据库访问数据，需要多次重复调用，因此定义方法，通过调用来提升程序性能。

设计界面

自定义方法：数据适配器连接

 string connStr = "Data Source=.; Initial Catalog = TestSchool; Integrated Security =TRUE"; string sql = "Select Id,PCity from Adress where Pcode= @id"; SqlParameter p = new SqlParameter("@id", id); SqlDataAdapter sda = new SqlDataAdapter(sql, connStr); sda.SelectCommand.Parameters.Add(p); 

注：采用数据适配器连接访问，简单方便。

实现源码

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; namespace PCityUnion2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { DataTable dt = LoadData(0); this.cmbProvince.DisplayMember = "PCity"; this.cmbProvince.ValueMember = "Id"; this.cmbProvince.DataSource = dt; } private void cmbProvince_SelectedIndexChanged(object sender, EventArgs e) { int id = (int)this.cmbProvince.SelectedValue; DataTable dt = LoadData(id); cmbCity.DisplayMember = "PCity"; cmbCity.ValueMember = "Id"; this.cmbCity.DataSource = dt; } DataTable LoadData(int id) { string connStr = "Data Source=.; Initial Catalog = TestSchool; Integrated Security =TRUE"; string sql = "Select Id,PCity from Adress where Pcode= @id"; SqlParameter p = new SqlParameter("@id", id); SqlDataAdapter sda = new SqlDataAdapter(sql, connStr); sda.SelectCommand.Parameters.Add(p); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } } }