'Rotate' data in a WinForms datagrid

Question

Using Winforms .NET 2.0, my client has requested that data in a Datagrid be displayed vertically rather than horizontally. The data layer of the app is built upon NHibernate, so I generally avoid writing raw SQL in favor of HQL. However, this scenario may lend itself to utilizing the 'crosstab' functionality of SQL Server 2005.

So for example, the current gridview layout (which mirrors the table design) is bound to an IList(of ClaimGroup) as such:

GroupName | ClaimType | PlanCode | AgeFrom | AgeTo | AgeSpan

BHFTConv 1| P         | CC       | 6       | 12    | Years

needs to become:

GroupName  | BHFTConv 1
ClaimType  | P
PlanCode   | CC
AgeFrom    | 6
AgeTo      | 12
AgeSpan    | Years

The grid currently has, and will still require, an 'Apply' button that will save any changes made to the rotated grid.

So what would you do? Use raw SQL to crosstab the data, thereby breaking the NHibernate law of avoiding db specific queries? Or is there some mechanism built into a DataSet that would allow the data to be rotated? Or just use brute force to rotate the data?

thanks in advance

Answer 1

I have the same requirement to rotate a Grid as I'm replicating the QueryWizard in ASP.NET and the data is more manageable flipped on its side.

My prototype consisted of a Repeater that dumped the row out vertical rows, but formatting is a bit messy if you have unpredictable data lengths as the tables need to have set line-height, width and an overflow:hidden to appear uniformed. - but I guess a simple nobr would suffice.

<HeaderTemplate>
   <table>
   <tr><td>
         <table>
         <tr><td>ColumnName1</td></tr>
         <td><td>ColumnName2</td></tr>
         </table>
       </td>
</HeaderTemplate>
<ItemTemplate>
       <td>
         <table>
         <tr><td><%# Eval("Column1") %></td></tr>
         <tr><td><%# Eval("Column2") %></td></tr>
         </table>
       </td>
</ItemTemplate>
<FooterTemplate>
    </tr>
    </table>
</FooterTemplate>

My plan of attack for Monday was to override the GridView render method and then loop the Rows to have them render as required. I will post back to let you know my progress

Pseudocode - something like

for(int c=0; c < columns.Count; c++)
{
   writer.Write("<tr>");
   writer.Write("<td>" + columns[c].Title + "</td>");

   for(int r=0; r < rows.Count; r++)
   {
       rows[r].Cells[c].render(writer);
   }

   writer.Write("</tr>");
}

By letting the Cell handle the rendering you shouldn't need to do anything different with Binding or the ViewState.

Answer 2

I would use the virtual mode of the datagridview.
Property VirtualMode = True

When you have fethed the data as normal
Create the 2 columns and prepare the datagridview

DataGridView.Columns.Add("colGroup", "Group");
DataGridView.Columns.Add("colBVH", "BVH");
DataGridView.Rows.Clear();
DataGridView.Rows.Add(6);
DataGridView.CellValueNeeded +=
  new DataGridViewCellValueEventHandler(NeedValue);

Now implement the "CellValueNeeded" event..

private void NeedValue(object sender, DataGridViewCellValueEventArgs e)
{
    e.Value
    e.ColumnIndex
    e.RowIndex
}

Use rowindex and columnindex to set the value that needs to be shown.
You know which property based on rowvalue and which row based on the columnvalue.

Answer 3

Have you considered using DataList instead of DataGridView?