Wednesday, June 5, 2013

Dynamic columns in listview

Creating a GridView with dynamic columns generated is easy, it even comes without much effort by simply configuring the AutoGenerateColumns Property. Some even can try to use Meta-Data.

However, creating a ListView with dynamic columns needs a bit of trick. Here's the ways:

1) First, we create a  Employee-vs-Vendor table, using the Common Table Expressions(CTE) and PIVOT. Left-join it we get the result in tabular form.


2) On the listview layout template, we have to declare a placeholder:


    

Employee vs Vendor



the CSS style used is to break the words if you specify too many columns.

3) Then in the ItemTemplate, we no need to add extra dynamic columns we want, since it's going to be done programmatically.


4) Depends on the level of the query, you have to create a nested ItemTemplate accordingly. I believe this can be done thru' AJAX. However, I leave this to the future.

5) Then when we start binds the query to the listview, we bind it level-by-level:

DataTable dt = new DataTable();
dt = ((DataTable)ViewState["cachedTable"]).Clone();

if (((DataTable)ViewState["cachedTable"]).Rows.Count > 0)
{
    DataRow[] drResults = ((DataTable)ViewState["cachedTable"]).Select("GENERATION = 0");

    foreach (DataRow dr in drResults)
    {
        object[] row = dr.ItemArray;
        dt.Rows.Add(row);
    }
}

lsvHighLvlFormAccess.DataSource = dt;
lsvHighLvlFormAccess.DataBind();

6) When highest level binding is done, we can populate the header caption. This is the part when you set the column-span & generate the column header dynamically.

protected void lsvHighLvlFormAccess_DataBound(object sender, EventArgs e)
{
    PlaceHolder phDynamicHdr = (PlaceHolder)lsvHighLvlFormAccess.FindControl("phDynamicHdr");
    // check phDynamicHdr.Controls.Count; could be called twice when postback
    if (phDynamicHdr != null /*&& phDynamicHdr.Controls.Count == 0*/)
    {
        Literal ltrl = new Literal();

        DataTable dt = ((DataTable)ViewState["cachedTable"]);
        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataColumn dc in dt.Rows[0].Table.Columns)
            {
                if (!(dc.ColumnName.Equals("GENERATION") ||
                        dc.ColumnName.Equals("hierarchy") ||
                        dc.ColumnName.Equals("rowNo") ||
                        dc.ColumnName.Equals("EmployeeID")))
                {
                    if (dc.ColumnName.Equals("LoginID"))
                    {
                        ltrl.Text += "" + dc.ColumnName + "

";
                    }
                    else
                        ltrl.Text += "" + dc.ColumnName + "

";
                }
            }
        }

        if (phDynamicHdr.Controls.Count > 0)
        {
            // if current dynamic columns is different with exisiting dynamic columns 
            if (!((Literal)phDynamicHdr.Controls[0]).Text.Equals(ltrl.Text))
            {
                // remove the whole previous dynamic columns
                phDynamicHdr.Controls.Remove(phDynamicHdr.Controls[0]);
                // replace with new dynamic columns
                phDynamicHdr.Controls.Add(ltrl);
            }
        }
        else
            phDynamicHdr.Controls.Add(ltrl);
    }

    HtmlTableCell td = (HtmlTableCell)lsvHighLvlFormAccess.FindControl("imgCollapseExpand");
    if (td != null)
    {
        DataTable dt = ((DataTable)ViewState["cachedTable"]);
        if (dt != null && dt.Rows.Count > 0)
        {
            td.ColSpan = dt.Rows[0].Table.Columns.Count + I_COLSPAN - 1;//I_COLSPAN - 1 : to put the 'minus.png'
        }
    }
}

7) On each high-level row is bound, we need to fire the event to populate its descendant. Here, checkboxes are used. You can replace with any other control too.

protected void lsvHighLvlFormAccess_ItemDataBound(object sender, ListViewItemEventArgs e)
{
    HtmlTableRow row = (HtmlTableRow)e.Item.FindControl("row");
    // http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.listviewdataitem.dataitem.aspx
    // http://forums.asp.net/t/1334142.aspx/1
    ListViewDataItem item = (ListViewDataItem)e.Item;
    System.Data.DataRowView drv = (System.Data.DataRowView)item.DataItem;

    dynamicPopulateRow(row, drv, 0);
 // more contents here
}

where dynamicPopulateRow() is:

