省市区号邮编级联

之前曾经做过好几个这样的省市级联查询，几乎每个老师讲到ADO.NET的时候都会把这个例子拿出来，每次我都没法百分百的自己写出来，都要多多少少看看之前写的代码。。。所以今天就把这个记录下来了。。（其中的数据库就是用我之前博文里的数据库）

using System.Data.SqlClient;

namespace 省市区号级联
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        //创建一个Province类
        public class Province
        {
            public string pName { get; set; }
            public int pId { get; set; }
            public override string ToString()
            {
                return this.pName;
            }
        }
        //创建一个City类
        public class City
        {
            public string cName { get; set; }
            public int cId { get; set; }
            public override string ToString()
            {
                return this.cName;
            }
        }
        //窗体加载时给“省”下拉框加载数据
        private void Form1_Load(object sender, EventArgs e)
        {
            string sql = "select pId, pName from province";
            SqlDataReader reader = SqlHelper.ExecuteReader(sql, null);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    cmbPro.Items.Add(
                            new Province
                            {
                                pName = reader.GetString(reader.GetOrdinal("pName")),
                                pId = reader.GetInt32(reader.GetOrdinal("pId"))
                            }
                        );
                }
            }
            //默认选择第一个省份
            cmbPro.SelectedIndex = 0;
        }
        //当“省”下拉框选择了数据时，触发事件，引起“市”下拉框跟着改变
        private void cmbPro_SelectedIndexChanged(object sender, EventArgs e)
        {
            //很重要！！改变省份时要将城市下拉框清空
            cmbCity.Items.Clear();
            int pid = ((Province)cmbPro.SelectedItem).pId;
            string sql = "select cid,cName from city where pid = @pid";
            SqlParameter[] para = {
                                      new SqlParameter("@pid",pid)
                                  };
            SqlDataReader reader = SqlHelper.ExecuteReader(sql, para);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    cmbCity.Items.Add(
                            new City
                            {
                                cName = reader.GetString(reader.GetOrdinal("cName")),
                                cId = reader.GetInt32(reader.GetOrdinal("cid"))
                            }
                        );
                }
            }
            //默认选择第一个城市
            cmbCity.SelectedIndex = 0;
        }
        //当“市”下拉框选择了数据时，触发事件，引起“区号”和“邮编”下拉框跟着改变
        private void cmbCity_SelectedIndexChanged(object sender, EventArgs e)
        {
            int cid = ((City)cmbCity.SelectedItem).cId;
            string sql = "select cpostcode,careanum from city where cid = @cid"; 
            SqlParameter[] para = {
                                      new SqlParameter("@cid",cid)
                                  };
            SqlDataReader reader = SqlHelper.ExecuteReader(sql, para);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    txtAreaNum.Text = reader.GetString(reader.GetOrdinal("cPostCode"));
                    txtPostCode.Text = reader.GetString(reader.GetOrdinal("cAreaNum"));
                }
            }
        }
    }
}

另外，在项目中添加SqlHelper类。。封装了ExecuteReader方法，这样调用会少写很多的代码！

using System.Data.SqlClient;

namespace 省市区号级联
{
    public static class SqlHelper
    {
        //连接字符串，可根据自己的电脑和数据库更改
        static string constr = "server=.;database=MyDB;uid=sa;pwd=xqt";
        //封装一个ExecuteReader方法
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(constr);
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                //SqlDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
        }
    }
}

可能这个用处不大。。但是保存下来总会有用到的一天的。。