“指定的演员表无效"；从OracleDataAdapter.Fill()填充DataTable时 [英] "Specified cast is not valid" when populating DataTable from OracleDataAdapter.Fill()

问题描述

我似乎在Google(或StackOverflow)上的任何地方都找不到这个问题，这确实让我感到惊讶，因此，我将其放在此处以帮助处于相同情况的其他人.

I can't seem to find this question anywhere on Google (or StackOverflow), which really surprised me, so I'm putting it on here to help others in the same situation.

我有一个在Oracle Sql Developer上运行良好的SQL查询，但是当我使用adapter.Fill(table)通过C#运行它以获取结果时，却出现了Specified cast is not valid错误(System.InvalidCastException).

I have a SQL query which runs fine on Oracle Sql Developer, but when I run it through C# usingadapter.Fill(table) to get the results, I get Specified cast is not valid errors (System.InvalidCastException).

这是C#代码的简化版本:

Here is cut-down version of the C# code:

var resultsTable = new DataTable();

using (var adapter = new OracleDataAdapter(cmd))
{
    var rows = adapter.Fill(resultsTable);  // exception thrown here, but sql runs fine on Sql Dev

    return resultsTable;
}

这是SQL的简化版本:

And here is a simplified version of the SQL:

SELECT acct_no, market_value/mv_total
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0

如果删除除法子句，则不会出错-因此它是特定于此的.但是，market_value和mv_total的类型均为Number(19,4)，我可以看到Oracle适配器期望使用小数，因此正在进行什么强制转换?为什么它可以在SqlDev上运行但不能在C#中运行?

If I remove the division clause, it doesn't error - so it's specific to that. However, both market_value and mv_total are of type Number(19,4) and I can see that the Oracle adapter is expecting a decimal, so what cast is taking place? Why does it work on SqlDev but not in C#?

推荐答案

回答我自己的问题:

因此，似乎Oracle数字类型可以比C#十进制类型保留更多的小数位，并且如果Oracle试图返回的数字超过C#可以容纳的位数，则会抛出InvalidCastException.

So it seems that the Oracle number type can hold many more decimal places than the C# decimal type and if Oracle is trying to return more than C# can hold, it throws the InvalidCastException.

解决方案?

在您的sql中，将可能有太多小数位的结果四舍五入为明智的结果.所以我这样做了:

In your sql, round any results that might have too many decimal places to something sensible. So I did this:

SELECT acct_no, ROUND(market_value/mv_total, 8)  -- rounding this division solves the problem
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0

它奏效了.

收获是:Oracle数字类型与C#十进制不兼容.限制您的Oracle小数位，以避免无效的强制转换异常.

The take away is: Incompatibility between Oracle number type and C# decimal. Restrict your Oracle decimal places to avoid the invalid cast exceptions.

希望这对其他人有帮助！

Hope this helps someone else!

这篇关于“指定的演员表无效"；从OracleDataAdapter.Fill()填充DataTable时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！