ASP.Net 嵌套中继器逻辑 hell

Question

寻求有关我遇到的三重嵌套中继器逻辑问题的帮助。

背景
我正在构建一个时间表系统，用于预订房间和从 SQL Server 数据库中提取数据的资源。

表结构

Table 1 - tblrooms  
room_id (INT) PK  
room_name (varchar(50))  
room_resource (INT)  

Table 2 - tblperiods  
period_id (INT) PK  
period_time_start (DATETIME)  
period_time_end (DATETIME)  
period_name (nvarchar(50))  

Table 3 - tblbookings  
booking_id (INT) PK  
period_id (INT)  
room_id (INT)  
booking_status (INT)  
booking_date (DATETIME)  
booking_subject (nvarchar(50))

问题
在大多数情况下，我让中继器根据需要提取数据，但是只有第一列被填充，我真的很难弄清楚如何提取预订数据并根据需要构建时间表。 (请参阅我已经开始工作的附加截图)

ASPX 页面代码

<asp:Repeater ID="drPeriods" runat="server" OnItemDataBound="drPeriods_OnItemDataBound">

        <HeaderTemplate>
        <table class="table table-striped table-bordered table-condensed">
            <tr>
                <th style="width:16.66%"><asp:Label ID="lblResourceHeader" runat="server" /></th>
        </HeaderTemplate>

         <ItemTemplate>

                <th style="width:16.66%"><asp:Label ID="lblPeriod" runat="server" Text='<%# Eval("period_name") %>' /> - <asp:Label ID="lblPeriodStart" runat="server" Text='<%# Eval("period_time_start") %>' /> to <asp:Label ID="lblPeriodEnd" runat="server" Text='<%# Eval("period_time_end") %>' /></th>

         </ItemTemplate>

         <FooterTemplate>
             </tr> 

             <asp:Repeater ID="drResources" runat="server" OnItemDataBound="drResources_OnItemDataBound">

                    <ItemTemplate>

                    <tr>
                        <td height="50">
                            <asp:Label ID="lblResource" runat="server" Text='<%# Eval("room_name") %>' />
                            <br /><asp:Label ID="lblResourceDetails" runat="server" />
                        </td>
                        <asp:Label ID="lblFreeBooking" runat="server" Visible="false" />

                        <asp:Repeater ID="drBookings" runat="server" OnItemDataBound="drBookings_OnItemDataBound">

                            <ItemTemplate>

                                <td height="50">
                                    <asp:Label ID="lblCellContent" runat="server" />
                                </td>

                            </ItemTemplate>

                        </asp:Repeater>

                    </tr>

                    </ItemTemplate>

                </asp:Repeater>

            </table>

         </FooterTemplate>

     </asp:Repeater>

后面的代码

Namespace Staff

