Two-level association between data tables bound to DropDownList

Scenario 1: When we Add the drop-down option under the DropDownList control, we will use its Item. Add method to Add it directly under the Code. If you want to add or modify the drop-down options, you must modify the source code. If the drop-down options of several DropDownList controls are the same, we need to add them multiple times, which is inconvenient for later maintenance work.

Scenario 2: When we buy a ticket on the 12306 website, we must first select the province of the destination, after the province is selected, the city of the province is automatically loaded in the city selection box to achieve two-level linkage.

For the above two scenarios, we can use DropDownList to directly bind data tables and dynamically load cities in the selected province. It's not a good guy. Let me use a Demo to demonstrate the detailed process.

First, we need to create two tables in the database, one is the Province (Province) Table and the other is the City (City) table. The table creation statement is as follows:

Create Table Province (ProID int primary key, ProName varchar (20) not null) Create Table City (CityID int primary key, ProID int foreign key references Province (ProID ), cityName varchar (20) Insert into Province values ('1', 'beijing') Insert into Province values ('2', 'hebei ') insert into Province values ('3', 'shandong ') insert into City values ('1', '1', 'haidian') insert into City values ('2 ', '1', 'fengtai ') insert into City values ('3', '1', 'daxing') insert into City values ('4', '2 ', 'hengshui ') insert into City values ('5', '2', 'langfang') insert into City values ('6', '2', 'baoding ') insert into City values ('7', '3', 'jinan ') insert into City values ('8', '3', 'yantai ') insert into City values ('9', '3', 'qingdao ')

Through the table creation statement, we can know that there are three cities in Beijing: Haidian, Fengtai, Daxing, and Hengshui, Langfang, And Baoding, shandong has three cities: Jinan, Yantai, and Qingdao.

Then, place the control in the Web form, as shown in the following figure:

The dropDownList control names are ddlProvince and ddlCity.

Then we implement the function in the Web Background code. When loading a Web form, bind the ddlProvince control to the Province table. When the ddlProvince drop-down option is changed, bind the ddlCity control to the City table. The implementation code is as follows:

Create a database connection class:

Public class DB {// The database connection string public static SqlConnection CreateConnection () {SqlConnection con = new SqlConnection ("Data Source = .; initial Catalog = test; uid = sa; pwd = 123456; "); return con ;}}

Run the following code when loading a Web form:

Protected void Page_Load (object sender, EventArgs e) {// if the form is loaded for the first time if (! This. isPostBack) {// bind the province SqlConnection con = DB. createConnection (); // open the database connection con. open (); SqlCommand extends Province = new SqlCommand ("select * from Province", con); SqlDataReader sdrProvince = extends Province. executeReader (); // bind content in sdrProvince to the ddlProvince drop-down list this. ddlProvince. dataSource = sdrProvince; // the content in the data table Province to be displayed this. ddlProvince. dataTextField = "ProName"; // The primary key of the data table that needs to be displayed in Province this. ddlProvince. dataValueField = "ProID"; this. ddlProvince. dataBind (); sdrProvince. close (); // Close the database connection con. close ();}}

Code executed when the drop-down menu of the ddlProvince control is changed:

Protected void ddlProvince_SelectedIndexChanged (object sender, EventArgs e) {SqlConnection con = DB. createConnection (); // open the database connection con. open (); // bind the City SqlCommand cmdCity = new SqlCommand ("select * from City where ProID =" + this. ddlProvince. selectedValue, con); SqlDataReader sdrCity = cmdCity. executeReader (); // bind content in sdrCity to the ddlCity drop-down list this. ddlCity. dataSource = sdrCity; // the content in the City table to be displayed this. ddlCity. dataTextField = "CityName"; // The primary key this. ddlCity. dataValueField = "CityID"; this. ddlCity. dataBind (); sdrCity. close (); // Close the database connection con. close ();}

In this way, we use DropDownList to dynamically bind data tables and implement the function of dynamically pulling down cities in the province according to the selected province. This achieves the goal of decoupling in the object-oriented design, enhances code maintainability and user experience.

I hope my explanation will be helpful to you.