Monday, March 8, 2010

GridView in GridView (2) - LINQ Eager Loading

In this blog, I provided a solution for nested GridViews. The problem with that, like other declarative data source bindings, is you have little control of the data source itself hence the database operations might not be optimal.
For example, as we have to use 2 separate data sources, one for each GridView, the ASP.NET engine will first query session data for the outer grid then query transaction logs for the inner grid.However, this actually can be done in one go. Following the pattern I mentioned in this blog to manually control data source object and using eager loading for LINQ, we can have far few SQL queries to the database hence improve the performance.
In .aspx, remove the data sources. As a result of not setting the DataSourceID property of the GridView, we have to add the OnPageIndexChanging event handler for paging.
<asp:GridView ID="gvSession" CssClass="datagrid" runat="server" 
  AllowPaging="True" PageSize="6" AutoGenerateColumns="False" 
  OnPageIndexChanging="gvSession_PageIndexChanging"
  OnRowDataBound="gvSession_RowDataBound" Width="680px">
  <Columns>
    <asp:BoundField DataField="StaffName" HeaderText="Staff Name" />
    <asp:BoundField DataField="SessionId" HeaderText="Session Id" />
    <asp:BoundField DataField="SessionStartTime" HeaderText="Session Start Time" />
    <asp:BoundField DataField="SessionDuration" HeaderText="Session Duration" />
    <asp:TemplateField HeaderText="Tx Logs">
      <ItemTemplate >
        <asp:GridView ID="gvTxLog" runat="server" AutoGenerateColumns="False" Width="100%">
          <Columns>
            <asp:BoundField DataField="Text" HeaderText="Tx Type" ItemStyle-Width="70%" />
            <asp:BoundField DataField="Value" HeaderText="Duration" />
          </Columns>
        </asp:GridView>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>
In .aspx.cs, bind outer GridView in Page_load, set the inner GridView's data source in outer grid's OnRowDataBound event handler, and implement the OnPageIndexChanging event handler:
ReportDAO _reportDao;

protected void Page_Load(object sender, EventArgs e)
{
    ...
    if (reportTabs.ActiveTab == txViewTab)
    {
        _reportDao = _reportDao ?? new ReportDAO();
        gvSession.DataSource = _reportDao.GetSessions(txtStartDate.Text, txtEndDate.Text,
            int.Parse(ddlStaffs.SelectedValue));
        gvSession.DataBind();
    }
    ...
}

protected void gvSession_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        GridView subView = (GridView)e.Row.FindControl("gvTxLog");
        subView.DataSource = _reportDao._subTables[
            gvSession.PageIndex * gvSession.PageSize + e.Row.RowIndex];
        subView.DataBind();
    }
}

protected void gvSession_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvSession.PageIndex = e.NewPageIndex;
    gvSession.DataBind();
}
The Data Access Object is rather simple. Using ListItem for inner GridView is because I only need 2 values: transaction type name and duration, corresponding to Text and Value DataField of inner GridView's BoundField in .aspx. You can change that to DataTable if more columns required.
public List<List<ListItem>> _subTables = new List<List<ListItem>>();
...
[DataObjectMethod(DataObjectMethodType.Select, false)]
public DataTable GetSessions(string start, string end, int staffId)
{
    if (staffId < 0) // None
        return null;

    ToDate(start, end, out _startDate, out _endDate);
    using (ImportTxDataContext dataContext = new ImportTxDataContext())
    {
        dataContext.EagerLoad();

        // staffId == 0 means all sessions, -1 means none
        var sessions = (from session in dataContext.Sessions
                        where session.StartTime > _startDate && session.EndTime < _endDate &&
                            (session.StaffId == staffId || staffId == 0)
                        select session).ToList();

        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("StaffName", typeof(string)));
        table.Columns.Add(new DataColumn("SessionId", typeof(int)));
        table.Columns.Add(new DataColumn("SessionStartTime", typeof(string)));
        table.Columns.Add(new DataColumn("SessionDuration", typeof(string)));
        
        foreach (Session session in sessions) // each row
        {
            DataRow row = table.NewRow();
            row["StaffName"] = session.Staff.Name;
            row["SessionId"] = session.Id;
            row["SessionStartTime"] = session.StartTime.ToString(Util.DATE_TIME_FORMAT);
            row["SessionDuration"] = Util.DurationAsString(session.EndTime - session.StartTime);
            table.Rows.Add(row);
            
            List<ListItem> subTable = new List<ListItem>();
            foreach (TxLog log in session.TxLogs)
            {
                subTable.Add(new ListItem(
                    log.TxType.Name, Util.DurationAsString(log.Duration, false)));
            }
            _subTables.Add(subTable);
        }

        return table;
    }
}
EagerLoad() is an extension method for the trick of LINQ eager loading:
public static void EagerLoad(this ImportTxDataContext dataContext)
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Session>(r => r.Staff);
    options.LoadWith<Session>(r => r.TxLogs);
    options.LoadWith<ImportTx.DAO.TxLog>(r => r.TxType);
    dataContext.LoadOptions = options;
}
The LINQ generated SQL is like this:
exec sp_executesql N'SELECT [t0].[Id], [t0].[StaffId], [t0].[LocationId], [t0].[StartTime], [t0].[EndTime], [t2].[SessionId], [t2].[TxTypeId], [t2].[Duration], [t3].[Id] AS [Id2], [t3].[Name], [t3].[Description], [t3].[Active], (
    SELECT COUNT(*)
    FROM [dbo].[TxLog] AS [t4]
    INNER JOIN [dbo].[TxType] AS [t5] ON [t5].[Id] = [t4].[TxTypeId]
    WHERE [t4].[SessionId] = [t0].[Id]
    ) AS [value], [t1].[Id] AS [Id3], [t1].[Name] AS [Name2], [t1].[NetworkName], [t1].[ModifiedBy], [t1].[LastModified], [t1].[Active] AS [Active2], [t1].[IsAdmin]