Public Class Rb
    Inherits System.Web.UI.Page
    Private _periodId As Integer


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then

            txtDate.Text = Request.QueryString("d")

            'check for weekend dates, show message if it is.
            Dim iWeekday As Integer = Weekday(Request.QueryString("d"))

            If iWeekday = 1 Or iWeekday = 7 Then

                lblMsg.Text = "<div class='alert alert-info alert-block'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;It's the weekend.</h4><p>The date you have choosen is a weekend, resources cannot be booked on weekends.</p></div>"
                lblMsg.Visible = True
                drPeriods.Visible = False

            Else

                Dim objConnection As SqlConnection
                Dim objCommand As SqlCommand
                Dim objDataReader As SqlDataReader

                objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OPSDConnectionString").ConnectionString)

                'Get Periods
                objCommand = New SqlCommand("SELECT period_id, CONVERT(char(5), period_time_start, 108) AS period_time_start, CONVERT(char(5), period_time_end, 108) AS period_time_end, period_name FROM tblrb_periods", objConnection)

                Try
                    objConnection.Open()
                    objDataReader = objCommand.ExecuteReader()

                    If objDataReader.HasRows Then

                        drPeriods.DataSource = objDataReader
                        drPeriods.DataBind()
                        objDataReader.Close()

                    Else

                        drPeriods.Visible = False
                        lblMsg.Text = "<div class='alert alert-error alert-block'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;Error!</h4><p>Periods have not yet been setup, please set these up by selecting the periods tab above, if you cannot see this tab please ask the helpdesk administrator to set these up for you.</p></div>"
                        lblMsg.Visible = True
                        objDataReader.Close()

                    End If

                Catch ex As Exception

                    'Inform of the error
                    Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

                Finally
                    objCommand.Dispose()
                    objConnection.Close()
                    objConnection.Dispose()

                End Try

            End If

        End If

    End Sub

    Protected Sub drPeriods_OnItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles drPeriods.ItemDataBound

        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then

            _periodId = e.Item.DataItem("period_id")

        End If

        If e.Item.ItemType = ListItemType.Header Then

            Dim lblResourceHeader As Label = drPeriods.Controls(0).Controls(0).FindControl("lblResourceHeader")
            Dim layoutView As Integer = Request.QueryString("v")
            Select Case layoutView
                Case 1 ' Rooms
                    lblResourceHeader.Text = "Rooms"
                Case 2 ' Resources
                    lblResourceHeader.Text = "Resources"
                Case 3 ' Both
                    lblResourceHeader.Text = "Rooms &amp; Resources"
                Case Else
                    lblResourceHeader.Text = "Rooms &amp; Resources"
            End Select

        End If

        If e.Item.ItemType = ListItemType.Footer Then

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim objDataReader As SqlDataReader

            'find repeater in the footer of drPeriods (repeater)
            Dim drResources As Repeater = drPeriods.Controls(drPeriods.Controls.Count - 1).Controls(0).FindControl("drResources")
            Dim layoutView As Integer = Request.QueryString("v")

            'Get Rooms
            objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OPSDConnectionString").ConnectionString)

            Select Case layoutView
                Case 1 ' Rooms
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms WHERE room_resource = 1 ORDER BY room_name", objConnection)
                Case 2 ' Resources
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms WHERE room_resource = 2 ORDER BY room_name", objConnection)
                Case 3 ' Both
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms ORDER BY room_name", objConnection)
                Case Else
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms ORDER BY room_name", objConnection)
            End Select

            Try
                objConnection.Open()
                objDataReader = objCommand.ExecuteReader()

                drResources.DataSource = objDataReader
                drResources.DataBind()
                objDataReader.Close()

            Catch ex As Exception

                'Inform of the error
                Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

            Finally
                objCommand.Dispose()
                objConnection.Close()
                objConnection.Dispose()

            End Try

        End If

    End Sub

    Protected Sub drResources_OnItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs)

        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then

            Dim lblResourceDetails As Label = e.Item.FindControl("lblResourceDetails")
            If e.Item.DataItem("room_resource") <> 2 Then
                lblResourceDetails.Text = "[ <a href='#' class='withajaxpopover' title='Room Details' data-load='resourceManagerViewDetails.aspx?id=" & e.Item.DataItem("room_id") & "'>View Room Details</a> ]"
            End If

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim objDataReader As SqlDataReader
            Dim drBookings As Repeater = e.Item.FindControl("drBookings")
            Dim lblFreeBooking As Label = e.Item.FindControl("lblFreeBooking")

            'Get Bookings
            objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OPSDConnectionString").ConnectionString)
            objCommand = New SqlCommand("SELECT tblrb_bookings.booking_status, tblrb_bookings.booking_subject, tblrb_bookings.booking_notes FROM tblrb_bookings WHERE (tblrb_bookings.room_id = @room_id) AND (tblrb_bookings.booking_date = @booking_date) AND (tblrb_bookings.period_id = @period_id)", objConnection)

            objCommand.Parameters.Add("@room_id", SqlDbType.Int, 10).Value = e.Item.DataItem("room_id")
            objCommand.Parameters.Add("@period_id", SqlDbType.Int, 10).Value = _periodId
            objCommand.Parameters.Add("@booking_date", SqlDbType.DateTime).Value = Request.QueryString("d")

            Try
                objConnection.Open()
                objDataReader = objCommand.ExecuteReader()

                If objDataReader.HasRows Then

                    drBookings.DataSource = objDataReader
                    drBookings.DataBind()
                    objDataReader.Close()

                Else

                    'free period
                    lblFreeBooking.Text = "<td height='50'><div class='alert alert-block alert-success'><h4 class='alert-heading'><i class='icon-ok'></i>&nbsp;Free.</h4><p><a href='#'>Click here to book this resource.</a></p></div></td>"
                    lblFreeBooking.Visible = True

                End If

            Catch ex As Exception

                'Inform of the error
                Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

            Finally
                objCommand.Dispose()
                objConnection.Close()
                objConnection.Dispose()

            End Try

        End If

    End Sub

    Protected Sub drBookings_OnItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs)

        Dim lblCellContent As Label = e.Item.FindControl("lblCellContent")

        Select Case e.Item.DataItem("booking_status")
            Case 1
                'timetabled
                lblCellContent.Text = "<div class='alert alert-block alert-error'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;Timetabled.</h4><p>" & e.Item.DataItem("booking_subject") & "</p></div>"

            Case 2
                'user booked
                lblCellContent.Text = "<div class='alert alert-block'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;Booked.</h4><p>" & e.Item.DataItem("booking_subject") & ".</p></div>"

        End Select

    End Sub


    Protected Sub btnDateSelect_Click(sender As Object, e As System.EventArgs) Handles btnDateSelect.Click

        'quick reload of page
        Response.Redirect("resourceManager.aspx?v=" & Request.QueryString("v") & "&d=" & txtDate.Text)

    End Sub

