Show many columns without setting them individually

We have a SQL table with many columns, and we show them in different tabs in a web site (asp.net). Right now we set a lable for each column, and it takes a lot of time.

Is there way to do this more efficiently somehow?

We tried to go automatically through all the column and create a label for each one, but it didn't do the job because a order of the columns is not as the order that need to be shown.

Well, in most cases, it often depends if you "know" the column name(s) before wanting to display such code, or not?

However, even in such cases (that you not really going to know ahead of time the column names)?

A GridView control works rather well.

So, say to display some hotels?

We have this markup:

<asp:GridView ID="GridView1" runat="server" Width="50%" CssClass="table">
</asp:GridView>

With above markup, we can now send the above any table data we wish.

The columns will automatic be generated for us.

Code to load the GridView - page load event is thus this:

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

    If Not IsPostBack Then
        LoadGrid
    End If

End Sub

Sub LoadGrid()

    GridView1.DataSource =
        MyRst("SELECT * FROM VhotelsA ORDER BY HotelName")
    GridView1.DataBind()


End Sub

And the result is now this:

However, often (hopefully!!!), you do know the columns ahead of time.

Thus, we can create the GridView like this:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ID" CssClass="table" width="50%">
    <Columns>
        <asp:BoundField DataField="Fighter" HeaderText="Fighter"  />
        <asp:BoundField DataField="Engine" HeaderText="Engine"  />
        <asp:BoundField DataField="Thrust" HeaderText="Thrust"  />
        <asp:BoundField DataField="Description" HeaderText="Description" />
        <asp:TemplateField HeaderText="Preview">
            <ItemTemplate>
            <asp:Image ID="Image1" runat="server" ImageUrl = '<%# Eval("ImagePath") %>'
                Height="68px" Width="149px"/> 
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

The advantages of above, is we can define a column heading, and the actual database columns can be different then the column headings we define.

The code to load above is this:

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

Sub LoadGrid()


    Dim strSQL As String =
            "SELECT * FROM Fighters"

    GridView1.DataSource = MyRst(strSQL)
    GridView1.DataBind()


End Sub

And note close in the above markup, we added an image control to the GridView.

So, the results are now this:

So, using a GridView allows you to feed it data, and it can "on the fly" generate the columns for you.

Or, as the 2nd example shows?

You can define the columns that will display, and this works EVEN when you pass many columns to the GridView, including columns that will not be displayed.

If you not looking for a table view, but looking for "many" labels on the side of the data?

Then you can try a DetailsView.

Say this markup:

        <asp:DetailsView ID="DetailsView" runat="server"  width="25%" 
            CssClass="table">
        </asp:DetailsView>

Code to load this data is same as above (that control only renders one reocrd at a time).

The result of above is thus this:

So, above generated the label for each column.

Note that in the above examples, I used a "helper" routine to return a data table based on the given SQL.

That rather useful routine was this:

Public Function MyRst(strSQL As String) As DataTable


    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            rstData.TableName = strSQL
        End Using
    End Using
    Return rstData
End Function

Edit: Hide or show columns based on tab or RB selection

So, to hide, or show columns based on that tab control selection?

Well, I don't know which tab control you are using (it looks like the jQuery one).

However, for now, let's just use a RadioButtonList control.

And for the GridView markup, we will have both templated columns, and some data bound columns.

So, the idea here is to "define" a list of columns for each tab selected.

So, the markup with our "fake" tab control (I often use a Radio Button list) is this:

<div style="width:60%">

    <asp:RadioButtonList ID="MyTabs" runat="server" CssClass="rMyChoice"
        Style="float: right"
        RepeatDirection="Horizontal" AutoPostBack="true"
        OnSelectedIndexChanged="MyTabs_SelectedIndexChanged">

            <asp:ListItem Value="0" Selected="True">Tab1</asp:ListItem>
            <asp:ListItem Value="1">Tab 2</asp:ListItem>
            <asp:ListItem Value="2">Tab 3</asp:ListItem>

    </asp:RadioButtonList>
    <br />

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="ID" CssClass="table">
        <Columns>
            <asp:BoundField DataField="Fighter" HeaderText="Fighter" />
            <asp:BoundField DataField="Engine" HeaderText="Engine" />
            <asp:BoundField DataField="Thrust" HeaderText="Thrust" />
            <asp:BoundField DataField="Description" HeaderText="Description" />
            <asp:TemplateField HeaderText="Preview">
                <ItemTemplate>
                    <asp:Image ID="Image2" runat="server" ImageUrl='<%# Eval("ImagePath") %>' Width="140" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="FirstFlight" HeaderText="First Flight"
                DataFormatString="{0:MMM-dd-yyyy}" ItemStyle-Width="110px" />
        </Columns>
    </asp:GridView>

And now our code

Dim ShowColumns As New List(Of String)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    ShowColumns.Add("0,1,2")    ' show columns for Tab1
    ShowColumns.Add("0,1,2,3")    ' show columns for Tab2
    ShowColumns.Add("0,1,2,3,4,5")    ' show columns for Tab3


    If Not IsPostBack Then
        LoadGrid
    End If

End Sub

Sub LoadGrid()

    For i = 0 To GridView1.Columns.Count - 1
        GridView1.Columns(i).Visible = True
    Next

    GridView1.DataSource = MyRst("SELECT * FROM Fighters ORDER BY Fighter")
    GridView1.DataBind()

    ' hide/show columns based on current tab selection
    Dim MyCols As String() = ShowColumns(MyTabs.SelectedIndex).Split(",")
    For i = 0 To GridView1.Columns.Count - 1
        GridView1.Columns(i).Visible = MyCols.Contains(i)
    Next

End Sub

Protected Sub MyTabs_SelectedIndexChanged(sender As Object, e As EventArgs)

    LoadGrid()

End Sub

And the result is this:

Note VERY close the code to un-hide the grid before we data bind it again.

This code:

    For i = 0 To GridView1.Columns.Count - 1
        GridView1.Columns(i).Visible = True
    Next

We MUST do this each time, since if a column has been set visible=false, then the settings persist, and .net will NOT render such columns. So, if we bind the grid with the column visible = false?
Then that column is not rendered, and that is the case EVEN when we re-bind the GridView. This is certainly an unexpected behavior, and one that the above code will address. In effect, we want all columns with visible = true BEFORE the data bind, or in this case the data re-binding.

So, we have to each time turn back on the visible of all rows, and THEN hide them after the databinding is completed.

Since the columns are then to about to be hidden (perhaps again)? Then that markup is not sent to the client side anyway, so from performance point of view, this approach is just fine.