FROM [dbo].[Session] AS [t0]
INNER JOIN [dbo].[Staff] AS [t1] ON [t1].[Id] = [t0].[StaffId]
LEFT OUTER JOIN ([dbo].[TxLog] AS [t2]
    INNER JOIN [dbo].[TxType] AS [t3] ON [t3].[Id] = [t2].[TxTypeId]) ON [t2].[SessionId] = [t0].[Id]
WHERE ([t0].[StartTime] > @p0) AND ([t0].[EndTime] < @p1)
ORDER BY [t0].[Id], [t1].[Id], [t2].[TxTypeId]',N'@p0 datetime,@p1 datetime',@p0='2009-03-01 00:00:00',@p1='2010-03-09 00:00:00'
Without eager loading, it'll be 4 times more queires.
Ref:

Sunday, March 7, 2010

GridView in GridView

There are many RAD controls on the market that can do very fancy presentation stuff. But even with the plain old ASP.NET controls, you can still exploit their interesting potentials. Embeding a GridView inside another GridView is not really difficult, but those examples I searched from Internet couldn't do me the favor. So here comes my solution.

The outer grid represents a session table of a user and each session contains a few transaction logs associated with the outer sessionId. The .aspx is straightforward: a TemplateField column is used to embed the inner GridView.
<asp:GridView ID="gvSession" CssClass="datagrid" runat="server" 
  AllowPaging="True" PageSize="6" AutoGenerateColumns="False" 
  DataSourceID="ldsSession" OnRowDataBound="gvSession_RowDataBound" Width="680px">
  <Columns>
    <asp:BoundField DataField="StaffName" HeaderText="Staff Name" />
    <asp:BoundField DataField="SessionId" HeaderText="Session Id" />
    <asp:BoundField DataField="SessionStartTime" HeaderText="Session Start Time" />
    <asp:BoundField DataField="SessionDuration" HeaderText="Session Duration" />
    <asp:TemplateField HeaderText="Tx Logs">
      <ItemTemplate >
        <asp:GridView ID="gvTxLog" runat="server" DataSourceID="ldsTxLog" AutoGenerateColumns="False" Width="100%">
          <Columns>
            <asp:BoundField DataField="TxType" HeaderText="Tx Type" ItemStyle-Width="70%" />
            <asp:BoundField DataField="Duration" HeaderText="Duration" />
          </Columns>
        </asp:GridView>
        <asp:ObjectDataSource ID="ldsTxLog" runat="server" SelectMethod="GetTxLogs" TypeName="DAO.ReportDAO">
          <SelectParameters>
            <asp:ControlParameter ControlID="txtStartDate" Name="start" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtEndDate" Name="end" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ddlStaffs" Name="staffId" PropertyName="SelectedValue" Type="Int32" />
            <asp:Parameter Name="sessionId" Type="Int32" />
          </SelectParameters>
        </asp:ObjectDataSource>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ldsSession" runat="server" SelectMethod="GetSessions" TypeName="DAO.ReportDAO">
  <SelectParameters>
    <asp:ControlParameter ControlID="txtStartDate" Name="start" PropertyName="Text" />
    <asp:ControlParameter ControlID="txtEndDate" Name="end" PropertyName="Text" />
    <asp:ControlParameter ControlID="ddlStaffs" Name="staffId" PropertyName="SelectedValue" Type="Int32" />
  </SelectParameters>
