动态列的 GridView 排序和数据源范围问题

Question

先阅读 我重写了这个以使其更具可读性。如果您想直接跳到后面的内容，请查看代码块中的全部大写注释。已包含用于调试的所有必要代码。

我搜索了多个论坛（包括 ASP.NET）和 MSDN 库，但无法修复此问题 >。<

目标：根据用户指定的列动态生成包含多达 20 个以上的表格/网格状“状态报告”（考虑因素：应该能够存储在用户的 cookie 中）首选项）。该网格将包含从 SQL Server 上的视图提供的数据，并且行必须是可单击的。分页非常容易实现，但排序已被证明是一个严峻的挑战。

问题（范围）：我决定重新发布所有相关代码，以便更轻松地进行故障排除。

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        chooseColumns()
    End If
End Sub

Protected Function queryToDataSet() As ArrayList
    'Code removed: Function returns the Columns to select from
End Function

'Handles the button click to build the `GridView` from the selected columns
Protected Sub ShowGrid(ByVal sender As Object, ByVal e As EventArgs) Handles btnSub.Click
    For Each item As ListItem In chkFields.Items
        If item.Selected Then
            Dim b As New BoundField()
            b.DataField = item.Value
            b.HeaderText = item.Value
            b.SortExpression = item.Value
            statusReportGrid.Columns.Add(b)
        End If
    Next
--> Me.GetData() 'HERE'S THE SCOPING ISSUE... UPON EXIT THE DATASOURCE IS LOST
End Sub

Private Sub GetData()
    statusReportGrid.DataSource = StatusDS
    statusReportGrid.DataBind()
End Sub

Protected Sub statusReportGrid_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs) Handles statusReportGrid.Sorting
    'CODE BREAKS HERE because the DataSource is no longer in scope
    Dim dataTable As DataTable = TryCast(statusReportGrid.DataSource, DataTable)
    If dataTable IsNot Nothing Then
        Dim dvSortedView As New DataView(dataTable)
        'The following line (when working properly) should return a string
        '  something like "StatusColumn DESC" for example. This format
        '  doesn't make sense to me and doesn't seem correct.
        dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)
        statusReportGrid.DataSource = dvSortedView
        statusReportGrid.DataBind()
    End If
End Sub

Private Function getSortDirectionString(ByVal sortDirection As SortDirection) As String
    Dim newSortDirection As String = String.Empty
    Select Case sortDirection
        Case sortDirection.Ascending
            newSortDirection = "ASC"
        Case sortDirection.Descending
            newSortDirection = "DESC"
    End Select
    Return newSortDirection
End Function

ASP 控件 GridView 控件（减去样式内容） - 我读过的教程说将 EnableViewState 设置为 false。当尝试设置 true 时，我会在刷新时得到重复的列。

<asp:GridView ID="statusReportGrid" runat="server" AutoGenerateColumns="False" 
    AllowPaging="True" AllowSorting="True"
    EnableViewState="False" ShowHeaderWhenEmpty="True"></asp:GridView>

状态报告内容的主要 SqlDataSource 控件。

<asp:SqlDataSource ID="StatusDS" runat="server" 
    ConnectionString="<%$ ConnectionStrings:StatusDBConnectionString %>"
    SelectCommand="SELECT * FROM [StatusTable]">
</asp:SqlDataSource>

问题 我刚刚发现这是一个范围问题，因为一旦导致 DataSource 绑定到我的 GridView 的调用函数退出，DataSource然后就变成空的了。我当然希望得到一些帮助来解决我的范围问题，以便我的 DataSource 保持可用并可由代码隐藏中的所有函数进行修改。只要页面或多或少地显示，它就应该是持久的。

Answer 1

下面是一个在没有 DataSource Control 的情况下使用 DataTable & 后面的代码执行您想要的操作的示例。缓存以及管理视图状态中排序方向的自定义 SortDirection 函数。

''' <summary>
''' Show Grid
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Protected Sub ShowGrid(sender As Object, e As EventArgs) Handles ShowGridButton.Click
    BuildGrid()
