jqGrid Report
Overview
A jQuery-powered gridview framework using the popular jqGrid plugin, jQuery ThemeRoller and a custom C# wrapper to deliver OData reports with style and ease.
Namespaces
This sample requires the following namespaces:
using ExigoOData; using Exigo.WebControls; using System.Data.Services.Client; using System.Text; using System.Linq.Expressions; using System.Reflection; using System.ComponentModel;
Exigo API Authentication
This sample accesses OData using the ExigoContext object:
public ExigoContext ExigoOData
{
get
{
var context = new ExigoOData.ExigoContext(new Uri("http://api.exigo.com/4.0/" + exigoAPICompany + "/model"));
context.IgnoreMissingProperties = true;
var credentials = Convert.ToBase64String(Encoding.ASCII.GetBytes(exigoAPILoginName + ":" + exigoAPIPassword));
context.SendingRequest +=
(object s, SendingRequestEventArgs e) =>
e.RequestHeaders.Add("Authorization", "Basic " + credentials);
return context;
}
}
jQuery
We use jQuery for easier Ajax calls and theming.
<link href="<%=this.ResolveUrl("../../Themes/start/jquery-ui.custom.css") %>" rel="stylesheet" type="text/css" />
<script src="<%=this.ResolveUrl("../../Scripts/jquery.min.js") %>" type="text/javascript"></script>
<script src="<%=this.ResolveUrl("../../Scripts/jquery-ui.min.js") %>" type="text/javascript"></script>
jqGrid GridView Framework
We are also using jqGrid (http://trirand.com/blog/jqgrid/jqgrid.html) to display our data.
<link href="<%=this.ResolveUrl("../../Plugins/jquery.jqGrid/css/ui.jqgrid.css") %>" rel="stylesheet" type="text/css" />
<script src="<%=this.ResolveUrl("../../Plugins/jquery.jqGrid/js/i18n/grid.locale-en.js") %>" type="text/javascript"></script>
<script src="<%=this.ResolveUrl("../../Plugins/jquery.jqGrid/js/jquery.jqGrid.min.js") %>" type="text/javascript"></script>
With our custom C# wrapper, rendering a jqGrid is as easy as using the standard ASP.Net GridView object:
<exigo:jqGrid ID="ReportGrid" runat="server" Width="818" Height="251" JSONDataSource="jqGridEnrollerReport.aspx?report=data">
<Settings AutoScaleColumnWidthsOnLoad="true" InitialSortField="CustomerID" InitialSortDirection="Ascending" LoadingText="Loading..." />
<Columns>
<exigo:jqGridColumn Name="ID" DataField="CustomerID" ColumnHeader="ID" Width="80" PrimaryKey="true" Frozen="true" Formatter="Integer" />
<exigo:jqGridColumn Name="FirstName" DataField="Customer.FirstName" ColumnHeader="First Name" Width="80" Frozen="true" />
<exigo:jqGridColumn Name="LastName" DataField="Customer.LastName" ColumnHeader="Last Name" Width="80" Frozen="true" />
<exigo:jqGridColumn Name="Level" DataField="Level" ColumnHeader="Level" Width="70" />
<exigo:jqGridColumn Name="Email" DataField="Customer.Email" ColumnHeader="Email" Width="170" Formatter="Email" />
<exigo:jqGridColumn Name="Phone" DataField="Customer.Phone" ColumnHeader="Phone" Width="90" />
<exigo:jqGridColumn Name="CreatedDate" DataField="Customer.CreatedDate" ColumnHeader="Enrollment Date" Width="120" Align="Right" Formatter="Date">
<Format DateFormat="ShortDate" />
</exigo:jqGridColumn>
</Columns>
</exigo:jqGrid>
Rendering Report Data
On the server-side, we have overridden the Page's Render method to handle page requests differently if the query string parameter "report" exists.
protected override void Render(HtmlTextWriter writer)
{
if (!string.IsNullOrEmpty(Request.QueryString["report"]))
{
Response.Clear();
switch (Request.QueryString["report"])
{
case "data": RenderReportGridJSON(writer); break;
default: writer.Write("Invalid Report Key"); break;
}
Response.End();
}
else
base.Render(writer);
}
Fetching Report Data
To fetch our report data, we are breaking our OData query into two methods. The ReportDataQuery method prepares our data query, and returns an IQueryable object. This method is responsible for establishing what OData query we are calling from, and applying any sorting and searchin parameters passed from the grid.
private IQueryable<EnrollerNode> ReportDataQuery()
{
// Base Query
var query = ExigoOData.EnrollerTree
.Where(c => c.TopCustomerID == customerID)
.Where(c => c.EnrollerID == customerID);
// Sorting and Searching
if (!string.IsNullOrEmpty(ReportGridParameters.SortFieldName)) query = query.OrderBy(ReportGridParameters.SortFieldName, ReportGridParameters.SortDirection.ToString());
if (!string.IsNullOrEmpty(ReportGridParameters.SearchCriteria)) query = query.Where(ReportGridParameters.SearchFieldName, ReportGridParameters.SearchCriteria);
// Now that we have a finished query, set up the total record count in our parameters object
ReportGridParameters.SetTotalRowCount(query.Count());
// Return the base query
return query;
}
The FetchReportData method takes the IQueryable object returned from the ReportDataQuery, and selects the desired fields from the query. We also handle pagination here by deciding how many records to skip and take.
private IQueryable FetchReportData()
{
// Define your report's fields
return ReportDataQuery().Select(c => new
{
c.CustomerID,
c.Customer.FirstName,
c.Customer.LastName,
c.Level,
c.Customer.Email,
c.Customer.Phone,
c.Customer.CreatedDate
})
.Skip((ReportGridParameters.RequestedPage - 1) * ReportGridParameters.RequestedPageSize)
.Take(ReportGridParameters.RequestedPageSize);
}
Finally, the RenderReportGridJSON method renders the JSON requested by the jqGrid to the response:
private void RenderReportGridJSON(HtmlTextWriter writer)
{
jqGridDataSource datasource = new jqGridDataSource(ReportGrid, FetchReportData(), ReportGridParameters);
writer.Write(datasource.ToJSON());
}
Custom LINQ Extensions
We created some custom LINQ extensions to help us sort and filter our OData queries.The following custom extensions help us order our OData queries using strings rather than explicitly-casted objects:
public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property, string method)
{
string methodName = string.Empty;
switch (method)
{
case "asc": methodName = "OrderBy"; break;
case "desc": methodName = "OrderByDescending"; break;
}
return ApplyOrder<T>(source, property, methodName);
}
private static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, string methodType)
{
string[] props = property.Split('.');
Type type = typeof(T);
ParameterExpression arg = Expression.Parameter(type, "x");
Expression expr = arg;
foreach (string prop in props)
{
// use reflection (not ComponentModel) to mirror LINQ
PropertyInfo pi = type.GetProperty(prop);
expr = Expression.Property(expr, pi);
type = pi.PropertyType;
}
Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);
object result = typeof(Queryable).GetMethods().Single(
method => method.Name == methodType
&& method.IsGenericMethodDefinition
&& method.GetGenericArguments().Length == 2
&& method.GetParameters().Length == 2)
.MakeGenericMethod(typeof(T), type)
.Invoke(null, new object[] { source, lambda });
return (IOrderedQueryable<T>)result;
}
The following custom extension allows us to filter our OData queries using strings rather than explicitly-casted objects:
public static IQueryable<T> Where<T>(this IQueryable<T> source, string property, object value)
{
var param = Expression.Parameter(typeof(T));
string[] props = property.Split('.');
Type type = typeof(T);
ParameterExpression arg = Expression.Parameter(type, "x");
Expression leftExpression = arg;
foreach (string prop in props)
{
// use reflection (not ComponentModel) to mirror LINQ
PropertyInfo pi = type.GetProperty(prop);
leftExpression = Expression.Property(leftExpression, pi);
type = pi.PropertyType;
}
dynamic typedValue = Convert.ChangeType(value, type);
BinaryExpression condition = Expression.Equal(leftExpression, Expression.Constant(typedValue));
return source.Where(Expression.Lambda<Func<T, bool>>(condition, param));
}