Display a SQL row vertically and column horizontally

Download source files - 3.03 Kb

Sample screenshot

Introduction

For displaying data in a table, DataGrid probably is the best choice. However, sometimes you may find it necessary to render your table in a different manner. Say, when you have a database table that has more than twenty fields and only a couple of records, I bet you would want to display all the rows vertically and the columns horizontally. Actually a week ago I stumbled into this problem myself. Checking online, I found some users had the same problem, yet there is not a workable answer ready for sharing. Therefore I present my solution below. It may not be the best answer, but it certainly is quick and simple to me.

The code

Solution 1

For saving the trouble of running SQL scripts to recreate a table, I created a .xml file for use. But the code works perfectly with any database table too.

Briefly speaking, the code first reads a .xml file into a DataSet, then saves all data into a two dimensional array. The size of array is dynamically decided by the size of the DataSet table. After the array is populated with data, we build up a table programmatically by adding TableRows and TableCells.

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.XML" %>

    <html>

    <script language="VB" runat="server">


        Sub Page_Load(Sender As Object, E As EventArgs)
            Dim dataArray(,) as string
            dim i, j as integer
        

        'Create instance of DataSet object
            
              dim DS as DataSet = new DataSet()
            DS.ReadXml(Server.MapPath("Employees.xml"))
            
            dim rowCount as integer= DS.Tables(0).Rows.Count
            dim colCount as integer= DS.Tables(0).Columns.Count 
            
            redim dataArray(rowCount, colCount)
            
            'save data in a two dimensional array
            
            for i = 0 to rowCount -1
                for j = 0 to colCount -1
                    if DS.Tables(0).Rows(i).item(j) is DBNull.value
                        dataArray(i,j)=""
                    else
                        dataArray(i,j) =DS.Tables(0).Rows(i).item(j)

                    end if
                next 
            next
            
            'Switch columns and rows, dynamically filling in the table
            
            dim r as TableRow
            dim c as TableCell
            
            for j = 0 to colCount -1
                r = new TableRow()
                c = new TableCell()
                c.Controls.Add(new _
                  LiteralControl(DS.Tables(0).Columns(j).ColumnName))
                c.VerticalAlign = VerticalAlign.Top
                c.Style("background-color") = "lightblue"
                r.cells.add(c)    
                for i = 0 to rowCount - 1
                    
                    c = new TableCell()
                    c.Controls.Add(new LiteralControl(dataArray(i,j)))
                    c.VerticalAlign = VerticalAlign.Top
                    r.cells.add(c)
                next i
            
            Table1.Rows.Add(r)
            next j
                
            
        End Sub

    </script>
    <center>
    <h3> Display Employee Table Vertically</h3>
    </center>
    <body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0">

    <form runat="server" ID="Form1">
    <asp:Table id="Table1" Font-Name="Verdana" Font-Size="8pt" 
        CellPadding=3 CellSpacing=0 BorderColor="black" 
        BorderWidth="1" Gridlines="Both" runat="server"/>
    </form>

    </body>
    </html>

Solution 2

This solution, using XSL to transform XML into HTML in a format desired, is based on a user's suggestion (Thanks). The following is part of the hard-coded XSL file.

       <TABLE border="1">
       <COLGROUP WIDTH="100" ALIGN="CENTER" bgcolor="lavender"></COLGROUP>
      <TR><TD>EmployeeID</TD>
          <xsl:for-each select="NewDataSet/Table">
              <TD><xsl:value-of select="EmployeeID"/></TD>
           </xsl:for-each>
      </TR>
      <TR><TD>LastName</TD>
       <xsl:for-each select="NewDataSet/Table">
              <TD><xsl:value-of select="LastName"/></TD>
          </xsl:for-each>
      </TR>
         ...

To use the XSL file, we need to add XSL reference to the original XML file:

<?xml-stylesheet type="text/xsl" href="Employees.xsl"?>

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Xun Ding

United States

Member

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

C# code available?

sanme98

18:22 12 May '04

Does this tutorial have any C# code? I am just starting become a web programmer, and not sure how to convert it to C#?
Sign In·View Thread·Permalink

Re: C# code available?