End Sub

''' <summary>
''' Sort GridView Columns
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Protected Sub Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs) Handles statusReportGrid.Sorting
    Dim dataTable As DataTable = BuildData()

    If DataTable IsNot Nothing Then
        Dim dvSortedView As New DataView(dataTable)

        dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString()
        statusReportGrid.DataSource = dvSortedView
        statusReportGrid.DataBind()
    End If

End Sub

''' <summary>
''' Get and Store GridView SortDirection
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Private Function getSortDirectionString() As String
    Dim sortDirection As String = "ASC"

    If ViewState("GridSortDirection") IsNot Nothing Then
        If ViewState("GridSortDirection").ToString() = "ASC" Then
            sortDirection = "DESC"
        Else
            sortDirection = "ASC"
        End If
    End If

    ViewState("GridSortDirection") = sortDirection

    Return sortDirection
End Function

''' <summary>
''' Build Dynamic GridView Columns
''' </summary>
''' <remarks></remarks>
Protected Sub BuildGrid()
    Dim dataTable As New DataTable
    dataTable = BuildData()

    If dataTable.Rows.Count > 0 Then
        For Each item As ListItem In chkFields.Items
            If item.Selected Then
                Dim b As New BoundField()
                b.DataField = item.Value
                b.HeaderText = item.Value
                b.SortExpression = item.Value
                statusReportGrid.Columns.Add(b)
            End If
        Next

        statusReportGrid.DataSource = dataTable
        statusReportGrid.DataBind()
    End If
End Sub

''' <summary>
''' Get DataTable from DB and Use Cache to Store 
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Protected Function BuildData() As DataTable
    Dim dataTable As New DataTable

    If Cache("StatusDBResults") IsNot Nothing Then
        dataTable = TryCast(Cache("StatusDBResults"), DataTable)
        Return dataTable
    End If

    Dim queryString As String
    queryString = "SELECT * FROM [StatusTable]"

    Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("StatusDBConnectionString").ConnectionString)
        Dim adapter As New SqlDataAdapter()
        adapter.SelectCommand = New SqlCommand(queryString, connection)
        adapter.Fill(dataTable)

        'Add To Cache for 2 Minutes for Sorting/Paging/Downloading
        Cache.Add("StatusDBResults", dataTable, Nothing, System.Web.Caching.Cache.NoAbsoluteExpiration, New TimeSpan(0, 2, 0), System.Web.Caching.CacheItemPriority.Default, Nothing)

        'MY CODE - Gets the Column Names and dumps them into the ASP.NET CheckBoxList control
        'There may be a better way to do this using the DataTable, but I wasn't able to get that to work. This works and it's pretty fast.
        connection.Open()
        Dim colQuery As New SqlCommand("select column_name from information_schema.columns where table_name = 'StatusTable'", connection)
        Dim dr As SqlDataReader = colQuery.ExecuteReader
        Dim colsArr As New ArrayList
        While dr.Read
            colsArr.Add(dr.Item(0))
        End While
        chkFields.DataSource = colsArr
        chkFields.DataBind()

        Return dataTable
    End Using
End Function

这是我用于测试的示例 HTML 控件源...

<asp:GridView runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" ID="statusReportGrid">
</asp:GridView>
<asp:CheckBoxList runat="server" ID="chkFields" RepeatDirection="Horizontal" RepeatLayout="Flow" />
<asp:Button runat="server" Text="Button" ID="ShowGridButton" />

请告诉我这是否有意义，我通常不使用 VB 编写代码。 SortDirection 是一个已知问题，因为它始终返回 Ascending。至于使用 DataTable 与 SqlDataSource，两者都可以使用，但我发现 DataTable 方法更容易。

Answer 2

由于您将 DataSource 设置为新的 DataView，因此当您再次返回时，您不再拥有 DataTable - 只有 DataView。所以你的 TryCast 不会产生任何结果。

我不知道为什么 IsNot Nothing 会成功。