在很多情况下,我们经常要显示几张关联表的数据.
下面的是采用表关联:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>DataList嵌套DataList的使用</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Button ID="Button1" runat="server" Text="交 卷" OnClick="Button1_Click" />
<!--父DataList开始-->
<asp:DataList ID="ExamTest" runat="server">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem, "SubName")%>
<br>
<!--子DataList开始-->
<asp:DataList runat="server" ID="SubInfo" DataSource='<%#((DataRowView)Container.DataItem).Row.GetChildRows("myrelation") %>'>
<ItemTemplate>
<% #DataBinder.Eval(Container.DataItem,"[\"Sub\"]") %>
<br>
<asp:TextBox runat="server" ID="Answer" Text='<%#DataBinder.Eval(Container.DataItem,"[\"Answer\"]") %>'></asp:TextBox>
<asp:Label ID="subID" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"[\"ID\"]") %>' Visible="false"></asp:Label>
</ItemTemplate>
</asp:DataList>
<!--子DataList结束-->
</ItemTemplate>
</asp:DataList>
<!--父DataList结束-->
</form>
</body>
</html>
protected void Page_Load(object sender, EventArgs e)
{
if (!this.Page.IsPostBack)
{
GridViewBD();
}
}
protected void GridViewBD()
{
string SqlConn = ConfigurationManager.AppSettings["SqlConn"];
SqlConnection conn = new SqlConnection(SqlConn);
SqlDataAdapter dataAda = new SqlDataAdapter("select * from ExamInfo", conn);
DataSet dset = new DataSet();
conn.Open();
//读取ExamInfo表,填充父表
dataAda.Fill(dset, "ExamInfo");
SqlDataAdapter dataAda01 = new SqlDataAdapter("select * from PageInfo", conn);
//读取PageInfo表,填充子表
dataAda01.Fill(dset, "PageSub");
conn.Close();
//创建父表和子表关系
dset.Relations.Add("myrelation", dset.Tables["ExamInfo"].Columns["ID"], dset.Tables["PageSub"].Columns["PageID"]);
//绑定ExamInfo表到父DataList
ExamTest.DataSource = dset.Tables["ExamInfo"];
ExamTest.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
string SqlConn = ConfigurationManager.AppSettings["SqlConn"];
SqlConnection conn = new SqlConnection(SqlConn);
SqlCommand cmd = new SqlCommand("Update PageInfo Set Answer=@answer where ID=@ID", conn);
conn.Open();
foreach (DataListItem item in ExamTest.Items)
{
DataList subInfo = (DataList)item.FindControl("SubInfo");
foreach(DataListItem childItem in subInfo.Items)
{
cmd.Parameters.Clear();
string str = ((TextBox)childItem.FindControl("Answer")).Text.ToString();
int subID = Convert.ToInt32( ((Label)childItem.FindControl("subID")).Text.Trim() );
cmd.Parameters.Add("@answer",SqlDbType.VarChar,50).Value=str;
cmd.Parameters.Add("@ID",SqlDbType.Int).Value=subID;
cmd.ExecuteNonQuery();
}
}
conn.Close();
//刷新页面，重新绑定数据
GridViewBD();
}
下面更灵活,采用程序生成数据源:
<asp:Repeater ID="Repeater_city" runat="server">
<ItemTemplate>
<TR>
<TD height="25"><%# Eval("城市名称")%> Hotels </TD>
<TD><p align="left">
<asp:DataList ID="DataList1" DataSource='<%# bind_hotelname(Eval("城市名称").ToString()) %>' RepeatColumns=2 runat="server" RepeatDirection="Horizontal" Width="100%">
<ItemTemplate>
<a href="../hotel/<%# Eval("[\"静态文件名\"]") %>.htm" target="_blank"> <%# Eval("[\"酒店名称\"]")%></a>
</ItemTemplate>
<ItemStyle Width="50%" />
</asp:DataList>
</p></TD>
</TR>
</ItemTemplate>
</asp:Repeater>
public DataView bind_hotelname(string cityname)
{
string strSQL = "select [酒店].[酒店名称],[酒店].[静态文件名] from [酒店] inner join [城市] on [酒店].[城市]=[城市].[城市名称] where [城市].[城市名称]='" + cityname + "'";
DataTable t = Luo.Base.ExecuteSql4Ds(strSQL).Tables[0];
return t.DefaultView;
}