CODEWITHSUNDEEP
c#oracle-database
Kannan
Kannan

Reputation: 51

Getting System.OutOfMemoryException in C# while fetching data from oracle DB

I want to reprocess 3.5 million data from database. We are using oracle pl/sql 11g. And I tried to fetch 1000 rows per batch for reprocessing. But after processing 42000 rows it throws out of memory exception. This is my code

internal void dataReprocessor(int rowCount)
{

    DataTable dataTable = new DataTable();
    int MaxProcessid = AssetProcessorDbHandler.GetMaxProcessId();
    startCount=AssetProcessorDbHandler.GetErrorCount();
    ShowStatus("Max. processid:" + MaxProcessid);
    ShowStatus("Fetching " + rowCount + " rows....");
    do
    {

        dataTable.Clear();
        dataTable = AssetProcessorDbHandler.GetHundreadrows(rowCount, MaxProcessid);
         if (dataTable.Rows.Count > 0)
         {


             for (int i = 0; i < dataTable.Rows.Count; i++)
             {
                int error = 0;
                mainId = Convert.ToInt32(dataTable.Rows[i]["MAINID"]);
                itemId = Convert.ToInt32(dataTable.Rows[i]["ITEMID"]);
                siteid = Convert.ToInt32(dataTable.Rows[i]["SITE_ID"]);
                equipmentId = Convert.ToInt32(dataTable.Rows[i]["EQUIPMENT_TYPE"]);
                qrCode = dataTable.Rows[i]["QRCODE"].ToString();
                string equipmentname = AssetProcessorDbHandler.Getequipmentname(equipmentId);
                error = DbHandler.CheckQRCofEquipment(siteid, equipmentId, itemId, qrCode,ref assetStatus);
                if (error == 1)//Not available in iMapp.
                {
                    MESSAGE=string.Concat("QRCode ",qrCode," for ",equipmentname," is not available in iMapp!");
                    AssetProcessorDbHandler.UpdateSMSintoQRCtable(itemId, MESSAGE);
                }
                else if (error == 2)//Not available in that site.
                {
                    int mappedsite = AssetProcessorDbHandler.Getmappedsiteid(qrCode);
                    MESSAGE = string.Concat("QRCode ", qrCode, " for ", equipmentname, " is mapped with site IN-", mappedsite);
                    AssetProcessorDbHandler.UpdateSMSintoQRCtable(itemId,MESSAGE);
                }
                else if (assetStatus == 1)//Equipment was in Finance verifiaction pending
                {
                    MESSAGE = string.Concat("Finance verification pending of ", equipmentname, " for site IN-", siteid, " in iMapp. Please contact corporate finance team.");
                    AssetProcessorDbHandler.UpdateSMSintoQRCtable(itemId, MESSAGE);
                }
                 AssetProcessorDbHandler.UpdateReprocessed(MaxProcessid + 1, itemId);
            }
        }
    } while (dataTable.Rows.Count > 0);

    ShowStatus("--Completed.--");
    finalCount = AssetProcessorDbHandler.GetErrorCount();
    ShowStatus(startCount + " ROWS PROCESSED.");
    ShowStatus(finalCount + " ROWS ARE STILL ON ERROR.");

}

Anybody help me out please? The issue is escalating.

Upvotes: 3

Views: 1717

Answers (2)

Bewar Salah
Bewar Salah

Reputation: 567

We had the same issue while we are always processing millions of records per day.

The problem is we had too many connections open to oracle database and did not close and dispose the objects well.

It is better to use the bellow snippet while interacting with oracle database.

using(var oracleConnection = new OracleConnection(connectionString))
{
      oracleConnection.Open()
      //do your stuff here.

}

Upvotes: 2

nvoigt
nvoigt

Reputation: 77304

You are never disposing your datatables. Stay with one datatable per loop and dispose it properly.

This part:

do
{

    dataTable.Clear();
    dataTable = AssetProcessorDbHandler.GetHundreadrows(rowCount, MaxProcessid);
     if (dataTable.Rows.Count > 0)
     {

Should read:

while(true)
{
    using(var dataTable = AssetProcessorDbHandler.GetHundreadrows(rowCount, MaxProcessid))
    {
        if (dataTable.Rows.Count == 0) break;

Upvotes: 0

Related Questions