c# - 从 Oracle DB 获取数据时在 C# 中获取 System.OutOfMemoryException

Question

我想从数据库中重新处理 350 万条数据。我们正在使用 oracle pl/sql 11g。我尝试每批获取 1000 行进行再处理。但在处理 42000 行后，它抛出内存异常。这是我的代码

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.");

}

有人帮帮我吗？问题正在升级。

Answer 1

我们每天处理数百万条记录时遇到了同样的问题。

问题是我们打开了太多到 oracle 数据库的连接，并且没有关闭和处理好对象。

在与 oracle 数据库交互时最好使用下面的代码段。

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

}