private void dynamicPopulateRow(HtmlTableRow row, System.Data.DataRowView drv, int iGeneration)
{
    if (row != null)
    {
        // http://www.pcreview.co.uk/forums/do-enumerate-all-columns-dataviewrow-t1244448.html
        foreach (DataColumn dc in drv.Row.Table.Columns)
        {
            string sEmployeeID = drv["LoginID"].ToString();

            if (dc.ColumnName.Equals("LoginID"))
            {
                // http://msdn.microsoft.com/en-US/library/e5daxzcy(v=vs.80).aspx
                // Define a new HtmlTableCell control.
                HtmlTableCell cell = new HtmlTableCell("td");

                // Create the text for the cell.
                cell.Controls.Add(new LiteralControl(Convert.ToString(drv[dc.ColumnName])));
                cell.ColSpan = dc.ColumnName.Equals("LoginID") ? I_COLSPAN - iGeneration : 1;

                // Add the cell to the HtmlTableRow Cells collection. 
                row.Cells.Add(cell);
            }
            else if (!(dc.ColumnName.Equals("GENERATION") ||
                        dc.ColumnName.Equals("hierarchy") ||
                        dc.ColumnName.Equals("rowNo") ||
                        dc.ColumnName.Equals("EmployeeID")))
            {
                // http://msdn.microsoft.com/en-US/library/e5daxzcy(v=vs.80).aspx
                // Define a new HtmlTableCell control.
                HtmlTableCell cell = new HtmlTableCell("td");

                bool bIsNull = drv[dc.ColumnName] is System.DBNull;

                Literal ltrl = new Literal();
                ltrl.Text += " 0 ? " checked>" : ">");

                cell.Controls.Add(ltrl);
                // Add the cell to the HtmlTableRow Cells collection. 
                row.Cells.Add(cell);
            }
            else
            {
                //other rows
            }
        }
    }
}

8) If you more nested level, just add these codes:

var lst1stLevel = (ListView)e.Item.FindControl("lst1stLevel");
populateLV(lst1stLevel, 1, (string)drv["hierarchy"], Convert.ToInt32(drv["rowNo"]));

where populateLV() is:

private void populateLV(ListView lv, int iNextGeneration, string sHierarchy, int iCurrRowNo)
{
    if (lv != null)
    {
        DataTable dt = new DataTable();
        dt = ((DataTable)ViewState["cachedTable"]).Clone();// clone schema only

        List levels = ((DataTable)ViewState["cachedTable"]).
                            Select("GENERATION = " + (iNextGeneration > 0 ? iNextGeneration - 1 : 0) +
                            " AND hierarchy LIKE '" + sHierarchy + "%'"). // no space
                            AsEnumerable().
                            Select(al => Convert.ToInt32(al.Field("rowNo"))).Distinct().ToList();

        // duplicate hierarchy, display at smallest rowNo will do
        if (levels.Count > 0 && levels.Min() == iCurrRowNo)
        {
            DataRow[] drResults = ((DataTable)ViewState["cachedTable"]).
                                Select("GENERATION = " + iNextGeneration + " AND hierarchy LIKE '" + sHierarchy + " %'");

            foreach (DataRow dr in drResults)
            {
                object[] obRow = dr.ItemArray;
                dt.Rows.Add(obRow);
            }
            lv.DataSource = dt;
            lv.DataBind();
        }
    }
}

9) Repeat this for second-level, third-level, etc.

protected void lst2ndLevel_ItemDataBound(object sender, ListViewItemEventArgs e)
{
    HtmlTableRow row = (HtmlTableRow)e.Item.FindControl("row");
    ListViewDataItem item = (ListViewDataItem)e.Item;
    System.Data.DataRowView drv = (System.Data.DataRowView)item.DataItem;
    int iCurrLvl = 2;
    //populate dynamic cells
    dynamicPopulateRow(row, drv, iCurrLvl);

    //populate nested LV
    var lst3rdLevel = (ListView)e.Item.FindControl("lst3rdLevel");
    populateLV(lst3rdLevel, iCurrLvl + 1, (string)drv["hierarchy"], Convert.ToInt32(drv["rowNo"]));
}

10) Done. This is what it looks like in Visual Studio Designer. Press F5 to go.



11) if you choose 4 columns, you get this:


12) If you choose 10 columns, you get this:


13) Lastly, a whopping 55 columns.



You can get the source-code here (DynamicColumns_LV.zip).
You can download the AdventureWorks database through Microsoft Download Center.