How can I get an error message that happens when using ExecuteNonQuery()?

Only high severity errors will be thrown back in ExecuteNonQuery. There is another scenario that I have observed with OdbcCommand.ExecuteNonQuery() method. May be this is true for SqlCommand.ExecuteNonQuery() as well. If the SQL contained in the CommandText property is a single statement (Example: INSERT INTO table (col1,col2) VALUES (2,'ABC'); ) and if there is a foreign key violation or primary key violation in the above statement ExecuteNonQuery will throw an exception. However, if your CommandText is a batch where you have more than one SQL Statements seperated by semi colon (Like Several INSERTS or UPDATES) and if one of them fails ExecuteNonQuery does not throw an exception back. You need to be explicitly checking for the number of records affected returned by the method. Simply putting the code in a try{}Catch{} wont help.

Inspired by the work of M Hassan, Stefan Steiger, and Mark Gravell in this thread, here is a minimum proof-of-concept example of what is going on here:

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

You'll only get an exception in C# if your error's severity is 16 or above. If you are using a PRINT, you won't get an exception in .NET.

If you can edit the raise error code, this would cause a SqlException in C#:

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

You can then get to each individual error in the SqlException.Errors collection.

Just a side-note - SQL Server will continue to run commands after the RAISERROR if you don't RETURN directly afterwards. If you don't return, you can get multiple errors back.

.NET does indeed raise an error message... if the severity is 16 or above (since it throws an exception) - the message will be in the exception .Message. If you are using RAISERROR with a lower severity (or using PRINT) then you will have to subscribe to the InfoMessage event on the connection.