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:
PeopleSearch
