如何获取使用 ExecuteNonQuery() 时发生的错误消息？

Question

我正在以这种方式执行命令：

var Command = new SqlCommand(cmdText, Connection, tr);

Command.ExecuteNonQuery();

命令中出现错误，但是 .NET 不会抛出任何错误消息。我怎么知道命令没有正确执行，以及如何获取异常？

Answer 1

仅当错误的严重性为 16 或更高时，您才会在 C# 中收到异常。如果您使用 PRINT，则在 .NET 中不会出现异常。

如果您可以编辑引发错误代码，这将导致 C# 中的 SqlException：

RAISERROR('Some error message', 16, 1)

然后您可以获取 SqlException.Errors 集合中的每个单独错误。

顺便说一句 - 如果您之后不直接 RETURN，SQL Server 将在 RAISERROR 之后继续运行命令。如果不返回，可能会返回多个错误。

Answer 2

.NET 确实会引发错误消息...如果严重性为 16 或更高（因为它引发异常） - 该消息将位于异常 .Message 中。如果您使用严重性较低的 RAISERROR（或使用 PRINT），则必须订阅 连接上的 InfoMessage 事件。

Answer 3

只有高严重性错误才会在 ExecuteNonQuery 中被抛出。我用 OdbcCommand.ExecuteNonQuery() 方法观察到了另一种情况。对于 SqlCommand.ExecuteNonQuery() 来说可能也是如此。如果 CommandText 属性中包含的 SQL 是单个语句（例如：INSERT INTO table (col1,col2) VALUES (2,'ABC'); ）并且上述语句中存在外键冲突或主键冲突 ExecuteNonQuery会抛出异常。但是，如果您的 CommandText 是一个批处理，其中有多个由分号分隔的 SQL 语句（例如多个插入或更新），并且其中一个失败，则 ExecuteNonQuery 不会抛出异常。您需要显式检查该方法返回的受影响的记录数。简单地将代码放入 try{}Catch{} 中是没有帮助的。

Answer 4

受到 M Hassan、Stefan Steiger 和 Mark Gravell 在本主题中的工作的启发，以下是此处发生的情况的最小概念验证示例：

private static void DoSql()
{
    // Errors of severity level of 10 or less 
    // will NOT bubble up to .Net as an Exception to be caught in the usual way
    const string sql = @"RAISERROR('A test error message of low severity', 10, 1)";

    using (SqlConnection conn = new SqlConnection(myConnString))
    {
        conn.Open();

        // Hook up my listener to the connection message generator
        conn.InfoMessage += new SqlInfoMessageEventHandler(MySqlMessageHandler);

        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
            // code happily carries on to this point
            // despite the sql Level 10 error that happened above
        }
    }
}


private static void MySqlMessageHandler(object sender, SqlInfoMessageEventArgs e)
{
    // This gets all the messages generated during the execution of the SQL, 
    // including low-severity error messages.
    foreach (SqlError err in e.Errors)
    {
        // TODO: Something smarter than this for handling the messages
        MessageBox.Show(err.Message);
    }
}

Answer 5

我发现这在 Oracle ODP.Net 的 WCF 服务中非常适合我 -

            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (OracleException oex)
            {
                string errmsg = oex.Message;
                Logger.Instance.WriteLog(@"Some error --> " + errmsg);
                throw new Exception(errmsg);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cleanup...
            }

Answer 6

使用此代码，当命令完成时，它将返回错误：

SqlCommand.EndExecuteNonQuery(result) 

这是我的完整类代码：

Imports System.Data.SqlClient
Imports System.DirectoryServices.ActiveDirectory