End Class End Namespace

任何人都可以帮助我如何获得所需的布局，以便在构建时间表时，房间和时段包含相应列中每个单元格的正确预订数据。

提前感谢您提供的任何指示，我花了 3 天时间在这上面，我可以用一双新鲜的眼睛来做 :-)

问候，

奥齐

Answer 1

首先，我认为您进行控制的方式过于复杂。如果您要使用 LINQ 或 EF 进行数据库访问，则可以使用 2 个嵌套中继器来执行此操作，并且不需要复杂的代码。但是，您可以首先将查询简化为以下内容:

SELECT *
FROM tblperiods p
    CROSS JOIN tblrooms r
    LEFT JOIN tblbookings b ON p.period_id = b.period_id AND r.room_id = b.room_id AND b.booking_date = '2012-08-01'

这将为您提供足够的信息，以便在给定日期的单个查询中生成整个输出。如果您不想使用 EF 或 LINQ-to-SQL，您仍然可以使用一些 LINQ 魔术来进一步按房间将其分开，然后您可以将其用于数据绑定(bind)(请原谅​​ C#，VB.NET 是有点生锈!):

DataTable dt = new DataTable();
dt.Fill(objDataReader);          // Loads all data into the DataTable
var groupedRows = dt.Rows.Cast<DataRow>().GroupBy(row => new { RoomId = (int) row["room_id"], RoomName = (string) row["room_name"] });
rpRows.DataSource = groupedRows;
rpRows.DataBind();

然后你会有这样的中继器:

<table>
<thead><tr><th>Rooms &amp; Resources</th>
<asp:Repeater runat="server" id="rpHeader">
    <ItemTemplate>
        <td><%# Eval("period_name") %></td>
    </ItemTemplate>
</asp:Repeater>
</tr></thead>
<asp:Repeater runat="server" id="rpRows">
    <ItemTemplate>
        <tr>
            <th><!-- Put room header stuff here --><%# Eval("Key.RoomName") %></th>
            <asp:Repeater runat="server" DataSource="<%# Container.DataItem %>">
                <ItemTemplate>
                    <td>
                         <!-- Per-booking info -->
                         <asp:Label runat="server" Visible='<%# Eval("booking_id") == DBNull.Value %>'>Not Booked</asp:label>
                         <asp:Label runat="server" Visible='<%# Eval("booking_id") != DBNull.Value %>'>Booked!</asp:label>
                    </td>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
    </ItemTemplate>
</asp:Repeater>
</table>

我没有给出数据绑定(bind)标题行的代码，但这只是一个 select * from tbl_periods
请注意，这些代码都没有经过测试，可能需要对数据绑定(bind)进行一些调整。