在表格中填充嵌套中继器

Question

我在另一个中继器中有一个中继器。如何在内部中继器中显示数据库中的所有字段（姓氏）。在函数outerRepeater_ItemDataBound中，DataSource不被识别。我不知道在哪里我需要为一个特定的名字选择所有的姓氏。

我的中继器里有这个。ascx:

<form id="form1" runat="server">
    <div>
        <asp:Repeater runat="server" ID="outerRepeater" OnItemDataBound="outerRepeater_ItemDataBound">
            <HeaderTemplate>
                <table>
                 <td><th>Name:</th></td>
                 <td><th>Surname:</th></td>
                </table>
            </HeaderTemplate>
            <ItemTemplate>
                    <h3><%#DataBinder.Eval(Container.DataItem,"Name")%></h3> 
                <asp:Repeater runat="server" ID="innerRepeater" >
                    <ItemTemplate>
                        <%#Eval("Surname") %>
                    </ItemTemplate>
                </asp:Repeater>
            </ItemTemplate>
        </asp:Repeater>
    </div>
</form>

这个在repeater.ascx.cs

public partial class Repeater : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            outerRepeater.DataSource = GetDataSource();
            outerRepeater.DataBind();
        }
    private DataTable GetDataSource()
    {
        SqlConnection myConnection = new SqlConnection(@"my connection string");
        SqlDataAdapter myCommand = new SqlDataAdapter("SELECT Distinct Name FROM Person ORDER BY Name ", myConnection);
        DataTable dt = new DataTable();
        myCommand.Fill(dt);
        myConnection.Close();
        return dt;
    }
    private void Page_Init(object sender, EventArgs e)
    {
        InitializeComponent();
    }
    private void InitializeComponent()
    {
        this.Load += new System.EventHandler(this.Page_Load);
    }
    protected void outerRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
           if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
           {
               Repeater innerRepeater = e.Item.FindControl("innerRepeater") as Repeater;
               DataTable dt = GetSurname();
               innerRepeater.DataSource = dt;//here isn't su
               innerRepeater.DataBind();
           }
    }
    private DataTable GetSurname()
    {
        SqlConnection myConnection = new SqlConnection(@"my connection string");
        SqlDataAdapter da = new SqlDataAdapter("Select Surname from Person ", myConnection);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

Answer 1

1。手动添加`Page_Load`处理程序

我不确定您是否真的需要在代码后面设置Page_Load事件处理程序。使用默认页面设置，它将自动连接。

只有当PagesSection.AutoEventWireup设置为false时，才需要手动设置。

2.带中继器的桌子

可以用repeater创建一个表-如何在ASP.Net repeater中创建一个三列表，但它需要一个不同的标记和正确设置的表标签：

<asp:Repeater runat="server" ID="outerRepeater" OnItemDataBound="outerRepeater_ItemDataBound">
    <HeaderTemplate>
        <table>
            <thead>
                 <th>Name:</th>
                 <th>Surname:</th>
            </thead>
            <tbody>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <h3><%#DataBinder.Eval(Container.DataItem,"Name")%></h3> 
            </td>
            <td>
                <asp:Repeater runat="server" ID="innerRepeater" >
                    <ItemTemplate>
                        <%#Eval("Surname") %>
                    </ItemTemplate>
                </asp:Repeater>
            </td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>   
            </tbody>             
        </table>
    </FooterTemplate>
</asp:Repeater>

而且，GridView也许是手动创建表的更好替代方案。

3.获取给定名称的所有姓氏

很明显，您会得到所有姓氏，因为Select Surname from People不过滤任何内容，它只选择所有项目。要实现您想要的，您必须添加一些WHERE子句，该子句根据与当前行关联的名称进行筛选。Name可以通过outerRepeater_ItemDataBound处理程序中的e.Item.DataItem获得。

protected void outerRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
    {
        Repeater innerRepeater = e.Item.FindControl("innerRepeater") as Repeater;
        String name = ((DataRowView)e.Item.DataItem)[0].ToString();
        DataTable dt = GetSurname(name);
        innerRepeater.DataSource = dt;//here isn't su
        innerRepeater.DataBind();
    }
}
private DataTable GetSurname(String name)
{
    using (SqlConnection myConnection = new SqlConnection(@"Data Source=(LocalDb)v11.0; Database = TSQL2012"))
    {
        using (SqlDataAdapter da = new SqlDataAdapter("Select Surname from Person WHERE Name = @name", myConnection))
        {
            da.SelectCommand.Parameters.AddWithValue("@name", name);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}

4.不总是处理连接

当您在连接上调用Close方法时，最好像以前的代码示例中那样使用using子句，因为using将保证无论发生什么，连接都会被关闭。这同样适用于SqlDataAdapter，但我不知道它是真的需要还是只是一个好的经验法则。

5.多个SQL请求

多个SQL请求，尤其是当所有所需信息都直接相关时，可能会导致性能非常差。您可以获取所有的名称和姓氏以在页面中处理它们，也可以应用连接/聚合字符串的最佳方式来获取具有所有所需数据的单个数据表：

aspx:

<asp:Repeater runat="server" ID="outerRepeater">
...
<ItemTemplate>
    <tr >
        <td>
            <h3><%#DataBinder.Eval(Container.DataItem,"Name")%></h3> 
        </td>
        <td>
            <label><%#DataBinder.Eval(Container.DataItem,"ConcatenatedSurnames")%></label>
        </td>
    </tr>
</ItemTemplate>

代码背后：

private const String SelectCommand = @"
WITH Partitioned AS
(
SELECT Name,
    Surname,
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS NameNumber,
    COUNT(*) OVER  (PARTITION BY Name ORDER BY Name) AS NameCount
FROM Person
),
Concatenated AS
(
SELECT Name,
    CAST(Surname AS nvarchar) AS ConcatenatedSurnames, 
    NameNumber, 
    NameCount 
FROM Partitioned 
WHERE NameNumber = 1
UNION ALL
SELECT 
    P.Name, 
    CAST(C.ConcatenatedSurnames + ', ' + P.Surname AS nvarchar), 
    P.NameNumber, 
    P.NameCount
FROM Partitioned AS P
    INNER JOIN Concatenated AS C 
    ON P.Name = C.Name AND 
        P.NameNumber = C.NameNumber + 1
)
SELECT 
Name,
ConcatenatedSurnames
FROM Concatenated
WHERE NameNumber = NameCount";
private DataTable GetDataSource()
{
    using (SqlConnection myConnection = new SqlConnection(@"Data Source=(LocalDb)v11.0; Database = TSQL2012"))
    {
        using (SqlDataAdapter myCommand = new SqlDataAdapter(SelectCommand, myConnection))
        {
            DataTable dt = new DataTable();
            myCommand.Fill(dt);
            return dt;
        }
    }
}