如何从不同表中的数据库填充dropdowlist中的数据？ [英] how to populate data in dropdowlist from database fom different tables?
问题描述
我有3张桌子
1.州（state_id，state_name） state_id PK，身份规范，
2.city（city_id， city_name.state_id） city_id PK，身份规范，
state_id FK
3.university（uni_id，uni_name， state_id，city_id） uni_id PK，身份规范， state_id FK， city_id FK
并拿了一个表格（university.aspx）有三个标签和各州，市，大学的下拉列表.......
我想要的是当我点击状态然后只有属于那个州的城市名单应该和大学相同......我试过以下代码
university.aspx.cs
BusinessLayer.State objstate = new BusinessLayer.State（System.Configuration.ConfigurationManager.ConnectionStrings [ te 。ST]的ToString（））;
BusinessLayer.cities objcity = new BusinessLayer.cities（System.Configuration.ConfigurationManager.ConnectionStrings [ test]。ToString（））;
BusinessLayer.university objuni = new BusinessLayer.university（System.Configuration.ConfigurationManager.ConnectionStrings [ test]。ToString（））;
受保护 void Page_Load（ object sender，EventArgs e）
{
if （！IsPostBack）
{
Fillstate（）;
}
}
public void Fillstate（ ）
{
DataTable dt = new DataTable（）;
dt = objstate.getdatastate（）;
ddlstate.DataSource = dt;
ddlstate.DataValueField = State_Id;
ddlstate.DataTextField = State_Name;
ddlstate.DataBind（）;
ddlstate.Items.Add（ new ListItem（ - 请选择 - ， - 1））;
ddlstate.SelectedValue = - 1;
int state_id = Convert.ToInt32（ddlstate.SelectedIndex）+1;
TextBox1.Text = state_id.ToString（）;
}
public void Fillcity（< span class =code-keyword> int state_id）
{
DataTable dt = new DataTable（）;
dt = objcity.getdatacity（state_id）;
ddlcity.DataSource = dt;
ddlcity.DataValueField = City_Id;
ddlcity.DataTextField = City_Name;
ddlcity.DataBind（）;
ddlcity.Items.Add（ new ListItem（ - 请选择 - ， - 1））;
ddlcity.SelectedValue = - 1;
}
public void Filluniversity（ int city_id）
{
DataTable dt = new DataTable（）;
dt = objuni.getdatauniversity（）;
ddluni.DataSource = dt;
ddluni.DataValueField = Uni_Id;
ddluni.DataTextField = Uni_Name;
ddluni.DataBind（）;
ddluni.Items.Add（ new ListItem（ - 请选择 - ， - 1））;
ddluni.SelectedValue = - 1;
}
受保护 void ddlstate_SelectedIndexChanged（< span class =code-keyword> object sender，EventArgs e）
{
int state_id = Convert.ToInt32（ddlstate .SelectedIndex）+1;
Fillcity（state_id）;
}
protected void ddlcity_SelectedIndexChanged（ object sender，EventArgs e）
{
int city_id = Convert.ToInt32（ddlcity.SelectedValue.ToString（））;
Filluniversity（city_id）;
}
BusinessLayer cities.cs
public class cities
{
DataLibrary DL;
public cities（ string dbconnectionstring）
{
dl = new DataLibrary（dbconnectionstring）;
}
public DataTable getdatacity（ int state_id）
{
DataTable dt = new DataTable（）;
dt = dl.GetDataTable（ 从City中选择City_Id，City_Name，其中State_Id = state_id ）;
return dt;
}}
datalibrary.cs
public class DataLibrary
{
public SqlConnection Sqlconn;
public SqlCommand sqlcmd;
public SqlDataAdapter SqlAdp;
public DataSet SqlDst;
public DataTable SqlDtTbl;
private string StrSqlconnection;
public DataLibrary（ String ConnectionString） // 构造函数声明
{
StrSqlconnection = ConnectionString;
}
public int DMLQuery（ string SqlCommandText）
{
Sqlconn = new SqlConnection（StrSqlconnection）;
Sqlconn.Open（）;
sqlcmd = new SqlCommand（）;
sqlcmd.CommandText = SqlCommandText;
sqlcmd.Connection = Sqlconn;
int i = sqlcmd.ExecuteNonQuery（）;
return i;
}
public DataSet GetDataSet（ string SqlCommandText）
{
SqlAdp = new SqlDataAdapter（SqlCommandText，StrSqlconnection）;
SqlDst = new DataSet（）;
SqlAdp.Fill（SqlDst）;
return SqlDst;
}
public DataTable GetDataTable（ string SqlCommandText）
{
SqlAdp = new SqlDataAdapter（SqlCommandText，StrSqlconnection）;
SqlDtTbl = new DataTable（）;
SqlAdp.Fill（SqlDtTbl）;
return SqlDtTbl;
}
}
页面加载我得到状态列表但是点击状态我没有得到城市..
您正在寻找级联下拉列表。尝试
http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/CascadingDropDown /CascadingDropDown.aspx [ ^ ]
将CascadingDropDown与数据库一起使用 [ ^ ]
页面加载我得到状态列表但是点击状态我没有得到城市..。
在这种情况下，您将状态值作为参数传递，然后只有您获得任何预期的解决方案..
for ex ：
选择 city_id，city_name 来自 city_table 其中 state_id = - 你在这里传递状态id ---
我同意Kishor你的数据库需要一个微妙的变化，但我完全不同意他的推理和方法。
您的数据是没有规范化，这是你的问题之一。我建议从初学者表中删除state_id。该栏目的存在仅仅是维护的噩梦，因为无论何时你为大学更新城市，你都必须更新状态以及对城邦关系的额外验证，以确保你的数据完整性。通过从大学表中删除state_id字段，可以删除整个问题。你的表应该是这样的：
[State]（
[ID] [ int ] IDENTITY （ 1 ， 1 ） NOT NULL ，
[名称] [ nvarchar ]（ 50 ） NOT NULL ）
[城市]（
[ID] [ int ] IDENTITY （ 1 ， 1 ） NOT NULL ，
[名称] [ nvarchar ]（ 50 ） NOT NULL ），
[StateID] [ int ] FK to [State]。[ID]
[大学]（
[ID] [< span class =code-keyword> int ] IDENTITY （ 1 ， 1 ） NOT NULL ，
[名称] [ nvarchar ]（ 50 ）不 NULL ），
[CityID] [ int ] FK to [City]。[ID]）
因为您想在WebForm上级联更新DropDownList控件，你可能想重温一下你是如何接近这个的。
你可以通过完成一个完整的回发来做到这一点，就像你正在尝试的那样但是，这是非常低效的，因为它导致整个表单回发到服务器，然后整个页面必须被重新呈现并在响应中发回。您在此方法中缺少的是每次收到请求时都必须将页面视为全新页面。例如，如果用户已选择状态并且您想要填充城市，则必须填充州和城市，然后在回发之前也设置所选的状态项。它听起来可能不是很多，但是你要做的数据和数据库的数量将是一个巨大的性能噩梦。
更好方法是为你的表单实现一个ajax或ajax / jquery泛型处理程序，这样你就可以请求你正在寻找的东西。
这里有一些关于如何以这种方式处理它的文章：
- ASP.NET Advanced Generic Handler ASHX [ ^ ]
- ASP.NET中的 jQuery AJAX和HttpHandlers [ ^ ]
- AJAX：使用Genric处理程序的ASP.NET WebForms中的简单方法 [ ^ ]
i have 3 tables
1. state(state_id,state_name) state_id PK,Identity specification,
2.city (city_id,city_name.state_id) city_id PK,Identity specification,
state_id FK
3.university(uni_id,uni_name,state_id,city_id) uni_id PK,Identity specification,, state_id FK , city_id FK
and took one form (university.aspx) with three labels and respective dropdownlist for state, city , university.......
what i want is when i click on state then only list of cities belonging to that state should come and same for university ...i tried following code
university.aspx.cs
BusinessLayer.State objstate = new BusinessLayer.State(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
BusinessLayer.cities objcity = new BusinessLayer.cities(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
BusinessLayer.university objuni = new BusinessLayer.university(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Fillstate();
}
}
public void Fillstate()
{
DataTable dt = new DataTable();
dt = objstate.getdatastate();
ddlstate.DataSource = dt;
ddlstate.DataValueField = "State_Id";
ddlstate.DataTextField = "State_Name";
ddlstate.DataBind();
ddlstate.Items.Add(new ListItem("- Please Select -", "-1"));
ddlstate.SelectedValue = "-1";
int state_id = Convert.ToInt32(ddlstate.SelectedIndex)+1;
TextBox1.Text = state_id.ToString();
}
public void Fillcity(int state_id)
{
DataTable dt = new DataTable();
dt = objcity.getdatacity(state_id);
ddlcity.DataSource = dt;
ddlcity.DataValueField ="City_Id";
ddlcity.DataTextField ="City_Name";
ddlcity.DataBind();
ddlcity.Items.Add(new ListItem("- Please Select -", "-1"));
ddlcity.SelectedValue ="-1";
}
public void Filluniversity(int city_id)
{
DataTable dt = new DataTable();
dt = objuni.getdatauniversity();
ddluni.DataSource = dt;
ddluni.DataValueField = "Uni_Id";
ddluni.DataTextField = "Uni_Name";
ddluni.DataBind();
ddluni.Items.Add(new ListItem("- Please Select -", "-1"));
ddluni.SelectedValue = "-1";
}
protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
{
int state_id = Convert.ToInt32(ddlstate.SelectedIndex)+1;
Fillcity(state_id);
}
protected void ddlcity_SelectedIndexChanged(object sender, EventArgs e)
{
int city_id = Convert.ToInt32(ddlcity.SelectedValue.ToString());
Filluniversity(city_id);
}
BusinessLayer cities.cs
public class cities
{
DataLibrary dl;
public cities(string dbconnectionstring)
{
dl=new DataLibrary(dbconnectionstring);
}
public DataTable getdatacity(int state_id)
{
DataTable dt = new DataTable();
dt = dl.GetDataTable("select City_Id,City_Name from City where State_Id=state_id");
return dt;
} }
datalibrary.cs
public class DataLibrary
{
public SqlConnection Sqlconn;
public SqlCommand sqlcmd;
public SqlDataAdapter SqlAdp;
public DataSet SqlDst;
public DataTable SqlDtTbl;
private string StrSqlconnection;
public DataLibrary(String ConnectionString) //Constructor declaration
{
StrSqlconnection = ConnectionString;
}
public int DMLQuery(string SqlCommandText)
{
Sqlconn = new SqlConnection(StrSqlconnection);
Sqlconn.Open();
sqlcmd = new SqlCommand();
sqlcmd.CommandText = SqlCommandText;
sqlcmd.Connection = Sqlconn;
int i = sqlcmd.ExecuteNonQuery();
return i;
}
public DataSet GetDataSet(string SqlCommandText)
{
SqlAdp = new SqlDataAdapter(SqlCommandText, StrSqlconnection);
SqlDst = new DataSet();
SqlAdp.Fill(SqlDst);
return SqlDst;
}
public DataTable GetDataTable(string SqlCommandText)
{
SqlAdp = new SqlDataAdapter(SqlCommandText, StrSqlconnection);
SqlDtTbl = new DataTable();
SqlAdp.Fill(SqlDtTbl);
return SqlDtTbl;
}
}
on page load i am getting list of states but on clicking states i am not getting cities..
You are looking for cascading dropdowns. Try
http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/CascadingDropDown/CascadingDropDown.aspx[^]
Using CascadingDropDown with a Database[^]
on page load i am getting list of states but on clicking states i am not getting cities.. .
in this case you pass the state value as a parameter then only you got the solution whatever you expect..
for ex :
select city_id,city_name from city_table where state_id=-- you pass the state id on here---
I agree with Kishor that your db needs a subtle change, but I totally disagree with him on the reasoning and method.
Your data is not normalized and that is one of your issues. I would suggest removing the state_id from the university table for starters. That column''s presence only serves as a maintenance nightmare because anytime you update the city for a university, you''ll have to update state as well along with an extra validation against the city-state relationship to ensure your data integrity. By removing the state_id field from the university table, you remove this entire issue. Your tables should look something like this:
[State]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL ) [City]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL ), [StateID] [int] FK to [State].[ID] [University]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL ), [CityID] [int] FK to [City].[ID] )
Because you want to cascade update DropDownList controls on your WebForm, you may want to revisit how you are approaching this as well.
You could do it by just doing a complete postback like you are trying right now, but that is extremely inefficient as it causes the entire form to postback to the server and then the entire page has to be rerendered and sent back in the Response. What you are missing in this approach is that the page must be treated as a brand new page every time you receive a request. For example, if the user has selected the State and you want to populate Cities, you will have to populate both State and Cities and then also set the selected item of State as well before posting back. It may not sound like much, but the amount of data and hits to the database that you are going to have to make will be a huge performance nightmare.
A better approach is to implement an ajax or ajax/jquery generic handler to your form so that you can request just what you are looking for.
Here are some articles on how to approach it this way:
- ASP.NET Advanced Generic Handler ASHX[^]
- jQuery AJAX and HttpHandlers in ASP.NET[^]
- AJAX: The Easy Way In ASP.NET WebForms Using Genric Handlers[^]
这篇关于如何从不同表中的数据库填充dropdowlist中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
