System.OutOfMemoryException occured while fetching data from ORACLE database.

Question

Hello Friends,

Urgent help required...

I am developing a Web Application using ASP.Net 2.0 and C#, my data base is ORACLE 9i. Previously everything was going right, but now as the data increases and the retrieving is become headache for me. Previously i was showing this data in the third party grid i.e. FARPOINT Spread. It happens with the spread too. so i write a program to convert the Generic List into excel sheet. This works fine on local (XP Professional 2 GB RAM and all) but once i deployed this application on the server (Win server 2003 12 GB RAM with 3GB Virtual Memory set) it gives me error that 'System.OutOfMemoryException'.

I checked that data is coming till UI layer from Business layer but still why it is throwing error. Couldn't make it out. And i need solution for this ASAP.

Thanks in advance.

Code to write excel sheet -

private void FpLoadEditData(ArrayList userName, ArrayList siteId, string from, string to)

{

oXL = new Application();

oXL.Visible = false;

//Get a new workbook.

oWB = (_Workbook)(oXL.Workbooks.Add(System.Reflection.Missing.Value));

oSheet = (_Worksheet)oWB.ActiveSheet;

//System.Data.DataTable dtGridData = ds.Tables[0];

int iRow = 2;

// Setting Column name for Sheet

int colCount = QiSpread.ActiveSheetView.Columns.Count;

int colHeaderIndex = 1;

for (int j = 0; j < colCount; j++)

{

oSheet.Cells[1, j + 1] = ConfigurationSettings.AppSettings["colHead" + colHeaderIndex];

colHeaderIndex++;

}

for (int rowNo = 0; rowNo < vzmLst.Count; rowNo++)

{

//if (rowNo < 150)

//{

for (int colNo = 0; colNo < QiSpread.ActiveSheetView.ColumnCount; colNo++)

{

PopulateSheet(oSheet, iRow, colNo, vzmLst[rowNo]);

}

iRow++;

//}

//else

// rowNo = vzmLst.Count;

}

if (vzmLst.Count > 6000)

Session.Timeout = 15;

oRng = oSheet.get_Range("A1", "IV1");

oRng.EntireColumn.AutoFit();

oXL.Visible = false;

oXL.UserControl = false;

string strFile = string.Empty;

if (Request.Params.Get("__EVENTARGUMENT") != null &&

Request.Params.Get("__EVENTARGUMENT").ToString().ToUpper().Equals("EDITDATA"))

{

strFile = "VzMEditData_" + loginName + from + "TO" + to + "_" + DateTime.Now.Second.ToString() + ".xls";

//strFile = "VzMEditData" + from, to DateTime.Now.Ticks.ToString() + ".xls";

}

else if (Request.Params.Get("__EVENTARGUMENT") != null &&

Request.Params.Get("__EVENTARGUMENT").ToString().ToUpper().Equals("VIEWDATA"))

{

strFile = "VzMViewData_" + loginName + from + "TO" + to + "_" + DateTime.Now.Second.ToString() + ".xls";

}

//string strFile = "report" + DateTime.Now.Ticks.ToString() + ".xls";//+

oWB.SaveAs(strCurrentDir + strFile, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlShared, false, false, null, null, null);

// Need all following code to clean up and remove all references!!!

oWB.Close(null, null, null);

oXL.Workbooks.Close();

oXL.Quit();

Marshal.ReleaseComObject(oRng);

Marshal.ReleaseComObject(oXL);

Marshal.ReleaseComObject(oSheet);

Marshal.ReleaseComObject(oWB);

Session.Add("fileUrl", strFile);

}

 

To display the generated excel -

 private void GetExcel()

{

if (Session["fileUrl"] != null && Session["fileUrl"].ToString() != string.Empty)

{

Response.ContentType = "application/vnd.ms-excel";

Response.AppendHeader("Content-Disposition", "attachment; filename=" + Session["fileUrl"].ToString());

Response.TransmitFile(Server.MapPath(".") + "\\EditViewDataFiles\\" + Session["fileUrl"].ToString());

HttpContext.Current.ApplicationInstance.CompleteRequest();

Response.Flush();

Response.Close();

}

}

 

Regards,

Abhijeet