For our Project, from C# we execute an Oracle SQL Query and store the result of the query in the output file (CSV or TXT or XML file). Since the number of records are huge (like 2,848,489 records) for some of the files, it takes more time to finish creating the Output file. So, I tried fetching the data in ranges(row limits) to a
datatable
in a
dataset
and write the corresponding data to an Output file in a loop. For each iteration, I clear the DataTable once the data from it is written into the output file, and then load the new set of data.
But, in this method (code part provided below), based on the row limits in the loop, the data load into a particular iteration of the DataTable fails with:
'
System.OutOfMemoryException' error as the CPU and Memory are fully utilised. How to avoid this error? Is there any alternate method for creating the output file without memory error?
What I have tried:
This is code part from the Windows Application I created :
private void button2_Click(object sender, EventArgs e)
{
mOraAuth = "Persist Security Info=False;Data Source=" + mOraDataSource.ToString();
mOraAuth = mOraAuth + ";User Id=" + mOraUserID;
mOraAuth = mOraAuth + ";Password=";
mOraAuth = mOraAuth + mOraPassword.ToString() + ";";
maxRows = 1000;
Conn = new OracleConnection(mOraAuth.ToString());
Conn.Open();
DataSet ds = new DataSet();
MainQuery = "SELECT * FROM t_ic_efeed_wsstandard WHERE JOBFILENAME = 'C416162_GE_WSSTANDARD_20150901.csv' ORDER BY confdate, part_start";
OracleDataAdapter adapter = new OracleDataAdapter(MainQuery, Conn);
string Count_Query = "SELECT Count(*) FROM (" + MainQuery + ")";
OracleCommand Command = new OracleCommand(Count_Query, Conn);
Command.CommandType = CommandType.Text;
OracleDataReader Reader = Command.ExecuteReader();
while (Reader.Read())
{
TotalRows = Reader[0].ToString();
}
LoopCount = Convert.ToInt32(TotalRows) / maxRows + 1;
string DataTableName = "MyTable";
ds.Tables.Add(DataTableName);
for (DTLoop = 0; DTLoop < LoopCount; DTLoop++)
{
if (DTLoop == 0) CurrRow = 1;
adapter.Fill(ds, CurrRow, (CurrRow + maxRows - 1), DataTableName);
CurrRow = CurrRow + maxRows;
CreateCSVFile(ds.Tables[0], AppDomain.CurrentDomain.BaseDirectory + "\\C416162_GE_WSSTANDARD_20150901.csv");
ds.Tables[0].Clear();
ds.Tables[0].Dispose();
}
ds.Clear();
ds.Dispose();
Conn.Close();
}