</asp:ObjectDataSource>

To make it simple, the outer grid just uses declarative data binding. While the inner grid, although appears to be the same, has to be initialized manually because, like I mentioned earlier, it relies on the sessionId from the outer grid after outer's data binding. So here is the trick:
protected void gvSession_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType .DataRow)
    {
        ObjectDataSource s = (ObjectDataSource )e.Row.FindControl("ldsTxLog" );
        s.SelectParameters["sessionId" ].DefaultValue = e.Row.Cells[1].Text;
        GridView subView = (GridView )e.Row.FindControl("gvTxLog" );
        subView.DataBind();
    }
}

This resolution is not perfect though, because you have to use 2 separate data sources, one for each GridView. And that brings the N+1 problem. But there is a better solution coming...

Control and access the binding ObjectDataSource instance programatically

The declarative data source provides a simple and transparent way of data binding for GridView. However, sometimes we need finer control of the ObjectDataSource instance that a GridView binds to. For example, it's usually easier to generate the footer in the data source rather than doing it in data bound event, like I did in this example. By accessing the ObjectDataSource instance directly, we can control when and how the binding happens as we desire.
In .aspx, we are not specifing the ObjectDataSource in GridView
<ajaxTool:TabPanel runat="server" HeaderText="Transaction Time" ID="serviceTab">
<ContentTemplate>
<asp:GridView ID="gvServiceTime" CssClass="datagrid" runat="server" ShowFooter="True"
          OnRowDataBound="gvServiceTime_RowDataBound" FooterStyle-CssClass="tfoot">
</asp:GridView>
</ContentTemplate>
</ajaxTool:TabPanel>
In .aspx.cs Page_Load, instantiate the DAO object, make the query and then assign it to GridView's DataSource. Then use this DAO object in the OnRowDataBound event to feed the footer. Prefer OnRowDataBound over OnDataBound because I want to customize the data row display and highlight some cells.
public partial class Report : System.Web.UI.Page
{
    ReportDAO _reportDao;

    protected void Page_Load(object sender, EventArgs e)
    {
        ...
        if (reportTabs.ActiveTab == serviceTab)
        {
            _reportDao = _reportDao ?? new ReportDAO();
            gvServiceTime.DataSource = _reportDao.GetServiceTimes(txtStartDate.Text, txtEndDate.Text);
            gvServiceTime.DataBind();
        }
        ...
    }

    /**
     * 1. Format each cell to mm:ss using Util.DurationAsString() rather than raw seconds
     * 2. Generate gvServiceTime footer
     */
    protected void gvServiceTime_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        e.Row.HorizontalAlign = HorizontalAlign.Right;
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int max = 0, maxsIndex = 0;
            for (int i = 1; i < e.Row.Cells.Count; i++) // i from 1 to skip the 1st col
            {   
                int duration = (int)_reportDao._report.Rows[e.Row.DataItemIndex][i];
                if (max < duration)
                {
                    max = duration;
                    maxsIndex = i;
                }
                e.Row.Cells[i].Text = Util.DurationAsString(duration);
            }
            if (maxsIndex > 0) // highlight the max cell in the row
                e.Row.Cells[maxsIndex].BackColor = System.Drawing.Color.Beige;
        }
        else if (e.Row.RowType == DataControlRowType.Footer)
        {
            e.Row.Cells[0].Text = "Average";
            for (int i = 1; i < _reportDao._footer.Length + 1; i++)
            {
                e.Row.Cells[i].Text = Util.DurationAsString(_reportDao._footer[i - 1]);
            }
        }
    }

    ...
}
This article also shows another way of adding dynamic columns in a table by using DataTable, which is far more flexible than arrays. Here is some snippet from DAO:
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("TransactionType", typeof(string)));
foreach (string staff in staffs)
{
    table.Columns.Add(new DataColumn(staff, typeof(int)));
}
table.Columns.Add(new DataColumn("Average", typeof(int)));

foreach (TransactionType transactionType in _transactionTypes) // each row
{
    DataRow row = table.NewRow();
    row["TransactionType"] = transactionType.Name;
    int total = 0;
    int n = 0;
    foreach (string staff in staffs) // each col
    {
        ServiceTime serviceTime = counts[transactionType.Name]
            .Where(r => r.Staff == staff).SingleOrDefault();
        row[staff] = serviceTime == null ? 0 : serviceTime.Count;
        total += serviceTime == null ? 0 : serviceTime.Count;
        n += serviceTime == null ? 0 : 1;
    }
    row["Average"] = (int)(total / (n == 0 ? 1 : n)); // last col is average
    table.Rows.Add(row);
}

