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

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.

"The process cannot access the file because it is being used by another process"

There'll be time when you need to enabling SSL on IIS 7.0 Using Self-Signed Certificates.
RobBagby & scottgu has 2 great tutorials on that topic. Looks like IIS7 simplify a lots.
I go to IIS and set the binding using the default port 443.

But life is not always a bed of roses.Then I hit this error message:
"There was an error while performing this operation."
"The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020)"

I was wonder what caused it to happen  After confirmed with network guy it's not their issue, i have to check what could be the possible reason for that.
From Microsoft Support, and also someone from stack-overflow pointed out this could be port is being used by other application.

Then I work it out from there:
1) run the "netstat" command from command prompt, to display active TCP connections, ports on which the computer is listening

2) then run the "Tasks Manager". The PID column is not displyed by default. I have to go to "View">"Select Column" and check it.

3) Compare the PID.

4) there's another process, called "nhttp.exe" is using that port. It's from Lotus Domino. :(

5) no choice but have to use another port 444. Back to the IIS site binding, and edit it.

6) of course, have to allow the firewall to let that port through by adding a new inbound rule.

7) Finally, I can browse my site.

ListView Grouping On Demand using AJAX and stored-procedure

Matt Berseth has a series of tutorials on building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls. It's quite a good control that able to display the data in master-detail format.

While using LinQ can help you to encapsulate the database layer, it might incur additional workload. And not every coder can write good LinQ query, thus it turns out to be an ugly resource-hunger monster. Personally, i found it's not easy to fine-tune when u need to reduce the page loading time after the data grows huge, if compare with ADO .NET using stored-procedure. At least, you can analyse the execution plan.

You can reduce the page size through paging, but in some scenario, your client might prefer loading all the data in one time (a bit ridiculous though). Then AJAX comes into the picture.


Muhammad Mosa has a series of tutorials on Building a grouping Grid with GridView and ASP.NET AJAX toolkit CollapsiblePanel


By combining the two, we can create Grouping ListView using AJAX and stored procedure.
1) we create the stored procedure to create the grouping data, with the help of GROUPING SETS.

Select a.*, b.OrderDate, b.ShipName, 
c.FirstName + ' ' + c.LastName as fullName
from
(
 SELECT CASE WHEN (GROUPING(CustomerID) = 1) THEN NULL--'...'--'ALL CustomerID'
    ELSE ISNULL(CustomerID, 'UNKNOWN')
     END AS CustomerID,
     CASE WHEN (GROUPING(EmployeeID) = 1) THEN NULL--'...'--'ALL EmployeeID'
    ELSE ISNULL(EmployeeID, 'UNKNOWN')
     END AS EmployeeID,
     CASE WHEN (GROUPING(OrderID) = 1) THEN NULL--'ALL OrderID'
    ELSE ISNULL(OrderID, 'UNKNOWN')
     END AS OrderID,
     COUNT(*) as [itemCount],
     SUM(freight) AS totalFreight
 FROM #temp l
 GROUP BY GROUPING SETS
 (
  (CustomerID, EmployeeID, OrderID),
  (CustomerID, EmployeeID),
  (CustomerID),
  ()
 )
) a

2) Then we need a extended AJAX toolkit CollapsiblePanel from Muhammad Mosa's article.

3) Embed the newly extended CollapsiblePanel in the ItemTemplate of the host listview.

4) Next we need to invoke the nested listview through WebMethod.

[System.Web.Services.WebMethod()]
public static string GetEmployees(string sParams)
{
    Page page = new Page();
    // restart Casini build-in IIS if error 
    COM.UsrCtrl_nestedListView ctl = (COM.UsrCtrl_nestedListView)page.LoadControl(PAGE_EMPLOYEE);
    page.Controls.Add(ctl);
    ctl.Params = sParams;
    System.IO.StringWriter writer = new System.IO.StringWriter();
    HttpContext.Current.Server.Execute(page, writer, false);
    string output = writer.ToString();
    writer.Close();
    return output;
}

