说明
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);
}
}
}
}