Thursday, March 4, 2010

Dynamic column in GridView binding an array

Recently I've done some manual reporting using ASP.NET. Well, orginally I developed it easily using SQL Server 2008 Reporting Service, which is not in our production environment yet, but the client was keen to have the application deployed.
Dynamic columns is actually easy in GridView because you can bind the GridView to whatever data source with just a little more work than the simple declarative binding. For example, you can bind it to a DataTable like this. In my case, the report has fixed number of rows each of which represents a transaction type, while the columns are dynamic depending on another database table for zones. To feed this report grid, an OjbectDataSource is created which returns an array of ReportRow.
public class ReportRow
{
    public string CallType { get; set; }
    public int[] Counts { get; set; } // Counts array for each location + the last is total
}
In .aspx file, we must set AutoGenerateColumns to false. The gvImportCounts binds to an ObjectDataSource with 2 parameters for the reporting period. The OnDataBound event is to add a footer in the report.
<ajaxTool:TabPanel runat="server" ID="importTab">
  <HeaderTemplate>Import Counts</HeaderTemplate>
  <ContentTemplate>
    <asp:GridView ID="gvImportCounts" CssClass="datagrid" runat="server" AutoGenerateColumns="False"
         DataSourceID="odsImportCounts" ShowFooter="True" Width="658px"
         OnDataBound="gvImportCounts_DataBound">
      <FooterStyle CssClass="tfoot" />
    </asp:GridView>

    <asp:ObjectDataSource ID="odsImportCounts" runat="server" SelectMethod="GetImportCounts"
        TypeName="DAO.ReportDAO">
      <SelectParameters>
        <asp:ControlParameter ControlID="txtStartDate" Name="start" PropertyName="Text" />
        <asp:ControlParameter ControlID="txtEndDate" Name="end" PropertyName="Text" />
      </SelectParameters>
    </asp:ObjectDataSource>
  </ContentTemplate>
</ajaxTool:TabPanel>
The difficulty of binding to array elements, like Frdrik pointed out in his blog (Ref 1), is that the ASP.NET's standard BoundField does not provide this functionality. So one of the elegant ways is to extend it by ourselves.
In aspx.cs, add this subclass that enables us to evaluate array elements:
/**
* Extend the BoundField to bind column to composite field even array element
*/
public class CompositeBoundField : BoundField
{
    protected override object GetValue(Control controlContainer)
    {
        object item = DataBinder.GetDataItem(controlContainer);
        return DataBinder.Eval(item, this.DataField);
    }
}
The rest important code in aspx.cs:
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack) // first load
    {   // Dynamically generate columns for Import report:
        BoundField bf = new BoundField();
        bf.DataField = "Import";
        bf.HeaderText = "Transaction Type";
        gvImportCounts.Columns.Add(bf);

        var zones = ReportDAO._zones; // get zones from DAO

        for (int i = 0; i &lt; zones.Count; i++)
        {
            bf = new CompositeBoundField();
            //Initalize the DataField and HeaderText field value:
            bf.DataField = "Counts[" + i + "]"; // bind to array element
            bf.HeaderText = zones[i].Name;

            //Add the newly created bound field to the GridView:
            gvImportCounts.Columns.Add(bf);
        }
        // Add the Total column
        bf = new CompositeBoundField();
        bf.DataField = "Counts[" + zones.Count + "]";
        bf.HeaderText = "Total";
        gvImportCounts.Columns.Add(bf);
    }
}

/**
* Generate gvImportCounts footer
*/
protected void gvImportCounts_DataBound(object sender, EventArgs e)
{
    GridView grid = (GridView)sender;
    GridViewRow footer = grid.FooterRow;
    if (footer != null) // footer defined, so fill it
    {
        footer.Cells[0].Text = "Total";
        for (int i = 1; i &lt; grid.Columns.Count; i++)
        {
            int total = 0;
            foreach (GridViewRow row in grid.Rows)
            {
                if (row.RowType == DataControlRowType.DataRow)
                    total += int.Parse(row.Cells[i].Text);
            }
            footer.Cells[i].Text = total.ToString();
        }
    }
}
Here is the result: Ref:
  1. Fixing BoundField Support for Composite Objects
  2. GridView and dynamic data sources