Is there an easy way to populate an HTML table with SQL in ASP.NET 2.0?

Question

I have 3 similar SQL queries that each will bring back 1 record with 5 fields.

I want to display each of these 3 records vertically in an HTML table.

Obviously I don't need sorting or paging so I don't want to waste overhead on a gridView.

    <table>
        <tr><td>Last</td><td>Col1</td><td>Col2</td><td>Col3</td></tr>
        <tr><td>First</td><td>Col1</td><td>Col2</td><td>Col3</td></tr>
        <tr><td>Middle</td><td>Col1</td><td>Col2</td><td>Col3</td></tr>
        <tr><td>Gender</td><td>Col1</td><td>Col2</td><td>Col3</td></tr>
        <tr><td>Birth Date</td><td>Col1</td><td>Col2</td><td>Col3</td></tr>
    </table>

I want Col1, Col2 and Col3 to display results from their respective SQL queries.

What is the easiest way to go about this and could you provide an example?

Answer 1

It's not clear whether your data series are in rows or columns, but the Repeater is close to what you want, if your records are in rows.

Answer 2

Since they all have the same fields, UNION them together and set the result as the source for a grid control or repeater:

<asp:SqlDataSource ID="MyData" runat="server" ConnectionString="..."
    SelectCommand=" (Query1) UNION (Query2) UNION (Query3) " />

<asp:GridView ID="ResultGrid" runat="server" DataSourceID="MyData" />

Answer 3

In your service layer you should run the 3 queries and have your 3 unique result sets (hopefully you use datareaders instead of datatables and build actual objects) and then after you have data you then create a new List<MyObject> that matches the scope that you want on your table and build up that collection from your result sets. Then you can databind your clean List<MyObject> to your gridview, repeater, listview or similar control for generating the html output.

I recommend using the ListView control for all purposes as it allows you to generate the exact html you want and offers all the functionality you get with the gridview control of easily configurable alternating rows, databinding events etc.

My Two Cents: It's always hard to understand reasoning from the outside looking in especially since none of us want to reveal too specific of business usage on here, but it seems like your data design is what is actually flawed which is why it is hard to represent on screen.

Answer 4

Sorting and paging are not enabled by default on a Gridview, so there is no reason not to use one because of that.

However, you could consider using the ListView control, which gives you more control over templating.

Answer 5

        '
        'begin sample data
        Dim dt As New DataTable
        dt.Columns.Add("Last")
        dt.Columns.Add("First")
        dt.Columns.Add("Middle")
        dt.Columns.Add("Gender")
        dt.Columns.Add("Birth Date")
        '
        Dim dr1 As DataRow = dt.NewRow
        dr1("Last") = "apple"
        dr1("First") = "is"
        dr1("Middle") = "red"
        dr1("Gender") = "male"
        dr1("Birth Date") = "2009"
        '
        Dim dr2 As DataRow = dt.NewRow
        dr2("Last") = "banana"
        dr2("First") = "is"
        dr2("Middle") = "yellow"
        dr2("Gender") = "female"
        dr2("Birth Date") = "2010"
        '
        Dim dr3 As DataRow = dt.NewRow
        dr3("Last") = "cherry"
        dr3("First") = "is"
        dr3("Middle") = "pink"
        dr3("Gender") = "both"
        dr3("Birth Date") = "2011"
        'end sample data
        '
        '
        Dim sb As New StringBuilder
        sb.Append("<table border='1'>")
        sb.Append("<tr>")
        sb.Append(String.Format("<td><b>Last</b></td><td>{0}</td><td>{1}</td><td>{2}</td>", dr1("Last"), dr2("Last"), dr3("Last")))
        sb.Append("</tr>")
        sb.Append("<tr>")
        sb.Append(String.Format("<td><b>First</b></td><td>{0}</td><td>{1}</td><td>{2}</td>", dr1("First"), dr2("First"), dr3("First")))
        sb.Append("</tr>")
        sb.Append("<tr>")
        sb.Append(String.Format("<td><b>Middle</b></td><td>{0}</td><td>{1}</td><td>{2}</td>", dr1("Middle"), dr2("Middle"), dr3("Middle")))
        sb.Append("</tr>")
        sb.Append("<tr>")
        sb.Append(String.Format("<td><b>Gender</b></td><td>{0}</td><td>{1}</td><td>{2}</td>", dr1("Gender"), dr2("Gender"), dr3("Gender")))
        sb.Append("</tr>")
        sb.Append("<tr>")
        sb.Append(String.Format("<td><b>Birth Date</b></td><td>{0}</td><td>{1}</td><td>{2}</td>", dr1("Birth Date"), dr2("Birth Date"), dr3("Birth Date")))
        sb.Append("</tr>")
        sb.Append("<table>")
        '
        Response.Write(sb.ToString)

Answer 6

If you are using SQL Server 2005+ you might try using the UNPIVOT option on your query first like this:

create table #mydata
   (
   id           integer,
   fld1         varchar(15),
   fld2         varchar(15),
   fld3         varchar(15),
   fld4         varchar(15),
   fld5         varchar(15)
   )
go
insert into #mydata values (1,'fld 1 - rec 1','fld 2 - rec 1','fld 3 - rec 1','fld 4 - rec 1','fld 5 - rec 1')
insert into #mydata values (1,'fld 1 - rec 2','fld 2 - rec 2','fld 3 - rec 2','fld 4 - rec 2','fld 5 - rec 2')
insert into #mydata values (1,'fld 1 - rec 3','fld 2 - rec 3','fld 3 - rec 3','fld 4 - rec 3','fld 5 - rec 3')
go

--Unpivot the table.
SELECT idc, field, value
FROM
   (SELECT cast(id as varchar(5)) idc, fld1, fld2, fld3, fld4, fld5 from #mydata ) d
UNPIVOT
   (Value FOR field IN (fld1, fld2, fld3, fld4, fld5)
)AS unpvt;

returns results like:

idc              value           
----- ---------- --------------- 
1     fld1       fld 1 - rec 1   
1     fld2       fld 2 - rec 1   
1     fld3       fld 3 - rec 1   
1     fld4       fld 4 - rec 1   
1     fld5       fld 5 - rec 1   
1     fld1       fld 1 - rec 2   
1     fld2       fld 2 - rec 2   
1     fld3       fld 3 - rec 2   
1     fld4       fld 4 - rec 2   
1     fld5       fld 5 - rec 2   
1     fld1       fld 1 - rec 3   
1     fld2       fld 2 - rec 3   
1     fld3       fld 3 - rec 3   
1     fld4       fld 4 - rec 3   
1     fld5       fld 5 - rec 3

Then you can use a gridView with auto-generated columns from that data source.

Ron