---------------------- ASP.Net+Unity开发、.Net培训、期待与您交流！ ----------------------详细请查看：www.itheima.com

实现一个可以动态的进行省市县查询的程序

一 基本原理: 通过ADO.NET技术查询数据库, 并绑定到WPF控件中, 从而实现动态的省市县查询.

二 实现技术:

ADO.NET

数据绑定

三 实现步骤:

①首先进行数据库创建, 在网上下载一个省市县的数据库脚本. 部分内容如下图:

以上脚本创建了一个名叫AreaFull的表, 表中存储了个省市的信息.

AreaId----------表示该区域的Id

AreaName----表示该区域的名称

AreaPid--------表示该区域的直属上级区域

② 创建了数据库后, 在vs中使用ADO.NET技术来使用它.

先在App.Config中定义连接字符串

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="dbConn" connectionString="Data Source=.; Initial Catalog=MyTest; User ID=sa; Password=123456"/>
  </connectionStrings>
</configuration>

定义完之后不能忘了添加引用System.Configuration

然后创建SqlHelper类, 在该类中定义对于数据库的操作

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

namespace 省市县联动查询
{
    public static class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;

        public static DataTable ExecuteDataSet(string sql, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.Parameters.AddRange(sqlParameters);
                    cmd.CommandText = sql;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    return dataSet.Tables[0];
                }
            }
        }

        public static int ExecuteNonQuery(string sql, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.Parameters.AddRange(sqlParameters);
                    cmd.CommandText = sql;
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

③ 创建主程序

在主程序中需要使用Area类的对象来暂时存储查询信息, 定义如下:

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

namespace 省市县联动查询
{
    public class Area
    {
        public int AreaID { get; set; }
        public string AreaName { get; set; }
    }
}

主窗口定义:

<Window x:Name="l" x:Class="省市县联动查询.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="省市县联动查询" Height="350" Width="450" Loaded="l_Loaded">
    <Grid>
        <StackPanel Orientation="Horizontal">
            <ListBox x:Name="ProvName" DisplayMemberPath="AreaName" Width="150" SelectionChanged="ProvName_SelectionChanged"></ListBox>
            <ListBox x:Name="cityName" DisplayMemberPath="AreaName" Width="150" SelectionChanged="cityName_SelectionChanged"></ListBox>
            <ListBox x:Name="countryName" DisplayMemberPath="AreaName" Width="150"></ListBox>
        </StackPanel>
    </Grid>
</Window>

给三个ListBox控件赋予Name属性用于后台调用, DisplayMemberPath用于绑定显示的值.

后台代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace 省市县联动查询
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void l_Loaded(object sender, RoutedEventArgs e)
        {
            List<Area> listProv = new List<Area>();
            //AreaPid 表示该区域直属的上级区域的 Id, Pid为0表示没有上级直属
            DataTable table = SqlHelper.ExecuteDataSet("Select * from AreaFull where AreaPid=0");  
            //foreach (DataRow row in table.Rows)
            //{
            //    Area prov = new Area();
            //    prov.AreaID = (int)row["AreaId"];
            //    prov.AreaName = (string)row["AreaName"];
            //    listProv.Add(prov);
            //}
            //定义一个方法多次使用, 代码复用
            listProv = TableToList(table);
            ProvName.ItemsSource = listProv;
        }

        private void ProvName_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            List<Area> listCity = new List<Area>();
            Area prov = (Area)ProvName.SelectedItem;
            int provId = prov.AreaID;
            DataTable table = SqlHelper.ExecuteDataSet("Select * from AreaFull where AreaPid=@provId",
                new SqlParameter("@provId", provId));
            listCity = TableToList(table);
            cityName.ItemsSource = listCity;
        }


        private void cityName_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            List<Area> listCountry = new List<Area>();
            Area city = (Area)cityName.SelectedItem;

            //当第一级发生改动之后, 引起ProvName_SelectionChanged, 更改掉第二级的数据源
            //这时会引发cityName_SelectionChanged, 并且由于当前的SelectedItem为null,
            //所以如果不判断的话, 会引起异常.
            if (city != null)
            {
                int cityId = city.AreaID;
                DataTable table = SqlHelper.ExecuteDataSet("Select * from AreaFull where AreaPid=@cityId",
                   new SqlParameter("@cityId", cityId));
                listCountry = TableToList(table);
                countryName.ItemsSource = listCountry;
            }
            else
                countryName.ItemsSource = null;
        }

        //将返回的表转换成List<Area>
        private List<Area> TableToList(DataTable table)
        {
            List<Area> area = new List<Area>();
            foreach (DataRow row in table.Rows)
            {
                Area city = new Area();
                city.AreaID = (int)row["AreaId"];
                city.AreaName = (string)row["AreaName"];
                area.Add(city);
            }
            return area;
        }
    }
}

在后台中, 使用List<Area>类型的集合存储查找返回的信息, 然后将控件的ItemsSource绑定到该对象上.

注意:

在进行三级联动的时候, 需要对第三级进行SelectedItem的判断.

当第一级发生改动之后, 引起ProvName_SelectionChanged, 更改掉第二级的数据源
这时会引发cityName_SelectionChanged, 并且由于当前的SelectedItem为null,
所以如果不判断的话, 会引起异常

每个用于保存存储信息的集合都不能在外部声明, 除非使用ObserableList<>

 效果图:

---------------------- ASP.Net+Unity开发、.Net培训、期待与您交流！ ----------------------详细请查看：www.itheima.com