在 C# 中获取 System.OutOfMemoryException，同时从 oracle DB 获取数据

本文关键字：获取 oracle DB 数据 System OutOfMemoryException | 更新日期: 2023-09-27 18:33:31

我想重新处理数据库中的 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.");
}

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

在 C# 中获取 System.OutOfMemoryException，同时从 oracle DB 获取数据

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

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

最好

在与 oracle 数据库交互时使用波纹管代码段。

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

你永远不会释放你的数据表。每个循环保留一个数据表并正确释放它。

这部分：

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

应为：

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