5) Eventually we have our final result.

Few things to take note:
i) ToolkitScriptManager has to enable the PageMethods

ii) Since host listView and nested listview are 2 separate html tables, we have to enforce the CSS table-layout Property.

You can get the source-code here (ListViewGrouping_OnDemand.zip).
You can download the Northwind database through Microsoft Download Center.
If you want to customize the CSS of the listView,  this article is a great place to start with.

multi-select dropdownlist in ASP.NET

(this one is heavily followed the article in dotnetfunda; credits go to the developer)

While doing the coding, at times you will need your user to select few options.
Using check-box or radio button is a good option, but they consume quite a space.
Reporting service comes with the multi-select dropdown-list control,

but you could not find the similar one in standard ASP .NET controls.
While searching for the multi-select dropdown list in ASP .NET, I bumped into this article. It's almost similar to what you might expected.

By adding extra ASP checkbox (let's call it: ID="checkAll") on top of CheckBoxList:

and some javascript:

    

    // http://stackoverflow.com/questions/5821993/checkboxlist-items-using-javascript
    function CheckAll() {
        var intIndex = 0;
        var rowCount = document.getElementById('<%=cblItems.ClientID %>').getElementsByTagName("input").length;
        for (intIndex = 0; intIndex < rowCount; intIndex++) {
            if (document.getElementById('<%=checkAll.ClientID %>').checked == true) {
                if (document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex)) {
                    if (document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex).disabled != true)
                        document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex).checked = true;
                }
            }
            else {
                if (document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex)) {
                    if (document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex).disabled != true)
                        document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex).checked = false;
                }
            }
        }
    }

    function ClearAll() {
        var intIndex = 0;
        var flag = 0;
        var rowCount = document.getElementById('<%=cblItems.ClientID %>').getElementsByTagName("input").length;
        for (intIndex = 0; intIndex < rowCount; intIndex++) {
            if (document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex)) {
                if (document.getElementById('<%=cblItems.ClientID %>' + "_" + intIndex).checked == true) {
                    flag = 1;
                }
                else {
                    flag = 0;
                    break;
                }
            }
        }
        if (flag == 0)
            document.getElementById('<%=checkAll.ClientID %>').checked = false;
        else
            document.getElementById('<%=checkAll.ClientID %>').checked = true;

    }

A re-usable user-control is at your disposal:

You can get the full source-code.

And you have more options:
1) dropdown-check-list
2) jQuery UI MultiSelect Widget

Print Zebra barcode label thru' ASP .NET

Recently, one of the projects I am maintaining required me to enhance the existing feature, which is printing the Zebra barcode label through ASP .NET. The old staff who developed it left the company long time ago, and there's no other colleagues know how it was developed and worked.

Printing it as WinForm application is not uncommon with the SerialPort Class, as there's a lot of resources out there, but not so true for ASP .NET.

So I needed to google around and posted question to the forums. With some lucks and googling skills, i managed to let it works and add enhancement on that. But i found not full steps on web, so just to post this to help someone who might need it in the future.

1.a) Set the "Generic/Text Only" as default printer driver. Yes, you see it correctly, it's not zebra printer driver that match your zebra printer, since it's printed through web.

1.b.i) If you do not have it, you can go to any existing printer property page (right-click on any other printer driver) and add this driver.


1.b.ii) Browse to driver list start with "G", then you will find it. Finish this installation process.

2) Once you have the driver, setup the ASP designer with Zebra programming language (ZPL) like this (The buttons are for user to trigger the ZPL to send).

3) By calling the undocumented print() function (not window.print(), and I think it works on IE only) in javascript, you can print the zebra bar-code label through ASP.NET.

4) Keep the web form as clean code as possible, you will send it as ZPL and ZPL only to printer.

5) Print it! You should get it ;)