private void Form1_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("server=localhost;Integrated security=yes;database=pubs");
SqlDataAdapter da = new SqlDataAdapter("select CountryID, CountryName from country", cn);
DataSet ds = new DataSet();
cn.Open();
da.Fill(ds);
cn.Close();
comboBox1.DataSource = ds.Tables[0];
comboBox1.ValueMember = "CountryID";
comboBox1.DisplayMember = "CountryName";
}private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.Text.Trim().Length == 0)
return; SqlConnection cn = new SqlConnection("server=.;Integrated security=yes;database=pubs");
string strSQL = "select ProvinceID, ProvinceName from Province where CountryID = @CountryID";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
da.SelectCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = Convert.ToInt32(comboBox1.Text);
DataSet ds = new DataSet();
cn.Open();
da.Fill(ds);
cn.Close();
comboBox2.DataSource = ds.Tables[0];
comboBox2.ValueMember = "ProvinceID";
comboBox2.DisplayMember = "ProvinceName";
}private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox2.Text.Trim().Length == 0)
return; SqlConnection cn = new SqlConnection("server=.;Integrated security=yes;database=pubs");
string strSQL = "select CityID,CityName from city where ProvinceID = @ProvinceID";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
da.SelectCommand.Parameters.Add("@ProvinceID", SqlDbType.Int).Value = Convert.ToInt32(comboBox2.Text);
DataSet ds = new DataSet();
cn.Open();
da.Fill(ds);
cn.Close();
comboBox3.DataSource = ds.Tables[0];
comboBox3.ValueMember = "CityID";
comboBox3.DisplayMember = "CityName";
}
{
SqlConnection cn = new SqlConnection("server=localhost;Integrated security=yes;database=pubs");
SqlDataAdapter da = new SqlDataAdapter("select CountryID, CountryName from country", cn);
DataSet ds = new DataSet();
cn.Open();
da.Fill(ds);
cn.Close();
comboBox1.DataSource = ds.Tables[0];
comboBox1.ValueMember = "CountryID";
comboBox1.DisplayMember = "CountryName";
}private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.Text.Trim().Length == 0)
return; SqlConnection cn = new SqlConnection("server=.;Integrated security=yes;database=pubs");
string strSQL = "select ProvinceID, ProvinceName from Province where CountryID = @CountryID";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
da.SelectCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = Convert.ToInt32(comboBox1.Text);
DataSet ds = new DataSet();
cn.Open();
da.Fill(ds);
cn.Close();
comboBox2.DataSource = ds.Tables[0];
comboBox2.ValueMember = "ProvinceID";
comboBox2.DisplayMember = "ProvinceName";
}private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox2.Text.Trim().Length == 0)
return; SqlConnection cn = new SqlConnection("server=.;Integrated security=yes;database=pubs");
string strSQL = "select CityID,CityName from city where ProvinceID = @ProvinceID";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
da.SelectCommand.Parameters.Add("@ProvinceID", SqlDbType.Int).Value = Convert.ToInt32(comboBox2.Text);
DataSet ds = new DataSet();
cn.Open();
da.Fill(ds);
cn.Close();
comboBox3.DataSource = ds.Tables[0];
comboBox3.ValueMember = "CityID";
comboBox3.DisplayMember = "CityName";
}
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_City]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_City]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE VIEW dbo.v_City
AS
select a.*,b.ProvinceName,b.CounntryID,c.CountryName
from City a left join Province b on a.ProvinceId=b.ProvinceId
left join Country c on b.CountryID=c.CountryID GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
选取数据时从创建的v_City视图中选取并按照CounntryID,ProvinceId,id排序做起来更好些。
改为
SqlConnection cn = new SqlConnection("server=127.0.0.1;Integrated security=yes;database=pubs");