This example demonstrates how to export a large amount of filtered data using LINQ-to-SQL.
Implementation Details
When you export a large amount of filtered data, Grid View export mechanism requests all data from a database and then filters it. Such a request can be fulfilled and the System.OutOfMemoryException
exception can occur. To resolve this issue, request filtered data and export it using the XtraReports Suite.
C#public ActionResult ExportTo(string filterString) {
CriteriaOperator filterCriteria = CriteriaOperator.Parse(filterString);
IList<Email> emails = LargeDatabaseDataProvider.GetEmailsByFilter(filterCriteria);
XtraReport1 report = new XtraReport1();
report.DataSource = emails;
ExportReport(report, "largeData", true, "Xlsx");
return GridViewPartial();
}
To pass the FilterExpression
to a controller's action, obtain and save the expression in a custom client property:
C#settings.CustomJSProperties = (s, e) => {
MVCxGridView gridView = (MVCxGridView)s;
e.Properties["cpGridFilterExpression"] = gridView.FilterExpression;
};
Codesettings.CustomJSProperties = Sub(s, e)
Dim gridView = TryCast(s, MVCxGridView)
e.Properties("cpGridFilterExpression") = gridView.FilterExpression
End Sub
When a user clicks the Export to XLSx button, the Click
event handler adds filter expression contained in the custom client property to the collection of jQuery selector parameters.
JavaScriptfunction onClick(s, e) {
var actionParams = $("form").attr("action").split("?filterString=");
actionParams[1] = encodeURIComponent(GridView.cpGridFilterExpression);
$("form").attr("action", actionParams.join("?filterString="));
}
Files to Review
- HomeController.cs (VB: HomeController.vb)
- GridViewPartial.cshtml (VB: GridViewPartial.vbhtml)
- Index.cshtml (VB: Index.vbhtml)
Documentation
More Examples
Example Code
C#using DevExpress.Data.Filtering;
using DevExpress.Web.Mvc;
using DevExpress.XtraReports.UI;
using E4121.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace E4121.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
EmailDataGenerator.Register();
return View();
}
[ValidateInput(false)]
public ActionResult GridViewPartial()
{
return PartialView("_GridViewPartial");
}
public ActionResult ExportTo(string filterString)
{
CriteriaOperator filterCriteria = CriteriaOperator.Parse(filterString);
IList<Email> emails = LargeDatabaseDataProvider.GetEmailsByFilter(filterCriteria);
XtraReport1 report = new XtraReport1();
report.DataSource = emails;
ExportReport(report, "largeData", true, "Xlsx");
return GridViewPartial();
}
public void ExportReport(XtraReport report, string fileName, bool saveAsFile, string fileFormat)
{
using (MemoryStream stream = new MemoryStream())
{
report.ExportToXlsx(stream);
string disposition = saveAsFile ? "attachment" : "inline";
Response.Clear();
Response.Buffer = false;
Response.AppendHeader("Content-Type", string.Format("application/{0}", fileFormat));
Response.AppendHeader("Content-Transfer-Encoding", "binary");
Response.AppendHeader("Content-Disposition", string.Format("{0}; filename={1}.{2}", disposition, HttpUtility.UrlEncode(fileName).Replace("+", "%20"), fileFormat));
Response.BinaryWrite(stream.ToArray());
Response.End();
}
}
}
}
Visual BasicImports System.IO
Imports DevExpress.Data.Filtering
Imports DevExpress.Web.Mvc
Imports DevExpress.XtraReports.UI
Public Class HomeController
Inherits System.Web.Mvc.Controller
Function Index() As ActionResult
EmailDataGenerator.Register()
Return View()
End Function
<ValidateInput(False)>
Public Function GridViewPartial() As ActionResult
Return PartialView("_GridViewPartial")
End Function
Public Function ExportTo(ByVal filterString As String) As ActionResult
Dim filterCriteria As CriteriaOperator = CriteriaOperator.Parse(filterString)
Dim emails As IList(Of Email) = LargeDatabaseDataProvider.GetEmailsByFilter(filterCriteria)
Dim report As XtraReport1 = New XtraReport1()
report.DataSource = emails
ExportReport(report, "largeData", True, "Xlsx")
Return GridViewPartial()
End Function
Public Sub ExportReport(ByVal report As XtraReport, ByVal fileName As String, ByVal saveAsFile As Boolean, ByVal fileFormat As String)
Using stream As MemoryStream = New MemoryStream()
report.ExportToXlsx(stream)
Dim disposition As String = If(saveAsFile, "attachment", "inline")
Response.Clear()
Response.Buffer = False
Response.AppendHeader("Content-Type", String.Format("application/{0}", fileFormat))
Response.AppendHeader("Content-Transfer-Encoding", "binary")
Response.AppendHeader("Content-Disposition", String.Format("{0}; filename={1}.{2}", disposition, HttpUtility.UrlEncode(fileName).Replace("+", "%20"), fileFormat))
Response.BinaryWrite(stream.ToArray())
Response.End()
End Using
End Sub
End Class
Razor@{
var grid = Html.DevExpress().GridView<E4121.Models.Email>(settings => {
settings.Name = "GridView";
settings.Width = Unit.Percentage(100);
settings.CallbackRouteValues = new { Controller = "Home", Action = "GridViewPartial" };
settings.KeyFields(m => m.ID);
settings.Settings.ShowFilterRow = true;
settings.Columns.Add(m => m.From).Width = Unit.Pixel(200);
settings.Columns.Add(m => m.Subject);
settings.Columns.Add(m => m.Sent, column =>
{
column.ColumnType = MVCxGridViewColumnType.DateEdit;
column.Settings.AutoFilterCondition = AutoFilterCondition.Equals;
column.Width = Unit.Pixel(100);
column.Settings.AllowHeaderFilter = DefaultBoolean.True;
column.SettingsHeaderFilter.Mode = GridHeaderFilterMode.DateRangePicker;
column.Settings.AllowAutoFilter = DefaultBoolean.False;
});
settings.Columns.Add("Size").Settings.AutoFilterCondition = AutoFilterCondition.Equals;
settings.Columns.Add(m => m.HasAttachment, column =>
{
column.Caption = "Attachment?";
column.ColumnType = MVCxGridViewColumnType.CheckBox;
column.Width = Unit.Pixel(100);
});
settings.CustomJSProperties = (s, e) =>
{
MVCxGridView gridView = (MVCxGridView)s;
e.Properties["cpGridFilterExpression"] = gridView.FilterExpression;
};
});
}
@grid.BindToLINQ("E4121.Models.LargeDatabaseDataContext", "Emails").GetHtml()
Code@Code
Dim grid = Html.DevExpress().
GridView(Of E4121VB.Email)(Sub(settings)
settings.Name = "GridView"
settings.Width = Unit.Percentage(100)
settings.CallbackRouteValues = New With {Key .Controller = "Home", Key .Action = "GridViewPartial"}
settings.KeyFields(Function(m) m.ID)
settings.Settings.ShowFilterRow = True
settings.Columns.Add(Function(m) m.From).Width = Unit.Pixel(200)
settings.Columns.Add(Function(m) m.Subject)
settings.Columns.Add(Function(m) m.Sent, Sub(column)
column.ColumnType = MVCxGridViewColumnType.DateEdit
column.Settings.AutoFilterCondition = AutoFilterCondition.Equals
column.Width = Unit.Pixel(100)
column.Settings.AllowHeaderFilter = DefaultBoolean.True
column.SettingsHeaderFilter.Mode = GridHeaderFilterMode.DateRangePicker
column.Settings.AllowAutoFilter = DefaultBoolean.False
End Sub)
settings.Columns.Add(Function(m) m.Size).Settings.AutoFilterCondition = AutoFilterCondition.Equals
settings.Columns.Add(Function(m) m.HasAttachment, Sub(column)
column.Caption = "Attachment?"
column.ColumnType = MVCxGridViewColumnType.CheckBox
column.Width = Unit.Pixel(100)
End Sub)
settings.CustomJSProperties = Sub(s, e)
Dim gridView = TryCast(s, MVCxGridView)
e.Properties("cpGridFilterExpression") = gridView.FilterExpression
End Sub
End Sub)
End Code
@grid.BindToLINQ("E4121VB.LargeDatabaseDataContext", "Emails").GetHtml()
Razor@using E4121.Models
@{
ViewBag.Title = "Home Page";
}
@if (!DatabaseGenerator.IsReady("GeneratedEmailTable"))
{
@Html.Partial("_CreateDatabasePartial", "GeneratedEmailTable")
}
else
{
<script type="text/javascript">
function onClick(s, e) {
var actionParams = $("form").attr("action").split("?filterString=");
actionParams[1] = encodeURIComponent(GridView.cpGridFilterExpression);
$("form").attr("action", actionParams.join("?filterString="));
}
</script>
using (Html.BeginForm(new { Controller = "Home", Action = "ExportTo" }))
{
Html.DevExpress().Button(btn =>
{
btn.Name = "btnExportToXLSX";
btn.Text = "Export to XLSx";
btn.UseSubmitBehavior = true;
btn.ClientSideEvents.Click = "onClick";
}).Render();
<br />
Html.RenderAction("GridViewPartial");
}
}
Code@Code
ViewData("Title") = "Home Page"
End Code
@Code If Not E4121VB.DatabaseGenerator.IsReady("GeneratedEmailTable") Then
@Html.Partial("_CreateDatabasePartial", "GeneratedEmailTable")
Else
End Code
<script type="text/javascript">
function onClick(s, e) {
var actionParams = $("form").attr("action").split("?filterString=");
actionParams[1] = encodeURIComponent(GridView.cpGridFilterExpression);
$("form").attr("action", actionParams.join("?filterString="));
}
</script>
@Using (Html.BeginForm(New With {.Controller = "Home", .Action = "ExportTo"}))
Html.DevExpress().Button(Sub(btn)
btn.Name = "btnExportToXLSX"
btn.Text = "Export to XLSx"
btn.UseSubmitBehavior = True
btn.ClientSideEvents.Click = "onClick"
End Sub).Render()
ViewContext.Writer.Write("<br />")
Html.RenderAction("GridViewPartial")
End Using
@Code
End If
End Code