通过OracleDataReader读取时是否处理NULL值? [英] Handle NULL values when reading through OracleDataReader?

问题描述

我正在开发我的第一个ASP.Net应用程序，似乎遇到了很多障碍(主要是WinForms中的背景，最近有一个MVC5项目在我的掌控之下).

I'm working on my first ASP.Net application, and seem to be hitting a lot of hurdles (background primarily in WinForms with a recent MVC5 project under my belt).

我已经成功地使用OracleCommand建立了数据库连接并执行了查询，但是当我尝试读取这些行时，我在odr.GetDecimal(1)的第二行上得到了Column contains NULL value.任何人都知道在读取OracleDataReader时如何处理空值吗?

I am successfully making my DB connection using OracleCommand and executing my query, but when I try reading through the rows I am getting a Column contains NULL value on the second row for odr.GetDecimal(1). Anyone know how to handle null values when reading through an OracleDataReader?

下面是我的代码:

        List<YearsOfService> yearsOfService = new List<YearsOfService>();
        string SQL = "SELECT SCHOOL_YEAR as YEAR, " +
                            "TOTAL_SERVICE_CREDIT as ServiceCredited, " +
                            "RETIREMENT_SALARY as Salary, " +
                            "SOURCE_VALUE as CoveredEmployer " +
                     "FROM " + Schema + ".RANDOM_ORACLE_TABLE a " +
                     "WHERE MEMBER_ACCOUNT_ID = :memberAccountId";

        DbConnection dbc = new DbConnection();
        OracleCommand cmd = dbc.GetCommand(SQL);
        cmd.Parameters.Add(new OracleParameter("memberAccountId", memberAccountId));
        OracleDataReader odr = cmd.ExecuteReader();

        int counter = 0;
        if (odr.HasRows)
        {
            while (odr.Read())
            {
                YearsOfService yos = new YearsOfService();
                yos.Year = odr.GetInt16(0);
                yos.ServiceCredited = odr.GetDecimal(1); // Error on Second Pass

                yos.Salary = odr.GetDecimal(2);

                yos.CoveredEmployer = odr.GetString(3);

                yearsOfService.Add(yos);
                counter++;
            }
        }

        return yearsOfService;
    }

我曾考虑过对NULL进行简单检查，如果这样，将其替换为0(因为期望一个Decimal值)可以解决以下问题，但是没有运气.相同的错误:yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)) ? 0 : odr.GetDecimal(1);.

I had thought a simple check for NULL and if so replace with 0 (since expecting a Decimal value) would work with the following, but no luck. Same error: yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)) ? 0 : odr.GetDecimal(1);.

完整错误是:

Oracle.DataAccess.dll中发生了'System.InvalidCastException'类型的异常，但未在用户代码中处理

其他信息:该列包含NULL数据

我已确认返回的2行采用以下格式:

I have confirmed that my 2 rows being returned are in the following format:

Year|CreditedService|Salary  |CoveredEmployer
2013|0.70128        |34949.66|ER
2014|NULL           | 2213.99|NULL

任何人都建议如何最好地进行?通过OracleDataReader读取时应该如何处理接收NULL值?

Anyone have advice on how best to proceed? How should I handle receiving the NULL value when reading through my OracleDataReader?

推荐答案

yos.ServiceCredited = odr.IsDBNull(1) ? 0 : odr.GetDecimal(1);

OracleDataReader提供了IsDBNull()方法.

以及 要求我们这样做

在调用此方法之前，调用IsDBNull以检查是否为空值.

Call IsDBNull to check for null values before calling this method.

这篇关于通过OracleDataReader读取时是否处理NULL值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！