ASP.NET Tutorial/Data Binding/DropDownList
Содержание
- 1 Binding ArrayList to asp:DropDownList
- 2 Binding to a Data Source
- 3 Data binding by filling list and drop-down list controls with the results of direct ADO.NET queries
- 4 Browse Customers
- 5 Determining the Selected List Item: SelectedIndex, SelectedItem, SelectedValue
- 6 Enabling Automatic PostBacks
- 7 Link DropDownList with SqlDataSource and do the editing
- 8 Master/Details form with a list control
- 9 Prevent overlapping edits.
- 10 Repeater and DropDownList
- 11 We have customers in the following cities
- 12 Table record editor by DropDownList and ListBox
Binding ArrayList to asp:DropDownList
<source lang="csharp">
<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
Sub Page_Load()
if Not Page.IsPostBack Then
Dim items As New ArrayList()
items.Add("A")
items.Add("B")
DropDownList1.DataSource = items
DropDownList1.DataBind()
End If
End Sub
Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Label1.Text = DropDownList1.SelectedItem.Text
End Sub
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show IsPostBack</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList
id="DropDownList1"
Runat="server" />
<asp:Button
id="Button1"
Text="Select"
OnClick="Button1_Click"
Runat="server" />
You selected:
<asp:Label
id="Label1"
Runat="server" />
</form>
</body> </html></source>
Binding to a Data Source
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Declarative DataBinding</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList
id="ddlProducts"
DataSourceID="srcProducts"
DataTextField="Title"
DataValueField="Id"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
SelectCommand="SELECT Id, Title FROM Products"
ConnectionString="<%$ ConnectionStrings:Products %>"
Runat="server" />
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings>
<add name="Products"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration></source>
Data binding by filling list and drop-down list controls with the results of direct ADO.NET queries
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>DataSource-based Binding</title>
</head> <body>
<form id="form1" runat="server">
Browse Customers
<asp:ListBox ID="CustomerList" runat="server" Height="200px" Width="280px" /> |
<asp:DropDownList runat="server" ID="CountryList" AppendDataBoundItems="True" Width="130px">
<asp:ListItem Text="[All]" />
</asp:DropDownList>
|
<asp:Button ID="CustomersButton" runat="server" Text="Get customers..."
OnClick="CustomersButton_Click" />
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.UI; using System.Web.UI.WebControls;
public partial class Default : System.Web.UI.Page {
protected void CountriesButton_Click(object sender, EventArgs e)
{
if (CountryList.Items.Count > 1)
return;
string connString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
string cmdText = "SELECT DISTINCT country FROM customers";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
CountryList.DataSource = reader;
CountryList.DataTextField = "country";
CountryList.DataBind();
}
}
}
protected void CustomersButton_Click(object sender, EventArgs e)
{
CustomerList.Items.Clear();
string connString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
string cmdText1 = "SELECT CustomerID, CompanyName, Country FROM customers";
string cmdText2 = "SELECT CustomerID, CompanyName, Country FROM customers WHERE country="{0}"";
string cmdText = cmdText1;
if (CountryList.SelectedIndex > 0)
cmdText = String.Format(cmdText2, CountryList.SelectedValue);
DataSet data = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connString);
adapter.Fill(data);
CustomerList.DataMember = "Table";
CustomerList.DataTextField = "companyname";
CustomerList.DataValueField = "customerid";
CustomerList.DataSource = data;
CustomerList.DataBind();
}
}</source>
Determining the Selected List Item: SelectedIndex, SelectedItem, SelectedValue
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void btnSelect_Click(object sender, EventArgs e)
{
lblSelectedProduct.Text = ddlProducts.SelectedItem.Text;
}
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Select Product</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList
id="ddlProducts"
DataSourceID="srcProducts"
DataTextField="Title"
DataValueField="Id"
Runat="server" />
<asp:Button
id="btnSelect"
Text="Select"
OnClick="btnSelect_Click"
Runat="server" />
<asp:Label
id="lblSelectedProduct"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
SelectCommand="SELECT Id, Title FROM Products"
ConnectionString="<%$ ConnectionStrings:Products %>"
Runat="server" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings>
<add name="Products"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration></source>
Enabling Automatic PostBacks
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void ddlProducts_SelectedIndexChanged(object sender, EventArgs e)
{
lblSelectedProduct.Text = ddlProducts.SelectedItem.Text;
}
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>AutoPostBack List Control</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList
id="ddlProducts"
DataSourceID="srcProducts"
DataTextField="Title"
DataValueField="Id"
AutoPostBack="true"
OnSelectedIndexChanged="ddlProducts_SelectedIndexChanged"
Runat="server" />
<asp:Label
id="lblSelectedProduct"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
SelectCommand="SELECT Id, Title FROM Products"
ConnectionString="<%$ ConnectionStrings:Products %>"
Runat="server" />
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings>
<add name="Products"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration></source>
Link DropDownList with SqlDataSource and do the editing
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true"%> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Record Editor</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList ID="lstProduct"
runat="server"
AutoPostBack="True"
Width="280px"
DataSourceID="sourceProducts"
DataTextField="ProductName"
DataValueField="ProductID">
</asp:DropDownList>
<asp:DetailsView ID="DetailsView1"
runat="server"
DataSourceID="sourceProductDetails"
Height="50px"
Width="200px"
AutoGenerateEditButton="True">
</asp:DetailsView>
|
<asp:SqlDataSource ID="sourceProducts"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductName, ProductID FROM Products"/>
<asp:SqlDataSource ID="sourceProductDetails"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductID=@ProductID"
UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=CONVERT(money, @UnitPrice), UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued WHERE ProductID=@ProductID">
<SelectParameters>
<asp:ControlParameter ControlID="lstProduct" Name="ProductID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblInfo" runat="server" EnableViewState="False" Font-Bold="True" ForeColor="#C00000"></asp:Label>
</form>
</body> </html>
File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/> </connectionStrings>
</configuration></source>
Master/Details form with a list control
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:DropDownList
id="ddlProductCategory"
DataSourceID="srcProductCategories"
DataTextField="Name"
DataValueField="Id"
Runat="server" />
<asp:Button
id="btnSelect"
Text="Select"
Runat="server" />
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
CssClass="gridView"
Runat="server" />
<asp:SqlDataSource
id="srcProductCategories"
SelectCommand="SELECT Id, Name FROM ProductCategories"
ConnectionString="<%$ ConnectionStrings:Products %>"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
SelectCommand="SELECT Title,Director FROM Products
WHERE CategoryId=@Id"
ConnectionString="<%$ ConnectionStrings:Products %>"
Runat="server">
<SelectParameters>
<asp:ControlParameter
Name="Id"
ControlID="ddlProductCategory"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings>
<add name="Products"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration></source>
Prevent overlapping edits.
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="MyPage" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Record Editor</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList ID="lstProduct"
runat="server"
AutoPostBack="True"
Width="300px"
DataSourceID="sourceProducts"
DataTextField="ProductName"
DataValueField="ProductID">
</asp:DropDownList>
<asp:DetailsView ID="DetailsView1"
runat="server"
DataSourceID="sourceProductDetails"
Height="50px"
Width="200px"
AutoGenerateEditButton="True">
</asp:DetailsView>
|
<asp:SqlDataSource ID="sourceProducts"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductName, ProductID FROM Products"/>
<asp:SqlDataSource ID="sourceProductDetails"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductID=@ProductID"
UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice, UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued WHERE ProductID=@ProductID AND ProductName=@original_ProductName AND UnitPrice=@original_UnitPrice AND UnitsInStock=@original_UnitsInStock AND UnitsOnOrder=@original_UnitsOnOrder AND ReorderLevel=@original_ReorderLevel AND Discontinued=@original_Discontinued"
OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues" OnUpdated="sourceProductDetails_Updated">
<SelectParameters>
<asp:ControlParameter ControlID="lstProduct" Name="ProductID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblInfo" runat="server" EnableViewState="False"></asp:Label>
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class MyPage : System.Web.UI.Page {
protected void sourceProductDetails_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows == 0)
{
lblInfo.Text = "No update was performed. A concurrency error is likely, or the command is incorrectly written.";
}
else
{
lblInfo.Text = "Record successfully updated.";
}
}
}
File: Web.config
<?xml version="1.0"?> <configuration>
<connectionStrings> <add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/> </connectionStrings>
</configuration></source>
Repeater and DropDownList
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Repeater in action</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList ID="Countries" runat="server"
AutoPostBack="true"
AppendDataBoundItems="True"
OnSelectedIndexChanged="Countries_SelectedIndexChanged">
<asp:ListItem>[No country]</asp:ListItem>
</asp:DropDownList>
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
We have customers in the following cities
</HeaderTemplate>
<SeparatorTemplate>
</SeparatorTemplate>
<ItemTemplate>
<%# Eval("City") %> <%# Eval("Country")%>
</ItemTemplate>
<FooterTemplate>
<%# CalcTotal() %> cities
</FooterTemplate>
</asp:Repeater>
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls;
public partial class Default : System.Web.UI.Page {
DataTable data;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillCountries();
}
}
protected void FillCountries()
{
string connString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
string cmdText = "SELECT DISTINCT country FROM customers;";
data = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connString);
adapter.Fill(data);
Countries.DataSource = data;
Countries.DataTextField = "country";
Countries.DataBind();
}
protected int CalcTotal()
{
return data.Rows.Count;
}
protected void Countries_SelectedIndexChanged(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
string cmdText = "SELECT DISTINCT country, city FROM customers WHERE country=@TheCountry";
data = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connString);
adapter.SelectCommand.Parameters.AddWithValue("@TheCountry", Countries.SelectedValue);
adapter.Fill(data);
Repeater1.DataSource = data;
Repeater1.DataBind();
}
}</source>
Table record editor by DropDownList and ListBox
<source lang="csharp">
File: index.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="RecordEditor" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Record Editor</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList ID="lstProduct"
runat="server"
AutoPostBack="True"
Width="280px"
OnSelectedIndexChanged="lstProduct_SelectedIndexChanged">
</asp:DropDownList>
| <asp:Label ID="lblRecordInfo" runat="server"></asp:Label> |
<asp:Panel ID="pnlCategory" runat="server" Visible="False">
<asp:ListBox ID="lstCategory"
runat="server"
Height="120px"
Width="152px">
</asp:ListBox>
<asp:Button ID="cmdUpdate"
runat="server"
Text="Update"
OnClick="cmdUpdate_Click" />
</asp:Panel>
|
</form>
</body> </html>
File: Default.aspx.cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Web.Configuration; using System.Data.SqlClient; public partial class RecordEditor : System.Web.UI.Page {
private string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string selectSQL = "SELECT ProductName, ProductID FROM Products";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
con.Open();
lstProduct.DataSource = cmd.ExecuteReader();
lstProduct.DataTextField = "ProductName";
lstProduct.DataValueField = "ProductID";
lstProduct.DataBind();
con.Close();
lstProduct.SelectedIndex = -1;
}
}
protected void lstProduct_SelectedIndexChanged(object sender, EventArgs e)
{
string selectProduct = "SELECT ProductName, QuantityPerUnit, " +
"CategoryName FROM Products INNER JOIN Categories ON " +
"Categories.CategoryID=Products.CategoryID " +
"WHERE ProductID=@ProductID";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmdProducts = new SqlCommand(selectProduct, con);
cmdProducts.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);
using (con)
{
con.Open();
SqlDataReader reader = cmdProducts.ExecuteReader();
reader.Read();
lblRecordInfo.Text = "Product: " + reader["ProductName"] + "
";
lblRecordInfo.Text += "Quantity: " + reader["QuantityPerUnit"] + "
";
lblRecordInfo.Text += "Category: " + reader["CategoryName"];
string matchCategory = reader["CategoryName"].ToString();
reader.Close();
string selectCategory = "SELECT CategoryName, CategoryID FROM Categories";
SqlCommand cmdCategories = new SqlCommand(selectCategory, con);
lstCategory.DataSource = cmdCategories.ExecuteReader();
lstCategory.DataTextField = "CategoryName";
lstCategory.DataValueField = "CategoryID";
lstCategory.DataBind();
lstCategory.Items.FindByText(matchCategory).Selected = true;
}
pnlCategory.Visible = true;
}
protected void cmdUpdate_Click(object sender, EventArgs e)
{
string updateCommand = "UPDATE Products SET CategoryID=@CategoryID WHERE ProductID=@ProductID";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateCommand, con);
cmd.Parameters.AddWithValue("@CategoryID", lstCategory.SelectedItem.Value);
cmd.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
}
}
} File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/> </connectionStrings>
</configuration></source>