Class clsExecuteAsync


    Public Event EnProceso(Identificador As Integer, Mensaje As String)
    Public Event Finalizado(IDentificador As Integer, Mensaje As String)
    Public Event CancelarProcesoEnEjecucion(Identificador As Integer, ByRef Cancel As Boolean)



    Dim Cancelar As Boolean

    Sub CancelarProceso()
        Cancelar = True
    End Sub


    Function test() As Boolean
        ' This is a simple example that demonstrates the usage of the
        ' BeginExecuteNonQuery functionality.
        ' The WAITFOR statement simply adds enough time to prove the
        ' asynchronous nature of the command.
        Dim commandText As String = "UPDATE Production.Product SET ReorderPoint = ReorderPoint + 1 " & "WHERE ReorderPoint Is Not Null;" & "WAITFOR DELAY '0:0:3';" & "UPDATE Production.Product SET ReorderPoint = ReorderPoint - 1 " & "WHERE ReorderPoint Is Not Null"
        Return (RunCommandAsynchronously(0, commandText, GetConnectionString()))
        Console.WriteLine("Press ENTER to continue.")
        Console.ReadLine()

    End Function

    Function ExecuteAsync(Identificador As Integer, Sql As String, Optional CadenaConexion As String = "") As String
        If CadenaConexion = "" Then
            CadenaConexion = clsIni.LeeIni("Provider")
        End If

        Return RunCommandAsynchronously(Identificador, Sql, CadenaConexion)

    End Function

    Function RunCommandAsynchronously(Identificador As Integer, commandText As String, connectionString As String) As String
        ' Given command text and connection string, asynchronously execute
        ' the specified command against the connection. For this example,
        ' the code displays an indicator as it is working, verifying the
        ' asynchronous behavior.
        Dim Resultado As String = ""
        Try

            Dim connection As SqlConnection
            Dim SqlCommand As SqlCommand

            connection = New SqlConnection(connectionString)
            Dim count As Integer = 0

            'testint to catch the error, but not run for me
            AddHandler connection.InfoMessage, AddressOf ErrorEnConexion


            SqlCommand = New SqlCommand(commandText, connection)
            connection.Open()
            Dim result As IAsyncResult = SqlCommand.BeginExecuteNonQuery()
            While Not result.IsCompleted
                Console.WriteLine("Waiting ({0})", count = count + 1)
                ' Wait for 1/10 second, so the counter
                ' does not consume all available resources
                ' on the main thread.
                System.Threading.Thread.Sleep(100)
                RaiseEvent EnProceso(Identificador, commandText)
                Application.DoEvents()

                If Cancelar Then
                    Cancelar = False

                    'cancelar 
                    Dim Cancel As Boolean = False
                    RaiseEvent CancelarProcesoEnEjecucion(Identificador, Cancel)

                    If Cancel = False Then
                        Resultado = "Cancelado"
                        GoTo SALIR
                    End If

                End If

            End While

            'Console.WriteLine("Command complete. Affected {0} rows.", Command.EndExecuteNonQuery(Result))

            '   MsgBox("El comando se ejecutó. " & SqlCommand.EndExecuteNonQuery(result), MsgBoxStyle.Information)

            'detect error: this code lunch and error: Cath with try cacth code
            SqlCommand.EndExecuteNonQuery(result)

            RaiseEvent Finalizado(Identificador, SqlCommand.EndExecuteNonQuery(result))

            Resultado = "OK"

        Catch ex As SqlException
            Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
            Resultado = ex.Message


        Catch ex As InvalidOperationException
            Console.WriteLine("Error: {0}", ex.Message)
            Resultado = ex.Message
        Catch ex As Exception
            ' You might want to pass these errors
            ' back out to the caller.
            Console.WriteLine("Error: {0}", ex.Message)
            Resultado = ex.Message
        End Try


SALIR:
        Return Resultado

    End Function

    Private Sub ErrorEnConexion(sender As Object, e As SqlInfoMessageEventArgs)
        MsgBox(e.Message)

    End Sub


    Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,
        ' you can retrieve it from a configuration file.
        ' If you have not included "Asynchronous Processing=true" in the
        ' connection string, the command is not able
        ' to execute asynchronously.
        Return "Data Source=(local);Integrated Security=SSPI;" & "Initial Catalog=AdventureWorks; Asynchronous Processing=true"

    End Function


End Class

Answer 7

尝试下面的方法。