Xun Ding

10:35 13 May '04

Do you want to try it out yourself? Change VB to C# is like change code format. If you still want a c# version, I will try to do it for you after these couple of days.
Sign In·View Thread·Permalink

Re: C# code available?

sanme98

16:59 13 May '04

Thanks for your answer and reply, i'll will try to do it. But, it you have free time to do it please post the C# version. Thanks you very much.
Sign In·View Thread·Permalink

Re: C# code available?

Xun Ding

17:17 13 May '04

OK. I have a 7-month-old daughter. If she is not so cute and seductive and time-consuming, I would do this earlier.
Sign In·View Thread·Permalink

Re: C# code available?

Xun Ding

12:01 14 May '04

Maybe you already figured out? Anyway, the following is the code:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Xml" %>
<html>

<Script language="C#" runat="server">

void Page_Load(Object sender, EventArgs e)
{
int i,j;

//Create instance of DataSet object

DataSet DS = new DataSet();
DS.ReadXml(Server.MapPath("Employees.xml"));

int rowCount = DS.Tables[0].Rows.Count;
int colCount = DS.Tables[0].Columns.Count;

//create a two-dimensional array
String[][] dataArray = new String[rowCount][];

//save data in a two dimensional array
for (i = 0; i < rowCount;i++)
{
dataArray[i] = new String[colCount];
for (j = 0; j < colCount; j++)
{
dataArray[i][j] =DS.Tables[0].Rows[i][j].ToString();
}
}

//Switch columns and rows, dynamically filling in the table

TableRow r;
TableCell c;

for (j=0; j < colCount; j++)
{
r= new TableRow();
c = new TableCell();
c.Controls.Add(new LiteralControl(DS.Tables[0].Columns[j].ColumnName));
c.VerticalAlign = VerticalAlign.Top;
c.Style["background-color"] = "lightblue";
r.Cells.Add(c);

for (i = 0; i

the rest is the same

Re: C# code available?

sanme98

16:28 16 May '04

Thank you very much for your help. I have some problem in the multi dimension array (from dataArray(i,j) to dataArray[i][j])). Finally, wish you have a happiness family. The code missing some word on line 47, it should be like this for (i = 0; i < rowCount; i++) {
Sign In·View Thread·Permalink	5.00/5 (1 vote)

Re: C# code available?

Navvy

13:32 17 Jun '04

Thanks a lot . This code is really helpful. I am using WinForms instead of WebForms since I am developing a stand alone app. I get an error:
The type or namespace name 'TableRow' could not be found (are you missing a using directive or an assembly reference?)
The type or namespace name 'TableCell' could not be found (are you missing a using directive or an assembly reference?)

Any suggestions

Navvy

Re: C# code available?

Xun Ding

11:37 18 Jun '04

Hi, I think tables are not for winforms at all. Right now, only one thing comes to my mind, that is you can dynamically add labels. But it is pretty cumbersome. Or you may do some sort of crystal report, but I am not sure either. Sorry, if i thought of something else, I will let you know
Sign In·View Thread·Permalink

C# code available!

_Groker

5:18 4 May '07

public static Table GetVerticalTable(DataTable data, Table table)
      {
            int rowCount = data.Rows.Count;
            int colCount = data.Columns.Count;
            string[,] dataArray = new string[rowCount, colCount];
            for (int i = 0; i <= rowCount - 1; i++)
            {
                  for (int j = 0; j <= colCount - 1; j++)
                  {
                        if (data.Rows[i].ItemArray[j] == DBNull.Value)
                        {
                              dataArray[i, j] = "";
                        }
                        else
                        {
                              dataArray[i, j] = data.Rows[i].ItemArray[j].ToString();
                        }
                  }
            }
            TableRow r;
            TableCell c;
            for (int j = 0; j <= colCount - 1; j++)
            {
                  r = new TableRow();
                  c = new TableCell();
                  c.Controls.Add(new LiteralControl(data.Columns[j].ColumnName));
                  c.VerticalAlign = VerticalAlign.Top;
                  c.Style["background-color"] = "lightblue";
                  r.Cells.Add(c);
                  for (int i = 0; i <= rowCount - 1; i++)
                  {
                        c = new TableCell();
                        c.Controls.Add(new LiteralControl(dataArray[i, j]));
                        c.VerticalAlign = VerticalAlign.Top;
                        r.Cells.Add(c);
                  }
                  table.Rows.Add(r);
            }
            return table;

<div class="ForumMod">modified on Thursday, July 31, 2008 6:47 PM</div>

Great Work!!

bonniew

4:17 24 Feb '04

Thank you so much for posting this. Your code is exactly what I needed and has saved me at least 3 hours of dev time!! Bonnie
Sign In·View Thread·Permalink

XSL ?

Sébastien Lorion

13:21 16 Aug '03

Why not simply export data as xml (using scheduled batch job or asp code in the page) and then use XSL to show them the way you want ? This way, you put the load on the client, greatly simplify so called layout code and centralize it in one place. Intelligence shared is intelligence squared. Homepage : http://www.slorion.webhop.org
Sign In·View Thread·Permalink

Re: XSL ?

curry

17:13 16 Aug '03

Thanks. You mean the transformed data or original data? The original data source is a xml file. It was written from a database table using xmlwriter. Isn't that true we still need to traverse the xml data node by node? I cannot see how .xsl can solve the problem. Or am i missing something? Please advise
Sign In·View Thread·Permalink

Re: XSL ?

Sébastien Lorion

19:34 16 Aug '03

Once you have original data in xml file, you can use a XSL stylesheet to transform it. In XSL file, depending on how you use for-each and value-of statements to traverse the XML file, you can go row by row or column by column.

You can state the XSL file to be used directly into the xml file using <?xml-stylesheet type="text/xsl" href="toto.xsl"?> and the browser will output a temp html file in the cache on the client.

ASP/PHP/CGI/etc. are good when you need to add/modify data, but for displaying it, I usually pick a solution involving XSL. The only drawback is that not all browser support it (eg. Opera support is lacking).

Tell me if you need more details ...

Intelligence shared is intelligence squared.

Homepage : http://www.slorion.webhop.org

Re: XSL ?

curry

11:11 17 Aug '03

Thanks. It indeed is another short cut to accomplish the same thing. I wonder if it is possible to write the xsl dynamically? I added a second solution accordingly. Also,is there another way to render the xsl, more efficient and straightforward? Thanks a lot, again
Sign In·View Thread·Permalink

Re: XSL ?

Sébastien Lorion

11:57 17 Aug '03

My pleasure really Smile | :)

When I first discovered XSL, I was amazed what you could accomplish with it.

At its base, XSL is just XML so all you can do with XML, you can do with XSL. It follows that you can use System.Xml to generate it dynamically.

That said, this would be in contradiction with the reason why you would use it in the first place : using DOM engine is expensive and most often than not, overkill. System.Xml.XmlTextWriter would be the most efficient way to generate the XSL since it is non-cached and forward-only.

Take a look at this excellent tutorial site about XSL/XSLT ... There are many built-in functions supported by XSL ! And then, you can judiciously use XPath to locate and traverse data.

You ask for a way to render more efficiently ... Since it's the client's browser engine that do all the work (internally, IE use exactly the same library you would use in code), I don't see how you could do better except by optimizing the XSL itself.

If you don't want to link the XML file directly to the XSL, which is not really a good way to do things I agree, check here to learn how to use Javascript instead. This would even let you specify different XSL stylesheets depending on browsers or other variables.

Also, an alternative would be to use System.Xml.Xsl.XslTransform class to do the transformation, but this way, it is done on server side.

Also, note that you can reference CSS files in XSL, further separating presentation layers and allowing reuse.

Wooo ... ! As you can see, one can easily get lost with all those technologies Smile | :)

And this is only the tip of the iceberg Cool | :cool:

Sébastien

Intelligence shared is intelligence squared.

Homepage : http://www.slorion.webhop.org

Fix ColumnNames?

spieler

21:26 12 Aug '03

this code should be re-usable? so why you use fix columnames? this is not necessary, you can read them from the dataset. regards SeeSharp
Sign In·View Thread·Permalink	3.00/5 (2 votes)