省市联动

简单点的数据表：

 

实体类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace 省市联动
{
    public class tblArea
    {
        public int AreaId { get; set; }
        public string AreaName { get; set; }
        public int AreaPid { get; set; }
        public override string ToString()
        {
            return this.AreaName;
        }
    }
}

 

SqlHelper.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace 省市联动
{
    public static class SqlHelper
    {
        //定义一个连接字符串,
        //readonly，只能在初始化和构造函数中赋值
        private static readonly string conStr=ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

        //1.执行增insert 删delete 改update的方法;
        public static int ExecuteNonQuery(string sql,params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        //2.执行查询，返回单个值的方法
        //ExecuteScalar()
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        //3.执行查询，返回多行，多列的方法
        //ExecuteReader
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection conn = new SqlConnection(conStr);
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch
                    {
                        conn.Close();
                        conn.Dispose();
                        throw;
                    }
                }
        }
    }
}

 

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="mssqlserver" connectionString="data source=.;initial catalog=testhome;integrated security=true;"/>
  </connectionStrings>
</configuration>

 

 

界面如下 :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 省市联动
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //1.加载所有省份
            try
            {
                LoadPrivce();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadPrivce()
        {
           //1.查询所有父ID为0 的那些数据
            string sql = "select areaid,areaname from tblArea where areapid=@pid";
            SqlParameter pl = new SqlParameter("@pid", SqlDbType.Int) { Value = 0 };
           using ( SqlDataReader reader= SqlHelper.ExecuteReader(sql,pl))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        tblArea model = new tblArea();
                        model.AreaId = reader.GetInt32(0);
                        model.AreaName = reader.GetString(1);
                        comboBox1.Items.Add(model);

                    }
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (comboBox1.SelectedItem != null)
            {
                tblArea model = comboBox1.SelectedItem as tblArea;
                MessageBox.Show(model.AreaName + "  " + model.AreaId);
            }
        }

        //选择项改变事件
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //1.获取当前省份的ID
            if (comboBox1.SelectedItem != null)
            {
                //comboBox2.Items.Clear();  //方式一时使用
                tblArea model = comboBox1.SelectedItem as tblArea;
                int areaId = model.AreaId;

                //2.根据areaid从数据库中查询对应的数据
                List<tblArea> cities = GetSubCity(areaId);

                //方式一：
                    //foreach (tblArea item in cities)
                    //{
                    //    comboBox2.Items.Add(item);
                    //}

                //方式二：通过数据绑定的方式向下拉菜单中增加项
                comboBox2.DataSource = cities;
                comboBox2.DisplayMember = "AreaName";
                comboBox2.ValueMember ="AreaID";
            }
        }

        private List<tblArea> GetSubCity(int areaId)
        {
            List<tblArea> list = new List<tblArea>();
            string sql = "select areaid,areaname from tblarea where areapid=@areaid";
            SqlParameter pl = new SqlParameter("@areaid", SqlDbType.Int) { Value = areaId };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql,pl))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        tblArea model = new tblArea();
                        model.AreaId= reader.GetInt32(0);
                        model.AreaName = reader.GetString(1);
                        list.Add(model);
                    }
                }
            }
            return list;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if(comboBox2.SelectedItem!=null)
            {
                MessageBox.Show(comboBox2.Text + "  " + comboBox2.SelectedValue.ToString());
            }
        }
    }
}