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:
