从SqlDataReader检索INSERT语句的基础异常

Question

我正在执行一条SQL插入语句，例如：

INSERT INTO Table (fk1, value1) OUTPUT inserted.pk VALUES ('fkv1', 'v1');

其中"pk"是自增键值，如下：

SqlDataReader reader = cmd.ExecuteReader();

外键与父表冲突，reader.HasRows() 反映了这一点，但我如何检索实际的异常对象，并获取错误描述？如果删除“OUTPUT”语句，则会抛出异常，但是如果在其中使用该语句，则会吞噬错误，并且仅返回“HasRows”== false。

我可以使用调试器在“结果视图”属性下看到错误，但是我如何在代码中获取此值？

Sql Server 2008r2 .NET 4.0

感谢任何帮助。

编辑：

此调用不会引发异常。唯一表明它未成功完成的指示是“HasRows”为false。

- Sako73

---

你尝试过用try/catch块包围这段代码吗？ - Joel Etherton

请确保使用 catch(ExceptionType ex) 来获取异常的信息。 - Ry-

Answer 1

try
{
   SqlDataReader reader = cmd.ExecuteReader();
}
catch(Exception ex)
{
   string errorMessage = String.Format(CultureInfo.CurrentCulture, 
                         "Exception Type: {0}, Message: {1}{2}", 
                         ex.GetType(),
                         ex.Message,
                         ex.InnerException == null ? String.Empty : 
                         String.Format(CultureInfo.CurrentCulture,
                                      " InnerException Type: {0}, Message: {1}",
                                      ex.InnerException.GetType(),
                                      ex.InnerException.Message));

  System.Diagnostics.Debug.WriteLine(errorMessage);
}

- sll

Answer 2

我有一段 SQL 代码，它产生了异常，但 try catch 却没有捕获到它 - 这太奇怪了！

以下是代码：

try
{
    // Run the SQL statement, and then get the returned rows to the DataReader.

    SqlDataReader MyDataReader = MyCommand.ExecuteReader();

    //note AT THIS POINT there is an exception in MyDataReader
    //if I view MyDataReader in Watch I see this in base->ResultsView->base
    //Conversion failed when converting the varchar value 'lwang' to data type int
    //and errors count is 1 but there is no exception catch!!

    int iRow = 0;
    if (MyDataReader.HasRows)
    {
        int iCol = 0;
        while (MyDataReader.Read())
        {

            //dt.Load(MyDataReader);
            List<String> strFields = new List<String>();

            for (int iField = 0; iField < MyDataReader.FieldCount; iField++)
            {
                strReturn = strReturn + MyDataReader[iField] + ",";
                strFields.Add(MyDataReader[iField].ToString());
            }
            DataRows.Add(strFields);
            iRow++;
        }
    }
}
catch (Exception ex)
{
    //no exception is caught in this case!!  This code is never reached!!
    strError = "An error occurred getting the data table: " + MyCommand.CommandText + " " + ex.ToString();
    throw new Exception(strError);
    return (DataRows);
}
finally
{
    Connection.Close();
}
    return (DataRows);
}

如果有帮助的话，这里是正在执行的 SQL 语句：

select hec_recommendation.RecID, hec_recommendation.UtilityID, hec_recommendation.CatID, hec_recommendation.Condition, hec_recommendation.RecommendationText, hec_recommendation.Active, hec_recommendation.Ordinal, hec_recommendation.StartDate, hec_recommendation.EndDate, hec_recommendation.CreateDate, hec_recommendation.CreatedByID, hec_recommendation.ModifyDate, hec_recommendation.ModifyByID from hec_recommendation, hec_utility, hec_reccategory where hec_recommendation.utilityid = hec_utility.id and hec_recommendation.catid = hec_reccategory.catid and hec_reccategory.utilityid = hec_utility.utilityid and hec_utility.utilityId = 'lwang'

- Michelle Sollicito

---

如果您有问题，请发布一个问题（http://stackoverflow.com/questions/ask） :-) - kleopatra