How do I programmatically add an unknown number of columns to an ASP.NET page using a repeater where some of the columns are declared at runtime in the SQL string?

Question

I am declaring a SQL string that looks something similar to the following:

string SQL = "SELECT Column1, Column2, Column3, Date1, Date2 FROM Somewhere";

The number of columns can vary by circumstance and the dates can be called different names such as StartDate, InterestDate and so on.

What I would like to do is bind this to an ASP.NET Repeater and create a table as below for the example above:

<table>
  <tr>
    <th>Column1</th>
    <th>Column2</th>
    <th>Column3</th>
    <th>Date1</th>
    <th>Date2</th>
  </tr>
  <tr>
    Rows of values...
  </tr>
</table>

I am fine with binding the data to a Repeater and using Eval but that is dependant on a fixed number of columns and column names. Can this be done in a programmatic way?

Answer 1

You could use a GridView which automatically generates columns based on the data source.

Answer 2

If you use a GridView you can set the AutoGenerate columns property to true (it is true by default) and then bind the GridView to your data.

To make is super simple, you may want to use a SQLDataSource control.

The asp.net site has an overview of databinding in asp.net (uses the ObjectDataSource, but the concepts is exactly the same). This page also covers some of the ways to customize the GridView.

Update: Here is a sample of how you could handle the rowdatabound event to format the date columns (per comments on other answer). This could probably be done better, but this should get you started.

protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
    DataRowView rowView = (DataRowView)e.Row.DataItem;

    for (int i = 0; i < rowView.Row.ItemArray.Length; i++)
    {
        DateTime? tmpDate = rowView[i] as DateTime?;

        if (tmpDate.HasValue)
        {
            if (tmpDate.Value.Second > 0)
                e.Row.Cells[i].Text = tmpDate.Value.ToShortTimeString();
            else
                e.Row.Cells[i].Text = tmpDate.Value.ToShortDateString();
        }
    }
}

Answer 3

If you use a DataReader to retrieve the data, the columns can be accessed with their indices rather than with their names. You should be able to loop through them all, even if their number is not constant.

<% while(myRepeaterWithData.Read()) {
       for (int i = 0; i < myReaderWithData.FieldCount; i++) { %>
           The data on this row in column number <%= i%> is 
           <%= myRepeaterWithData[i] %>.
<%     }
    } %>

Answer 4

Here's a link to an example of dynamically creating a repeater at runtime. The only requirement is that an ASP:Panel be on your form in order to inject it.

http://programming.top54u.com/post/ASP-Net-Repeater-dynamically-using-C-sharp-code.aspx