Comment puis-je obtenir un message d'erreur qui se produit lors de l'utilisation de ExecuteNonQuery ()?

.NET posent effectivement un message d'erreur... si la gravité est de 16 ans ou au-dessus (car il déclenche une exception) - le message sera à l'exception .Message. Si vous utilisez RAISERROR avec une baisse de la gravité (ou à l'aide de PRINT), alors vous devrez vous abonner à la InfoMessage événement sur la connexion.

Essayer de le ci-dessous.

PS: Juste parce que vous utilisez une transaction, vous ne pouvez pas négliger la gestion des exceptions et des restaurations.

 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;
      }

Et c'est la bonne gestion des transactions:

//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

Inspiré par le travail de M Hassan, Stefan Steiger, et Marc Gravel dans ce thread, ici, c'est un minimum de preuve de concept exemple de ce qui se passe ici:

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);
    }
}