利用下拉框选择省份、城市、地区

说明

1.首先封装操作数据库的类SqlHelper,当然前提还得创建数据库连接

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
namespace 省市联动
{
    public class SqlHelper
    {
        private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        //封装三个方法
        /// <summary>
        /// 此方法可以做增删改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">sql语句中的参数</param>
        /// <returns>返回受影响的行数,int类型</returns>
        public static int ExecuteNonQuery(string sql,params SqlParameter[] ps)
        {
            //连接数据库
            using (SqlConnection con=new SqlConnection(str))
            {
                using (SqlCommand cmd=new SqlCommand(sql,con))
                {
                    con.Open();
                    if (ps!=null)
                    {
                        cmd.Parameters.AddRange(ps);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 该方法用在查询上
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">sql语句中的参数</param>
        /// <returns>首行首列,object类型</returns>
        public static object ExecuteSclar(string sql,params SqlParameter[] ps)
        {
            using (SqlConnection con=new SqlConnection(str))
            {
                using (SqlCommand cmd=new SqlCommand(sql,con))
                {
                    con.Open();
                    if (ps != null)
                    {
                        cmd.Parameters.AddRange(ps);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 该方法用于查询读数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">sql语句中的参数</param>
        /// <returns>返回的是SqlDataReader对象,里面有数据</returns>
        public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] ps)
        {
            SqlConnection con = new SqlConnection(str);
            using (SqlCommand cmd=new SqlCommand(sql,con))
            {
                if (ps!=null)
                {
                    cmd.Parameters.AddRange(ps);
                }
                try
                {
                    con.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    con.Close();
                    con.Dispose();
                    throw ex;
                }
            }
        }
    }
}

2.修改App.config中的内容,做好准备

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="conStr" connectionString="Data Source=LENOVO-PC;Initial Catalog=MyDatabase;Integrated Security=True" />
    <add name="省市联动.Properties.Settings.MyDatabaseConnectionString"
      connectionString="Data Source=LENOVO-PC;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=sa;Password=pingxing9132"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
    </startup>
</configuration>

3.添加引用,右键项目下的引用,添加引用,程序集,System.Configuration,确定
4.窗体设计如下
这里写图片描述
5.窗体代码

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 省市联动
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        int r = -1;//判断标志,全局变量
        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“myDatabaseDataSet2.S_District”中。您可以根据需要移动或删除它。
            this.s_DistrictTableAdapter1.Fill(this.myDatabaseDataSet2.S_District);
            // TODO: 这行代码将数据加载到表“myDatabaseDataSet1.S_District”中。您可以根据需要移动或删除它。
            this.s_DistrictTableAdapter.Fill(this.myDatabaseDataSet1.S_District);
            // TODO: 这行代码将数据加载到表“myDatabaseDataSet.S_City”中。您可以根据需要移动或删除它。
            this.s_CityTableAdapter.Fill(this.myDatabaseDataSet.S_City);
            r = 1;
            LoadAreaByAreaId(0);
        }   
        private void LoadAreaByAreaId(int p)
        {
            List<Area> list = new List<Area>();
            if (r == 1)//窗体加载时显示省份
            {
                list.Clear();
                list.Add(new Area() { Id = -1, Name = "请选择" });//只是在集合存在,在数据库中是不存在的,查询不到的
                string sql = "select ProvinceID,Provincename from .S_Province where AreaId=" + p;
                using (SqlDataReader reader = SqlHelper.ExecuteReader(sql))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Area a = new Area();
                            a.Id = Convert.ToInt32(reader["ProvinceID"]);
                            a.Name = reader["ProvinceName"].ToString();
                            list.Add(a);
                        }
                    }
                }
                cmbProvince.DataSource = list;
                cmbProvince.DisplayMember = "Name";//设置要显示的属性
                cmbProvince.ValueMember = "Id";//设置要显示的属性隐藏的值
                /*
                实际上在Combox中显示的值是表面的,在内存中存在的是前面的id
                数字,意思就是说,Combox中显示的是Name,而在内存中存储的是Id,将
                来如果用cmbProvince.SelectedValue拿到的值就是Id的值。
                */
            }
            else if (r==2)//点击显示城市
            {
                list.Clear();
                list.Add(new Area() { Id = -1, Name = "请选择" });
                string sql = "select CityID,CityName from .S_City where ProvinceID="+p;
                using (SqlDataReader reader=SqlHelper.ExecuteReader(sql))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Area b = new Area();
                            b.Id = Convert.ToInt32(reader["CityID"]);
                            b.Name = reader["CityName"].ToString();
                            list.Add(b);
                        }
                    }
                }
                cmbCity.DataSource = list;
                cmbCity.DisplayMember = "Name";
                cmbCity.ValueMember = "Id";
                listBox1.DataSource = list;
                listBox1.DisplayMember = "Name";
            }
            else
            {
                list.Clear();
                list.Add(new Area() { Id = -1, Name = "请选择" });
                string sql = "select DistrictID,DistrictName from .S_District where CityID="+p;
                using (SqlDataReader reader=SqlHelper.ExecuteReader(sql))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Area c = new Area();
                            c.Id = Convert.ToInt32(reader["DistrictID"]);
                            c.Name = reader["DistrictName"].ToString();
                            list.Add(c);
                        }
                    }
                }
                cmbDistrict.DataSource = list;
                cmbDistrict.DisplayMember = "Name";
                cmbDistrict.ValueMember = "Id";
                listBox2.DataSource = list;
                listBox2.DisplayMember = "Name";
                listBox2.ValueMember = "Id";
            }
        }
        private void cmbProvince_SelectedIndexChanged(object sender, EventArgs e)
        {
            r = 2;
            if (cmbProvince.SelectedIndex != 0)//刚开始的值是类
            {
                int selectValue = Convert.ToInt32(cmbProvince.SelectedValue);
                LoadAreaByAreaId(selectValue);
            }  

        }

        private void cmbCity_SelectedIndexChanged(object sender, EventArgs e)
        {
            r = 3;
            if (cmbCity.SelectedIndex != 0)
            {
                int selectValue = Convert.ToInt32(cmbCity.SelectedValue);
                LoadAreaByAreaId(selectValue);
            }
        }
    }
}
  • 1
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值