PS：仅仅因为您使用事务，并不意味着您可以忽略处理异常和回滚。

 public static void MessageEventHandler( object sender, SqlInfoMessageEventArgs e ) {
         foreach( SqlError error in e.Errors ) {
            Console.WriteLine("problem with sql: "+error);
            throw new Exception("problem with sql: "+error);
         }
      }
      public static int executeSQLUpdate(string database, string command) {
         SqlConnection connection = null;
         SqlCommand sqlcommand = null;
         int rows = -1;
         try {
            connection = getConnection(database);
            connection.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );
            sqlcommand = connection.CreateCommand();
            sqlcommand.CommandText = command;
            connection.Open();
            rows = sqlcommand.ExecuteNonQuery();
          } catch(Exception e) {
            Console.Write("executeSQLUpdate: problem with command:"+command+"e="+e);
            Console.Out.Flush();
            throw new Exception("executeSQLUpdate: problem with command:"+command,e);
         } finally {
            if(connection != null) { connection.Close(); }
         } 
         return rows;
      }

这是正确的事务处理：

//public static void ExecuteInTransaction(Subtext.Scripting.SqlScriptRunner srScriptRunner)
        public override void ExecuteInTransaction(string strSQL)
        {

            System.Data.Odbc.OdbcTransaction trnTransaction = null;

            try
            {


                System.Threading.Monitor.Enter(m_SqlConnection);
                if (isDataBaseConnectionOpen() == false)
                    OpenSQLConnection();

                trnTransaction = m_SqlConnection.BeginTransaction();

                try
                {
                    /*
                    foreach (Subtext.Scripting.Script scThisScript in srScriptRunner.ScriptCollection)
                    {
                        System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(scThisScript.ScriptText, m_sqlConnection, trnTransaction);
                        cmd.ExecuteNonQuery();
                    }
                    */

                    // pfff, mono C# compiler problem...
                    // System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strSQL, m_SqlConnection, trnTransaction);
                    System.Data.Odbc.OdbcCommand cmd = this.m_SqlConnection.CreateCommand();
                    cmd.CommandText = strSQL;

                    cmd.ExecuteNonQuery();

                    trnTransaction.Commit();
                } // End Try
                catch (System.Data.Odbc.OdbcException exSQLerror)
                {
                    Log(strSQL);
                    Log(exSQLerror.Message);
                    Log(exSQLerror.StackTrace);
                    trnTransaction.Rollback();
                } // End Catch
            } // End Try
            catch (Exception ex)
            {
                Log(strSQL);
                Log(ex.Message);
                Log(ex.StackTrace);
            } // End Catch
            finally
            {
                strSQL = null;
                if(m_SqlConnection.State != System.Data.ConnectionState.Closed)
                    m_SqlConnection.Close();
                System.Threading.Monitor.Exit(m_SqlConnection);
            } // End Finally


        } // End Sub ExecuteInTransaction

Answer 8

您可以使用 try/catch 捕获 SqlException

 try
  {
       //.......
    Command.ExecuteNonQuery();      
   }
    catch (SqlException ex)
     {   
       log (SqlExceptionMessage(ex).ToString());
     }

以下方法 Catch 可以记录或向用户显示 SqlException 的详细信息

  public StringBuilder SqlExceptionMessage(SqlException ex)
    {
        StringBuilder sqlErrorMessages = new StringBuilder("Sql Exception:\n");

        foreach (SqlError error in ex.Errors)
        {
            sqlErrorMessages.AppendFormat("Mesage: {0}\n", error.Message)
                .AppendFormat("Severity level: {0}\n", error.Class)
                .AppendFormat("State: {0}\n", error.State)
                .AppendFormat("Number: {0}\n", error.Number)
                .AppendFormat("Procedure: {0}\n", error.Procedure)
                .AppendFormat("Source: {0}\n", error.Source)
                .AppendFormat("LineNumber: {0}\n", error.LineNumber)
                .AppendFormat("Server: {0}\n", error.Server)
                .AppendLine(new string('-',error.Message.Length+7));

        }
        return sqlErrorMessages;
    }

生成的消息如下所示：

 Sql Exception:
 Mesage: Error converting data type nvarchar to datetime.
 Severity level: 16
 State: 5
 Number: 8114
 Procedure: Sales by Year
 Source: .Net SqlClient Data Provider
 LineNumber: 0
 Server: myserver
 -------------------------------------------------------