I am building a sample mvc application. I am trying to export search results to excel. I stored my search results in a session and export that session list to excel. It was working fine when I was playing around with small database that has only 2000 rows. Now I have around 300K rows in my database. Searching the results and exporting to excel still working fine. But my application slowed down drastically.
When I comment out below code my application runs fine again, as soon as I uncomment it will slowed down.
What would be the best way to do it when there is large database?
I read post about closedxml, but not really sure how to implement it. Do I need to create store procedure if I go that route? Or is there any better way.
Please suggests.
Below is my controller codes:
What I have tried:
public class APPET1Controller : Controller
{
private APContext db = new APContext();
public ActionResult Index(string search, string cagecode, string sortBy,
int? page, string docNumber, string remark, string status)
{
APPETViewModel viewModel = new APPETViewModel();
var aPPET1 = db.APPET1.Include(t =>T.APPETMedia)
.Include(t => t.Status)
.Include(t => t.APPETCCode)
.Include(t => t.APPETDType);
............some more codes..............
DateTime searchDate;
if (!String.IsNullOrEmpty(search))
{
bool isDateSearch = DateTime.TryParse(search, out searchDate);
if (isDateSearch)
{
aPPET1 = aPPET1.Where(s => s.Date_Received == searchDate);
}
else
{
aPPET1 = aPPET1.Where(t.Doc_Number.Contains(search)
|| t.Status.Status1.Contains(search)
|| t.Remark.Contains(search)
|| t.CCode.Contains(search));
..............some more codes..............
viewModel.Search = search;
}
}
var stats = db.Status.Select(s => s.Status1);
viewModel.Statuses = new SelectList(stats);
{
if (!String.IsNullOrEmpty(status))
{
aPPET1 = aPPET1.Where(t => t.Status.Status1.Contains(status));
}
}
if (!String.IsNullOrEmpty(docNumber))
{
aPPET1 = aPPET1.Where(t => t.Doc_Number.Contains(docNumber));
}
if (!String.IsNullOrEmpty(remark))
{
aPPET1 = aPPET1.Where(t => t.Remark.Contains(remark));
}
............some more codes.........
Session["SearchResults"] = aPPET.ToList<APPET>();
return View(viewModel);
}
public ActionResult ExportToExcel()
{
var AppetList= (List<APPET1>)Session["SearchResults"];
var result = from r in AppetList
select new
{
DocNumber = r.DocNum,
Status = r.Status.Status1,
Remark = r.Remark
-----some more codes------
};
GridView gv = new GridView();
gv.DataSource = result.ToList();
gv.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=TaciList.xls");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
return RedirectToAction("Index");