C#捕获OracleException与一般异常

Question

我正在更新一个先前项目的代码。我想捕捉SQL命令中的任何问题，所以我在SQL查询的开头放了一个"X“。在微软，通常SqlException会发现这样的问题。在下面的代码中，我得到的是一般异常，而不是OracleException。我是否有适当的例外，如果有，为什么不开火？

public static string getSKUByPONumber(string poNumber)
{
    Logging.logMethodStart("getSKUByPONumber poNumber=" + poNumber);

    string rt = "";
    string sql = "x select i.segment1 || '.' || i.segment2 " +
                 " from po_headers_all h " +
                 " inner join po.po_lines_all l on( h.po_header_id = l.po_header_id ) " +
                 " inner join inv.mtl_system_items_b i on( l.item_id = i.inventory_item_id ) " +
                 " where h.segment1 = '" + poNumber.Trim() + "'";

    try
    {
        rt = getOracleSingleValue(sql);
    }
    catch (OracleException ex)
    {
        string errMsg = "OracleException: getSKUByPONumber failed with error code: " + ex.Message + " Sql=" + sql;
        Logging.logHelperError("getSKUByPONumber", ex.Message);
        Logging.TraceWriteLine(errMsg); 
        throw new Exception(errMsg);
    }
    catch (Exception ex)
    {
        Logging.logHelperError("getSKUByPONumber", ex.Message);
        throw new Exception("getSKUByPONumber failed with error code: " + ex.Message + " Sql=" + sql );
    }

    Logging.logMethodEnd("getSKUByPONumber poNumber=" + poNumber + " sku=" + rt);
    return rt;
}

经修订的守则：

    public static string getOracleSingleValue(string sql)
    {
        OracleConnection conn = Config.GetOracleSqlconnection();
        string rt = "";

        try
        {
            DataSet ds = new DataSet();
            OracleDataAdapter adapter = new OracleDataAdapter(sql, conn);
            OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

            adapter.Fill(ds, "data");

            if (ds.Tables["data"].Rows.Count > 0)
            {
                DataRow row = ds.Tables["data"].Rows[0];
                rt = row[0].ToString();
            }
        }
        catch (SqlException ex)
        {
            Logging.logHelperError("getOracleSingleValue: SqlException:", ex.Message);
            throw new Exception("getOracleSingleValue failed with SqlException: " + ex.Message);
        }
        catch (Exception ex)
        {
            Logging.logHelperError("getOracleSingleValue", ex.Message);
            throw new Exception("getOracleSingleValue failed with error code: " + ex.Message);
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }

        return rt;
    }



public static string getSKUByPONumber(string poNumber)
{
    Logging.logMethodStart("getSKUByPONumber poNumber=" + poNumber);

    string rt = "";
    string sql = "x select i.segment1 || '.' || i.segment2 " +
                 " from po_headers_all h " +
                 " inner join po.po_lines_all l on( h.po_header_id = l.po_header_id ) " +
                 " inner join inv.mtl_system_items_b i on( l.item_id = i.inventory_item_id ) " +
                 " where h.segment1 = '" + poNumber.Trim() + "'";

    try
    {
        rt = getOracleSingleValue(sql);
    }
        catch (SqlException ex)
        {
            string errMsg = "SqlException: getDropShipFlag failed with error code: " + ex.Message + " sql=" + sql;
            Logging.TraceWriteLine(errMsg);
            Logging.logHelperError("getDropShipFlag", ex.Message);
            throw new Exception(errMsg);
        }
        catch (Exception ex)
        {
            string innerExMessage = ""; 
            if (ex.InnerException != null)
            {
                innerExMessage = ex.Message + " Internal Exception:" + ex.InnerException; 
            }
            Logging.logHelperError("getDropShipFlag", ex.Message + innerExMessage);
            throw new Exception("Exception: getDropShipFlag failed with error code: " + ex.Message);
        }

    Logging.logMethodEnd("getSKUByPONumber poNumber=" + poNumber + " sku=" + rt);
    return rt;
}

最后一个错误是：

{“异常: getSKUByPONumberfailed与错误代码: getOracleSingleValue失败与错误代码: ORA-00900:无效SQL语句”}

Answer 1

问题在于，您正在调用自己的getOracleSingleValue()例程。这个例程必须抛出一个异常，以便捕获任何异常。这看起来像是我怀疑的自定义(非Oracle)函数。看看那个。